Merging datasets
Analyzing Police Activity with pandas
Kevin Markham
Founder, Data School
apple
date time price
date_and_time
2018-02-14 09:30:00 2/14/18 9:30 163.04
2018-02-14 16:00:00 2/14/18 16:00 167.37
2018-02-15 09:30:00 2/15/18 9:30 169.79
2018-02-15 16:00:00 2/15/18 16:00 172.99
apple.reset_index(inplace=True)apple
date_and_time date time price
0 2018-02-14 09:30:00 2/14/18 9:30 163.04
1 2018-02-14 16:00:00 2/14/18 16:00 167.37
2 2018-02-15 09:30:00 2/15/18 9:30 169.79
3 2018-02-15 16:00:00 2/15/18 16:00 172.99
Preparing the second DataFrame
high_low
DATE HIGH LOW
0 2/14/18 167.54 162.88
1 2/15/18 173.09 169.00
2 2/16/18 174.82 171.77
high = high_low[['DATE', 'HIGH']]high
DATE HIGH
0 2/14/18 167.54
1 2/15/18 173.09
2 2/16/18 174.82
Merging the DataFrames
apple_high = pd.merge(left=apple, right=high,
left_on='date', right_on='DATE',
how='left')
left=apple: Left DataFrameright=high: Right DataFrameleft_on='date': Key column in left DataFrameright_on='DATE': Key column in right DataFramehow='left': Type of join
apple_high
date_and_time date time price DATE HIGH
0 2018-02-14 09:30:00 2/14/18 9:30 163.04 2/14/18 167.54
1 2018-02-14 16:00:00 2/14/18 16:00 167.37 2/14/18 167.54
2 2018-02-15 09:30:00 2/15/18 9:30 169.79 2/15/18 173.09
3 2018-02-15 16:00:00 2/15/18 16:00 172.99 2/15/18 173.09
apple
date_and_time date time price
0 2018-02-14 09:30:00 2/14/18 9:30 163.04
1 2018-02-14 16:00:00 2/14/18 16:00 167.37
2 2018-02-15 09:30:00 2/15/18 9:30 169.79
3 2018-02-15 16:00:00 2/15/18 16:00 172.99
high
DATE HIGH
0 2/14/18 167.54
1 2/15/18 173.09
2 2/16/18 174.82
Setting the index
apple_high.set_index('date_and_time', inplace=True)apple_high
date time price DATE HIGH
date_and_time
2018-02-14 09:30:00 2/14/18 9:30 163.04 2/14/18 167.54
2018-02-14 16:00:00 2/14/18 16:00 167.37 2/14/18 167.54
2018-02-15 09:30:00 2/15/18 9:30 169.79 2/15/18 173.09
2018-02-15 16:00:00 2/15/18 16:00 172.99 2/15/18 173.09
Let's practice!
Analyzing Police Activity with pandas
