Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how can i unstack without sorting in pandas?

Tags:

i have below time-series data couple of day.. and i wanna unstack by 'Date' it. but i used .unstack() then automatically sorted of time.. (Date/Time is multi index)

Date    Time             a     b     c      d       e   
2015-12-06  22:00:00    21.26   0   2.62    242.195 0
2015-12-06  22:15:00    21.14   0   2.55    255.516 0
2015-12-06  22:30:00    21.2    0   2.49    241.261 0
2015-12-06  22:45:00    21.18   0   2.48    232.058 0
2015-12-06  23:00:00    21.12   0   2.38    239.661 0
2015-12-06  23:15:00    21  0   2.23    228.324 0
2015-12-06  23:30:00    21.13   0   2.29    0   0
2015-12-06  23:45:00    21.12   0   2.29    0   0
2015-12-06  0:00:00 21.02   0   2.17    0   0
2015-12-06  0:15:00 21.09   0   2.13    0   0
2015-12-06  0:30:00 20.96   0   2.21    0   0
2015-12-06  0:45:00 20.92   0   2.19    0   0
2015-12-06  1:00:00 20.99   0   2.13    0   0
2015-12-06  1:15:00 20.92   0   2.14    0   0
2015-12-06  1:30:00 20.97   0   2.13    0   0
2015-12-06  1:45:00 20.85   0   2.11    0   0
2015-12-06  2:00:00 20.76   0   1.72    0   0

my wanted results is like below. how can i do it?

        a               a               a               a...
Date    2015-12-06 0:00 2015-12-13 0:00 2015-12-20 0:00 2015-12-23 0:00...
Time                
22:00:00    21.02   21.26   20.75   22.61
22:15:15:00 21.09   21.36   20.74   22.65
..
0:00:00 20.92   21.2    20.79   22.37
0:15:00 20.99   21.33   20.77   22.44
0:30:00 20.92   21.24   20.76   22.28
..
like image 652
jerry han Avatar asked Feb 06 '18 05:02

jerry han


1 Answers

You need unstack by first level and then reindex by unique values of second level, last sort_index of second level of MutiIndex in columns:

df =df.unstack(0).reindex(pd.unique(df.index.get_level_values(1))).sort_index(axis=1,level=1)
print (df)
                  a          b          c        c e      valve
Date     2015-12-06 2015-12-06 2015-12-06 2015-12-06 2015-12-06
Time                                                           
22:00:00      21.26          0       2.62    242.195          0
22:15:00      21.14          0       2.55    255.516          0
22:30:00      21.20          0       2.49    241.261          0
22:45:00      21.18          0       2.48    232.058          0
23:00:00      21.12          0       2.38    239.661          0
23:15:00      21.00          0       2.23    228.324          0
23:30:00      21.13          0       2.29      0.000          0
23:45:00      21.12          0       2.29      0.000          0
0:00:00       21.02          0       2.17      0.000          0
0:15:00       21.09          0       2.13      0.000          0
0:30:00       20.96          0       2.21      0.000          0
0:45:00       20.92          0       2.19      0.000          0
1:00:00       20.99          0       2.13      0.000          0
1:15:00       20.92          0       2.14      0.000          0
1:30:00       20.97          0       2.13      0.000          0
1:45:00       20.85          0       2.11      0.000          0
2:00:00       20.76          0       1.72      0.000          0

EDIT:

idx = (pd.date_range('2015-01-01','2015-01-01 23:45:00', freq='15T') + 
      pd.to_timedelta('22:00:00')).time
df = df.unstack(0).reindex(idx)
like image 72
jezrael Avatar answered Sep 22 '22 13:09

jezrael