Fill Data from DataFrame When 2 Column Values Match

Question

I am trying to populate an empty column dataframe when two conditions (columns ['SITE','week'] are equal) are met when comparing two dataframes. Here is my example:

df1_small: 
    week    SITE    LAL
0   1   BARTON CHAPEL   1.1
1   2   BARTON CHAPEL   1.8
2   3   BARTON CHAPEL   1.4
3   1   PENASCAL I  1.7
4   2   PENASCAL I  2.9
5   3   PENASCAL I  2.2

df2_large:
    SITE       hour day week    POWER   LAL
0   BARTON CHAPEL   1   1   1   54  
1   BARTON CHAPEL   2   1   1   32  
2   BARTON CHAPEL   3   1   1   56  
3   BARTON CHAPEL   4   1   1   81  
4   BARTON CHAPEL   5   1   1   90  
5   BARTON CHAPEL   6   1   1   12  
6   BARTON CHAPEL   7   1   1   10  
7   BARTON CHAPEL   8   1   1   73  
8   BARTON CHAPEL   9   1   1   55  
9   BARTON CHAPEL   10  1   1   66  
10  PENASCAL I      1   1   1   39  
11  PENASCAL I      2   1   1   90  
12  PENASCAL I      3   1   1   13  
13  PENASCAL I      4   1   1   44  
14  PENASCAL I      5   1   1   51

After the 2 condtions are met when the dataframe column values match, then df_large column 'LAL' is populated using the 'LAL' column from df1_small. The final df_large looks like this:

df2_large:
    SITE       hour day week    POWER   LAL
0   BARTON CHAPEL   1   1   1   54      1.1
1   BARTON CHAPEL   2   1   1   32      1.1
2   BARTON CHAPEL   3   1   1   56      1.1
3   BARTON CHAPEL   4   1   1   81      1.1
4   BARTON CHAPEL   5   1   1   90      1.1
5   BARTON CHAPEL   6   1   1   12      1.1
6   BARTON CHAPEL   7   1   1   10      1.1
7   BARTON CHAPEL   8   1   1   73      1.1
8   BARTON CHAPEL   9   1   1   55      1.1
9   BARTON CHAPEL   10  1   1   66      1.1
10  PENASCAL I      1   1   1   39      1.7
11  PENASCAL I      2   1   1   90      1.7
12  PENASCAL I      1   1   2   13      2.9
13  PENASCAL I      2   1   2   44      2.9
14  PENASCAL I      3   1   2   51      2.9

thank you,

score 0 · Answer 1 · answered Jun 01 '23 at 16:34

You can just use pandas merge here:

df2_large = df2_large.merge(df1_small[['SITE', 'week', 'LAL']], on=['SITE', 'week'], how='left')

The how='left' parameter ensures that all rows from df2_large are kept, whether or not there's a matching row in df1_small.

Fill Data from DataFrame When 2 Column Values Match

1 Answers1