python - Pandas/Python - 数据帧和字典之间的多重条件匹配

Question

我有 Pandas 数据框和一个每个键有两个值的字典。如何检查字典中的两个值与数据框中的两个列值之间的匹配？如果存在匹配，我需要返回匹配的字典元素的键和第三个数据帧列值。

示例数据框:

MACHINE     MONTH       possible hours

M301        December    0.051136
M304        December    0.215909
M305        December    0.230114
M306        December    0.198864
M307        December    0.130682
M400        November    0.122024
M400        December    0.252841
M714        November    0.005952

字典示例:

mapping_dict = {"O36": ("November", "M101"), "O37": ("November", "M102"), "O38": ("November", "M103"), "O39": ("November", "M104"),
             "O40": ("November", "M105"), "O41": ("November", "M106"), "O42": ("November", "M107"), "O43": ("November", "M201"),
             "O44": ("November", "M202"), "O45": ("November", "M203"), "O46": ("November", "M204"), "O47": ("November", "M205"),
             "O48": ("November", "M206"), "O49": ("November", "M207"), "O50": ("November", "M301"), "O51": ("November", "M302"),
             "P36": ("December", "M101"), "P37": ("December", "M102"), "P38": ("December", "M103"), "P39": ("December", "M104"),
             "P40": ("December", "M105"), "P41": ("December", "M106"), "P42": ("December", "M107"), "P43": ("December", "M201"),
             "P44": ("December", "M202"), "P45": ("December", "M203"), "P46": ("December", "M204"), "P47": ("December", "M205"),
             "P48": ("December", "M206"), "P49": ("December", "M207"), "P50": ("December", "M301"), "P51": ("December", "M302"),
             "P52": ("December", "M303"), "P53": ("December", "M304"), "P54": ("December", "M305"), "P55": ("December", "M306"),
             "P56": ("December", "M307"), "P57": ("December", "M400"), "P58": ("December", "M401"), "P59": ("December", "M402"),
             "P60": ("December", "M403"), "P61": ("December", "M404"), "P62": ("December", "M405"), "P63": ("December", "M406"),
             "P64": ("December", "M407"), "P65": ("December", "M712"), "P66": ("December", "M713"), "P67": ("December", "M714")}

我需要写入possible hours的值到 Excel 电子表格中的特定单元格。该单元格是 key在字典里。必须写入的数据是possible hours专栏。

例如，在 df 和 dict 中检查 M400 时和December ，我需要返回0.252841和P57 。如果有帮助，df 和 dict 中的所有值都是字符串(或对象)。

Answer 1

将左连接与merge结合使用，并将嵌套字典转换为帮助器DataFrame，省略列名称，因为通过df1之间的列名称交集进行合并> 和 df:

df1 = pd.DataFrame([(k, v1, v2) for k, (v1, v2) in mapping_dict.items()], 
                    columns = ['val','MONTH','MACHINE'])
df = df.merge(df1, how='left')
print (df)
  MACHINE     MONTH  possible hours  val
0    M301  December        0.051136  P50
1    M304  December        0.215909  P53
2    M305  December        0.230114  P54
3    M306  December        0.198864  P55
4    M307  December        0.130682  P56
5    M400  November        0.122024  NaN
6    M400  December        0.252841  P57
7    M714  November        0.005952  NaN

<小时/>

df1 = df.merge(df1, how='left').dropna(subset=['val'])[['possible hours','val']]
print (df1)
   possible hours  val
0        0.051136  P50
1        0.215909  P53
2        0.230114  P54
3        0.198864  P55
4        0.130682  P56
6        0.252841  P57

如果需要功能:

def lookup(machine, month):
    c = [k for k,(v1, v2) in mapping_dict.items() if (v1 == month) and (v2 == machine)]
    h = df.loc[df['MACHINE'].eq(machine) & df['MONTH'].eq(month), 'possible hours']
    try:
        return(c[0], h.iat[0])
    except:
        return ('no match')

print (lookup('M400','December'))
('P57', 0.252841)

print (lookup('M4','December'))
no match