[FIXED] Pandas function to create multiple columns based on a single column

Issue

I have a dataset which looks like this

A	B	C
A1	1	1.3
A1	2	1.4
A1	3	1.3
A2	1	1.7
A2	2	1.6
A2	3	1.5
A1	1	1
A1	2	2.7
A1	3	2.4
A2	1	1.6
A2	2	1.7
A2	3	1.2

Desired output :

A	1	2	3
A1	1.3	1.4	1.3
A1	1	2.7	2.4
A2	1.7	1.6	1.5
A2	1.6	1.7	1.2

I have tried a pivot table but it's giving aggregation and without aggregation pivot it is showing a duplicate index error because of the duplicate index. What is the best way to handle this?

Solution

You can iterate over using number of elements (assuming all combos have same number of elements) as follows:

dfx = df.pivot_table(index='A', columns='B', values='C', aggfunc=list)
pd.concat([dfx.applymap(lambda x:x[idx]) for idx in range(dfx.apply(lambda x: len(x)).max())])

A	1	2	3
A1	1.3	1.4	1.3
A2	1.7	1.6	1.5
A1	1	2.7	2.4
A2	1.6	1.7	1.2

Old answer:

Seeing your sample dataframe, a simple pivot should do:

df.pivot_table(index='A', columns='B', values='C', aggfunc='mean')

Answered By - Hamza