Pandas

Pandas#

import pandas as pd

print(f"Pandas version: {pd.__version__}")

Pandas version: 2.2.3

DataFrame Creation#

There are a ton of ways to create a DataFrame. For example:

pd.read_csv(‘name.csv’)
pd.read_excel(‘name.xlsx’)
pd.read_html(url)
pd.read_json(path)
pd.read_sql(query, connection)

For a simple example below, I just create it manually with a dict object.

employee_data = {
    "id": [1, 2, 3, 4, 5],
    "name": ["Joe", "Jim", "Henry", "Sam", "Max"],
    "salary": [70000, 90000, 80000, 60000, 90000],
    "departmentId": [1, 1, 2, 2, 1]
}

employee_df = pd.DataFrame(employee_data)
employee_df['bonus'] = employee_df['salary'] * 0.012345
employee_df

	id	name	salary	departmentId	bonus
0	1	Joe	70000	1	864.15
1	2	Jim	90000	1	1111.05
2	3	Henry	80000	2	987.60
3	4	Sam	60000	2	740.70
4	5	Max	90000	1	1111.05

You can save/read from your clipboard as well!

employee_df.to_clipboard()  
pd.read_clipboard().head(1)

	id	name	salary	departmentId	bonus
0	1	Joe	70000	1	864.15

Options#

Options allow you to control many aspects. My favorites are the display options, but a full list can be found here: Available options

print("Display Rows:", pd.get_option("display.max_rows"))
print("Display Columns:", pd.get_option("display.max_columns"))
print("Display Precision:", pd.get_option("display.precision"))
employee_df

Display Rows: 60
Display Columns: 20
Display Precision: 6

	id	name	salary	departmentId	bonus
0	1	Joe	70000	1	864.15
1	2	Jim	90000	1	1111.05
2	3	Henry	80000	2	987.60
3	4	Sam	60000	2	740.70
4	5	Max	90000	1	1111.05

pd.set_option("display.max_rows", 2)
pd.set_option("display.max_columns", 2)
pd.set_option("display.precision", 1)
pd.set_option("display.float_format", lambda x: "%.6f" % x)
employee_df

	id	...	bonus
0	1	...	864.150000
...	...	...	...
4	5	...	1111.050000

5 rows × 5 columns

Display options can be reset like so:

pd.reset_option("^display")

Or set temporarily using with pd.option_context()

with pd.option_context('display.float_format', lambda x: '%.6f' % x):
    display(employee_df)

	id	name	salary	departmentId	bonus
0	1	Joe	70000	1	864.150000
1	2	Jim	90000	1	1111.050000
2	3	Henry	80000	2	987.600000
3	4	Sam	60000	2	740.700000
4	5	Max	90000	1	1111.050000

Data Explore#

print("df.shape \t",  employee_df.shape )
print("df.index \t",  employee_df.index )
print("C.columns \t", employee_df.columns)

display(employee_df.describe())

df.shape 	 (5, 5)
df.index 	 RangeIndex(start=0, stop=5, step=1)
C.columns 	 Index(['id', 'name', 'salary', 'departmentId', 'bonus'], dtype='object')

	id	salary	departmentId	bonus
count	5.000000	5.00000	5.000000	5.000000
mean	3.000000	78000.00000	1.400000	962.910000
std	1.581139	13038.40481	0.547723	160.959107
min	1.000000	60000.00000	1.000000	740.700000
25%	2.000000	70000.00000	1.000000	864.150000
50%	3.000000	80000.00000	1.000000	987.600000
75%	4.000000	90000.00000	2.000000	1111.050000
max	5.000000	90000.00000	2.000000	1111.050000

employee_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 5 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   id            5 non-null      int64  
 1   name          5 non-null      object 
 2   salary        5 non-null      int64  
 3   departmentId  5 non-null      int64  
 4   bonus         5 non-null      float64
dtypes: float64(1), int64(3), object(1)
memory usage: 332.0+ bytes

employee_df.memory_usage(deep=True)

Index           132
id               40
name            302
salary           40
departmentId     40
bonus            40
dtype: int64

employee_df.departmentId.value_counts().to_frame()

	count
departmentId
1	3
2	2

display(employee_df.head(1))
display(employee_df.tail(1))
display(employee_df.sample(3))

	id	name	salary	departmentId	bonus
0	1	Joe	70000	1	864.15

	id	name	salary	departmentId	bonus
4	5	Max	90000	1	1111.05

	id	name	salary	departmentId	bonus
1	2	Jim	90000	1	1111.05
0	1	Joe	70000	1	864.15
3	4	Sam	60000	2	740.70

Iterate#

Never modify something you are iterating over. It returns a copy and dtypes are not preserved across the rows.

Items()#

Iterates over the DataFrame columns, returning a tuple with the column name and the content as a Series.

Itertuples()#

Generally faster than items() and returns row data as a tuple.

for column_name, series in employee_df.items():
    print("column_name:", column_name)
    print(series)
    break

for index, item in employee_df['name'].items():
    print(index, item)

for row in employee_df.itertuples():
    print(row)

print("\nindex=False __________________________")    
for row in employee_df.itertuples(index=False):
    print(row)

print("\nname='t' __________________________")
for row in employee_df.itertuples(name='t'):
    print(row)

column_name: id
0    1
1    2
2    3
3    4
4    5
Name: id, dtype: int64
0 Joe
1 Jim
2 Henry
3 Sam
4 Max
Pandas(Index=0, id=1, name='Joe', salary=70000, departmentId=1, bonus=864.15)
Pandas(Index=1, id=2, name='Jim', salary=90000, departmentId=1, bonus=1111.05)
Pandas(Index=2, id=3, name='Henry', salary=80000, departmentId=2, bonus=987.6)
Pandas(Index=3, id=4, name='Sam', salary=60000, departmentId=2, bonus=740.7)
Pandas(Index=4, id=5, name='Max', salary=90000, departmentId=1, bonus=1111.05)

index=False __________________________
Pandas(id=1, name='Joe', salary=70000, departmentId=1, bonus=864.15)
Pandas(id=2, name='Jim', salary=90000, departmentId=1, bonus=1111.05)
Pandas(id=3, name='Henry', salary=80000, departmentId=2, bonus=987.6)
Pandas(id=4, name='Sam', salary=60000, departmentId=2, bonus=740.7)
Pandas(id=5, name='Max', salary=90000, departmentId=1, bonus=1111.05)

name='t' __________________________
t(Index=0, id=1, name='Joe', salary=70000, departmentId=1, bonus=864.15)
t(Index=1, id=2, name='Jim', salary=90000, departmentId=1, bonus=1111.05)
t(Index=2, id=3, name='Henry', salary=80000, departmentId=2, bonus=987.6)
t(Index=3, id=4, name='Sam', salary=60000, departmentId=2, bonus=740.7)
t(Index=4, id=5, name='Max', salary=90000, departmentId=1, bonus=1111.05)

Join / Merge / Concate#

Concate #

df1 = pd.DataFrame([['a', 1], ['b', 2]], columns=['letter', 'number'])
#       letter  number
# 0      a       1
# 1      b       2
df2 = pd.DataFrame([['c', 3], ['d', 4]], columns=['letter', 'number'])
#       letter  number
# 0      c       3
# 1      d       4
df3 = pd.concat([df1, df2])
df3 

	letter	number
0	a	1
1	b	2
0	c	3
1	d	4

Merge #

Inner-join (A, B) (default): Keep only rows of A and B where the on-keys match in both.
Outer-join (A, B): Keep all rows of both frames, but merge rows when the on-keys match. For non-matches, fill in missing values with not-a-number (NaN) values.
Left-join (A, B): Keep all rows of A. Only merge rows of B whose on-keys match A.
Right-join (A, B): Keep all rows of B. Only merge rows of A whose on-keys match B.

You can use merge’s how=... parameter, which takes the (string) values, 'inner’, 'outer', 'left', and 'right'. Here are some examples of these types of joins.

mergeA = pd.DataFrame({'a': ['a1', 'a2'], 
                       'b': ['b1', 'b2']})
mergeB = pd.DataFrame({'a': ['a1', 'a2','a3'], 
                       'c': ['c1', 'c2','c3']})
print("\n=== Inner-join ===")
display(mergeA.merge(mergeB, on=['a']))
print("\n=== Outer-join ===")
display(mergeA.merge(mergeB, on=['a'], how='outer'))
print("\n=== Left-join ===")
display(mergeA.merge(mergeB, on=['a'], how='left'))
print("\n=== Right-join ===")
display(mergeA.merge(mergeB, on=['a'], how='right'))


mergeC = pd.DataFrame({'d': ['a1', 'a2','a3'], 
                       'c': ['c1', 'c2','c3']})
pd.merge(mergeA, mergeC, left_on=['a'], right_on=['d'])

=== Inner-join ===

	a	b	c
0	a1	b1	c1
1	a2	b2	c2

=== Outer-join ===

	a	b	c
0	a1	b1	c1
1	a2	b2	c2
2	a3	NaN	c3

=== Left-join ===

	a	b	c
0	a1	b1	c1
1	a2	b2	c2

=== Right-join ===

	a	b	c
0	a1	b1	c1
1	a2	b2	c2
2	a3	NaN	c3

	a	b	d	c
0	a1	b1	a1	c1
1	a2	b2	a2	c2

Groupby / Aggregate #

employee_df.groupby('departmentId').size().reset_index().rename(columns={0:'count'})

	departmentId	count
0	1	3
1	2	2

employee_df.groupby('departmentId')['salary'].agg(['min', 'max', 'mean', 'median'])

	min	max	mean	median
departmentId
1	70000	90000	83333.333333	90000.0
2	60000	80000	70000.000000	70000.0

Filtering#

df.filter()

filter_df = pd.DataFrame([[1, 2, 3], [4, 5, 6]],
                  index=['mouse', 'rabbit'],
                  columns=['one', 'two', 'three'])

display(filter_df.filter(items=['one']))           # by column
display(filter_df.filter(items=['mouse'], axis=0)) # by row
display(filter_df.filter(like='o'))                # non-exact match
display(filter_df.filter(regex='e$'))              # using regular expression (e$ matches to strings that end in e)

	one
mouse	1
rabbit	4

	one	two	three
mouse	1	2	3

	one	two
mouse	1	2
rabbit	4	5

	one	three
mouse	1	3
rabbit	4	6

df.all() Returns True if all elements are True.
df.any() Returns True if any elements are True.

print("All salaries in employee_df are less than 80000:", (employee_df['salary'] < 80000).all(),"\n") 
print("Any salaries in employee_df are less than 80000:", (employee_df['salary'] < 80000).any(),"\n")

All salaries in employee_df are less than 80000: False 

Any salaries in employee_df are less than 80000: True 

isna() / isnull() Detect missing values
notna() / notnull() Detect non-missing values

df.drop()

df.drop_duplicates()

dropdf = employee_df.copy()

print('Drop indexes: \ndropdf.drop([0,1]')
dropdf = dropdf.drop([0,1]) 
display(dropdf)

print('dropdf.drop(["'"bonus"'"], axis=1)')
dropdf = dropdf.drop(['bonus'], axis=1) 
display(dropdf)

print('del dropdf["'"departmentId"'"]')
del dropdf['departmentId']
display(dropdf)

employee_df.drop_duplicates(subset='departmentId')

Drop indexes: 
dropdf.drop([0,1]

	id	name	salary	departmentId	bonus
2	3	Henry	80000	2	987.60
3	4	Sam	60000	2	740.70
4	5	Max	90000	1	1111.05

dropdf.drop(["bonus"], axis=1)

	id	name	salary	departmentId
2	3	Henry	80000	2
3	4	Sam	60000	2
4	5	Max	90000	1

del dropdf["departmentId"]

	id	name	salary
2	3	Henry	80000
3	4	Sam	60000
4	5	Max	90000

	id	name	salary	departmentId	bonus
0	1	Joe	70000	1	864.15
2	3	Henry	80000	2	987.60

df.isin()

employee_df[employee_df['name'].isin(['Joe', 'Henry'])]

	id	name	salary	departmentId	bonus
0	1	Joe	70000	1	864.15
2	3	Henry	80000	2	987.60

iloc#

Primarily integer position based (from 0 to length-1 of the axis), but may also be used with a boolean array.

employee_df.iloc[1:2]

	id	name	salary	departmentId	bonus
1	2	Jim	90000	1	1111.05

loc#

Label based, but may also be used with a boolean array.

employee_df.loc[:, employee_df.columns != 'departmentId']

	id	name	salary	bonus
0	1	Joe	70000	864.15
1	2	Jim	90000	1111.05
2	3	Henry	80000	987.60
3	4	Sam	60000	740.70
4	5	Max	90000	1111.05

Data Types#

astype

dtype

employee_df.dtypes.reset_index().rename(columns={'index':'column', 0:'dtype'})

	column	dtype
0	id	int64
1	name	object
2	salary	int64
3	departmentId	int64
4	bonus	float64

Datetime #

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html?highlight=to_datetime

date_df = pd.DataFrame({
    'year': [2015, 2016],
    'month': [2, 3],
    'day': [4, 5]
})
date_df['datetime'] = pd.to_datetime(date_df)
date_df

	year	month	day	datetime
0	2015	2	4	2015-02-04
1	2016	3	5	2016-03-05

date_df['Year'] = date_df.datetime.dt.year
date_df['Month'] = date_df.datetime.dt.month
date_df['Day'] = date_df.datetime.dt.day
date_df['Day Name'] = date_df.datetime.dt.day_name()
date_df['Month Name'] = date_df.datetime.dt.month_name()
date_df

	year	month	day	datetime	Year	Month	Day	Day Name	Month Name
0	2015	2	4	2015-02-04	2015	2	4	Wednesday	February
1	2016	3	5	2016-03-05	2016	3	5	Saturday	March

date_df[date_df['datetime'] > pd.Timestamp('2015-12-12')]

	year	month	day	datetime	Year	Month	Day	Day Name	Month Name
1	2016	3	5	2016-03-05	2016	3	5	Saturday	March

date_df[date_df['datetime'].between('2015-01-01', '2015-07-01')]

	year	month	day	datetime	Year	Month	Day	Day Name	Month Name
0	2015	2	4	2015-02-04	2015	2	4	Wednesday	February

Apply #

employee_df['above_average_salary'] = (
    employee_df['salary'].apply(lambda x: x > employee_df['salary'].mean())
)
employee_df

	id	name	salary	departmentId	bonus	above_average_salary
0	1	Joe	70000	1	864.15	False
1	2	Jim	90000	1	1111.05	True
2	3	Henry	80000	2	987.60	True
3	4	Sam	60000	2	740.70	False
4	5	Max	90000	1	1111.05	True

Date Features#

`pd.offsets`#

date = pd.Timestamp('2023-01-06')  # Friday
new_date = date + pd.offsets.DateOffset(months=3) # Timestamp('2023-04-06')

next_day = date + pd.offsets.Day() # 2023-01-07  # Saturday
next_business_day = date + pd.offsets.BusinessDay() # 2023-01-09  # Monday

next_week = date + pd.offsets.Week(weekday=0)  # 2023-01-09 Move to next Monday (weekday must be 0<=day<=6)

month_start = date + pd.offsets.MonthBegin() # 2023-02-01
month_end = date + pd.offsets.MonthEnd() # 2023-01-31'

year_start = date + pd.offsets.YearBegin() # 2024-01-01
year_end = date + pd.offsets.YearEnd() # 2023-12-31

Holidays#

Panda’s holiday methods live here and are a great jumping off point: pandas-dev/pandas

from pandas.tseries.holiday import Holiday, AbstractHolidayCalendar, MO, nearest_workday

class CanadaHolidayCalendar(AbstractHolidayCalendar):
    '''https://www.statutoryholidays.com/'''
    rules = [
        Holiday("New Year's Day", month=1, day=1, observance=nearest_workday),
        Holiday("Family Day", month=2, day=1, offset=pd.DateOffset(weekday=MO(3))),
        pd.tseries.holiday.GoodFriday,
        pd.tseries.holiday.EasterMonday,
        Holiday('Victoria Day', month=5, day=25, offset=pd.DateOffset(weekday=MO(-1))),
        Holiday('Saint-Jean-Baptiste Day', month=6, day=24),  # Quebec only
        Holiday('Canada Day', month=7, day=1, observance=nearest_workday),
        Holiday('Civic Holiday', month=8, day=5),  # Excluding Quebec
        Holiday('Labour Day', month=9, day=1, offset=pd.DateOffset(weekday=MO(1))),
        Holiday('National Day for Truth and Reconciliation', month=9, day=30),
        Holiday('Thanksgiving Day', month=10, day=1, offset=pd.DateOffset(weekday=MO(2))),
        Holiday('Remembrance Day', month=11, day=11, observance=nearest_workday),
        Holiday('Christmas Day', month=12, day=25),
        Holiday('Boxing Day', month=12, day=26),
    ]

ca_holidays = CanadaHolidayCalendar().holidays(start='2024-01-01', end='2024-12-31', return_name=True)
ca_holiday_df = pd.DataFrame(ca_holidays, columns=['Holiday'])
ca_holiday_df

	Holiday
2024-01-01	New Year's Day
2024-02-19	Family Day
2024-03-29	Good Friday
2024-04-01	Easter Monday
2024-05-20	Victoria Day
2024-06-24	Saint-Jean-Baptiste Day
2024-07-01	Canada Day
2024-08-05	Civic Holiday
2024-09-02	Labour Day
2024-09-30	National Day for Truth and Reconciliation
2024-10-14	Thanksgiving Day
2024-11-11	Remembrance Day
2024-12-25	Christmas Day
2024-12-26	Boxing Day

Reduce Memory Usage#

Credit to Matthias for this from his second place submission in the m5 forecasting competition.

import numpy as np 

def reduce_mem_usage(df, verbose=True):
    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']
    start_mem = df.memory_usage().sum() / 1024**2    
    for col in df.columns:
        col_type = df[col].dtypes
        if col_type in numerics:
            c_min = df[col].min()
            c_max = df[col].max()
            if str(col_type)[:3] == 'int':
                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:
                    df[col] = df[col].astype(np.int8)
                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:
                       df[col] = df[col].astype(np.int16)
                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:
                    df[col] = df[col].astype(np.int32)
                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:
                    df[col] = df[col].astype(np.int64)  
            else:
                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:
                    df[col] = df[col].astype(np.float16)
                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:
                    df[col] = df[col].astype(np.float32)
                else:
                    df[col] = df[col].astype(np.float64)    
    end_mem = df.memory_usage().sum() / 1024**2
    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))
    return df