按不同列进行查找（一次一列）

Question

表1:-这是我的主表和格式化表 [表一][1]

Table1 = pd.DataFrame({'NEWLSCasGambIN':["N  ('0','')=No/Unknown", 
                                         "N ('0','')=No/Unknown", 
                                         "N  ('0','')=No/Unknown", 
                                         "N  ('0','')=No/Unknown",
                                         "N  ('0','')=No/Unknown"],
                        'NEWLSBibDevIN':["N  ('0','')=No/Unknown", 
                                         "N  ('0','')=No/Unknown", 
                                         "N  ('0','')=No/Unknown",
                                         "N             ('1','Y') = Yes",
                                         "N  ('0','')=No/Unknown"],
                        'ADVNTG_MARITAL_STAT':["Q   <'2'> = 1+Sngl", 
                                               "Q   <'2'> = 1+Sngl", 
                                               "Q   <'2'> = 1+Sngl", 
                                               "Q   <'1'> = 1+Marrd",
                                               "Q   <'2'> = 1+Sngl"],
                        'ADVTG_TRGT_INC':["Q   <'5'> =$40-$49K", 
                                          "Q   <'6'> =$50-$74K", 
                                          "Q   <'2'> =$15-$19K", 
                                          "Q   <'7'> =$75-$99K",
                                          "Q   <'1'> =      < $15K"]})

表2:-[表2:-][2]我的映射表。我需要使用这个表映射来生成finalout表

Table2 = pd.DataFrame({'FLDNAME':["N  ('0','')=No/Unknown","N             ('1','Y') = Yes","N  ('0','')=No/Unknown","N             ('1','Y') = Yes",
                                  "Q   <'3'> = Mrrd N Sngl","Q   <'2'> = 1+Sngl","Q   <'1'> = 1+Marrd","Q   <'D'> =>$250K","Q   <'C'> =$200-$249K",
                                  "Q   <'B'> =$175-$199K","Q   <'A'> =$150-$174K","Q   <'9'> =$125-$149K","Q   <'8'> =$100-$124K","Q   <'7'> =$75-$99K",
                                  "Q   <'6'> =$50-$74K","Q   <'5'> =$40-$49K","Q   <'4'> =$30-$39K","Q   <'3'> =$20-$29K","Q   <'2'> =$15-$19K",
                                  "Q   <'1'> =      < $15K"],
                        'NEWBIN':[2, 1, 2, 1, 1, 3, 2, 2, 1, 2, 1, 2, 2, 3, 3, 3, 3, 4, 4, 4,],
                        'VARNAME':["NEWLSCasGambIN", "NEWLSCasGambIN", "NEWLSBibDevIN", "NEWLSBibDevIN", "ADVNTG_MARITAL_STAT", 
                                   "ADVNTG_MARITAL_STAT", "ADVNTG_MARITAL_STAT", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", 
                                   "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", 
                                   "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC", "ADVTG_TRGT_INC",]})
Table2

最终输出：这是映射表1和表2后得到的最终输出。我怎样才能达到这个最终的结果呢。粘贴每个表的数据帧代码

[定稿][3]

 finaloutput = pd.DataFrame({'NEWLSCasGambIN':[2,2,2,2,2],
                             'NEWLSBibDevIN':[2,2,2,1,2],
                             'ADVNTG_MARITAL_STAT':[3,3,3,2,3],
                             'ADVTG_TRGT_INC':[3,3,4,3,4]})
 
Example:-
Table 1:- 
NEWLSCasGambIN
N  ('0','')=No/Unknown
N  ('0','')=No/Unknown
N  ('0','')=No/Unknown
N  ('0','')=No/Unknown
N  ('0','')=No/Unknown
    
    Table2 consists of element mapping of  NEWLSCasGambIN
    FLDNAME                      | NEWBIN      |      VARNAME
    N  ('0','')=No/Unknown       |   2         |      NEWLSCasGambIN
    N             ('1','Y') = Yes|   1         |      NEWLSCasGambIN
    
    
    Final output :-
    NEWLSCasGambIN
    2
    2
    2
    2
    2

提前感谢。
[1] ：https://i.stack.imgur.com/wuycI.png [2] ：https://i.stack.imgur.com/6vbJU.png [3] ：https://i.stack.imgur.com/PVTeL.png

Answer 1

这里有一个解决方案：

df = pd.merge(Table1.reset_index().melt(id_vars="index"), 
              Table2, 
              left_on = ["variable", "value"], 
              right_on = ["VARNAME", "FLDNAME"])

res = pd.pivot_table(df, index = "index", columns="VARNAME", values="NEWBIN")
print(res)

输出为：

VARNAME  ADVNTG_MARITAL_STAT  ADVTG_TRGT_INC  NEWLSBibDevIN  NEWLSCasGambIN
index                                                                      
0                          3               3              2               2
1                          3               3              2               2
2                          3               4              2               2
3                          2               3              1               2
4                          3               4              2               2

（请注意，表1的一个值中有一个输入错误）