Skip to content
Advertisement

Search for column values in another column and assign a value from the next column from the row found to another column

I am working with Pandas and am trying to populate a column with the values in another column, where another column contains the same value as in a specific column.

Example:

>>> df = pd.DataFrame({'StringToCheck': ['10T','125T', '',''],
                   'FromNumber' :['','','',''],
                   'ToNumber' : ['','','',''],
                   'CheckStringHere': ['AAA_ER', 'FGGR_DBC', '10T', '125T'],
                   'AssociatedValue1': ['','','56','16'],
                   'AssociatedValue2': ['','','58','24']})
>>> df
  StringToCheck FromNumber ToNumber CheckStringHere AssociatedValue1 AssociatedValue2
0           10T                              AAA_ER                                  
1          125T                            FGGR_DBC                                  
2                                               10T               56               58
3                                              125T               16               24

I want to search for each value in StringToCheck inside the CheckStringHere column, and assign the value from the AssociatedValue1 on the row found to the FromNumber column but on the row where the value in StringToCheck is.

I have tried

>>> df['FromNumber'] = df[df['CheckStringHere'].isin(df['StringToCheck'])]['AssociatedValue1']
>>> df
  StringToCheck FromNumber ToNumber CheckStringHere AssociatedValue1 AssociatedValue2
0           10T        NaN                   AAA_ER                                  
1          125T        NaN                 FGGR_DBC                                  
2                       56                      10T               56               58
3                       16                     125T               16               24

As you see, the values have been added in FromNumber column, but on the row where the values from StringToCheck were found in CheckStringHere, I need to have

  StringToCheck FromNumber ToNumber CheckStringHere AssociatedValue1 AssociatedValue2
0           10T        56                    AAA_ER                                  
1          125T        16                  FGGR_DBC                                  
2                                               10T               56               58
3                                              125T               16               24

Next, if it is possible to get the values from AssociatedValue2 to ToNumber column simultaneously, it would be a real treat.

P.S.
I am out of ideas what is wrong with this question, I researched and tried a lot of solutions, explained my approach and what did not work. Downvoting questions like this is definitely wrong.

Advertisement

Answer

You can try creating a dictionary from columns ['CheckStringHere','AssociatedValue1'] and replace values from StringToCheck column:

d = dict(df[['CheckStringHere','AssociatedValue1']].to_numpy())
df['FromNumber'] = df['StringToCheck'].replace(d)
#or df['FromNumber'] = df['StringToCheck'].map(d).fillna(df['FromNumber'])

print(df)
  StringToCheck FromNumber ToNumber CheckStringHere AssociatedValue1  
0           10T         56                   AAA_ER                    
1          125T         16                 FGGR_DBC                    
2                                               10T               56   
3                                              125T               16   

  AssociatedValue2  
0                   
1                   
2               58  
3               24  
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement