🐼

Pandas Cheatsheet

KEY IMPORTS
We’ll use shorthand in this Import these to start
cheat sheet import pandas as pd
import numpy as np
df - A pandas DataFrame

object
s - A pandas Series object

IMPORTING DATA
If file you are importing is in different directory so in place of filename, write path
of your file.

CODE WORKING

pd.read_csv(filename) From a CSV file

pd.read_table(filename) From a delimited text file (like TSV)

pd.read_excel(filename) From an Excel file

pd.read_sql(query, connection_object) Reads from a SQL table/database

pd.read_json(json_string) Reads from a JSON formatted string, URL or file.

Pandas Cheatsheet 1
 CODE WORKING

From a dict, # keys for columns names, # values for

pd.DataFrame(dict)
data as lists.

EXPORTING DATA
CODE WORKING

df.to_csv(filename) Writes to a CSV file

df.to_excel(filename) Writes to an Excel file

df.to_sql(table_name,
Writes to a SQL table
connection_object)

df.to_json(filename) Writes to a file in JSON format

VIEWING/INSPECTING DATA
CODE WORKING

df.head(n) First n rows of the DataFrame

df.tail(n) Last n rows of the DataFrame

df.shape Number of rows and columns

df.info( ) Index, Datatype and Memory information

df.describe( ) Summary statistics for numerical columns

s.value_counts(dropna=False) Views unique values and counts

df.apply(pd.Series.value_counts) Unique values and counts for all columns

SELECTION
CODE WORKING

df[col] Returns column with label col as series

df[[col1, col2]] Returns Columns as a new DataFrame

s.iloc[0] Selection by position

s.loc[0] Selection by index

df.iloc[0, :] First row

df.iloc[0, 0] First element of first column

Pandas Cheatsheet 2
 DATA CLEANING
CODE WORKING

df.columns = ['a', 'b', 'c'] Renames columns

pd.isnull() Checks for null Values, Returns Boolean Array

pd.notnull() Opposite of s.isnull()

pd.dropna() Drops all rows that contain null values

df.dropna(axis=1) Drops all columns that contain null values.

Drop all columns that have fewer than n non-NaN

df.dropna(thresh=n)
values

df.fillna(x) Replaces all null values with x

Replaces all null values with the mean(mean can

s.fillna(s.mean()) be replaced with almost any function from the
statistics section)

s.astype(float) Converts the datatype of the series to float

s.replace(1, 'one') Replaces all values equal to 1 with ‘one’

s.replace([1, 3], ['one', 'three']) Replaces all 1 with ‘one’and 3 with ‘three’

df.rename(columns=lambda x: x + 1) Mass renaming of columns

df.rename(columns={'old_name':
Selective renaming
'new_name'})
df.set_index('column_one') Changes the index

df.rename(index = lambda x: x + 1) Mass renaming of index

FILTER, SORT, & GROUPBY

CODE WORKING

df[df[col] > 5] Rows where the col column is greater than 5

df[(df[col] > 5) & (df[col] < 7)] Rows where 7 > col > 5

df.sort_values(col1) Sorts values by col1 in ascending order

df.sort_values(col2, ascending = False) Sorts values by col2 in descending order

df.sort_values([col1, col2], ascending = Sorts values by col1 in ascending order then

[True, False]) col2 in descending order.
Returns a groupby object for values from one
df.groupby(col)
columns

Pandas Cheatsheet 3
 CODE WORKING

Returns a groupby object values from multiple

df.groupby([col1, col2])
columns

Returns the mean of the values in col2, grouped

df.groupby(col1)[col2].mean( ) by the values in col1 (mean can be replaced with
almost any function from the statistics section)

Finds the average across all columns for every

df.groupby(col1).agg(np.mean)
unique column 1 group

df.apply(np.mean) Applies a function across each column

df.apply(np.max, axis = 1) Applies a function across each row.

JOIN/COMBINE
CODE WORKING

Adds the rows in df1 to the end of df2(columns

df1.append(df2)
should be identical)

Adds the columns in df1 to the end of df2 (rows

pd.concat([df1, df2], axis=1)
should be identical)

STATISTICS
CODE WORKING

df.describe( ) Summary statistics for numerical columns

df.mean( ) Returns the mean of all columns

Returns the correlation between columns in a

df.corr( )
DataFrame

Returns the number of non-null values in each

df.count( )
DataFrame column

df.max( ) Return the highest value in each column

df.min( ) Returns the lowest value in each column

df.median( ) Returns the median of each column

df.std( ) Returns the standard deviation of each column

Pandas Cheatsheet 4