====== Pandas Cheat Sheet ======

https://pandas.pydata.org/

https://pypi.org/project/pandas/

===== Import =====

<code python>
import pandas as pd
</code>

===== Series =====

Example inputs:

<code python>
# list:
a = [1, 7, 2]  

# dictionary:
kv = {"day1": 420, "day2": 380, "day3": 390}
</code>

Simple series, no labels:

<code python>
myseries1 = pd.Series(a)

print(myseries1)
</code>

<code>
0    1
1    7
2    2
dtype: int64
</code>

Series with labels:

<code python>
myseries2 = pd.Series(a, index=["x","y","z"])

print(myseries2)
</code>

<code>
x    1
y    7
z    2
dtype: int64
</code>

Key-value as series:

<code python>
mykvseries = pd.Series(kv)

print(mykvseries)
</code>

<code>
day1    420
day2    380
day3    390
dtype: int64
</code>

Subset of key-value input:

<code python>
mykvseries_filtered = pd.Series(kv, index = ["day1","day2"])

print(mykvseries_filtered)
</code>

<code>
day1    420
day2    380
dtype: int64
</code>

===== Dataframes =====

Input:

<code python>
mydataset = {
    'cars': ["BMW", "Volvo", "Ford"],
    'passings': [3, 7, 2]
}
</code>

Load into a dataframe:

<code python>
mydataframe = pd.DataFrame(mydataset)

print(mydataframe)
</code>

<code>
    cars  passings
0    BMW         3
1  Volvo         7
2   Ford         2
</code>

===== Load from a File =====

CSV:

<code python>
df = pd.read_csv('data.csv')
</code>

JSON:

<code python>
df = pd.read_json('data.json')
</code>

===== Simple Analysis =====

First 10 rows:

<code python>
print(df.head(10))
</code>

Last 5 rows (default):

<code python>
print(df.tail())
</code>

Dataset info:

<code python>
print(df.info())
</code>

The result tells us the following:

  * Row count and column count
  * The name of each column, with the data type
  * How many non-null values there are present in each column

===== Clean Empty Cells =====

Drop empty cells, placing the results in a new dataframe:

<code python>
new_df = df.dropna()
</code>

Drop empty cells, modifying the original dataframe:

<code python>
df.dropna(inplace = True)
</code>

Replace empty cells with a default value (''130'' in this example):

<code python>
# WARNING: This affects all columns!

df.fillna(130, inplace = True)
</code>

Replace with a default value in a specific column:

<code python>
df.fillna({"Calories": 130}, inplace=True)
</code>

Replace using the **mean**:

<code python>
# Mean is the average value (the sum of all values divided by number of values).

x = df["Calories"].mean()

df.fillna({"Calories": x}, inplace=True)
</code>

Replace using the **median**:

<code python>
# Median is the value in the middle, after you have sorted all values ascending.

x = df["Calories"].median()

df.fillna({"Calories": x}, inplace=True)
</code>

Replace using the **mode**:

<code python>
# Mode is the value that appears most frequently.

x = df["Calories"].mode()[0]

df.fillna({"Calories": x}, inplace=True)
</code>

===== Clean Wrong Format =====

This example assumes that we have values that are not in a consistent format, but that can still be converted to a date:

<code python>
df['Date'] = pd.to_datetime(df['Date'], format='mixed')
</code>

But, there may be some that can't be converted at all.  They will end up with **NaT** (not a time) values. We can remove them with this:

<code python>
df.dropna(subset=['Date'], inplace = True)
</code>

===== Clean Wrong Data =====

Sometimes, data is just wrong, e.g., typos.

For simple fixes, we can update the row directly:

<code python>
# Assign a value of 45 to the Duration column in row 7:

df.loc[7, 'Duration'] = 45
</code>

For large data sets, use rules-based updating:

<code python>
# For each row with a Duration value larger than 120, assign a new value of 120:

for x in df.index:
    if df.loc[x, "Duration"] > 120:
        df.loc[x, "Duration"] = 120
</code>

Remove bad rows altogether:

<code python>
# For each row with a Duration value larger than 120, drop the row:

for x in df.index:
    if df.loc[x, "Duration"] > 120:
        df.drop(x, inplace = True)
</code>

===== Remove Duplicates =====

Find duplicates:

<code python>
print(df.duplicated())
</code>

Remove them:

<code python>
df.drop_duplicates(inplace = True) 
</code>

===== Correlation =====

The ''corr()'' method calculates the relationship between each column in a data set.  The closer to 1 a correlation value is, the more closely related the columns are.

A positive correlation means values are likely to move together, e.g., if one goes up, the other probably will too.  A negative correlation shows the opposite, e.g., if one goes up, the other is likely to go down.

<code python>
df.corr()
</code>

Example output:

^ ^ Duration ^ Pulse ^ Maxpulse ^ Calories ^
| Duration | 1.000000 | -0.155408 | 0.009403 | 0.922717 |
| Pulse | -0.155408 | 1.000000 | 0.786535 | 0.025121 |
| Maxpulse | 0.009403 | 0.786535 | 1.000000 | 0.203813 |
| Calories | 0.922717 | 0.025121 | 0.203813 | 1.000000 |

===== Plotting =====

Import ''matplotlib'':

<code python>
import matplotlib.pyplot as plt
</code>

Line plot (default):

<code python>
df.plot()

plt.show()
</code>

Scatter plot:

<code python>
# You can use .corr() to check for strong correlation and determine good
# argument candidates for a scatter plot.

df.corr()
</code>

<code python>
df.plot(kind = 'scatter', x = 'Duration', y = 'Calories')

plt.show()
</code>

Histogram:

<code python>
df["Duration"].plot(kind = 'hist')
</code>

{{tag>python}}