I'm using Pandas, and I would like to reposition elements with the columns, I currently have:
| Type Label | Initial | 2022 | 2023 | Difference |
|---|---|---|---|---|
| APPS | A/B/C | 500 | 469 | 31 |
| BACS | B/C/D | 5 | 3 | 2 |
| CAPS | C/D/E | 10 | 5 | 5 |
I would like to have the table to be displayed like this:
| Type Label/Initial | 2022 | 2023 | Difference |
|---|---|---|---|
| APPS | 500 | 469 | 31 |
| A | |||
| B | |||
| C | |||
| BACS | 5 | 3 | 2 |
| B | |||
| C | |||
| D | |||
| CAPS | 10 | 5 | 5 |
| C | |||
| D | |||
| E |
Join columns Type and Initial with DataFrame.pop for droping column, then split by / and use DataFrame.explode, rename columns names and set empty string for repeated values:
s = (df['Type Label'] + '/ ' + df.pop('Initial')).str.split('/')
df = df.assign(**{'Type Label':s} ).explode('Type Label').rename(columns={'Type Label':'Type Label/Initial'})
df.iloc[:, 1:] = df.iloc[:, 1:].mask(df.index.to_series().duplicated(), '')
df = df.reset_index(drop=True)
print (df)
Type Label/Initial 2022 2023 Difference
0 APPS 500 469 31
1 A
2 B
3 C
4 BACS 5 3 2
5 B
6 C
7 D
8 CAPS 10 5 5
9 C
10 D
11 E