Python, Pandas: check each element in list values of column to exist in other dataframe

user_11932905 0
Viewed 521

I have dataframe column with values in lists, want to add new column with filtered values from list if they are in other dataframe.

df:

df = pd.DataFrame({'a':[1,2,5,7,9],'b':[[10,1,'xxx'],[],[1,2,3],[5],[25,27]]})

**a**|**b**
:-----:|:-----:
1|[10, 1, 'xxx']
2|[]
5|[1, 2, 3]
7|[5]
9|[25, 27]

df2:

df2 = pd.DataFrame({'d':[324,21,4353,345,4535,23],'e':[5,1,23,25,25,'xxx']})

I need to add new column with filtered column b in df so that it contains lists with only elements which are in df2 column e.

Result:

**a**|**b**|**c**
:-----:|:-----:|:-----:
1|[10, 1, 'xxx']|[1,'xxx']
2|[]|[]
5|[1, 2, 3]|[1]
7|[5]|[5]

Speed is crucial, as there is a huge amount of records.

What I did for now:

  1. created a set of possible values
l = list(df2['e'].unique())
  1. Try to use df.assign with comprehensive lists, but it's not quite working and too slow.
df.assign(mapped=[[x for x in row if x in l] for row in df.b])

Appreciate any help.

UPD

In lists and df2 not always integer values, sometimes it's strings.

pandas dataframe mapping list python
3 Answers

You can try casting to str and then series.str.findall

l = map(str,df2['e'].unique())
df['c'] = df['b'].astype(str).str.findall('|'.join([fr"\b{i}\b" for i in l]))

or shorter on the findall pattern courtesy @Shubham:

l = map(str,df2['e'].unique())
df['c'] = df['b'].astype(str).str.findall(fr"\b({'|'.join(l)})\b")

print(df)

   a             b     c
0  1  [10, 1, 100]   [1]
1  2            []    []
2  5     [1, 2, 3]   [1]
3  7           [5]   [5]
4  9      [25, 27]  [25]
user_98406370

You can use np.intersect1d() as follows:

l = df2['e'].unique()     # unique() already returns ndarray, no need to use list()

df['c'] = df['b'].map(lambda x: np.intersect1d(x, l))


print(df)

   a             b     c
0  1  [10, 1, 100]   [1]
1  2            []    []
2  5     [1, 2, 3]   [1]
3  7           [5]   [5]
4  9      [25, 27]  [25]

Edit

According to OP's edit on mixed type data, we just need to modify the way the unique values of column e of df2 is extracted. No need to change the main code of creating column c.

l = list(map(str, df2['e'].unique()))        # only change this

df['c'] = df['b'].map(lambda x: np.intersect1d(x, l))
 

print(df)


   a             b         c
0  1  [10, 1, xxx]  [1, xxx]
1  2            []        []
2  5     [1, 2, 3]       [1]
3  7           [5]       [5]
4  9      [25, 27]      [25]
user_150706970

Today, I have worked on a similar problem where I need to fetch rows containing a value we are looking for, but the data frame's columns' values are in the list format.

This is the solution I have come up with.

fetched_rows = df.loc [ df['column_name'].map( lambda x : True if check_element in x else False) == True ]

Where column_name ---> the column name that we need to look into

check_element ---> the value that we use to check whether it exists.

user_105795530
Related