pandas pivot table with uneven number of entry

Question

I am trying to transform table (df):

area    cat   value
0       A       3
0       B       2
1       A       9
2       A       8
2       B       3
2       C       1

to table (df2):

area    A     B    C
0       3     2    nan
1       9     nan  nan
2       8     3     1

I've tried df2=df.pivot(index='area',columns='cat',value='value') however got the error:

'Exception: Data must be 1-dimensional'

It seems that because I have uneven members of cat across the index area? How would we fix this problem?

Thanks in advance.

Cheers, Tom

Answer 1

you could try the pivot_table and give columns as cat

  df = pd.read_csv(StringIO.StringIO(data))
  print df

   area cat  value
0     0   A      3
1     0   B      2
2     1   A      9
3     2   A      8
4     2   B      3
5     2   C      1

  print pd.pivot_table(df, index=['area'], columns=['cat'])

cat      A    B    C
area
0      3.0  2.0  NaN
1      9.0  NaN  NaN
2      8.0  3.0  1.0