CODEWITHSUNDEEP
pythonpandasdataframe
powerPixie
powerPixie

Reputation: 708

Alternative values in a column to test with .isin() pandas (python)

Consider the two dataframes:

df1 = pd.DataFrame(['apple and banana are sweet fruits','how fresh is the banana','cherry from japan'],columns=['fruits_names'])
df2 = pd.DataFrame([['apple','red'],['banana','yellow'],['cherry','black']],columns=['fruits','colors'])

Then the code:

colors =[]
for f in df1.fruits_names.str.split().apply(set):   #convert content in a set with splitted words

    color = [df2[df2['fruits'].isin(f)]['colors']]  #matching fruits in a list
    colors.append(color)

I can easily insert the color in df1

df1['color'] = colors

output:
                    fruits_names            color
0  apple and banana are sweet fruits  [[red, yellow]]
1            how fresh is the banana       [[yellow]]
2                  cherry from japan        [[black]]

The problem is if column 'fruits' has alternative values, like:

df2 = pd.DataFrame([[['green apple|opal apple'],'red'],[['banana|cavendish banana'],'yellow'],['cherry','black']],columns=['fruits','colors'])

How to keep this code working?

What I've tried for last was to create a new column with separated values for fruits:

df2['Types'] = cf['fruits'].str.split('|')

And .apply(tuple) here:

color = [df[df['Types'].apply(tuple).isin(f)]['colors']]

But it doesn't match.

Upvotes: 1

Views: 353

Answers (2)

Lakshmana Perumal
Lakshmana Perumal

Reputation: 47

import pandas as pd
import numpy as np
df1 = pd.DataFrame(['green apple and banana are sweet fruits','how fresh is the banana','cherry from japan'],columns=['fruits_names'])
df2 = pd.DataFrame([['green apple|opal apple','red'],['banana|cavendish banana','yellow'],['cherry','black']],columns=['fruits','colors'])
df2['sep_colors'] = np.where(df2['fruits'], (df2['fruits'].str.split(pat='|')), df2['fruits'])


dic = dict(zip(df2['colors'].tolist(),df2['sep_colors'].tolist()))

final = []
for row in range(len(df1.fruits_names)):
    list1 = []
    for key, value in dic.items():
        for item in value:
            if item in df1.iloc[row][0]:
                list1.append(key)
    final.append(list1)

df1['colors'] = final

Upvotes: 1

Sociopath
Sociopath

Reputation: 13401

I think you need:

print(df1)

    fruits_names
0   green apple and banana are sweet fruits
1   how fresh is the banana
2   cherry and opal apple from japan

Use split and df.explode()

df2["fruits"] = df2["fruits"].apply(lambda x: x.split("|"))

df2 = df2.explode("fruits")

print(df2)

Output: 

   fruits              colors
0   green apple        red
0   opal apple         red
1   banana             yellow
1   cavendish banana   yellow
2   cherry             black

Convert it into dict

d = {i:j for i,j in zip(df2["fruits"].values, df2["colors"].values)}

Create a column based on a condition 

df1["colors"] = [[v for k,v in d.items() if k in x] for x in df1["fruits_names"]]

print(df1)

Final Output:

    fruits_names                            colors
0   green apple and banana are sweet fruits [red, yellow]
1   how fresh is the banana                 [yellow]
2   cherry and opal apple from japan        [red, black]

Upvotes: 1

Related Questions