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

Question

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'])

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

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

question from:https://stackoverflow.com/questions/65873915/extract-new-columns-and-fill-values-based-on-categorical-values-data-frame-in-py

Answer 1

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