How to implement vlookup cross referencing with pandas

user_7758804 0
Viewed 87

Given the Following DataFrames

  1. jg
 Job  Group
   1  116.0
   2  116.0
   3  116.0
   4  116.0
   5  116.0
   6  116.0
   7  124.0
   8  124.0
   9   74.0
  10   74.0
  11   74.0
  12  126.0
  13  126.0
  1. ul
 UpperGroup  LowerGroup  Time
        116         116     5
        116         124    15
        116          74    15
        116         126    15
        124         116    15
        124         124     5
        124          74    15
        124         126    15
         74         116    15
         74         124    15
         74          74     5
         74         126    15
        126         116    15
        126         124    15
        126          74    15
        126         126     5

Desired Result

  • The following table is produced with an Excel vlookup
  1. The colors are for reference
    • The Grey filled area isn't needed in the DataFrame result
  2. I'm trying to create a DataFrame / Table where the 'Job' numbers from jg are the column and the index.
    • The 'Group' value for the 'Job' number in the columns, should be used to reference 'UpperGroup' from ul
    • The 'Group' value for the 'Job' number in the index, should be used to reference 'LowerGroup' from ul
  3. The values are 'Time' from ul corresponding to 'UpperGroup' and 'LowerGroup'.
    • 'UpperGroup' = 116 and 'LowerGroup' = 224, map to 'Time' = 15

enter image description here

Reproducible DataFrames

import pandas as pd

ul_data = {'UpperGroup': [116, 116, 116, 116, 124, 124, 124, 124, 74, 74, 74, 74, 126, 126, 126, 126],
           'LowerGroup': [116, 124, 74, 126, 116, 124, 74, 126, 116, 124, 74, 126, 116, 124, 74, 126],
           'Time': [5, 15, 15, 15, 15, 5, 15, 15, 15, 15, 5, 15, 15, 15, 15, 5]}
ul = pd.DataFrame(ul_data)

jg_data = {'Job': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13],
           'Group': [116.0, 116.0, 116.0, 116.0, 116.0, 116.0, 124.0, 124.0, 74.0, 74.0, 74.0, 126.0, 126.0]}
jg = pd.DataFrame(jg_data)

Research

  • I have reviewed several of the questions from the search, but they don't really seem to fit, and I just can't think of a way to bring it all together.
    1. Google - pandas vlookup site:stackoverflow.com
    2. Considered merge/join
    3. Considered map
vlookup pandas-groupby pandas python
1 Answers

Here's one way to get the desired results.

ul.set_index(['UpperGroup', 'LowerGroup'])['Time'].unstack().reindex(index=jg['Group'], columns=jg['Group'])

Output:

Group  116.0  116.0  116.0  116.0  116.0  116.0  124.0  124.0  74.0   74.0   74.0   126.0  126.0
Group                                                                                           
116.0      5      5      5      5      5      5     15     15     15     15     15     15     15
116.0      5      5      5      5      5      5     15     15     15     15     15     15     15
116.0      5      5      5      5      5      5     15     15     15     15     15     15     15
116.0      5      5      5      5      5      5     15     15     15     15     15     15     15
116.0      5      5      5      5      5      5     15     15     15     15     15     15     15
116.0      5      5      5      5      5      5     15     15     15     15     15     15     15
124.0     15     15     15     15     15     15      5      5     15     15     15     15     15
124.0     15     15     15     15     15     15      5      5     15     15     15     15     15
74.0      15     15     15     15     15     15     15     15      5      5      5     15     15
74.0      15     15     15     15     15     15     15     15      5      5      5     15     15
74.0      15     15     15     15     15     15     15     15      5      5      5     15     15
126.0     15     15     15     15     15     15     15     15     15     15     15      5      5
126.0     15     15     15     15     15     15     15     15     15     15     15      5      5

Going further:

ul.set_index(['UpperGroup', 'LowerGroup'])['Time']\
  .unstack()\
  .reindex(index=jg['Group'], columns=jg['Group'])\
  .set_axis(pd.MultiIndex.from_frame(jg[['Group', 'Job']]))\
  .set_axis(pd.MultiIndex.from_frame(jg[['Group', 'Job']]), axis=1)

Output:

Group     116.0                     124.0     74.0          126.0    
Job          1   2   3   4   5   6     7   8     9   10  11    12  13
Group Job                                                            
116.0 1       5   5   5   5   5   5    15  15    15  15  15    15  15
      2       5   5   5   5   5   5    15  15    15  15  15    15  15
      3       5   5   5   5   5   5    15  15    15  15  15    15  15
      4       5   5   5   5   5   5    15  15    15  15  15    15  15
      5       5   5   5   5   5   5    15  15    15  15  15    15  15
      6       5   5   5   5   5   5    15  15    15  15  15    15  15
124.0 7      15  15  15  15  15  15     5   5    15  15  15    15  15
      8      15  15  15  15  15  15     5   5    15  15  15    15  15
74.0  9      15  15  15  15  15  15    15  15     5   5   5    15  15
      10     15  15  15  15  15  15    15  15     5   5   5    15  15
      11     15  15  15  15  15  15    15  15     5   5   5    15  15
126.0 12     15  15  15  15  15  15    15  15    15  15  15     5   5
      13     15  15  15  15  15  15    15  15    15  15  15     5   5
user_63615310
Related