Adding value to dataframe based on dict-CodePudding

I have problem with a list of dicts like this:

list_validation = [{'name': 'Alice', 'street': 'Baker Street', 'stamp': 'T05', 'city': 'London'}, {'name': 'Margaret', 'street': 'Castle Street', 'stamp': 'T01', 'city': 'Cambridge'}, {'name': 'Fred', 'street': 'Baker Street', 'stamp': 'T012', 'city': 'London'}]

Now in my dataframe there are columns

df = pd.DataFrame({'name': ['Fred', 'Jane', 'Alice', 'Margaret'], 'street': ['Baker Street', 'Downing Street', 'Baker Street', 'Castle Street'],
'stamp': ['', 'T03', '', ''],
'city': ['', 'London', '', ''],
'other irrelevant columns for this task' : [1, 2, 3, 4]
})

What I want is to fill the gaps of the stamp columns and the city columns, so it looks like this:

df2 = pd.DataFrame({'name': ['Fred', 'Jane', 'Alice', 'Margaret'], 'street': ['Baker Street', 'Downing Street', 'Baker Street', 'Downing Street'],
'stamp': ['T012', 'T03', 'T05', 'T01'],
'city': ['London', 'London', 'London', 'Cambridge'],
'other irrelevant columns for this task' : [1, 2, 3, 4]
})

I have been trying this, but it is not working and going great:

new_dict = df[['name', 'street', 'stamp', 'city']].to_dict()
list(new_dict)
for l in list_validation:
    for row in new_dict:
        if l['name'] == row['name'] and l['street'] == row['street']:
            row['stamp'] = l['stamp']
            row['city'] = l['city']

CodePudding user response：

This is one approach iterate over each row in the dataframe and fill the missing values from the list.

List Definition:

list_validation = [{'name': 'Alice', 'street': 'Baker Street', 'stamp': 'T05', 'city': 'London'}, {'name': 'Margaret', 'street': 'Castle Street', 'stamp': 'T01', 'city': 'Cambridge'}, {'name': 'Fred', 'street': 'Baker Street', 'stamp': 'T012', 'city': 'London'}]

DataFrame Definition:

df = pd.DataFrame({'name': ['Fred', 'Jane', 'Alice', 'Margaret'], 'street': ['Baker Street', 'Downing Street', 'Baker Street', 'Castle Street'],
'stamp': ['', 'T03', '', ''],'city': ['', 'London', '', ''],'other irrelevant columns for this task' : [1, 2, 3, 4]})

Logic

for r,i in df.iterrows():
        
        name_in_df = i['name']
    
        # if pd.isna(i['stamp']):
        if not i['stamp']:        
            for j in list_validation:
                if j['name'] == name_in_df:
                    value_in_list = j['stamp']
                    df.loc[r,'stamp'] = value_in_list
                    break
                
        # if pd.isna(i['city']):
        if not i['city']:
            name_in_df = i['name']
            for j in list_validation:
                if j['name'] == name_in_df:
                    value_in_list = j['city']
                    df.loc[r,'city'] = value_in_list
                    break
    
                
    df

CodePudding user response：

Here is the approach that I would use

Set the index of given dataframe to name and street
Create a new dataframe from list_validation and set its index to name and street as well.
Mask the empty values in df1 and fill the masked values using the values from df2

c = ['name', 'street']
df1 = df.set_index(c)
df2 = pd.DataFrame(list_validation).set_index(c)

df1.mask(df1.eq('')).fillna(df2).reset_index()

       name          street stamp       city  other irrelevant columns for this task
0      Fred    Baker Street  T012     London                                       1
1      Jane  Downing Street   T03     London                                       2
2     Alice    Baker Street   T05     London                                       3
3  Margaret   Castle Street   T01  Cambridge                                       4