Pandas pick an item from list column and search in other list column

I have a dataframe like as shown below

sample_df = pd.DataFrame({'single_proj_name': [['jsfk'],['fhjk'],['ERRW'],['SJBAK']],
                          'single_item_list': [['ABC_123'],['DEF123'],['FAS324'],['HSJD123']],

I would like to do the below

a) Pick the value from single_item_list for each row

b) search that value in multi_item_list column of the same row

c) If match found, keep only that value in multi_item_list and remove all other non-matching values from multi_item_list

d) Based on the position of the match item, look for corresponding value in multi_id list and keep only that item. Remove all other position items from the list

So, I tried the below but it doesn’t work

def func(df):
    return list(set(sample_df['single_item_list']) - set(sample_df['multi_item_list']))

sample_df['col3'] = sample_df.apply(func, axis = 1)

I expect my output to be like as below

enter image description here

>Solution :

You can explode the two lists in parallel (pandas 1.3+), and filter:

  .explode(['multi_item_list', 'multi_id'])
  .loc[lambda d: d['single_item_list'].str[0].eq(d['multi_item_list'])]

If you want your lists back:

  .explode(['multi_item_list', 'multi_id'])
  .loc[lambda d: d['single_item_list'].str[0].eq(d['multi_item_list'])]
  .assign(multi_item_list=lambda d: d['multi_item_list'].apply(lambda x: [x]),
          multi_id=lambda d: d['multi_id'].apply(lambda x: [x]),


  single_proj_name single_item_list single_id           multi_proj_name multi_item_list multi_id
0           [jsfk]        [ABC_123]    [1234]         [AAA, VVVV, SASD]       [ABC_123]  [29481]
1           [fhjk]         [DEF123]    [5678]  [QEWWQ, SFA, JKKK, fhjk]        [DEF123]  [57567]
2           [ERRW]         [FAS324]   [91011]              [ERRW, TTTT]        [FAS324]   [8775]
3          [SJBAK]        [HSJD123]  [121314]             [SJBAK, YYYY]       [HSJD123]  [43512]

alternative using a list comprehension:

sample_df[['multi_item_list', 'multi_id']] = \
pd.DataFrame([next(([[x], [c[i]]] for i,x in enumerate(b) if x==a[0]), None)
              for a, b, c in
              columns=['multi_item_list', 'multi_id'])

Leave a Reply