CODEWITHSUNDEEP
pythonpandasdataframemerge
NoNameMLer
NoNameMLer

Reputation: 21

Python Pandas merge on row index and column index across 2 dataframes

Am trying to do something where I calculate a new dataframe which is dataframe1 divided by dataframe2 where columnname match and date index matches bases on closest date nonexact match)

idx1 = pd.DatetimeIndex(['2017-01-01','2018-01-01','2019-01-01'])
idx2 = pd.DatetimeIndex(['2017-02-01','2018-03-01','2019-04-01'])
df1 = pd.DataFrame(index = idx1,data = {'XYZ': [10, 20, 30],'ABC': [15, 25, 30]})
df2 = pd.DataFrame(index = idx2,data = {'XYZ': [1, 2, 3],'ABC': [3, 5, 6]})

#looking for some code
#df3 = df1/df2 on matching column and closest matching row

This should produce a dataframe which looks like this

           XYZ  ABC
2017-01-01  10  5
2018-01-01  10  5
2019-01-01  10  5

Upvotes: 1

Views: 175

Answers (1)

ALollz
ALollz

Reputation: 59519

You can use an asof merge to do a match on a "close" row. Then we'll group over the columns axis and divide.

df3 = pd.merge_asof(df1, df2, left_index=True, right_index=True,
                    direction='nearest')
#            XYZ_x  ABC_x  XYZ_y  ABC_y
#2017-01-01     10     15      1      3
#2018-01-01     20     25      2      5
#2019-01-01     30     30      3      6

df3 = (df3.groupby(df3.columns.str.split('_').str[0], axis=1)
           .apply(lambda x: x.iloc[:, 0]/x.iloc[:, 1]))
#            ABC   XYZ
#2017-01-01  5.0  10.0
#2018-01-01  5.0  10.0
#2019-01-01  5.0  10.0

Upvotes: 1

Related Questions