How to convert column values into new columns showing frequency

user01b91 0
Viewed 21

I created a new dataframe by splitting a column and expanding it.

I now want to convert the dataframe to create new columns for every value and only display the frequency of the value.

I wrote an example below.

Example dataframe:

import pandas as pd
import numpy as np

df= pd.DataFrame({0:['cake','fries', 'ketchup', 'potato', 'snack'],
                1:['fries', 'cake', 'potato', np.nan, 'snack'],
                2:['ketchup', 'cake', 'potatos', 'snack', np.nan],
                3:['potato', np.nan,'cake', 'ketchup',np.nan],
                'index':['james','samantha','ashley','tim', 'mo']})

df.set_index('index')

Expected output:

output = pd.DataFrame({'cake': [1, 2, 1, 0, 0],
                       'fries': [1, 1, 0, 0, 0],
                       'ketchup': [1, 0, 1, 1, 0],
                       'potatoes': [1, 0, 2, 1, 0],
                       'snack': [0, 0, 0, 1, 2],
                       'index': ['james', 'samantha', 'asheley', 'tim', 'mo']})

output.set_index('index')
pivot split dataframe pandas python
1 Answers

Based on the description of what you want, you would need a crosstab on the reshaped data:

df2 = df.reset_index().melt('index')

out = pd.crosstab(df2['index'], df2['value'].str.lower())

This, however, doesn't match the provided output.

Output:

value     apple  berries  cake  chocolate  drink  fries  fruits  ketchup  potato  potatoes  snack
index                                                                                            
Ashley        0        0     0          0      0      0       0        1       1         0      1
James         0        1     1          0      0      1       1        0       0         0      0
Mo            0        0     0          1      0      0       1        1       0         1      0
samantha      1        0     0          1      0      1       0        0       0         0      0
tim           0        0     0          0      1      0       0        0       0         0      1
mozway0
Related