Using a string value from one column to search another column and pull out corresponding value

wtialyll 0
Viewed 22

A somewhat complicated question but I will try to explain with a df.

With the data frame created as below:

df = {'Food': ['Apple', 'orange','kiwi'], 'Bowl': ['Apple, orange, kiwi','Apple, orange, banana','Orange, apple, banana'],'Price' : [4,8,8]}
df = pd.DataFrame(df)

    Food    Bowl                    Price
0   Apple   Apple, orange, kiwi     4
1   orange  Apple, orange, banana   8
2   kiwi    Orange, apple, banana   8

I am looking to create a new column ("new") that takes the "Food" and searches the column "Bowl" to see it it contains "Food". If it does then it takes the value for "Price" for the column "new". If the food appears multiple times in "Bowl" then it would be the mean of the "Price" values. If the fruit does not appear in "Bowl" this would return a NaN value in "new".

The new columns would therefore be:

new
6.67
6.67
4

So far I have attempted the below:

for i in df.index:
  if i%100 ==0:
    print(i)
  fruit = df.at[i,'Food']

  new = statistics.mean(df['Price'][df['Bowl'].str.contains(fruit,na=False,case=False)])

Any help appreciated!

contains search string python
1 Answers

Try:

tmp_df = df[["Bowl", "Price"]].copy()
tmp_df["Bowl"] = tmp_df["Bowl"].str.split(r"\s*,\s*", regex=True)
tmp_df = tmp_df.explode("Bowl")
tmp_df["Bowl"] = tmp_df["Bowl"].str.lower()
tmp_df = tmp_df.groupby("Bowl").mean()

df["new"] = df.Food.str.lower().map(tmp_df["Price"])

print(df)

Prints:

     Food                   Bowl  Price       new
0   Apple    Apple, orange, kiwi      4  6.666667
1  orange  Apple, orange, banana      8  6.666667
2    kiwi  Orange, apple, banana      8  4.000000
Andrej Kesely0
Related