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

Question

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

jamoreiras · Accepted Answer · 2020-07-20 04:04:18Z

1

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

edited Jul 20, 2020 at 4:04

jamoreiras

3432 silver badges14 bronze badges

answered Jul 20, 2020 at 3:27

ALollz

59.7k7 gold badges73 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related