Connecting two Columns with list Elements for Pivot in Pandas-CodePudding

I have two columns (keys, values) wich i want to pivot with pandas.

After importing, my dataset looks like this:

	Work Center	key	value
0	k1	a,	1,
1	k2	a,b,	3,5,
2	k1	c,a,	7.5,5,

I want to get this:

Work Center	key.a	key.b
k1	sum val a	sum val b
..	..	..

…

Please help :) Thanks

CodePudding user response：

First you can split on key and value columns and explode them to rows

df_ = (df
   .set_index(['Work Center'])
   .apply(pd.Series.explode).reset_index()
   .replace('', pd.NA)
   .dropna(subset=['key'])
)

  Work Center key value
0          k1   a     1
2          k2   a     3
3          k2   b     5
5          k1   c   7.5
6          k1   a     5

Then use pivot_table to pivot it

df_['value'] = pd.to_numeric(df_['value'])

df_ = (df_
   .pivot_table(index='Work Center', columns='key', values='value', aggfunc='sum', fill_value=0)
   .add_prefix('key.')
)

print(df_)

key          key.a  key.b  key.c
Work Center
k1               6      0    7.5
k2               3      5    0.0