Concatenate rows of Pandas multiindex dataframe by index value

Question

Given the following DataFrame,

              00:00:00 01:00:00 02:00:00
Date       ID                           
2018-01-01 A1       x1       x2       x3
           B3       y1       y2       y3
2018-01-02 A1       x4       x5       x6
           B3       y4       y5       y6
2018-03-02 A1       x7       x8       x9
           B3       y7       y8       y9

which was obtained with

import pandas as pd
idx = pd.MultiIndex.from_product([pd.to_datetime(["2018-01-01", "2018-01-02", "2018-03-02"]),
                                 ["A1", "B3"]], names=["Date", "ID"])
col = pd.timedelta_range("00:00:00", periods=3, freq="1H")
df = pd.DataFrame([["x1", "x2", "x3"], ["y1", "y2", "y3"], ["x4", "x5", "x6"],
                   ["y4", "y5", "y6"], ["x7", "x8", "x9"], ["y7", "y8", "y9"]], idx, col)

I want to transform it to the following form,

datetime          A1   B3
2018-01-01 00:00  x1   y1
2018-01-01 01:00  x2   y2
2018-01-01 02:00  x3   y3
2018-01-02 00:00  x4   y4
2018-01-02 01:00  x5   y5
2018-01-02 02:00  x6   y6
2018-03-02 00:00  x7   y7
2018-03-02 01:00  x8   y8
2018-03-02 02:00  x9   y9

But how?

BENY · Accepted Answer · 2018-02-23 15:53:08Z

3

IIUC

df.unstack().swaplevel(axis=1).stack()
Out[1736]: 
ID                   A1  B3
Date                       
2018-01-01 00:00:00  x1  y1
           01:00:00  x2  y2
           02:00:00  x3  y3
2018-01-02 00:00:00  x4  y4
           01:00:00  x5  y5
           02:00:00  x6  y6
2018-03-02 00:00:00  x7  y7
           01:00:00  x8  y8
           02:00:00  x9  y9

answered Feb 23, 2018 at 15:53

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Bella Over a year ago

Does this guarantee chronological ordering or should a reordering of the index be performed for one to be sure?

BENY Over a year ago

@Bella it will follow your df order , it still the multiindex

Bella Over a year ago

Ok, great. Really nice in one line! Thanks for teaching me this.

BENY Over a year ago

@Bella yw~ :-) happy coding

BENY Over a year ago

@joaoavf df.index=df.index.map('{0[0]}{0[1]}'.format) , when you do merge , make sure you change the datetime to str firstly

|

Collectives™ on Stack Overflow

Concatenate rows of Pandas multiindex dataframe by index value

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related