Place elements of one Pandas DataFrame to locations of elements in another DataFrame

user_11758143 0
Viewed 102

I have two Pandas DataFrames, df1 and df2.

The first one specifies the 'locations' of the elements using zeros and ones.

The second one specifies the values of the elements, but not their location (i.e. it is simply filled from left to right from Col1 through Col4).

df1 = pd.DataFrame([[1,0,0,0], [1,0,0,1], [0,1,0,1], [0,1,1,1]], columns=['Col1', 'Col2', 'Col3', 'Col4'])
df2 = pd.DataFrame([[1,0,0,0], [0.4,0.6,0,0], [0.8,0.2,0,0], [0.1,0.4,0.5,0]], columns=['Col1', 'Col2', 'Col3', 'Col4'])

df1
  Col1 Col2 Col3 Col4
0    1    0    0    0
1    1    0    0    1
2    0    1    0    1
3    0    1    1    1

df2
  Col1 Col2 Col3 Col4
0    1    0    0    0
1  0.4  0.6    0    0
2  0.8  0.2    0    0
3  0.1  0.4  0.5    0

I would like to create a third DataFrame, df3, which places the non-zero values from df2 in the corresponding locations of ones in df1. I would like to work from left to right, i.e. the leftmost non-zero element in each row of df2 should be placed in the location of the leftmost one in df1.

df3 = pd.DataFrame([[1,0,0,0], [0.4,0,0,0.6], [0,0.8,0,0.2], [0,0.1,0.4,0.5]], columns=['Col1', 'Col2', 'Col3', 'Col4'])
    
df3
  Col1 Col2 Col3 Col4
0    1    0    0    0
1  0.4    0    0  0.6
2    0  0.8    0  0.2
3    0  0.1  0.4  0.5

As the real DataFrames are relatively large, an efficient solution is required (i.e. looping through elements might not be an option).

Many thanks in advance for your help!

pandas dataframe python
3 Answers

There is a really efficient solution to your problem if you use numpy. There is a method called place that does exactly what you need.

# get series with right typing
values1 = df1.values.astype(np.float64)
values2 = df2.values.astype(np.float64)

#replace 1 values in the series1 with non zero values from series2
np.place(values1, values1==1, values2[values2 != 0])

# replace values in the dataframe1
df1.loc[:] = values1
user_109761820

I'm sure there are be more efficient solutions to this, but you can use melt on both dfs, exchange the values (make sure you preserve order and indices) and pivot to create the dataframe structure again:

# Melt dataframes
melted_df1 = df1.reset_index().melt(id_vars="index").sort_values(by=["index","variable"])
melted_df2 = df2.reset_index().melt(id_vars="index").sort_values(by=["index","variable"])

output for melted:

    index variable  value
0       0     Col1      1
4       0     Col2      0
8       0     Col3      0
12      0     Col4      0
1       1     Col1      1
5       1     Col2      0
9       1     Col3      0
13      1     Col4      1
2       2     Col1      0
6       2     Col2      1
10      2     Col3      0
14      2     Col4      1
3       3     Col1      0
7       3     Col2      1
11      3     Col3      1
15      3     Col4      1
    index variable  value
0       0     Col1    1.0
4       0     Col2    0.0
8       0     Col3    0.0
12      0     Col4    0.0
1       1     Col1    0.4
5       1     Col2    0.6
9       1     Col3    0.0
13      1     Col4    0.0
2       2     Col1    0.8
6       2     Col2    0.2
10      2     Col3    0.0
14      2     Col4    0.0
3       3     Col1    0.1
7       3     Col2    0.4
11      3     Col3    0.5
15      3     Col4    0.0 

# Keep only non zero values in the melted lists
melted_df1 = melted_df1[melted_df1["value"] > 0]
melted_df2 = melted_df2[melted_df2["value"] > 0]

output for filtered melted dfs:

     index variable  value
0       0     Col1      1
1       1     Col1      1
13      1     Col4      1
6       2     Col2      1
14      2     Col4      1
7       3     Col2      1
11      3     Col3      1
15      3     Col4      1
    index variable  value
0       0     Col1    1.0
1       1     Col1    0.4
5       1     Col2    0.6
2       2     Col1    0.8
6       2     Col2    0.2
3       3     Col1    0.1
7       3     Col2    0.4
11      3     Col3    0.5

# replace the first filtered melted values with the second
melted_df1["value"] = melted_df2["value"].to_list()

output for replaced melted dataframe:

index variable  value
0       0     Col1    1.0
1       1     Col1    0.4
13      1     Col4    0.6
6       2     Col2    0.8
14      2     Col4    0.2
7       3     Col2    0.1
11      3     Col3    0.4
15      3     Col4    0.5

# pivot the result to get back to a the intial dataframe structure
df3 = melted_df1.pivot(index="index", columns="variable", values="value").fillna(0)

output in df3:

variable  Col1  Col2  Col3  Col4
index
0          1.0   0.0   0.0   0.0
1          0.4   0.0   0.0   0.6
2          0.0   0.8   0.0   0.2
3          0.0   0.1   0.4   0.5
user_166255060

I'll write it here as an answer. If the one table is only binary you can just multiply the dataframes.

df3 = pd.DataFrame(df1.values*df2.values, columns=df.columns, index=df.index)
user_133200760
Related