Combining data from two Pandas dataframes with different axes?

user_16973222 0
Viewed 73

I have one dataframe where the columns contain monthly data:

ID Jan Feb Mar so on...
1 valJ1 valF1 valM1 ...
2 valJ2 valF2 valM2 ...

and another dataframe where the month is given in a column (where 1 = January, 2 = March, ...):

Month ID data1 data2
1 1 num num
2 1 num num
3 1 num num
4 1 num num
1 2 num num
2 2 num num
3 2 num num
4 2 num num

How can I write a function to combine this data such that:

Month ID data1 data2 DF1
1 1 num num valJ1
2 1 num num valF1
3 1 num num valM1
4 1 num num valA1
1 2 num num valJ2
2 3 num num valF2
3 2 num num valM2
4 2 num num valA2

I've applied for one dataframe but I'm not sure how to combine 2

months = ['Jan', 'Feb', 'Mar', 'Apr',...]

def fun(df2,months):
    if df2['Month'] == 1:
        val = df1[months[0]]
    if df2['Month'] == 2:
        val = df1[months[1]]
    #and so on
    return val

df2['DF1'] = pd.DataFrame(df2.apply(fun, axis=1)
pandas dataframe python
1 Answers

Use melt to transpose your first dataframe into the shape of the second one the merge them. Before you just need a dictionary to map a month number to the abbreviated month name:

months = dict(zip(range(1, 4), ['Jan', 'Feb', 'Mar']))

out = pd.merge(df2.assign(Month=df2['Month'].map(months)),
               df1.melt(id_vars='ID', var_name='Month', value_name='DF1'), 
               on=['Month', 'ID'])

Output:

>>> out
  Month  ID data1 data2    DF1
0   Jan   1   num   num  valJ1
1   Feb   1   num   num  valF1
2   Mar   1   num   num  valM1
3   Jan   2   num   num  valJ2
4   Feb   2   num   num  valF2
5   Mar   2   num   num  valM2
user_152399510
Related