CODEWITHSUNDEEP
pandas
yahop19531
yahop19531

Reputation: 223

Pandas - move one level of multi-index to a column

I have a dataframe with three indices ("Date", "ID", "Period") and two Metrics (A & B).

                  MetricA  MetricB
2021-01-01 id1 1        1        6
               2        3        1
2021-02-01 id1 1        8        1
               2        3        4
2021-01-01 id2 1       10        5
               2        6        4
2021-02-01 id2 1        3        4
               2        6        8

I want to restructure the table so that the third index level "Period" becomes an extra column, with the output like this:

                MetricA-1  MetricA-2  MetricB-1  MetricB-2
Date       ID                                             
2021-01-01 id1          1          3          6          1
2021-02-01 id1          8          3          1          4
2021-01-01 id2         10          6          5          4
2021-02-01 id2          3          6          4          8

How can I do this?

Dummy data:

d= {'MetricA': {('2021-01-01', 'id1', 1): 1, ('2021-01-01', 'id1', 2): 3, ('2021-02-01', 'id1', 1): 8, ('2021-02-01', 'id1', 2): 3, ('2021-01-01', 'id2', 1): 10, ('2021-01-01', 'id2', 2): 6, ('2021-02-01', 'id2', 1): 3, ('2021-02-01', 'id2', 2): 6}, 'MetricB': {('2021-01-01', 'id1', 1): 6, ('2021-01-01', 'id1', 2): 1, ('2021-02-01', 'id1', 1): 1, ('2021-02-01', 'id1', 2): 4, ('2021-01-01', 'id2', 1): 5, ('2021-01-01', 'id2', 2): 4, ('2021-02-01', 'id2', 1): 4, ('2021-02-01', 'id2', 2): 8}}
df = pd.DataFrame(data=d)
print(df)

Upvotes: 1

Views: 453

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150805

Use unstack then rename:

out = df.unstack()
out.columns = [f'{x}-{y}' for x,y in out.columns]

Output:

                MetricA-1  MetricA-2  MetricB-1  MetricB-2
2021-01-01 id1          1          3          6          1
           id2         10          6          5          4
2021-02-01 id1          8          3          1          4
           id2          3          6          4          8

Upvotes: 1

Related Questions