How to replace values in a dataframes with values in another dataframe-CodePudding

I have 2 dataframes

df_1: 

Week   Day    Coeff_1      ...  Coeff_n

1      1             12           23
1      2             11           19   
1      3             23           68
1      4             57           81
1      5             35           16
1      6             0            0
1      7             0            0

...

50      1             12           23
50      2             11           19   
50      3             23           68
50      4             57           81
50      5             35           16
50      6             0            0
50      7             0            0

df_2: 

Week   Day    Coeff_1      ...  Coeff_n

1      1             0           0
1      2             0           0   
1      3             0           0
1      4             0           0
1      5             0           0
1      6             56          24
1      7             20          10

...

50      1             0           0
50      2             0           0   
50      3             0           0
50      4             0           0
50      5             0           0
50      6             10          84
50      7             29          10

In the first dataframe df_1 I have coefficients for monday to friday. In the second dataframes df_2 I have coefficients for the week end. My goal is to merge both dataframes such that I have no longer 0 values which are obsolete.

What is the best approach to do that? I found that using df.replace seems to be a good approach

CodePudding user response：

Assuming that your dataframes follow the same structure, you can capitalise on pandas functionality to align automatically on indexes. Thus you can replace 0's with np.nan in df1, and then use fillna:

df1.replace({0:np.nan},inplace=True)
df1.fillna(df2)

    Week  Day  Coeff_1  Coeff_n
0    1.0  1.0     12.0     23.0
1    1.0  2.0     11.0     19.0
2    1.0  3.0     23.0     68.0
3    1.0  4.0     57.0     81.0
4    1.0  5.0     35.0     16.0
5    1.0  6.0     56.0     24.0
6    1.0  7.0     20.0     10.0
7   50.0  1.0     12.0     23.0
8   50.0  2.0     11.0     19.0
9   50.0  3.0     23.0     68.0
10  50.0  4.0     57.0     81.0
11  50.0  5.0     35.0     16.0
12  50.0  6.0     10.0     84.0
13  50.0  7.0     29.0     10.0

CodePudding user response：

Can't you just append the rows df_1 where day is 1-5 to the rows of df_2 where day is 6-7?

df_3 = df_1[df_1.Day.isin(range(1,6))].append(df_2[df_2.Day.isin(range(6,8))])

To get a normal sorting, you can sort your values by week and day:

df_3.sort_values(['Week','Day'])