user6749426 user6749426 - 23 days ago 10
Python Question

Python pandas map multilevel column heading df to another df

what I am trying to do is use the map function to tie back a pivot table to another dataframe. A1:A4 are columns that denote a different location. So the value in the pivot table represents the # of instances at A1 or A3, etc.

I have one dataframe and one pivot table. The dataframe (df1) looks like this:

SubscriberKey Inst A1 A2 A3 A4
'abc' 2 0 0 0 0
'bcd' 4 0 0 0 0
'cde' 1 0 0 0 0
'def' 0 0 0 0 0
'efg' 0 0 0 0 0


My pivot table (pt1) looks like this. It also has a multilevel column heading due to this being a pivot table. To clarify what I mean by multilevel column heading is that a single column heading outputs ('Instance', 'A1'). Anyways below is a snapshot of my pivot table (pt1):

Subscriber Key Instance Instance Instance Instance
Linkname A1 A2 A3 A4
'abc' 2 0 2 0
'bcd' 4 1 1 2
'cde' 1 1 0 0


I would like to somehow populate my df's A1:A4 columns based on what is in the pivot. The df has more subscriber keys than the pivot table contains and so the row counts are not the same.

The output would look like:

SubscriberKey Inst A1 A2 A3 A4
'abc' 2 0 0 2 0
'bcd' 4 0 1 1 2
'cde' 1 1 0 0 0
'def' 0 0 0 0 0
'efg' 0 0 0 0 0


Any help is appreciated, thanks! I have already tried df1.update(pt1[column]) and it returns 'Type Error: expected tuple, got str'

Answer Source

Update should work. Try this:

df1 = df1.set_index('SubscriberKey')
df1


Output:             
             Inst   A1  A2  A3  A4
SubscriberKey                   
'abc'          1    0   0   0   0
'bcd'          2    0   0   0   0
'cde'          1    0   0   0   0
'def'          3    0   0   0   0
'efg'          0    0   0   0   0

df2 = df2[1:]
df2.columns = ["SubscriberKey","A1","A2","A3","A4"]
df2 = df2.set_index('SubscriberKey')



Output:
              A1    A2  A3  A4
SubscriberKey               
'abc'          1    0   2   0
'bcd'          0    1   1   2
'cde'          1    1   0   0

Then doing:

df1.update(df2)
df1

which gives:

              Inst  A1  A2  A3  A4
SubscriberKey                   
'abc'           1   1   0   2   0
'bcd'           2   0   1   1   2
'cde'           1   1   1   0   0
'def'           3   0   0   0   0
'efg'           0   0   0   0   0