0

I have the below table which shows rainfall by month in the UK across a number of years. I want to transpose it so that each row is one month/year and the data is chronological. 

Year    JAN     FEB     MAR     APR
2010    79.7    74.8    79.4    48
2011    102.8   114.5   49.7    36.7
2012    110.9   60  37  128
2013    110.5   59.8    64.6    63.6

I would like it so the table looks like the below with year, month & rainfall as the columns:

2010    JAN 79.7
2010    FEB 74.8
2010    MAR 79.4
2010    APR 48
2011    JAN 102.8
2011    FEB 114.5

I think I need to use a for loop and iterate through each row to create a new dataframe but I'm not sure of the syntax. I've tried the below loop which nearly does what I want but doesn't output as a dataframe. 

for index, row in weather.iterrows():
    print(row["Year"],row)

2014.0 Year    2014.0
JAN      188.0
FEB      169.2
MAR       80.0
APR       67.8
MAY       99.6
JUN       54.8
JUL       64.7

Any help would be appreciated.

roganjosh
  • 12,594
  • 4
  • 29
  • 46
Catatouille
  • 1
  • 1
  • 3
    No, you definitely don't want a `for` loop. You should always consider that as a last resort with pandas. This is a "wide-to-long" issue. – roganjosh Jun 08 '19 at 12:22

1 Answers1

3

You should avoid using for-loops and instead use stack.

df.set_index('Year') \
  .stack() \
  .reset_index() \
  .rename(columns={'level_1': 'Month', 0: 'Amount'})

    Year Month  Amount
0   2010   JAN    79.7
1   2010   FEB    74.8
2   2010   MAR    79.4
3   2010   APR    48.0
4   2011   JAN   102.8
5   2011   FEB   114.5
6   2011   MAR    49.7
7   2011   APR    36.7
8   2012   JAN   110.9
9   2012   FEB    60.0
etc...
gold_cy
  • 13,648
  • 3
  • 23
  • 45