Pandas lookup by different columns (one at a time)

Question

Table 1:- this my main table with formatted one [Table 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"]})

Table 2:-[Table 2 :-][2] my mapping table. i need to use this table mapping to produced the finalout table.

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

Final Output: this is my final output i will get after mapping table1 and table2. how can i achieve this final out. pasted the DataFrame code for each table.

[Final Output][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

Thanks in advance.
[1]: https://i.stack.imgur.com/wuycI.png [2]: https://i.stack.imgur.com/6vbJU.png [3]: https://i.stack.imgur.com/PVTeL.png

Welcome to SO. Please read this post, https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples, and make sure you include the input data and expected output **as text** in your question. — Roy2012, Jun 29 '20 at 17:54
As @Roy2012 said, code, tables and example data should always be attached as text, preferably in code blocks, which can be created using triple backticks `\`\`\``. From [review](https://stackoverflow.com/review/first-posts/26544868). — sanitizedUser, Jun 29 '20 at 18:29
thanks for posting the data. Could you please explain how do you get from table1 and table2 to the output? What kind of mapping are you looking to do? — Roy2012, Jun 30 '20 at 08:25
print the NEWBIN value in table1 for each variable corresponding to FLDNAME and VARNAME to get the finaloutput:- — Deepak Kumar, Jun 30 '20 at 09:44
yes, that newbin value print in table 1 for final out put on based on FLDNAME and VARNAME in table2. example for NEWLSCasGambIN variable NEWLSCasGambIN N ('0','')=No/Unknown N ('0','')=No/Unknown N ('0','')=No/Unknown N ('0','')=No/Unknown N ('0','')=No/Unknown FLDNAME NEWBIN VARNAME N ('0','')=No/Unknown 2 NEWLSCasGambIN N ('1','Y') = Yes 1 NEWLSCasGambIN NEWLSCasGambIN 2 2 2 2 2 — Deepak Kumar, Jun 30 '20 at 11:04
It's really hard to read the last comment, which is only in a single line. Perhaps you'd like to clarify that in the body of the question, where formatting is possible. — Roy2012, Jun 30 '20 at 11:08
sure i will paste the finaloutput for first variable, so you can have a look at it.. kn — Deepak Kumar, Jun 30 '20 at 11:14

score 0 · Accepted Answer · answered Jun 30 '20 at 11:48

Here's a solution:

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)

The output is:

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

(Note that there's a typo in one of the values of Table1)

Pandas lookup by different columns (one at a time)

1 Answers1