user6749426 - 23 days ago 10

Python Question

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
```