KeyError: 'One or more row labels was not found' >> lookup python pandas

Question

I have a problem with my script which generates KeyError: 'One or more row labels was not found'

df1 = pd.DataFrame({'Destcode' : ['A','B','C','D','E','F','G'],
                     'City A' : ['Available','Available','Available','Available','Not Available','Not Available','Available'],
                     'City B' : ['Not Available','Available','Not Available','Available','Not Available','Not Available','Available'],
                     'City C' : ['Available','Available','Not Available','Available','Not Available','Available','Available']})

df2 = pd.DataFrame({'Destcode' : ['C','F','G','D','E'],
                     'Origin' : ['City A','City C','City A','City B','City D']})

so, i have 2 DataFrame.

DataFrame 1

df1
   Destcode       City A         City B         City C
0     A       Available      Not Available   Available
1     B       Available      Available       Available
2     C       Available      Not Available   Not Available
3     D       Available      Available       Available
4     E       Not Available  Not Available   Not Available
5     F       Not Available  Not Available   Available
6     G       Available      Available       Available

DataFrame 2

df2
    Destcode    Origin
0      C        City A
1      F        City C
2      G        City A
3      D        City B
4      E        City D

And I run this script

df2['Cek Available'] = df1.set_index('Destcode').lookup(df2.Destcode, df2.Origin)

And I get an error enter image description here

I know the problem is in Origin City D which is not in DataFrame df1.

How do you do that if the data you are looking for does not exist, it will return the value "Not Available"? Please help me to solve this problem

   Destcode   Origin    Cek Available
0      C      City A          ?
1      F      City C          ?
2      G      City A          ?
3      D      City B          ?
4      E      City D          ?

Answer 1

You can use try/except and a for loop:

cek = []
df1 = df1.set_index('Destcode')
for c, o in zip(df2['Destcode'], df2['Origin']):
    try:
        x = df1.loc[c,o]
    except:
        x = np.nan

    cek.append(x)

df2['Cek Available'] = cek

Output:

  Destcode  Origin Cek Available
0        C  City A     Available
1        F  City C     Available
2        G  City A     Available
3        D  City B     Available
4        E  City D           NaN