I want to vlookup dataframe.If value is present in another df ,keep same value,otherwise put #N/A in pandas python

Question

import pandas as pd
data = {'Car':['Jeep', 'Maruti Suzuki', 'Audi','Kia'],
        'order':[10,15,2,5]}
        # Create DataFrame
df = pd.DataFrame(data)

print (df)
output:
             Car  order
0           Jeep     10
1  Maruti Suzuki     15
2           Audi      2
3            Kia      5

data = {'Car':['Jeep', 'Maruti Suzuki','Kia'],
        'City':['M','P',"D"]
        }
df2 = pd.DataFrame(data)

print (df2)
            Car City
0           Jeep    M
1  Maruti Suzuki    P
2            Kia    D

Required output

           Car          Available     order
0           Jeep           Jeep     10
1  Maruti Suzuki  Maruti Suzuki     15
2           Audi           #N/A      2
3            Kia            Kia      5

I want to vlookup.If df['Car'] is present in df2,Keep as the same value.and if not present in df2,add #N/A in df[' Available'].

Required output

             Car      Available  order
0           Jeep           Jeep     10
1  Maruti Suzuki  Maruti Suzuki     15
2           Audi           #N/A      2
3            Kia            Kia      5

Corralien · Accepted Answer · 2021-07-16T09:46:19.890

0

Use merge with indicator parameter:

out = pd.merge(df, df2['Car'], on='Car', how='left', indicator=True)
out['Available'] = np.where(out['_merge'] == 'both', out['Car'], '#N/A')
# out = out.drop(columns='_merge')

>>> out[['Car', 'Available', 'order']]
             Car      Available  order
0           Jeep           Jeep     10
1  Maruti Suzuki  Maruti Suzuki     15
2           Audi           #N/A      2
3            Kia            Kia      5

edited Jul 16 '21 at 09:46

answered Jul 15 '21 at 19:42

Corralien

109,409
8
28
52

Thanks.But i want to add new column with car data.and if data is not available df2 then add #N/A – San Jul 16 '21 at 06:19
@San. I updated my answer. It should be good now. – Corralien Jul 16 '21 at 06:37
np.where works only if two dataframe are same in length.what if i have different length of df – San Jul 16 '21 at 08:59
`out` has always the length of `df` because you merge `left`. I should replace `df['Car']` by `out['Car']` – Corralien Jul 16 '21 at 09:34
@San, Do you have time to try the code? – Corralien Jul 19 '21 at 19:54
Hi Corralian,This code works for me.Thank u – San Sep 12 '21 at 11:22

I want to vlookup dataframe.If value is present in another df ,keep same value,otherwise put #N/A in pandas python

1 Answers1