Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas NaN introduced by pivot_table

I have a table containing some countries and their KPI from the world-banks API. this looks like no nan values present. As you can see no nan values are present.

However, I need to pivot this table to bring int into the right shape for analysis. A pd.pivot_table(countryKPI, index=['germanCName'], columns=['indicator.id']) For some e.g. TUERKEI this works just fine:

for turkey it works But for most of the countries strange nan values are introduced. How can I prevent this?

strange nan values

like image 691
Georg Heiler Avatar asked Sep 22 '16 06:09

Georg Heiler


1 Answers

I think the best way to understand pivoting is to apply it to a small sample:

import pandas as pd
import numpy as np

countryKPI = pd.DataFrame({'germanCName':['a','a','b','c','c'],
                           'indicator.id':['z','x','z','y','m'],
                           'value':[7,8,9,7,8]})

print (countryKPI)
  germanCName indicator.id  value
0           a            z      7
1           a            x      8
2           b            z      9
3           c            y      7
4           c            m      8

print (pd.pivot_table(countryKPI, index=['germanCName'], columns=['indicator.id']))
             value               
indicator.id     m    x    y    z
germanCName                      
a              NaN  8.0  NaN  7.0
b              NaN  NaN  NaN  9.0
c              8.0  NaN  7.0  NaN

If need replace NaN to 0 add parameter fill_value:

print (countryKPI.pivot_table(index='germanCName', 
                              columns='indicator.id', 
                              values='value', 
                              fill_value=0))
indicator.id  m  x  y  z
germanCName             
a             0  8  0  7
b             0  0  0  9
c             8  0  7  0
like image 135
jezrael Avatar answered Oct 16 '22 18:10

jezrael