Create new column from another column's particular value using pandas-CodePudding

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