Pandas Cheat Sheet for Quick reference

Syntax for Creating DataFrames

Specify values for each column.

df = pd.DataFrame ({“a” : [4 ,5, 6],  “b” : [7, 8, 9], “c” : [10, 11, 12] }, 
index = [1, 2, 3])

Specify values for each row.
df = pd.DataFrame ([[4, 7, 10], [5, 8, 11], [6, 9, 12]],
index=[1, 2, 3],
columns=[‘a’, ‘b’, ‘c’])

Create DataFrame with a MultiIndex

df = pd.DataFrame( {“a” : [4 ,5, 6], “b” : [7, 8, 9], “c” : [10, 11, 12]},
index = pd.MultiIndex.from_tuples( [(‘d’,1),(‘d’,2),(‘e’,2)], names=[‘n’,’v’]))

Gather columns into rows.
pd.melt(df)

Spread rows into columns.
df.pivot(columns=’var’, values=’val’)

Append rows of DataFrames
pd.concat([df1,df2])

Append columns of DataFrames
pd.concat([df1,df2], axis=1)

Order rows by values of a column (low to high).
df.sort_values(‘mpg’)

df.sort_values(‘mpg’,ascending=False)
Order rows by values of a column (high to low).

Rename the columns of a DataFrame
df.rename(columns = {‘y’:’year’})

Sort the index of a DataFrame
df.sort_index()

Reset index of DataFrame to row numbers, moving index to columns.
df.reset_index()

Drop columns from DataFrame
df.drop(columns=[‘Length’,’Height’])

Gather columns into rows.
pd.melt(df)

Spread rows into columns.
df.pivot(columns=’var’, values=’val’)

Append rows of DataFrames
pd.concat([df1,df2])

Append columns of DataFrames
pd.concat([df1,df2], axis=1)

Extract rows that meet logical criteria.
df[df.Length > 7]

Remove duplicate rows (onlyconsiders columns).
df.drop_duplicates()

Select first n rows.
df.head(n)

Select last n rows.
df.tail(n)

Randomly select fraction of rows.
df.sample(frac=0.5)

Randomly select n rows.
df.sample(n=10)

Select rows by position.
df.iloc[10:20]

Select and order top n entries.
df.nlargest(n, ‘value’)

Select and order bottom n entries.
df.nsmallest(n, ‘value’)

Most pandas methods return a DataFrame so that another pandas method can be applied to the result. This improves readability of code.
df = (pd.melt(df).rename(columns={‘variable’ : ‘var’,’value’ : ‘val’}).query(‘val >= 200’))

Select multiple columns with specific names.
df[[‘width’,’length’,’species’]]

Select single column with specific name.
df[‘width’] or df.width

Select columns whose name matches regular expression regex.
df.filter(regex=’regex’)

Select all columns between x2 and x4 (inclusive).
df.loc[:,’x2′:’x4′]

Select columns in positions 1, 2 and 5 (first column is 0).
df.iloc[:,[1,2,5]]

Select rows meeting logical condition, and only the specific columns
df.loc[df[‘a’] > 10, [‘a’,’c’]]

Count number of rows with each unique value of variable
df[‘w’].value_counts()

# of rows in DataFrame.
len(df)

# of distinct values in a column.
df[‘w’].nunique()

Basic descriptive statistics for each column (or GroupBy)
df.describe()

Drop rows with any column having NA/null data.
df.dropna()

Replace all NA/null data with value.
df.fillna(value)

Compute and append one or more new columns.
df.assign(Area=lambda df: df.Length*df.Height)

Add single column.
df[‘Volume’] = df.Length*df.Height*df.Depth

Bin column into n buckets.
pd.qcut(df.col, n, labels=False)

pandas provides a large set of summary functions that operate on different kinds of pandas objects (DataFrame columns, Series, GroupBy, Expanding and Rolling (see below)) and produce single values for each of the groups. When applied to a DataFrame, the result is returned as a pandas Series for each column
Sum values of each object: sum()

Count non-NA/null values of each object : count()

Median value of each object. : median()

Quantiles of each object. : quantile([0.25,0.75])

Apply function to each object.: apply(function)

Minimum value in each object. : min()

Maximum value in each object. : max()

Mean value of each object. : mean()

Variance of each object. : var()

Standard deviation of each object : std()

Compute and append one or more new columns. : df.assign(Area=lambda df: df.Length*df.Height)

Add single column. : df[‘Volume’] = df.Length*df.Height*df.Depth

Bin column into n buckets. : pd.qcut(df.col, n, labels=False)

pandas provides a large set of vector functions that operate on all columns of a DataFrame or a single selected column (a pandas Series). These functions produce vectors of values for each of the columns, or a single Series for the individual Series.

Element-wise max. ” max(axis=1)

Trim values at input thresholds : clip(lower=-10,upper=10)

Element-wise min.: min(axis=1)

Absolute value : abs()

Return a GroupBy object, grouped by values in column named “col”.
df.groupby(by=”col”)

Return a GroupBy object, grouped by values in index level named “ind”.
df.groupby(level=”ind”)

All of the summary functions listed above can be applied to a group. Additional GroupBy functions:

Size of each group. : size()

Aggregate group using function : agg(function)

Return an Expanding object allowing summary functions to be applied cumulatively.
df.expanding()

Return a Rolling object allowing summary functions to be applied to windows of length n.
df.rolling(n)

Histogram for each column : df.plot.hist()

Scatter chart using pairs of points : df.plot.scatter(x=’w’,y=’h’)

Join matching rows from bdf to adf.
pd.merge(adf, bdf,how=’left’, on=’x1′)

Join matching rows from adf to bdf.
pd.merge(adf, bdf, how=’right’, on=’x1′)

Join data. Retain all values, all rows.
pd.merge(adf, bdf,how=’outer’, on=’x1′)

All rows in adf that have a match in bdf.
adf[adf.x1.isin(bdf.x1)]

All rows in adf that do not have a match in bdf.
adf[~adf.x1.isin(bdf.x1)]

Rows that appear in both ydf and zdf (Intersection).
pd.merge(ydf, zdf)

Rows that appear in either or both ydf and zdf (Union).
pd.merge(ydf, zdf, how=’outer’)

Rows that appear in ydf but not zdf (Setdiff).
pd.merge(ydf, zdf, how=’outer’, indicator=True) .query(‘_merge == “left_only”‘) .drop(columns=[‘_merge’])