Home/Coding Guide/Pandas Cheatsheet
⌂ Main Page
Coding Interview Preparation Guide

Pandas Cheatsheet

A rapid-fire pandas reference: indexing, reshaping, aggregation, and idioms for data-heavy interviews.

Challenges on this page

Advanced Pandas

More advanced: https://towardsdatascience.com/learn-advanced-features-for-pythons-main-data-analysis-library-in-20-minutes-d0eedd90d086

Python
import pandas as pd
Python
df = pd.DataFrame({ 'genus_overall': ['avian', 'canine', 'cephalothorax', 'pisces'],
                    'rating_overall': [1.2, 3.4, 5.2, 7.8 ],
                    'num_legs_1178': [2, 4, 8, 0],
                    'num_wings': [2, 0, 0, 0],
                    'num_specimen': [10, 2, 1, 8],
                   
                  },
                  index=['falcon', 'dog', 'spider', 'fish'])
df
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen
falcon          avian             1.2              2          2            10
dog            canine             3.4              4          0             2
spider  cephalothorax             5.2              8          0             1
fish           pisces             7.8              0          0             8
Python
df.dtypes.value_counts()
Output
int64      3
object     1
float64    1
dtype: int64
Python
# SELECT COLS BY TYPE
df.select_dtypes(include=['int'])
Output
        num_legs_1178  num_wings  num_specimen
falcon              2          2            10
dog                 4          0             2
spider              8          0             1
fish                0          0             8
Python
# SELECT COLS BY NAME
df.filter(like='num')
Output
        num_legs_1178  num_wings  num_specimen
falcon              2          2            10
dog                 4          0             2
spider              8          0             1
fish                0          0             8
Python
# SELECT COLS BY NAME
df.filter(like='overall')
Output
        genus_overall  rating_overall
falcon          avian             1.2
dog            canine             3.4
spider  cephalothorax             5.2
fish           pisces             7.8
Python
# SELECT COLS BY REGEX FOR COL NAME
df.filter(regex='\d')
Output
        num_legs_1178
falcon              2
dog                 4
spider              8
fish                0

This is equivalent to: df[ df['num_legs_1178'] > 2]:

Python
df.query('num_legs_1178 > 2')
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen
dog            canine             3.4              4          0             2
spider  cephalothorax             5.2              8          0             1

Rank

Rank col values in descend or ascend order
Method - How rank group of records
that have the same value (ties): * average: average rank of group * min: lowest rank in group * max: highest rank in group * first: in order ranks appear * dense: = ‘min’, but rank increases by 1

Ascending=False => max value has rank 1

Python
df['rank'] = df["num_legs_1178"].rank( method="first",  
                                       ascending=False ).astype("int")
df
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen  \
falcon          avian             1.2              2          2            10   
dog            canine             3.4              4          0             2   
spider  cephalothorax             5.2              8          0             1   
fish           pisces             7.8              0          0             8   

        rank  
falcon     3  
dog        2  
spider     1  
fish       4

Select rows and columns

Rows & cols = scalars, lists, slice obj, bools
Simultaneous selection of rows & cols - rows left, cols right of ","

Python
# SELECT ROW WITH POSITION 1 (SECOND ROW)
df.iloc[1]
Output
genus_overall     canine
rating_overall       3.4
num_legs_1178          4
num_wings              0
num_specimen           2
Name: dog, dtype: object
Python
# SELECT ROW WITH INDEX=DOG
df.loc['dog']
Output
genus_overall     canine
rating_overall       3.4
num_legs_1178          4
num_wings              0
num_specimen           2
Name: dog, dtype: object
Python
# ALL ROWS + COLS 1 & 4
df.iloc[:,[1,4]]
Output
        rating_overall  num_specimen
falcon             1.2            10
dog                3.4             2
spider             5.2             1
fish               7.8             8
Python
# SAME WITH LOC
df.loc[:,['rating_overall','num_specimen']]
Output
        rating_overall  num_specimen
falcon             1.2            10
dog                3.4             2
spider             5.2             1
fish               7.8             8
Python
df
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen
falcon          avian             1.2              2          2            10
dog            canine             3.4              4          0             2
spider  cephalothorax             5.2              8          0             1
fish           pisces             7.8              0          0             8
Python
# SLICING - LAST ELEMENT EXCLUSIVE
df.iloc[1:4, 1:4]
Output
        rating_overall  num_legs_1178  num_wings
dog                3.4              4          0
spider             5.2              8          0
fish               7.8              0          0
Python
df.iloc[1:4, 3:0:-1]
Output
        num_wings  num_legs_1178  rating_overall
dog             0              4             3.4
spider          0              8             5.2
fish            0              0             7.8
Python
# SLICING - LAST ELEMENT INCLUSIVE
df.loc['dog':'fish', 'rating_overall':'num_wings']
Output
        rating_overall  num_legs_1178  num_wings
dog                3.4              4          0
spider             5.2              8          0
fish               7.8              0          0
Python
positions = [0, 2, 3]
df.iloc[positions]
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen
falcon          avian             1.2              2          2            10
spider  cephalothorax             5.2              8          0             1
fish           pisces             7.8              0          0             8
Python
idxs = ['falcon', 'spider', 'fish']
df.loc[idxs]
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen
falcon          avian             1.2              2          2            10
spider  cephalothorax             5.2              8          0             1
fish           pisces             7.8              0          0             8
Python
df.iloc[1:4]                       # LIKE LISTS
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen
dog            canine             3.4              4          0             2
spider  cephalothorax             5.2              8          0             1
fish           pisces             7.8              0          0             8
Python
df.loc['dog':'fish']
Output
        genus_overall  rating_overall  num_legs_1178  num_wings  num_specimen
dog            canine             3.4              4          0             2
spider  cephalothorax             5.2              8          0             1
fish           pisces             7.8              0          0             8
Python
rows = [0, 2, 3]
cols = [2, 3, 4]
df.iloc[rows, cols]
Output
        num_legs_1178  num_wings  num_specimen
falcon              2          2            10
spider              8          0             1
fish                0          0             8
Python
rows = ['falcon', 'dog',]
cols = ['num_legs_1178', 'num_wings', 'num_specimen']
df.loc[rows, cols]
Output
        num_legs_1178  num_wings  num_specimen
falcon              2          2            10
dog                 4          0             2

Smmary of the above:

Python
# BY POSITION (2ND ROW)
df.iloc[1]
positions = [0, 2, 3]
df.iloc[positions]
# SLICING - EXCLUSIVE (as lists)
df.iloc[1:4, 1:4]

# BY INDEX
df.loc['dog']
idxs=['falcon','spider','fish']
df.loc[idxs]
# SLICING - INCLUSIVE
df.loc['dog':'fish','height':'weight']

# ALL ROWS+COLS 1&4
df.iloc[:,[1,4]]
rows = [0, 2, 3]
cols = [2, 3, 4]
df.iloc[rows, cols]

# SAME W/LOC
df.loc[:,['rating','spec']]
rows = ['falcon', 'dog',]
cols = ['legs', 'wings', 'spec']
df.loc[rows, cols]

Select rows and columns using "get_loc" and "index" methods

Python
start = df.columns.get_loc('num_legs_1178')
end   = df.columns.get_loc('num_specimen')
df.iloc[:4, start:end+1]
Output
        num_legs_1178  num_wings  num_specimen
falcon              2          2            10
dog                 4          0             2
spider              8          0             1
fish                0          0             8
Python
start = df.index.get_loc('dog')
end   = df.index.get_loc('fish')
df.iloc[start:end+1, 2:5]
Output
        num_legs_1178  num_wings  num_specimen
dog                 4          0             2
spider              8          0             1
fish                0          0             8
Python
start = df.index[2]
end   = df.index[3]
df.loc[start:end, ['num_legs_1178', 'num_wings']]
Output
        num_legs_1178  num_wings
spider              8          0
fish                0          0

Set value for particular cell

Python
# https://stackoverflow.com/questions/13842088/set-value-for-particular-cell-in-pandas-dataframe-using-index
df.at['C', 'a'] = 10                          # BY INDEX
df.iat[5,10] = 2                              # BY POSITION


df.set_value('C', 'x', 10)                    # DEPRICATED
df.ix['x','C'] = 10                           # DEPRICATED
df.xs('C')['x'] = 10                          # MODIFIES NEW DF RETURNED BY xs(), NOT THE EXISTING ONE
df['x']['C'] = 10                             # AVOID CHAINED INDEXING NOT TO OPERATE ON COPIES/VIEWS (UNPREDICTABLE)
Python
# THIS IS COOL WHEN MORE DIVERSE DATA IS AVAILABLE
pd.crosstab(
    df["genus_overall"], 
    df["rating_overall"],
    margins=True, 
    normalize=0
)
Output
rating_overall   1.2   3.4   5.2   7.8
genus_overall                         
avian           1.00  0.00  0.00  0.00
canine          0.00  1.00  0.00  0.00
cephalothorax   0.00  0.00  1.00  0.00
pisces          0.00  0.00  0.00  1.00
All             0.25  0.25  0.25  0.25
Python
df.groupby("genus_overall")["rating_overall"].mean()
Output
genus_overall
avian            1.2
canine           3.4
cephalothorax    5.2
pisces           7.8
Name: rating_overall, dtype: float64
Python
df.groupby("genus_overall")["rating_overall"].std()
Output
genus_overall
avian           NaN
canine          NaN
cephalothorax   NaN
pisces          NaN
Name: rating_overall, dtype: float64

Add these features

Python
merged_df = pd.merge(df1, df2, on=['Name', 'Job'])
Python
# reshape w/new cols + aggr.
pivot_df = pd.pivot_table(
    df,
    index='Name',
    columns=['Pets', 'Stores'],
    values='Weight'
    aggfunc='mean')
Python
# Unpivot a DataFrame from wide to long format
melted_df = pd.melt(df, id_vars=['Name', 'Gender'], value_vars=['Pets', 'Weight'])
Python
df['col_name'].rolling(2).sum()
df['col_name'].rolling(2, min_periods=1).sum()
df['col_name'].rolling(2).mean()
Python
df['col_name'].shift(periods=2, [fill_value=0]).sum()
Python
df.to_json(file_name)
df = pd.read_json(file_name)
Python
df = pd.read_html()
Python
df = pd.read_clipboard()
Python
# SQLite
import sqlite3

query = 'SELECT * FROM dune_table'
conn  = sqlite3.connect('dune.db')
dune_df = pd.read_sql(query, conn)
dune_df.to_sql('dune_table', conn, index=False)