Filter values in rows contain specific string for further calculation

Question

This is my dataframe:

In [2]: fruits = pd.DataFrame({"apple_price": [100, 100, 200, 500, 100, 600, 200], 
   ...:                        "cherry_price": [2, 3, 1, 0, 2, 1, 1], 
   ...:                        "banana_price": [2, 4, 5, 2, 3, 5, 3], 
   ...:                        "prices": ["apple_price", "apple_price", "cherry_price", "banana_price", "cherry_price",
   ...:  "banana_price", "apple_price"], 
   ...:                        "price_fruits": [100, 100, 100, 2, 3, 5, 200]})

In [3]: fruits
Out[3]:
   apple_price  cherry_price  banana_price        prices  price_fruits
0          100             2             2   apple_price           100
1          100             3             4   apple_price           100
2          200             1             5  cherry_price           100
3          500             0             2  banana_price             2
4          100             2             3  cherry_price             3
5          600             1             5  banana_price             5
6          200             1             3   apple_price           200

Basically the prices for apple ["apple_price"] must be divided by 100 (as the prices in apple_price are in cents instead of Euro) whereas the prices for the other fruits should remain unchanged.

So, this would be my expect output:

In [5]: fruits_ad
Out[5]:
   apple_price  cherry_price  banana_price        prices  price_fruits  pr_fruits_adjusted
0          100             2             2   apple_price           100                   1
1          100             3             4   apple_price           100                   1
2          200             1             5  cherry_price           100                   1
3          500             0             2  banana_price             2                   2
4          100             2             3  cherry_price             3                   3
5          600             1             5  banana_price             5                   5
6          200             1             3   apple_price           200                   2

What have you tried so far? – Nicholas Hunter May 10 '21 at 12:17 — Nicholas Hunter, May 10 '21 at 12:17
why `price` for `cherry` is 1 instead of 100? – ThePyGuy May 10 '21 at 12:22 — ThePyGuy, May 10 '21 at 12:22

score 1 · Answer 1 · answered May 10 '21 at 12:51

1

Applying the adjustment on the price based on a condition can be done like this:

fruits['pr_fruits_adjusted'] = np.where((fruits.price_fruits >=100), fruits['price_fruits']/100, fruits['price_fruits']).astype('int32')

You can change the condition based on your requirements. Here I have written a condition to apply adjustment if the price is greater than or equal to 100. fruits.price_fruits >=100

answered May 10 '21 at 12:51

Kabilan Mohanraj

1,856
1
7
17

It should be a bit more dynamic something like lamba x: fruits.prices.str.startswith("apple_")/100 if x == fruits.prices.str.startswith("apple_") else fruits.prices. – RMNB May 10 '21 at 13:03
What I can understand is that you wish to adjust based on a string column. But as I can see from the data you have provided, cherry_price is given as cents as well as Euros. Wouldn't that be contradictory? For example, when you adjust cherry_price, the one in Euro will also get adjusted. – Kabilan Mohanraj May 10 '21 at 13:09

RMNB · Answer 2 · 2021-05-10T15:27:11.757

Sorry guys I was in a hurry and messed up my example dataframe.

So there you guy:

You start here...

In [2]: fruits = pd.DataFrame({"Variety": ["apple_price", "cherry_price", "banana_price", "apple_price", "banana_price"
   ...: ], 
   ...:                        "Price": [100, 2, 3, 200, 3]})

In [3]: fruits
Out[3]:
        Variety  Price
0   apple_price    100
1  cherry_price      2
2  banana_price      3
3   apple_price    200
4  banana_price      3

and this should be the expected output:

In [4]: fruits_adj = pd.DataFrame({"Variety": ["apple_price", "cherry_price", "banana_price", "apple_price", "banana_pr
   ...: ice"], ^M
   ...:                            "Price": [100, 2, 3, 200, 3], ^M
   ...:                            "Price_Adj": [1, 2, 3, 2, 3]})

In [5]: fruits_adj
Out[5]:
        Variety  Price  Price_Adj
0   apple_price    100          1
1  cherry_price      2          2
2  banana_price      3          3
3   apple_price    200          2
4  banana_price      3          3

In a nutshell the code i am looking for shout split the string in column "Variety" and check if i contains "apple" and if this condition == True you should divide the price for apple_price by 100 else take the price for the according fruit from column "Price".

So this would be my attempt maybe you have something more elegant:

In [6]: chk = fruits_adj['Variety'].str.contains(r'apple', na=True)

In [7]: fruits.loc[chk, "Price_Adj"] = fruits["Price"] / 100

In [8]: fruits.loc[~chk, "Price_Adj"] = fruits["Price"]

In [9]: fruits
Out[9]:
        Variety  Price  Price_Adj
0   apple_price    100        1.0
1  cherry_price      2        2.0
2  banana_price      3        3.0
3   apple_price    200        2.0
4  banana_price      3        3.0

Cheers Ra

Filter values in rows contain specific string for further calculation

2 Answers2