Using merge_asof() to study stocks
You have a feed of stock market prices that you record. You attempt to track the price every five minutes. Still, due to some network latency, the prices you record are roughly every 5 minutes. You pull your price logs for three banks, JP Morgan (JPM), Wells Fargo (WFC), and Bank Of America (BAC). You want to know how the price change of the two other banks compare to JP Morgan. Therefore, you will need to merge these three logs into one table. Afterward, you will use the pandas
.diff()
method to compute the price change over time. Finally, plot the price changes so you can review your analysis.
The three log files have been loaded for you as tables named jpm
, wells
, and bac
.
This exercise is part of the course
Joining Data with pandas
Exercise instructions
- Use
merge_asof()
to mergejpm
(left table) andwells
together on thedate_time
column, where the rows with the nearest times are matched, and withsuffixes=('', '_wells')
. Save tojpm_wells
. - Use
merge_asof()
to mergejpm_wells
(left table) andbac
together on thedate_time
column, where the rows with the closest times are matched, and withsuffixes=('_jpm', '_bac')
. Save tojpm_wells_bac
. - Plot the close prices of
close_jpm
,close_wells
, andclose_bac
fromprice_diffs
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Use merge_asof() to merge jpm and wells
jpm_wells = ____
# Use merge_asof() to merge jpm_wells and bac
jpm_wells_bac = ____
# Compute price diff
price_diffs = jpm_wells_bac.diff()
# Plot the price diff of the close of jpm, wells and bac only
price_diffs.plot(y=[____, ____, ____])
plt.show()