What is the best way to convert a pandas DataFrame from wide format into stacked/record/database/long format?
Here's a small code example:
Wide format:
date hour1 hour2 hour3 hour4
2012-12-31 9.18 -0.10 -7.00 -64.92
2012-12-30 13.91 0.09 -0.96 0.08
2012-12-29 12.97 11.82 11.65 10.20
2012-12-28 22.01 16.04 15.68 11.67
2012-12-27 11.44 0.07 -19.97 -67.98
...
Stacked/record/database/long format (needed):
date hour price
2012-12-31 00:00:00 hour1 9.18
2012-12-31 00:00:00 hour2 -0.1
2012-12-31 00:00:00 hour3 -7
2012-12-31 00:00:00 hour4 -64.92
...
2012-12-30 00:00:00 hour1 7.18
2012-12-30 00:00:00 hour2 -1.1
2012-12-30 00:00:00 hour3 -9
2012-12-30 00:00:00 hour4 -74.91
...
You can use melt to convert a DataFrame from wide format to long format:
import pandas as pd
df = pd.DataFrame({'date': ['2012-12-31', '2012-12-30', '2012-12-29', '2012-12-28', '2012-12-27'],
'hour1': [9.18, 13.91, 12.97, 22.01, 11.44],
'hour2': [-0.1, 0.09, 11.82, 16.04, 0.07]})
print pd.melt(df, id_vars=['date'], value_vars=['hour1', 'hour2'], var_name='hour', value_name='price')
Output:
date hour price
0 2012-12-31 hour1 9.18
1 2012-12-30 hour1 13.91
2 2012-12-29 hour1 12.97
3 2012-12-28 hour1 22.01
4 2012-12-27 hour1 11.44
5 2012-12-31 hour2 -0.10
6 2012-12-30 hour2 0.09
7 2012-12-29 hour2 11.82
8 2012-12-28 hour2 16.04
9 2012-12-27 hour2 0.07