python pandas dataframe index match

Question

In a python pandas dataframe "df", I have the following three columns:

song_id | user_id | play_count

I have a rating table I invented based on play_count (how many times a user listened to a song):

play_count | rating
1-33       | 1
34-66      | 2
67-99      | 3   
100-199    | 4
>200       | 5

I am trying to add a column "rating" to this table based on play count. For example, if play_count=2, the rating will be "1".

So it looks like this

song_id | user_id | play_count | rating
X232    | u8347   | 2          | 1
X987    | u3701   | 50         | 2
X271    | u9327   | 10         | 1
X523    | u1398   | 175        | 4

In excel I would do this with match/index, but I don't know how to do it in python/pandas.

Would it be a combination of an if/else loop and isin?

score 3 · Accepted Answer · 2016-05-30 15:26:36Z

3

You need the endpoints of those ranges like you would need in Excel:

import numpy as np
bins = [1, 33, 66, 99, 199, np.inf]

Then you can use pd.cut to find the corresponding rating:

pd.cut(df['play_count'], bins=bins, include_lowest=True, labels=[1, 2, 3, 4, 5]).astype(int)

I added astype(int) at the end because pd.cut returns a categorical series so you cannot do arithmetic calculations on it.

edited May 30, 2016 at 15:26

answered May 30, 2016 at 15:20

user2285236

Sign up to request clarification or add additional context in comments.

1 Comment

jeangelj Over a year ago

Great, I just did this df['rating']= pd.cut(df['play_count'], bins=bins, include_lowest=True, labels=[1, 2, 3, 4, 5]).astype(int) and it works! Thank you very much for the quick reply, very helpful! Now I can create a content filtering recommendation model

jeffery_the_wind · Accepted Answer · 2016-05-30 15:19:26Z

0

I think if you change your play_count tables to use min/max values, like this:

playcount:

min | max | rating
1   |33   | 1
34  |66   | 2
67  |99   | 3   
100 |199  | 4
200 |np.inf  | 5

of course you need to import numpy as np

Then you can do something like this:

df['rating'] = play_count[(df['play_count'] >= play_count['min']) & (df['play_count'] <= play_count['max'])].rating

answered May 30, 2016 at 15:19

jeffery_the_wind

18.3k35 gold badges109 silver badges165 bronze badges

Collectives™ on Stack Overflow

python pandas dataframe index match

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related