Pandas extra

01-12-2020 / Edit on Github

In this note, a general dataframe is called df (type pandas.core.frame.DataFrame), a general series is call s (type pandas.core.series.Series).

Import library #

import pandas as pd
import numpy as np # import numpy if necessary

Read/Write .csv file #

# READ
df = pd.read_csv('filename.csv', sep=';') # default sep=','

# if 1st col contains 0,1,2,...
df = pd.read_csv('filename.csv', index_col=1)

# with datetime info
df = pd.read_csv(PATH_DATA_FOLDER+"raw_data.csv",
parse_dates=['timestamp'],
infer_datetime_format=True,
cache_dates=True)
# WRITE
df.to_csv(path, index=False) # don't incldue index

Create a dataframe #

# FROM A LIST
pd.DataFrame(a_list, colummns=['col_name'])
# FROM A DICTIONARY
names = ['John', 'Thi', 'Bi', 'Beo', 'Chang']
ages = [10, 20, 21, 18, 11]
marks = [8, 9, 10, 6, 8]
city = ['Ben Tre', 'Paris', 'Ho Chi Minh Ville', 'New York', 'DC']

my_dict = {'Name':names, 'Ages':ages, 'Marks':marks, 'Place': city}
students = pd.DataFrame(my_dict)
NameAgesMarksPlace
0John108Ben Tre
1Thi209Paris
2Bi2110Ho Chi Minh Ville
3Beo186New York
4Chang118DC

Adding #

# a column
df['new_col] = [new_values]
# a row
df.loc['new_index'] = [new_value]
# add a new col based on another's values
df_im = df0.copy()[['col']]
df_im['status'] = df0['col'].apply(lambda row: 1 if row>=80 else 0)

Shuffle rows #

# shuffle all rows and reset the index
df_new = df.sample(frac=1).reset_index(drop=True)

Sorting #

df.sort_values(by='col1', ascending=False)

Select rows/columns/item(s) #

👉 Indexing and selecting data — pandas 1.1.2 documentation

Select Single value #

Select a single value (with condition): Get the mark of Thi (9).

# interchange `.values[0]` and `.iloc[0]`
df[df.Name=='Thi'].Marks.values[0]
df.loc[df.Name=='Thi', 'Marks'].values[0]
# with indexes
df.iloc[1,2] # row 2, column 3
# column's name with row's index
df[['Marks']].iloc[1].values[0] # column 'Marks', row 2
# column's index with row's value
df[df.Name=='Thi'].iloc[:,2].values[0] # column 3, row of 'Thi'

Select integer rows and named columns #

df.loc[1:5, 'col']

Select columns #

Select a column (returns a Series)

# with column's name
df['Name']
df.loc[:, 'Name']
# with an index
df.iloc[:,0]

Returns a pd.DataFrame,

df[['Name']]
df.loc[:, ['Name']]
# with an index
df.iloc[:,[0]]

Select multi-columns (type DataFrame): Get columns Name & Place:

# using columns's names
df[['Name', 'Place']]
df.loc[:, ['Name', 'Place']]
# using indexes
df.iloc[:, [0,-1]]

Select rows #

Select a row (returns a Series)

# with an index
df.iloc[1]
# with a condition
df[df['Name']=='Thi'] # DataFrame
df[df['Name']=='Thi'].iloc[0] # Series
df[df.Name=='Thi'] # DataFrame
df[df.Name=='Thi'].iloc[0] # Series
df[df.Name=='Thi'].values[0] # ndarray

Select multi-rows (type DataFrame)

# using indexes
df.iloc[:3]
df.loc[:2]
# with conditions
df[df['A'].isin([3, 6])]

MultiIndex #

👉 MultiIndex / advanced indexing — pandas 1.1.2 documentation

All multiindex #

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo'], ['one', 'two', 'one', 'two', 'one', 'two']]
index = pd.MultiIndex.from_arrays(arrays)
df = pd.DataFrame(np.random.randn(3, 6), index=['A', 'B', 'C'], columns=index)
barbazfoo
onetwoonetwoonetwo
A-0.7523330.4905810.7746290.4871851.7677730.028956
B-0.057864-0.221516-0.568726-0.5637321.362453-0.563213
C-0.338319-0.3465900.0128450.7554551.260937-0.038209

Selection,

df.loc['A', ('baz', 'two')]
0.487185
df.loc[:,('baz', 'two')]
A    0.487185
B   -0.563732
C    0.755455
Name: (baz, two), dtype: float64

With a single name column #

If there are some column with single name,

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo'], [i for i in range(2)]*3]
index = pd.MultiIndex.from_arrays(arrays)
df1 = pd.DataFrame(np.random.randn(3, 6), index=['A', 'B', 'C'], columns=index)

Good practice #

# GOOD PRACTICE
df1['time'] = [1,2,3]
df_rs2 = df1
barbazfootime
010101
A-1.386119-0.4967551.4828550.943795-1.173290-0.4453651
B-0.900710-1.5710091.0869641.546927-1.5644260.6227632
C0.7122310.235247-0.8070310.6718020.5971490.1113323

Selection,

# FOR GOOD PRACTICE
df_rs2.loc['A', ('baz', 1)]
df_rs2.loc['A', 'baz']
0.943795
0 1.482855
1 0.943795

Bad practice #

# BAD PRACTICE
df2 = pd.DataFrame([1,2,3], index=['A', 'B', 'C'], columns=['time'])
df_rs1 = pd.concat([df1, df2], axis=1)
(bar, 0)(bar, 1)(baz, 0)(baz, 1)(foo, 0)(foo, 1)time
A-1.386119-0.4967551.4828550.943795-1.173290-0.4453651
B-0.900710-1.5710091.0869641.546927-1.5644260.6227632
C0.7122310.235247-0.8070310.6718020.5971490.1113323

Selection,

# FOR BAD PRACTICE
df.loc['A', [('baz', 0)]]
df_rs1.loc['A', [('baz', i) for i in [0,1]]]
(baz, 0)    0.729023
(baz, 0) 1.482855
(baz, 1) 0.943795

Rename multiindex #

# all columns' name at the level 1
df.columns.set_levels(['b1','c1','f1'], level=1, inplace=True)

Drop multiindex #

df.columns = df.columns.droplevel()
   a
b c b c
0 1 2 -> 0 1 2
1 3 4 1 3 4

Compare 2 dataframes #

df1.equals(df2)

True / False #

# Invert True/False value in Series
s = pd.Series([True, True, False, True])
~s
# Convert True / False to 1 / 0
df['col'] = df['col'].astype(int)
# int or float