Warm tip: This article is reproduced from stackoverflow.com, please click
lookup pandas python vlookup

lookup across row

发布于 2020-05-03 05:06:56

I have the following data frame

df1 =  DataFrame([['OBJ1', 10, 'BX', 'pool1', 'OBJ2'],['OBJ2', 0, '', '', 'OBJ1'],['OBJ3', 10, 'BY', 'pool2', 'OBJ4'],['OBJ4', 0, '', '', 'OBJ3'],['OBJ5', 10, 'BZ', 'pool3', '']], columns=['OBJ', 'value', 'conf', 'Res', 'Key'])

enter image description here

What I am trying to do is to:

  • check when value is 0
  • Conf and Res takes the value of the row with the key matching the OBJ
  • for example, OBJ2 value is 0 and it is key is OBJ 2, then it's conf should become BX and Res becomes pool1

I tried multiple solutions using either lookup or some other posts but nothing seems to work.

df1.loc[df1['value']==0, 'conf'] = df1.loc[df1['OBJ']==df1['Key']]['conf']

failed as I realized this is looking for rows with OBJ = Key

Questioner
Philip Fawzy
Viewed
23
分享
jezrael 2020-02-14 16:33

Use DataFrame.merge with left_on and right_on parameters and index column created by DataFrame.reset_index for new DataFrame, then convert index column to index by DataFrame.set_index and last set new volumns by DataFrame.loc:

m = df1['value'].eq(0)
cols = ['conf','Res']
df = (df1.reset_index().loc[m, ['index','OBJ']]
          .merge(df1, left_on='OBJ', right_on='Key')
          .set_index('index')[cols])
print (df)
      conf    Res
index            
1       BX  pool1
3       BY  pool2

df1.loc[m, cols] = df
print (df1)
    OBJ  value conf    Res   Key
0  OBJ1     10   BX  pool1  OBJ2
1  OBJ2      0   BX  pool1  OBJ1
2  OBJ3     10   BY  pool2  OBJ4
3  OBJ4      0   BY  pool2  OBJ3
4  OBJ5     10   BZ  pool3

Related issues

1
How to use python cut method to create bins, accept one parameter and return appropriate bin?
2
Create a dictionary from a list of lists with certain criteria
3
selecting columns based on row value, Python, Pandas
4
plotting count of zeros and ones in a dataframe
5
BeautifulSoup find.all() web scraping returns empty
6
python function. output a keys list from a dictionary if the key is todays date
7
Best way to perform multiple amount of Pandas lookups between two DataFrames
8
How to get the number of columns and the width of each column in a Pandas pivot table?
9
Display a column when a desired value is missing while grouping in Pandas dataframe
10
Python hide ticks but show tick labels