pivoting and multi-indexing

Question

How do I transform a pandas dataframe that is in the following format:

Index   Code   Year   Week   Count
    0   AE    2005     1      0
    1   AE    2005     2      0
    2   AE    2005     3      2
    3   AE    2005     4      0
    4   AE    2005     5      0
    .....
    51  AE    2005     52     1
    52  AE    2006     1      3
    53  AE    2006     2      0
    54  AE    2006     3      1
    55  AE    2006     4      0
    56  AE    2006     5      0
    .....
    102  AE    2006     52     1
    103  AU    2005     1      0
    104  AU    2005     2      0
    105  AU    2005     3      2
    106  AU    2005     4      0
    107  AU    2005     5      0
    .....
    153  AU    2005     52     1
    154  AU    2006     1      3
    155  AU    2006     2      0
    156  AU    2006     3      1
    157  AU    2006     4      0
    158  AU    2006     5      0
    .....
    203  AU    2006     52     1

There are multiple codes, multiple years, 52 weeks entries for each year and a count value for each week

The required format:

      Year      2005                  2006
      Week      1     2    3  ... 52  1     2    3 ... 
Code
AE              0     0    2  ... 0   0     1    2 ... 
AU
...
ZC

I have tried looking up different solutions here as well as have tried pivot, pivot_table, combinations of stack and unstack, but haven't been able to workout the solution :(.

@Wen, I have seen that post and none of the scenarios seem to match my case. The pivoting cases there include aggregations, I can't aggregate. — Kam, May 09 '18 at 02:12

score 0 · Answer 1 · answered May 09 '18 at 02:22

0

You can do with set_index and stack

df.set_index(['Code','Year','Week']).Count.unstack([1,2])
Year 2005            
Week    1  2  3  4  5
Code                 
AE      0  0  2  0  0

answered May 09 '18 at 02:22

BENY

317,841
20
164
234

Thanks Wen, this is giving me the following error AttributeError: 'DataFrame' object has no attribute 'Count' – Kam May 09 '18 at 02:29
@Kam check your columns , whether it contain space or not – BENY May 09 '18 at 02:31
not sure what are you asking? Do you mean space in column names? The answer is no. The code column is alpha-numeric, all other columns are integer values – Kam May 09 '18 at 02:59
@Kam can you try my code with your sample data frame , check whether it work or not – BENY May 09 '18 at 03:00
I have updated the sample data frame to better explain what I am trying to do. – Kam May 09 '18 at 04:15

pivoting and multi-indexing

1 Answers1