Python Pandas - Slice DataFrame based on Another Table's Values to Match to Column Name

user_6873107 0
Viewed 39

I have two dataframes, df_stats and df_ratings.

df_stats looks like this

Fruit Rating_Threshold_Low Rating_Threshold_High
1 Apple 4 7
2 Banana 5 9
3 Kiwi 6 8

df_ratings looks like this (the first column is the Fruit and each subsequent column represents a rating.

Fruit 1 2 3 4 5 6 7 8 9 10
1 Apple 2 4 7 13 2 0 16 1 9 22
2 Banana 6 4 2 1 8 7 5 3 9 0
3 Kiwi 21 4 3 6 8 9 9 8 7 5

What my goal is to get the sum of each the number of ratings within the rating threshold for each fruit (each fruit's rating threshold is different). In other words, I want to add the column, Rating_Threshold_Sum in df_stats, which computes the sum of the ratings within the threshold df_ratings. For example, for Apple, the Rating threshold is between 4 and 7 (inclusive), so the Rating_Threshold_Sum would be 13+2+0+16 = 31.

So as a result, df_stats would have the Ratings_Threshold_Sum column:

Fruit Rating_Threshold_Low Rating_Threshold_High Rating_Threshold_Sum
1 Apple 4 7 31
2 Banana 5 9 32
3 Kiwi 6 8 26

I am not sure how to exactly do that, I know that I may have to use df.apply with a custom function, or looping through each row, but aside from that, I'm not sure the best way to tackle this problem. Any advice / direction would be much appreciated. Thank you!

pandas dataframe python
1 Answers

You could do something like this

sums = []
for i in range(len(df_stats)):
   min_v, max_v = df_stats["Rating_Threshold_Low"].values()[i], df_stats["Rating_Threshold_High"].values()[i]  
   values = []
   for z in range(min_v, max_v+1):
      x = df_ratings[str(z)][i]
      values.append(x)
   sums.append(sum(values))
df_stats["Rating_Threshold_Sum"] = sums

This is really complicated and there is probably a better way to do it but it should work.

user_104998130
Related