'Reshaping dataframe from categories cross-table to one with multi-index in the columns
Problem statement
I am trying to turn a dataframe with a table that crosses categories with items at different dates into a dataframe with dates in the index and multiindex columns with categories as first level and items as the second level. Below you can find an example of original and target tables.
Is there a pythonic way to achieve this in Pandas?
Input table
- All of the table is in the dataFrame data. Indices and columns are just integers*
| Category A | Category B | Category C | |
|---|---|---|---|
| 31/12/2020 | |||
| item one | 10 | 100 | 1000 |
| item two | 20 | 200 | 2000 |
| 31/12/2021 | |||
| item one | 30 | 300 | 3000 |
| item two | 40 | 400 | 4000 |
Desired output table
Dates should be in the index and columns should be a multiindex of Category and item
| Category A | Category B | Category C | ||||
|---|---|---|---|---|---|---|
| item one | item two | item one | item two | item one | item two | |
| 31/12/2020 | 10 | 20 | 100 | 200 | 1000 | 2000 |
| 31/12/2021 | 30 | 40 | 300 | 400 | 3000 | 4000 |
Python code to create input dataframe
import pandas as pd
input_df = pd.DataFrame(
index = [0,1,2,3,4,5,6],
columns = [0,1,2,3],
data = [
["","Category A","Category B","Category C"],
["31/12/2020","","",""],
["item one",10,100,1000],
["item two",20,200,2000],
["31/12/2021","","",""],
["item one",30,300,3000],
["item two",40,400,4000],
]
)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
