Pivot category column having multiple corresponding value columns-CodePudding

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.

CodePudding user response：

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