Lookup for a value in one column and return corresponding row value from another column

Developers
,
1

Hello Everyone,

I know it is a fundamental python question, but I couldn’t solve it.
Any help will be appreciated It is part of my dynamoscript development.

I hope this can help to understand my issue.

(Plese find the image below)
I have total 1058 records in list1[ID] which has matching records in list2['Positionnummer'].
So when both equals I want to get corresponding value from list2['New Bewehrungsgehalt'] and append to out output list i.e value, if not equal append old(default) value to the list value.
At the end I want len(list1) == len(value) i.e 1058.

For example: in first iteration when U1.ST.XX from list1[ID] ==
U1.ST.XX from list2['Positionsnummer'] then value.append(New Bewehrungehalt) i.e '88888888', if not matching append the default value.
`import pandas as pd

list1 = pd.read_excel(r"C:\Users\kuk\Downloads\Dynamo\dummy.xlsx", sheet_name='Sheet1')
list2 = pd.read_excel(r"C:\Users\kuk\Downloads\Dynamo\dummy.xlsx", sheet_name='Sheet2')

list2=((list2.dropna(subset=['Positionsnummer'])).drop_duplicates(subset=['Positionsnummer'])).reset_index()

value=[]
for i in range(len(list1)):

    for j in range(len(list2)):
        if list1["ID"][i] == list2["Positionsnummer"][j]:
            value.append(list2['New Bewehrungsgehalt'])
            break
        else:
            value.append(list2['Bewehrungsgehalt'])
            break`

But I am not getting as I wanted. I tried using break, continue everything, but it is not working.

  • When using break, it’s appending 6 times when it is not matching
  • When using continue, appending ‘I don’t know’ many times.

Please find the image here
Please find the image here1420×834 52.9 KB

@Vikram_Subbaiah @SeanP @Draxl_Andreas , There was no help on stack overflow when I tried it.
I know I will never be let down by the Dynamo community. Now I’m hoping you guys can help me.

2

Suggest trying zip iteration to run through the columns in parallel, or a range to the length of the first column, then indexing other columns when you find matches. Probably more computer friendly ways to do it, but its a valuable technique to learn anyway.

2 Likes
3

Hello,
a solution using dataFrame update

image
image1483×960 79.5 KB 

import sys
import clr
clr.AddReference('Python.Included')
import Python.Included as pyInc
path_py3_lib = pyInc.Installer.EmbeddedPythonHome
sys.path.append(path_py3_lib + r'\Lib\site-packages')

import pandas as pd
import numpy as np

path_xls = IN[0]
df1 =  pd.read_excel(path_xls, sheet_name='Feuil1')
df2 =  pd.read_excel(path_xls, sheet_name='Feuil2')

# add empty column with same name as df2
df1['Value'] = np.nan
# set index df1
df1 = df1.set_index('ID')
# remove empty rows and set index df2
df2 = df2.dropna(subset =['UNIQ_ID']).set_index('UNIQ_ID')

print('df1 before update\n', df1)
print('df2\n', df2)
df1.update(df2)
print('df1 after update\n', df1)
# convert to list values
OUT = df1['Value'].values
2 Likes
4

I got solution, thanks for everyone

import pandas as pd
  
list1 = pd.DataFrame()
list2 = pd.DataFrame()

list1['ID'] = range(10)
list1['Bewehrungsgehalt']= range(10)
list2["Positionsnummer"] = [4,5,1,3]
list2['New Bewehrungsgehalt']= [4000, 5000, 1000, 3000] 
 

value = []

for i in range(len(list1["ID"])):
    if list1["ID"][i] in list(list2["Positionsnummer"]):
        new_val = list2[list2["Positionsnummer"]==list1["ID"][i]]["New Bewehrungsgehalt"].tolist()[0]
        value.append(new_val)
    else:
        old_val = list1['Bewehrungsgehalt'][i]
        value.append(old_val)

print(len(value))