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