CODEWITHSUNDEEP
pythonpandas
equanimity
equanimity

Reputation: 2533

Pandas: key error when passing margins=True in pivot_table

I'm getting a key error when passing margins=True in a Pandas pivot_table. Here is some sample data:

import pandas as pd
import numpy as np

df = pd.DataFrame({'System_Key':['MER-002', 'MER-003', 'MER-004', 'MER-005', 'BAV-378', 'BAV-379', 'BAV-380', 'BAV-381', 'AUD-220', 'AUD-221', 'AUD-222', 'AUD-223'],
                   'Manufacturer':['Mercedes', 'Mercedes', 'Mercedes', 'Mercedes', 'BMW', 'BMW', 'BMW', 'BMW', 'Audi', 'Audi', 'Audi', 'Audi'],
                   'Region':['Americas', 'Europe', 'Americas', 'Asia', 'Asia', 'Europe', 'Europe', 'Europe', 'Americas', 'Asia', 'Americas', 'Americas'],
                   'Department':[np.nan, 'Sales', np.nan, 'Operations', np.nan, np.nan, 'Accounting', np.nan, 'Finance', 'Finance', 'Finance', np.nan]
                  })

    System_Key  Manufacturer    Region       Department
0   MER-002     Mercedes        Americas     NaN
1   MER-003     Mercedes        Europe       Sales
2   MER-004     Mercedes        Americas     NaN
3   MER-005     Mercedes        Asia         Operations
4   BAV-378     BMW             Asia         NaN
5   BAV-379     BMW             Europe       NaN
6   BAV-380     BMW             Europe       Accounting
7   BAV-381     BMW             Europe       NaN
8   AUD-220     Audi            Americas     Finance
9   AUD-221     Audi            Asia         Finance
10  AUD-222     Audi            Americas     Finance
11  AUD-223     Audi            Americas     NaN

I pivot the data frame, as follows:

df.pivot_table(df, index='Manufacturer', columns='Region', values='System_Key', aggfunc='size', fill_value=0, margins=True).astype(int)

This gives me the following error:

KeyError: 'Americas'

Does anyone know what causes this key error? Thanks in advance!

Upvotes: 3

Views: 508

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150785

Use count instead of size works. Probably size doesn't work because some of the combinations are missing:

df.pivot_table(index='Manufacturer', columns='Region', 
               values='System_Key', aggfunc='count',
               fill_value=0, margins=True)

Output:

Region        Americas  Asia  Europe  All
Manufacturer                             
Audi                 3     1       0    4
BMW                  0     1       3    4
Mercedes             2     1       1    4
All                  5     3       4   12

Upvotes: 2

Related Questions