CODEWITHSUNDEEP
pythonpandas
RookiePython
RookiePython

Reputation: 145

Need to FillNa with a value from a different column on the same index

My last line is the issue. What I'm trying to do is fillna in 'New Hire' with the 'New Hire' value on the same index as 'Trainer' and the 'Trainer' value needs to be included in one of the strings in Words.

df:

   Trainer New_Hire
0  Trainer1    Hire1
1  Trainer2    Hire2
2  Trainer3    Hire3
3  Trainer4      NaN

desired df:

    Trainer New_Hire
0  Trainer1    Hire1
2  Trainer3    Hire3
3  Trainer4    Hire2

Current Code:

import pandas as pd

df = pd.DataFrame({'Trainer': ['Trainer1','Trainer2','Trainer3','Trainer4'], 
'New_Hire':['Hire1','Hire2','Hire3', 0]})

words = ['Hi, Trainer2', 'Hello Bob!', 'Bonjour, Fransico']

for trainer in df['Trainer']:
    for word in words:
        if trainer in word:
            df['New_Hire'].fillna(?))

Upvotes: 0

Views: 51

Answers (1)

sansh
sansh

Reputation: 41

I'm a little unclear as to what logic you used to arrive at your desired answer. But, I'm assuming that you want the NAs in New_Hire to be replaced with the New_Hire values whose Trainer values are present in words. And, you want the rows containing those Trainer values to be removed from the dataframe. Also, I'm assuming that only one Trainer value can appear in words. Based on that here's how I would go about it:

import pandas as pd
import numpy as np

# Note: You cannot replace 0 using fillna, hence using np.nan
df = pd.DataFrame({'Trainer': ['Trainer1','Trainer2','Trainer3','Trainer4'], 
                   'New_Hire':['Hire1','Hire2','Hire3', np.nan]})

words = ['Hi, Trainer2', 'Hello Bob!', 'Bonjour, Fransico']

# Separate the words and flatten the list
ext_words = [word.split() for word in words]
flat_words = [w for word in ext_words for w in word]

# Extract replacement value
replace_val = df.loc[df['Trainer'].isin(flat_words), 'New_Hire'].values[0]

# Fill NAs with replacement value and remove the rows that match condition
df['New_Hire'].fillna(replace_val, inplace=True)
df = df.loc[~ df['Trainer'].isin(flat_words)]
print(df)

This would give:

    Trainer New_Hire
0  Trainer1    Hire1
2  Trainer3    Hire3
3  Trainer4    Hire2

Upvotes: 1

Related Questions