Python: How to use values from one dataframe as position information for another dataframe?

user_8312752 0
Viewed 61

I have two Dataframes. The first one, "df1", looks as follows:

df1:
     b1    b2    b3    b4
a1   0     0     0     0
a2   0     0     0     0
a3   0     0     0     0
a4   0     0     0     0

Note, a (indices) and b (name of the columns) are numerical values.

I want to fill this dataframe with values from another one ("df2"), whereby where the value should go in df1 is contained in df2.

df2 looks something like this:

df2:
           namecol1     namecol2     namecol3
indexrow1  b1           a1           x1
indexrow2  b1           a3           x2
indexrow3  b1           a4           x3
indexrow4  b3           a2           y1
indexrow5  b3           a4           y2

Note, x and y are arbitrary numerical values.

Now, I want to overwrite the 0's in df1 with the values from namecol3 at the positions contained in namecol1 (=b values) and namecol2 (=a values) in the following manner:

df1:
     b1    b2    b3    b4
a1   x1    0     0     0
a2   0     0     y1    0
a3   x2    0     0     0
a4   x3    0     y2    0

How do I do that? Plus, the real dataframes can have several million datapoints, so a solution that's also performance oriented would be nice.

Any help is much appreciated!

Addendum:

I tested both of the proposed solutions from @mozway and also the one from @QuangHoang. They all work, but differ partly quiet extremely in terms of performance.

Here is a plot for the elapsed time of the solutions:

elapsed time plot

Generally speaking, up to about 4.5kk elements, out of the three variants, pivot+.notna() is the fastest. Although, pivot+.combine_first doesn't differ that much performance wise. After that, pivot+.combine_first becomes the best solution, its computing time not exceeding 1.6s for 13kk elements.

Out of all of the three solutions, update is the slowest, although it's equivalent to the others up to about 500k elements.

pandas dataframe python
2 Answers

A simple method would be to pivot and combine_first:

df3 = (df2
 .pivot('namecol2', 'namecol1', 'namecol3')
 .combine_first(df1)
 .rename_axis(index=None, columns=None)
)

or, for in place modification, use update:

df1.update(df2.pivot('namecol2', 'namecol1', 'namecol3'))

output:

    b1  b2  b3  b4
a1  x1   0   0   0
a2   0   0  y1   0
a3  x2   0   0   0
a4  x3   0  y2   0
user_163434640

You can use pivot:

vals = df2.pivot('namecol2','namecol1', 'namecol3')

df[vals.notna()] = vals

Output:

    b1  b2  b3  b4
a1  x1   0   0   0
a2   0   0  y1   0
a3  x2   0   0   0
a4  x3   0  y2   0
user_42384080
Related