updating a slice of dataframe from another dataframe accoding to the item number, Factor, and week number

Suppose you have dataframes look like that:

df1:

df2:

How can I updating the df1 data with df2 data, under the right factor row, model name and only apply to their common week (without changing other week's and model's number)?

My expected output is like:

I tried to use iterrows to get each row in df1 and sliced the comon week cells out to match df2, but it doesn't work because the chained indexing won't let me change the orignial dataframe.

If anyone can help me out? I would really appricate it!

You can use update with a MultiIndex :

cols = ["Item Number", "Model", "Factor"]
df1 = df1.set_index(cols)

df1.update(df2.set_index(cols)) # returns `None`

df1 = df1.reset_index()

Output :

print(df1)

   Item Number Model             Factor  2/4/22  2/11/22  2/18/22  2/25/22  3/4/22  3/11/22  3/18/22  3/25/22  4/1/22  4/8/22  4/15/22  4/22/22  4/29/22
0          123     A  Sell out forecast       5        6        7        8       9       10       11       12      13       0        0        0        0
1          123     A          Inventory       0        0        0        0       0        0        0        0       0       0        0        0        0
2          123     A           Incoming       0        0        0        0       0        0        0        0       0       0        0        0        0
3          345     B  Sell out forecast       2        2        2        2       2        2        2        2       2       0        0        0        0
4          345     B          Inventory       0        0        0        0       0        0        0        0       0       0        0        0        0
5          345     B           Incoming       0        0        0        0       0        0        0        0       0       0        0        0        0
6          678     C  Sell out forecast       0        0        0        0       0        0        0        0       0       0        0        0        0
7          678     C          Inventory       0        0        0        0       0        0        0        0       0       0        0        0        0
8          678     C           Incoming       0        0        0        0       0        0        0        0       0       0        0        0        0

I end up with a sudo-code like this:

for index, row in df1.iterrows():

    # Update Sell thru forecast

    for idx, r in df2.iterrows():

        if row["Factor"] == "Sell out Forecast" and row["Item Number"] == r["Item Number"]:

            df1.iloc[index,common_week_start:common_week_end+1]=r[common_week]


        break



    # Update Inventory

    # Update Incoming

But some pre-workouts need to do:

1. You need to for loop the df1.column and df2.column to get the common week list.
2. Set two variables, Common_week_start and Common_week_end
3. Make sure the Factor name is identical (Lazy way, lol. Also, could use the regular expression)

Reason I was failed at the very beginning is that I used the df.iloc[index][common_week] the chain slicing creates a copy of the data, each loop only changes the copy values. Now I used.iloc[index,column_start:column_end], it works very well.