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?
CodePudding user response：
Try:
df = df.pivot_table(index="A", columns="B", values="C", aggfunc=list)
df = df.explode(df.columns.to_list()).reset_index()
df.columns.name = None
print(df)
Prints:
A 1 2 3
0 A1 1.3 1.4 1.3
1 A1 1.0 2.7 2.4
2 A2 1.7 1.6 1.5
3 A2 1.6 1.7 1.2
CodePudding user response：
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')
CodePudding user response：
One option is to create an extra column that introduces uniqueness to your data, before pivoting:
a = df.groupby('A').ngroup().diff().fillna(0, downcast = 'infer')
a = a.mask(a < 0, 1).cumsum()
(df
.assign(temp = a)
.pivot(['temp', 'A'], 'B', 'C')
.droplevel(0)
.rename_axis(columns = None)
)
1 2 3
A
A1 1.3 1.4 1.3
A2 1.7 1.6 1.5
A1 1.0 2.7 2.4
A2 1.6 1.7 1.2