replace empty strings in a dataframe with values from another dataframe-CodePudding

two sample dataframes

df1 = pd.DataFrame([[1, '', 3], ['', 2, '']], columns=['A', 'B', 'C'])
df2 = pd.DataFrame([['', 4, ''], [5, '', 6]], columns=['A', 'B', 'C'])

df1

    A   B   C
0   1       3
1       2

df2

   A    B   C
0       4   
1   5       6

I know how to concat the two dataframes, but that gives this:

   A    B   C
0   1       3
1       2   
0       4   
1   5       6

The result I am trying to achieve is:

    A   B   C
0   1   4   3   
1   5   2   6

I want to combine the rows with the same index values from each df so that missing values in one df are replaced by the corresponding value in the other.

Concat and Merge are not up to the job I have found.
I assume I have to have identical indexes in each df which correspond to the values I want to merge into one row. But, so far, no luck getting it to come out correctly. Any pandas transformational wisdom is appreciated.

This merge attempt did not do the trick:

df1.merge(df2, on='A', how='outer')

CodePudding user response：

Let us try mask

out = df1.mask(df1=='',df2)
Out[428]: 
   A  B  C
0  1  4  3
1  5  2  6

CodePudding user response：

This is a tedious way (and perhaps not a good practice) to do it but here's what you can do:

df1["A"][1]=df2["A"][1]

df1["B"][0]=df2["B"][0]

df1["C"][1]=df2["C"][1]

CodePudding user response：

for i in range(df1.shape[0]):
    for j in range(df1.shape[1]):
        if df1.iloc[i,j]=="":
            df1.iloc[i,j] = df2.iloc[i,j]

print(df1)


    A   B   C
0   1   4   3
1   5   2   6