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

Question

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

Does this answer your question? Replace zeros in one dataframe with values from another dataframe — Paul
– Paul, Commented Dec 22, 2021 at 12:42

sophocles · Accepted Answer · 2021-12-22 12:40:28Z

2

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

answered Dec 22, 2021 at 12:40

sophocles

13.9k3 gold badges18 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul · Accepted Answer · 2021-12-22 12:35:53Z

1

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'])

answered Dec 22, 2021 at 12:35

Paul

1,8971 gold badge17 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related