1

I've had a really tough time figuring out how to reshape this DataFrame. Sorry about the wording of the question, this problem seems a bit specific.

I have data on several countries along with a column of 6 repeating features and the year this data was recorded. It looks something like this (minus some features and columns):

   Country        Feature           2005    2006    2007    2008    2009

0  Afghanistan    Age Dependency    99.0    99.5    100.0   100.2   100.1
1  Afghanistan    Birth Rate        44.9    43.9    42.8    41.6    40.3
2  Afghanistan    Death Rate        10.7    10.4    10.1    9.8     9.5
3  Albania        Age Dependency    53.5    52.2    50.9    49.7    48.7
4  Albania        Birth Rate        12.3    11.9    11.6    11.5    11.6
5  Albania        Death Rate        5.95    6.13    6.32    6.51    6.68

There doesn't seem to be any way to make pivot_table() work in this situation and I'm having trouble finding what other steps I can take to make it look how I want:

                       Age Dependency    Birth Rate    Death Rate

Afghanistan    2005    99.0              44.9          10.7
               2006    99.5              43.9          10.4
               2007    100.0             42.8          10.1   
               2008    100.2             41.6          9.8
               2009    100.1             40.3          9.5

Albania        2005    53.5              12.3          5.95
               2006    52.2              11.9          6.13
               2007    50.9              11.6          6.32
               2008    49.7              11.5          6.51
               2009    48.7              11.6          6.68

Where the unique values of the 'Feature' column each become a column and the year columns each become part of a multiIndex with the country. Any help is appreciated, thank you!

EDIT: I checked the "duplicate" but I don't see how that question is the same as this one. How would I place the repeated values within my feature column as unique columns while at the same time moving the years to become a multi index with the countries? Sorry if I'm just not getting something.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Adrian Herrmann
  • 13
  • 4

1 Answers1

0

Use melt with reshape by set_index and unstack:

df = (df.melt(['Country','Feature'], var_name='year')
      .set_index(['Country','year','Feature'])['value']
      .unstack())
print (df)
Feature           Age Dependency  Birth Rate  Death Rate
Country     year                                        
Afghanistan 2005            99.0        44.9       10.70
            2006            99.5        43.9       10.40
            2007           100.0        42.8       10.10
            2008           100.2        41.6        9.80
            2009           100.1        40.3        9.50
Albania     2005            53.5        12.3        5.95
            2006            52.2        11.9        6.13
            2007            50.9        11.6        6.32
            2008            49.7        11.5        6.51
            2009            48.7        11.6        6.68
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252