Merge data frames based on date

Question

I want to merge 2 data frames.

The first data frame looks like:

Date    Value
1968-04-01  38.0
1968-04-02  37.6
1968-04-03  37.7
1968-04-04  36.7
1968-04-05  37.2
1968-04-08  37.0
1968-04-09  37.25
1968-04-10  37.6

The second data frame looks like:

1991-06-21  4.44
1991-06-22  4.39
1991-06-24  4.39
1991-06-25  4.37
1991-06-26  4.41
1991-06-27  4.36

Both data sets go up to present day.

How do I create a new dataframe that starts the data in 1991 since that is where the second data set starts? And has a column for each price.

I tried:

df_all_rows = pd.concat([df1, df2])

but this just puts one set of data below the other one.

I guess currently the data does not go up to present day. But how can I just create a new dataframe with only dates that both series have?

Here is what I have:

import numpy as np
import pandas as pd
import pickle
import quandl
from datetime import datetime

df1=quandl.get("BUNDESBANK/BBK01_WT5511", authtoken="6F92X3NEV8DdrhAc_d5_")
df2=quandl.get("PERTH/SLVR_USD_D", authtoken="6F92X3NEV8DdrhAc_d5_")
df2=df2.dropna()

T1 = pd.merge(df1, df2, on=df1.index, how='inner')

Is there a specific issue? Please see [ask], [help/on-topic]. — AMC, Jun 30 '20 at 00:30
You outlined the goal or objective, not a specific problem or obstacle. — AMC, Jun 30 '20 at 00:35
do you want all the data from both dataframes or just the ones with the same date? — Shiraz Chokshi, Jun 30 '20 at 00:38
Just updated my question with the code I have right now, but as you can see, it does not work — Jackson, Jun 30 '20 at 00:41

Shiraz Chokshi · Accepted Answer · 2020-06-30T00:43:36.903

1

try:

import pandas as pd
merge=pd.merge(df1,df2, how='inner', left_index=True, right_index=True)

you can change the way you merge with the how parameter. See for more information on joining here

edited Jun 30 '20 at 00:43

answered Jun 30 '20 at 00:36

Shiraz Chokshi

36
11

I tried this, changing T1 and T2 to df1 and df2 so it matches what I had, and it gave me the error: KeyError: DatetimeIndex(['1968-04-01', '1968-04-02', '1968-04-03', '1968-04-04', '1968-04-05', '1968-04-08', '1968-04-09', '1968-04-10', '1968-04-11', '1968-04-16', ... '2016-04-05', '2016-04-06', '2016-04-07', '2016-04-08', '2016-04-11', '2016-04-12', '2016-04-13', '2016-04-14', '2016-04-15', '2016-04-18'], dtype='datetime64[ns]', name='Date', length=12152, freq=None) – Jackson Jun 30 '20 at 00:39
hm, try setting both the indexes in the merge by: `merge=pd.merge(df1,df2, how='inner', left_index=True, right_index=True)` – Shiraz Chokshi Jun 30 '20 at 00:41
I will try this, I just added my code to the question so you can see what is going on – Jackson Jun 30 '20 at 00:42

Merge data frames based on date

1 Answers1