使用.map和lambda从另一个数据帧检索信息

Question

我有两个数据帧。product_list:

Local Product Code  Country EAN Number      
163 IE492583            Ireland 483759374
171 IE490275            Ireland 103642947
179 IE492648            Ireland 103856384
183 IE498634            Ireland 946294729
189 IE499584            Ireland 649265104
... ... ... ... ... ...

和inventory_helper:

Product Quantity    Date        Country  Comment    EAN (for IE)
188         1256        2022-09-01  IE       NaN        483759374
189         7032        2022-09-01  IE       NaN        103642947
190         12113       2022-09-01  IE       NaN        103856384
191         1822        2022-09-01  IE       NaN        946294729
192         3094        2022-09-01  IE       NaN        649265104

两个数据帧都包含EAN编号。我想将相应的Local Product Code从product_list添加到inventory_helper。请记住，数据帧product_list包含多个国家/地区的数据，因此可能包含具有相同EAN编号但适用于不同国家/地区(因此也适用于不同的本地产品代码(的多行。我尝试使用：

inventory_helper['Product'] = np.where(inventory_helper['Country'] != 'IE',
(inventory_helper['Country'] + inventory_helper['Product']),
inventory_helper['EAN (for IE)'].map(lambda x: product_list[(product_list['Country'] == 'Ireland') & (product_list['EAN Number'] == x)]['Local Product Code'])
)

然而，我在Product列中的输出包含一个系列，而不仅仅是产品代码：

Product                                        Quantity Date        Country Comment EAN (for IE)
188 217 IE492583 Name: Local Product Code, dty...   1256    2022-09-01  IE      NaN     483759374
189 497 IE490275 Name: Local Product Code, dty...   7032    2022-09-01  IE      NaN     103642947
190 498 IE492648 Name: Local Product Code, dty...   12113   2022-09-01  IE      NaN     103856384
191 511 IE498634 Name: Local Product Code, dty...   1822    2022-09-01  IE      NaN     946294729
192 370 IE499584 Name: Local Product Code, dty...   3094    2022-09-01  IE      NaN     649265104

我做错了什么？

Answer 1

您可能需要使用类似此处提到的｛Country to 2_letter_code｝来映射product_list数据帧中的"Country"列，然后基于['Country', 'EAN Number']进行合并

假设Country-2_letter_code dict是country_codes，那么解决方案是：

inventory_helper.merge(product_list.assign(Country_Code=product_list['Country'
].map(country_codes)), left_on=['Country', 'EAN'
], right_on=['Country_Code', 'EAN Number'],
how='left')