I have a dataframe which looks like this
| A | B | C | Category | Value |
|---|---|---|---|---|
| 1 | A | Prime | Unique | 0 |
| 2 | A | Prime | One | Initials |
| 1 | A | Prime | Two | Seconds |
| 2 | B | Prime | Unique | 1 |
| 2 | B | Prime | One | Firsts |
I want to create a new dataframe where I group first 3 columns and based on Category value make it new column i.e. If Category has value Unique, Make it a column and add it's value to the correspondings in the group
I want my new dataframe to look like this:
| A | B | C | Category | Value | Unique |
|---|---|---|---|---|---|
| 2 | A | Prime | One | Initials | |
| 1 | A | Prime | Two | Seconds | 0 |
| 2 | B | Prime | One | Firsts | 1 |
CodePudding user response：
First need distingusih which rows are pivoted, here if Category is Unique, filter by boolean indexing with DataFrame.pivot and add to original not matched rows by ~ with DataFrame.join:
m = df['Category'].eq('Unique')
#if need test multiple values
#m = df['Category'].isin(['Unique', 'Another val'])
df1 = df[m].pivot(index=['A','B','C'], columns='Category', values='Value')
print (df1)
Category Unique
A B C
1 A Prime 0
2 B Prime 1
df = df[~m].join(df1, on=['A','B','C'])
print (df)
A B C Category Value Unique
1 2 A Prime One Initials NaN
2 1 A Prime Two Seconds 0
4 2 B Prime One Firsts 1