python/pandas - converting column headers into index

Question

The data I have to deal with treats hourly data as the columns. I want to convert this as an index. Sample looks like this:

 year    month    day    1    2    3    4    5    ...   24
 2015      1       1     a    b   ...................    c
 2015      1       2     d    e   ...................    f
 2015      1       3     g    h   ...................    i

I want to make the output file something like this:

 year    month    day   hour value
 2015      1       1     1     a  
 2015      1       1     2     b 
  .        .       .     .     . 
 2015      1       1     24    c 
 2015      1       2     1     d
  .        .       .     .     .

Currently using python 3.4 with the pandas module

jezrael · Accepted Answer · 2016-05-25 08:10:03Z

Use set_index with stack:

print (df.set_index(['year','month','day'])
         .stack()
         .reset_index(name='value')
         .rename(columns={'level_3':'hour'}))

   year  month  day hour value
0  2015      1    1    1     a
1  2015      1    1    2     b
2  2015      1    1   24     c
3  2015      1    2    1     d
4  2015      1    2    2     e
5  2015      1    2   24     f
6  2015      1    3    1     g
7  2015      1    3    2     h
8  2015      1    3   24     i

Another solution with melt and sort_values:

print (pd.melt(df, id_vars=['year','month','day'], var_name='hour')
         .sort_values(['year', 'month', 'day','hour']))

   year  month  day hour value
0  2015      1    1    1     a
3  2015      1    1    2     b
6  2015      1    1   24     c
1  2015      1    2    1     d
4  2015      1    2    2     e
7  2015      1    2   24     f
2  2015      1    3    1     g
5  2015      1    3    2     h
8  2015      1    3   24     i

Collectives™ on Stack Overflow

python/pandas - converting column headers into index

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related