Pandas: create data frame with binary values for each instance of a column

user_5844628 0
Viewed 187

I currently have a DataFrame that looks like this

user    | preference
----------------
Alex    | tea
Alex    | cookie
Alex    | apple
Peter   | coffee
Peter   | apple
Peter   | banana
John    | tea

I need to transform the above to a DF that will map a user to all the unique values of the preference column. If user has that preference, we will assign 1 if not 0. So, the result should look like this

user     | preference | value 
-------------------------
Alex     | tea        | 1
Alex     | coffee     | 0
Alex     | cookie     | 1
Alex     | apple      | 1
Alex     | banana     | 0
Peter    | tea        | 0
Peter    | coffee     | 1
Peter    | cookie     | 0
Peter    | apple      | 1
Peter    | banana     | 1
John     | tea        | 1
John     | coffee     | 0
John     | cookie     | 0
John     | apple      | 0
John     | banana     | 0
pandas dataframe python
2 Answers

Try:

x = (
    pd.crosstab(df["user"], df["preference"])
    .stack()
    .reset_index()
    .rename(columns={0: "value"})
)
print(x)

Prints:

    user preference  value
0      1      apple      1
1      1     banana      0
2      1     coffee      0
3      1     cookie      1
4      1        tea      1
5      2      apple      1
6      2     banana      1
7      2     coffee      1
8      2     cookie      0
9      2        tea      0
10     3      apple      0
11     3     banana      0
12     3     coffee      0
13     3     cookie      0
14     3        tea      1
user_100359850

Here is another way:

cols = df.columns.tolist()

(df.assign(value = 1)
 .set_index(cols)
 .reindex(pd.MultiIndex.from_product([df['user'].unique(),df['preference'].unique()],names=cols))
 .fillna(0)
 .reset_index())
user_138021150
Related