基于dict向数据帧添加值

Question

我对这样的一份口述清单有问题：

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'}]

现在在我的数据框中有列

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]
})

我想要的是填补邮票栏和城市栏的空白，所以看起来是这样的：

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]
})

我一直在尝试这一点，但它不起作用，而且进展得很顺利：

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']

Answer 1

这是一种迭代数据帧中每一行并从列表中填充缺失值的方法

列表定义：

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'}]

数据帧定义：

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]})

逻辑

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    

Answer 2

以下是我将使用的方法

• 将给定数据帧的索引设置为name和street
• 从list_validation创建一个新的数据帧，并将其索引设置为name和street
• 屏蔽df1中的空值，并使用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