Pandas lookup to update value by refereeing col and row with 2 data frames

September 15, 2022

I’ve a df 1 and df2 like below and need to lookup the part and week column value from df2 and update the qty value in df1 .. Initially I’ve tried using melt function to change weeks as col and used merge function to join them but when i do pivot to get back to same as df1 with updated value it says grouper is not 1 dimensional since part and weeks are repeated — is there any other better approach pls help.

{'Part': {0: 'Part1', 1: 'part2', 2: 'Part3'},
 'Week26': {0: nan, 1: nan, 2: nan},
 'Week27': {0: nan, 1: nan, 2: nan},
 'Week28': {0: nan, 1: nan, 2: nan},
 'Week29': {0: nan, 1: nan, 2: nan},
 'Week30': {0: nan, 1: nan, 2: nan},
 'Week31': {0: nan, 1: nan, 2: nan},
 'Week32': {0: nan, 1: nan, 2: nan},
 'Week33': {0: nan, 1: nan, 2: nan},
 'Week34': {0: nan, 1: nan, 2: nan}}
 
 
 {'ITM_NO': {0: 'Part1',
  1: 'Part1',
  2: 'Part1',
  3: 'part2',
  4: 'part2',
  5: 'part2',
  6: 'part2',
  7: 'Part3',
  8: 'Part3',
  9: 'Part3',
  10: 'Part3'},
 'WEEK': {0: 'Week26',
  1: 'Week27',
  2: 'Week28',
  3: 'Week26',
  4: 'Week27',
  5: 'Week28',
  6: 'Week29',
  7: 'Week29',
  8: 'Week30',
  9: 'Week31',
  10: 'Week32'},
 'QTY': {0: 12,
  1: 10,
  2: 30,
  3: 20,
  4: 40,
  5: 60,
  6: 70,
  7: 20,
  8: 10,
  9: 30,
  10: 20}}

Expected output

>Solution :

Pivot the 2nd dataframe, then concatenate with the first dataframe, and finally get the sum by grouping the Part column. You can reset_index() at last if you want to

(pd.concat([
    df2
    .pivot('ITM_NO', 'WEEK', 'QTY')
    .reset_index()
    .rename(columns={'ITM_NO': 'Part'}),
    df1])
 .groupby('Part').sum())

       Week26  Week27  Week28  Week29  Week30  Week31  Week32  Week33  Week34
Part                                                                         
Part1    12.0    10.0    30.0     0.0     0.0     0.0     0.0     0.0     0.0
Part3     0.0     0.0     0.0    20.0    10.0    30.0    20.0     0.0     0.0
part2    20.0    40.0    60.0    70.0     0.0     0.0     0.0     0.0     0.0