Assigning values in DataFrame when columns names and values are in single row

Question

I have two dataframes like below,

import numpy as np
import pandas as pd

df1 = pd.DataFrame({1: np.zeros(5), 2: np.zeros(5)}, index=['a','b','c','d','e'])

and

df2 = pd.DataFrame({'category': [1,1,2,2], 'value':[85,46, 39, 22]}, index=[0, 1, 3, 4])

The value from second dataframe is required to be assigned in first dataframe such that the index and column relationship is maintained. The second dataframe index is iloc based and has column category which is actually containing column names of first dataframe. The value is value to be assigned.

Following is the my solution with expected output,

for _category in df2['category'].unique():
    df1.loc[df1.iloc[df2[df2['category'] == _category].index.tolist()].index, _category]  = df2[df2['category'] == _category]['value'].values

Is there a pythonic way of doing so without the for loop?

mozway · Accepted Answer · 2022-04-29 16:25:07Z

2

One option is to pivot and update:

df3 = df1.reset_index()
df3.update(df2.pivot(columns='category', values='value'))
df3 = df3.set_index('index').rename_axis(None)

Alternative, reindex df2 (in two steps, numerical and by label), and combine_first with df1:

df3 = (df2
 .pivot(columns='category', values='value')
 .reindex(range(max(df2.index)+1))
 .set_axis(df1.index)
 .combine_first(df1)
)

output:

      1     2
a  85.0   0.0
b  46.0   0.0
c   0.0   0.0
d   0.0  39.0
e   0.0  22.0

edited Apr 29, 2022 at 16:25

answered Apr 29, 2022 at 15:55

mozway

267k13 gold badges56 silver badges106 bronze badges

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

Comments

user7864386 · Accepted Answer · 2022-04-29 15:54:45Z

2

Here's one way by replacing the 0s in df1 with NaN; pivotting df2 and filling in the NaNs in df1 with df2:

out = (df1.replace(0, pd.NA).reset_index()
       .fillna(df2.pivot(columns='category', values='value'))
       .set_index('index').rename_axis(None).fillna(0))

Output:

      1     2
a  85.0   0.0
b  46.0   0.0
c   0.0   0.0
d   0.0  39.0
e   0.0  22.0

answered Apr 29, 2022 at 15:54

user7864386

Collectives™ on Stack Overflow

Assigning values in DataFrame when columns names and values are in single row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related