I need to fill a column’s fields, based on the other columns, in this way:
The first df has two columns – names and ages of the kids, but some rows are NaNs, however nothing should be done with the NaNs, just ignore them. But the ages should be filled in, based on the second df.
First df:
data = {'Name Kids': ['Valentina', 'Mark', 'Sofia', np.nan, 'Manny', 'Alex', 'Claire', np.nan, np.nan], 'Age Kids': [np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan, np.nan] } df = pd.DataFrame (data, columns = ['Name Kids','Age Kids']) print (df)
is this:
Name Kids Age Kids 0 Valentina NaN 1 Mark NaN 2 Sofia NaN 3 NaN NaN 4 Manny NaN 5 Alex NaN 6 Claire NaN 7 NaN NaN 8 NaN NaN
So based on the second df, the ages of the children should be inserted in the first df. However, in this df are more kids then in the first one, but the kids that are in plus should not be copied.
Second df:
data1 = {'Children Names': ['Eloise', 'Valentina', 'Brian', 'Daphne', 'Mark', 'Sofia', 'Betty', 'Manny', 'Ronnie', 'Alex', 'Claire'], 'Children Ages': [17, 13, 11, 7, 12, 3, 16, 10, 1, 5, 14] } df1 = pd.DataFrame (data1, columns = ['Children Names', 'Children Ages']) print (df1)
is this:
Children Names Children Ages 0 Eloise 17 1 Valentina 13 2 Brian 11 3 Daphne 7 4 Mark 12 5 Sofia 3 6 Betty 16 7 Manny 10 8 Ronnie 1 9 Alex 5 10 Claire 14
Therefore, the final df if the first one but modified, in such way that the kids that were also found in the second df now have their matching age.
Final df is the first one but modified:
Name Kids Age Kids 0 Valentina 13.0 1 Mark 12.0 2 Sofia 3.0 3 NaN NaN 4 Manny 10.0 5 Alex 5.0 6 Claire 14.0 7 NaN NaN 8 NaN NaN
I tried something like this, but I could not figure out how to verify for the matching names and how to copy the ages from second df to first df.
df.loc[df['Name Kids'] != np.nan, 'Age Kids'] =
How to get to the final result? I am very new to this and I would really need help if you can help me please!
Advertisement
Answer
You can use .map
to map the ages across where the names match.
df['Age Kids'] = df['Name Kids'].map(dict(df1[['Children Names', 'Children Ages']].to_numpy())