[FIXED] Apply for loop while replacing NaN values in a dataframe with values from another column

Issue

Lets say that i have a dataframe like this:

    Col1                       Col2
0  AAA_BBB_123_DD              123
1  AAA_123_BBB_DD              123
2  123_AAA_BBB_DD              123
3  123_AAA_BB_DDD              NaN
4  456_AAA_BBB_DD              456
5  AAA_BBB_456_DD              456
6  AAA_789_BBB_DD              NaN
7  AAA_BBB_789_DD              789
8  AAA_000_BBB_DD              NaN

What I want is, for NaN values in Col2, I want to check the string in Col1, split it by "_" and if it contains something put it to Col2.

In a normal scenario without dataframe if i have a string like this 123_AAA_BB_DDD i would do this:

str = 123_AAA_BB_DDD
values = ['123','456','789']
split_str = str.split("_")
for i in split_str:
    if any(value in i for value in values):
        col2_value = i
    else:
        col2_value = 'Not Found'

My desirable output would look like this:

    Col1                       Col2
0  AAA_BBB_123_DD              123
1  AAA_123_BBB_DD              123
2  123_AAA_BBB_DD              123
3  123_AAA_BB_DDD              123
4  456_AAA_BBB_DD              456
5  AAA_BBB_456_DD              456
6  AAA_789_BBB_DD              789
7  AAA_BBB_789_DD              789
8  AAA_000_BBB_DD           Not Found

EDITED:

The solution worked good for the cases where the values from the list are matched exactly to the string in Col1, eg (123 in list and 123 in Col1 string). But if i have something like this: AAA_PORT123_BBB_DD the soultion will put like 'Not Found' in the Col2, so lets say i have a df like this:

    Col1                       Col2
0  AAA_BBB_PORT123_DD        PORT123
1  AAA_123_BBB_DD              123
2  STD123_AAA_BBB_DD          STD123
3  123_AAA_BB_DDD              NaN
4  456_AAA_BBB_DD              456
5  AAA_BBB_456_DD              456
6  AAA_MAN789_BBB_DD          NaN
7  AAA_BBB_789_DD              789
8  AAA_000_BBB_DD              NaN

My desirable output would be:

    Col1                       Col2
0  AAA_BBB_PORT123_DD        PORT123
1  AAA_123_BBB_DD              123
2  STD123_AAA_BBB_DD          STD123
3  123_AAA_BB_DDD              123
4  456_AAA_BBB_DD              456
5  AAA_BBB_456_DD              456
6  AAA_MAN789_BBB_DD          MAN789
7  AAA_BBB_789_DD              789
8  AAA_000_BBB_DD            Not Found

Solution

For rows with missing values in Col2 call custom function for matched first value from list value, for run function only for matched rows use DataFrame.loc with mask in both sides:

values = ['123','456','789']

m = df['Col2'].isna()

f = lambda x: next((y for y in x.split('_') if y in values), 'Not Found')
df.loc[m, 'Col2'] = df.loc[m, 'Col1'].apply(f)
print (df)
             Col1       Col2
0  AAA_BBB_123_DD      123.0
1  AAA_123_BBB_DD      123.0
2  123_AAA_BBB_DD      123.0
3  123_AAA_BB_DDD        123
4  456_AAA_BBB_DD      456.0
5  AAA_BBB_456_DD      456.0
6  AAA_789_BBB_DD        789
7  AAA_BBB_789_DD      789.0
8  AAA_000_BBB_DD  Not Found

Answered By - jezrael