I have a DataFrame defined like this:
from collections import OrderedDict
from pandas import DataFrame
import pandas as pd
import numpy as np
table = OrderedDict((
('year', [1900, 1900, 1900, 1900, 1901, 1901, 1901, 1901]),
('variable',['PRCP', 'PRCP', 'TAVG', 'TAVG', 'PRCP', 'PRCP', 'TAVG', 'TAVG']),
('month', [1, 2, 1, 2, 1, 2, 1, 2]),
('first_day', [5, 8, 7, 3, 9, 2, 4, 1]),
('second_day', [5, 8, 7, 3, 9, 2, 5, 8]),
('third_day', [1, 7, 5, 7, 3, 5, 8, 9])
))
df = DataFrame(table)
The DataFrame is now this:
year variable month first_day second_day third_day 0 1900 PRCP 1 5 5 1 1 1900 PRCP 2 8 8 7 2 1900 TAVG 1 7 7 5 3 1900 TAVG 2 3 3 7 4 1901 PRCP 1 9 9 3 5 1901 PRCP 2 2 2 5 6 1901 TAVG 1 4 5 8 7 1901 TAVG 2 1 8 9
I want to pivot the DataFrame so that it then looks like this:
variable year month day value 0 PRCP 1900 1 1 5 1 PRCP 1900 1 2 5 2 PRCP 1900 1 3 1 3 PRCP 1900 2 1 8 4 PRCP 1900 2 2 8 5 PRCP 1900 2 3 7 6 PRCP 1901 1 1 7 7 PRCP 1901 1 2 7 8 PRCP 1901 1 3 5 9 PRCP 1901 2 1 3 10 PRCP 1901 2 2 3 11 PRCP 1901 2 3 7 12 TAVG 1900 1 1 9 13 TAVG 1900 1 2 9 14 TAVG 1900 1 3 3 15 TAVG 1900 2 1 2 16 TAVG 1900 2 2 2 17 TAVG 1900 2 3 5 18 TAVG 1901 1 1 4 19 TAVG 1901 1 2 5 20 TAVG 1901 1 3 8 21 TAVG 1901 2 1 1 22 TAVG 1901 2 2 8 23 TAVG 1901 2 3 9
I think I want to do this via pivoting, but I’ve not yet worked out how to do this using the pivot() or pivot_table()functions. How can I do this, with or without using a pivot?
Advertisement
Answer
You can use melt, but first rename columns by dict:
d = {'first_day':1,'second_day':2,'third_day':3}
df = pd.melt(df.rename(columns=d), id_vars=['variable','year','month'], var_name='day')
df = df.sort_values(['variable','year','month', 'day']).reset_index(drop=True)
print (df)
variable year month day value
0 PRCP 1900 1 1 5
1 PRCP 1900 1 2 5
2 PRCP 1900 1 3 1
3 PRCP 1900 2 1 8
4 PRCP 1900 2 2 8
5 PRCP 1900 2 3 7
6 PRCP 1901 1 1 9
7 PRCP 1901 1 2 9
8 PRCP 1901 1 3 3
9 PRCP 1901 2 1 2
10 PRCP 1901 2 2 2
11 PRCP 1901 2 3 5
12 TAVG 1900 1 1 7
13 TAVG 1900 1 2 7
14 TAVG 1900 1 3 5
15 TAVG 1900 2 1 3
16 TAVG 1900 2 2 3
17 TAVG 1900 2 3 7
18 TAVG 1901 1 1 4
19 TAVG 1901 1 2 5
20 TAVG 1901 1 3 8
21 TAVG 1901 2 1 1
22 TAVG 1901 2 2 8
23 TAVG 1901 2 3 9
Or map column day by dict:
d = {'first_day':1,'second_day':2,'third_day':3}
df = pd.melt(df, id_vars=['variable','year','month'], var_name='day')
df.day = df.day.map(d)
df = df.sort_values(['variable','year','month', 'day']).reset_index(drop=True)
print (df)
variable year month day value
0 PRCP 1900 1 1 5
1 PRCP 1900 1 2 5
2 PRCP 1900 1 3 1
3 PRCP 1900 2 1 8
4 PRCP 1900 2 2 8
5 PRCP 1900 2 3 7
6 PRCP 1901 1 1 9
7 PRCP 1901 1 2 9
8 PRCP 1901 1 3 3
9 PRCP 1901 2 1 2
10 PRCP 1901 2 2 2
11 PRCP 1901 2 3 5
12 TAVG 1900 1 1 7
13 TAVG 1900 1 2 7
14 TAVG 1900 1 3 5
15 TAVG 1900 2 1 3
16 TAVG 1900 2 2 3
17 TAVG 1900 2 3 7
18 TAVG 1901 1 1 4
19 TAVG 1901 1 2 5
20 TAVG 1901 1 3 8
21 TAVG 1901 2 1 1
22 TAVG 1901 2 2 8
23 TAVG 1901 2 3 9
Another solution with stack:
d = {'first_day':1,'second_day':2,'third_day':3}
df = df.rename(columns=d).set_index(['variable','year','month'])
.stack()
.reset_index(name='value')
.rename(columns={'level_3':'day'})
print (df)
variable year month day value
0 PRCP 1900 1 1 5
1 PRCP 1900 1 2 5
2 PRCP 1900 1 3 1
3 PRCP 1900 2 1 8
4 PRCP 1900 2 2 8
5 PRCP 1900 2 3 7
6 TAVG 1900 1 1 7
7 TAVG 1900 1 2 7
8 TAVG 1900 1 3 5
9 TAVG 1900 2 1 3
10 TAVG 1900 2 2 3
11 TAVG 1900 2 3 7
12 PRCP 1901 1 1 9
13 PRCP 1901 1 2 9
14 PRCP 1901 1 3 3
15 PRCP 1901 2 1 2
16 PRCP 1901 2 2 2
17 PRCP 1901 2 3 5
18 TAVG 1901 1 1 4
19 TAVG 1901 1 2 5
20 TAVG 1901 1 3 8
21 TAVG 1901 2 1 1
22 TAVG 1901 2 2 8
23 TAVG 1901 2 3 9