Reshaping a pandas DataFrame into stacked/record/database/long format

Question

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
...

Answer 1

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