CODEWITHSUNDEEP
pythondataframepivotmultiple-columnscategorical-data
Salman
Salman

Reputation: 113

extract new columns and fill values based on categorical values data frame in python

I have a data frame where one column is categorical strings and the next one is the values corresponding to it:

df = pd.DataFrame(list((['a', 'b', 'c', 'buy', 5],
                      ['f', 'b', 'a', 'buy', 2],
                      ['a', 'b', 'c', 'sold', 6],
                      ['a', 'b', 'f', 'buy', 4],
                      ['a', 'b', 'c', 'returned', 'yes'])), columns = ['attr1', 'attr2','attr3','status','value'])

initial df with too many rows that are duplicated

I want to create new columns based on df.status column, and fill empty ones with np.nan, requires pivot on multiple columns:

result df after pivot on multiple indexes

I am looking for an efficient solution that works for large data frames.

Upvotes: 0

Views: 139

Answers (1)

Mayank Porwal
Mayank Porwal

Reputation: 34056

You want:

In [255]: df.pivot(index=['attr1', 'attr2', 'attr3'],columns='status', values='value').rename_axis(None, axis=1).reset_index()
Out[255]: 
  attr1 attr2 attr3 buy returned sold
0     a     b     c   5      yes    6
1     a     b     f   4      NaN  NaN
2     f     b     a   2      NaN  NaN

Upvotes: 1

Related Questions