Python: Calcualtions on rows with same variables except one

user_12899567 0
Viewed 31

I am trying to get the ratio between rows that share the same variable except one:

Dataframe example:

Brand    Model   Color   Insured    Price
------------------------------------------
X        Y       Red     True        10
X        Y       Red     False       11
Z        W       Black   True        15
Z        W       Black   False       18

Output:

Brand    Model   Color   Insured    Price   Ratio
-------------------------------------------------
X        Y       Red     True        10     1
X        Y       Red     False       11     1.1
Z        W       Black   True        15     1
Z        W       Black   False       18     1.2

So I want to separate each product and divide the prices based on the "True" value for insured.

I thought about doing a join on the Brand, Model, and Color, but don't know where to go from there.

df_product = df_product.merge(df, on=['Brand','Model','Color'], how = "left")

How could I do the above?

Thanks

pandas python
1 Answers

Use:

#filtering Trues in Insured
df1 = df.loc[df['Insured'], ['Brand','Model','Color', 'Price']]
#if possible multiple 'Brand','Model','Color' combinations remove them
#df1 = df1.drop_duplicates(['Brand','Model','Color'])
#left join by categories
df_product = df.merge(df1, on=['Brand','Model','Color'], how = "left", suffixes=('','_'))
#divide columns with remove by pop
df_product['Ratio'] = df_product['Price'].div(df_product.pop('Price_'))
print (df_product)
  Brand Model  Color  Insured  Price  Ratio
0     X     Y    Red     True     10    1.0
1     X     Y    Red    False     11    1.1
2     Z     W  Black     True     15    1.0
3     Z     W  Black    False     18    1.2
user_29010020
Related