I have the following structure
| Company | Category | Year1 | Year2 | Year3 |
|---|---|---|---|---|
| MC | Online | 10 | 20 | 25 |
| MC | On-site | 14 | 18 | 20 |
| BK | Online | 50 | 34 | 33 |
| BK | On-site | 30 | 31 | 31 |
I want to pivot the above table so I get a single row for each of the companies:
| Company | Year1-Online | Year1-On-Site | Year2-Online | Year2-On-site | Year3-Online | Year3-On-site |
|---|---|---|---|---|---|---|
| MC | 10 | 14 | 20 | 18 | 25 | 20 |
| BK | 50 | 30 | 34 | 31 | 33 | 31 |
Struggle to get it done. The names of the columns should be a concatenation of both ideally so I know which one refers to what.
Try .set_index(), .stack() followed by .unstack() specified levels:
x = df.set_index(["Company", "Category"]).stack().unstack(level=[1, 2])
x.columns = [f"{b}-{a}" for a, b in x.columns]
print(x[sorted(x.columns)].reset_index())
Prints:
Company Year1-On-site Year1-Online Year2-On-site Year2-Online Year3-On-site Year3-Online
0 BK 30 50 31 34 31 33
1 MC 14 10 18 20 20 25