0

This a new concept to me in pandas. I am very familiar with vlookup in Excel, so here is what I'm trying to do:

df1=                      df2=
FUR  Total   otherT          FUR   Total
123    3     no value        123    8
345   -2     no value        345    3
234    6     no value        234   -1

so all I want to do is take the total column for df2 and put it in the empty column in df1 named OtherT. 'no value' just indicates that the field is blank.

I have tried using merge

pd.merge(df1, df2, how = 'left', on = 'FUR')

I have also tried using join

df1.join(df2, on = 'FUR', how  = 'left')
onlettinggo
  • 15
  • 3
  • Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – Mako212 Jul 22 '20 at 18:32
  • 1
    is otherT always missing (if so why even bother with that column). If it's not and you want to update only if missing, perhaps modify the example so you get a solution that solves this problem and not a bunch of others that just happen to work in this specific case. – ALollz Jul 22 '20 at 18:32
  • Thanks all! appreciate your fast responses. I will look over Pandas Merging 101. – onlettinggo Jul 22 '20 at 18:41

1 Answers1

0

I know is an old question, but I had the same problem today. What I did is using apply to emulate Excel VLOOKUP function, for example:

df1=                      df2=
FUR  Total   otherT          FUR   Total
123    3     no value        123    8
345   -2     no value        345    3
234    6     no value        234   -1

df['otherT'] = df[df.otherT == 'no value']. \
               FUR.apply(lambda x: df2[df2.FUR = x] \                                                         
                        ['Total'].values[0] if len (df2[df2.FUR = x] \                                                         
                        .values) > 0 else 'no value')

To apply the VLOOKUP on 'no value' rows only:

df[df.otherT == 'no value']

And this part is to avoid an error if nothing is found.

if len (df2[df2.FUR = x].values) > 0 else 'no value'

As excel VLOOKUP this function will grab the first result.

tlentali
  • 3,407
  • 2
  • 14
  • 21
AndroidePsicokiller
  • 1
  • 2