pandas create paired data from a dataframe

Question

I have a pd.DataFrame that like this

    User  M&Ms  Skittles  Snickers  Laffy Taffy  Caramel Chew
0   Lara     3         0         0            0             0
1  Daisy     4         0         1            1             0
2   Alyx     0         0         0            0             5
3  Sarah     0         3         0            0             0

I want to create a dataframe that looks like this

    user          item  rating
0   Lara          m_ms       3
1   Lara      snickers       5
2  Daisy          m_ms       4
3  Daisy   laffy_taffy       1
4   Alyx  caramel_chew       5
5  Sarah      skittles       3
6  Sarah      snickers       2

Is there anyway to do that? Thanks

Seems like you're trying to `melt` your data: https://stackoverflow.com/questions/34830597/pandas-melt-function. In your output, why does "Lara" have a rating 5 for snickers, when in the input she rates snickers as 0? — Cameron Riddell, Nov 05 '20 at 20:25

score 2 · Accepted Answer · answered Nov 05 '20 at 20:28

2

Use melt + query

df.melt('User').query('value > 0')

     User      variable  value
0    Lara          M&Ms      3
1   Daisy          M&Ms      4
7   Sarah      Skittles      3
9   Daisy      Snickers      1
13  Daisy   Laffy Taffy      1
18   Alyx  Caramel Chew      5

answered Nov 05 '20 at 20:28

rafaelc

57,686
15
58
82

1

Thanks! It is really helpful – Yaosong Yu Nov 05 '20 at 20:36

score 0 · Answer 2 · answered Nov 05 '20 at 20:25

0

You can use df.stack()

# First set the index to user
df.set_index(‘user’, inplace =True)
# then stack
df_new = df.stack()
# reset index
df_new.reset_index(inplace=True)

Using pd.melt() is another option.

answered Nov 05 '20 at 20:25

Niels Henkens

2,553
1
12
27

pandas create paired data from a dataframe

2 Answers2