CODEWITHSUNDEEP
pandassortingstacktime-series
jerry han
jerry han

Reputation: 435

How can i unstack without sorting in pandas?

I have below time-series data couple of day and i wanna unstack it by Date. But i used .unstack() then it automatically sorted by 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.20  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.00  0  2.23  228.324  0
2015-12-06  23:30:00  21.13  0  2.29    0.000  0
2015-12-06  23:45:00  21.12  0  2.29    0.000  0
2015-12-06  00:00:00  21.02  0  2.17    0.000  0
2015-12-06  00:15:00  21.09  0  2.13    0.000  0
2015-12-06  00:30:00  20.96  0  2.21    0.000  0
2015-12-06  00:45:00  20.92  0  2.19    0.000  0
2015-12-06  01:00:00  20.99  0  2.13    0.000  0
2015-12-06  01:15:00  20.92  0  2.14    0.000  0
2015-12-06  01:30:00  20.97  0  2.13    0.000  0
2015-12-06  01:45:00  20.85  0  2.11    0.000  0
2015-12-06  02:00:00  20.76  0  1.72    0.000  0

My wanted results is like below. How can i do it?

         a           a           a           a           ...
Date     2015-12-06  2015-12-13  2015-12-20  2015-12-23  ...
Time
22:00:00      21.02       21.26       20.75       22.61
22:15:00      21.09       21.36       20.74       22.65
...
00:00:00      20.92       21.20       20.79       22.37
00:15:00      20.99       21.33       20.77       22.44
00:30:00      20.92       21.24       20.76       22.28
...

Upvotes: 9

Views: 4570

Answers (2)

creanion
creanion

Reputation: 2743

From Pandas v2.1 you can use:

df.unstack("Date", sort=False)

Now it has sort=False.

See also pandas.DataFrame.unstack docs.

Upvotes: 4

jezrael
jezrael

Reputation: 863226

You need to unstack by first level and then reindex by unique values of second level, lastly 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
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
00:00:00      21.02          0       2.17      0.000          0
00:15:00      21.09          0       2.13      0.000          0
00:30:00      20.96          0       2.21      0.000          0
00:45:00      20.92          0       2.19      0.000          0
01:00:00      20.99          0       2.13      0.000          0
01:15:00      20.92          0       2.14      0.000          0
01:30:00      20.97          0       2.13      0.000          0
01:45:00      20.85          0       2.11      0.000          0
02: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)

Upvotes: 7

Related Questions