Replacing values based on other columns

Question

Let me write a dataframe for explaining my question:

Loc   Length   Size
A       5       50
A       10      90
A       8       0
B       20      140
B       5       50
B       12      0

Consider I have a dataframe like this. What I want to do is replacing Size values which are equals 0. I want to replace this values like: If the size in Loc A, what I want to do is (sum of sizes in A) / (sum of lengths in A) multiple by Length and change zero this value. I want to do this on every Size values which equals 0, and it is depending on his Location's sumSize/sumLength and his row's Length value. I tried but I couldn't do anything. Please help me about this, thanks!

Henry Yik · Accepted Answer · 2020-11-08 14:46:18Z

2

IIUC groupby on Loc to get the sum and then map the result:

s = df.loc[df["Size"].ne(0)].groupby("Loc").sum()

df.loc[df["Size"].eq(0), "Size"] = df["Loc"].map(s["Size"]/s["Length"])*df["Length"]

print (df)

  Loc  Length        Size
0   A       5   50.000000
1   A      10   90.000000
2   A       8   74.666667
3   B      20  140.000000
4   B       5   50.000000
5   B      12   91.200000

edited Nov 8, 2020 at 14:46

answered Nov 8, 2020 at 14:29

Henry Yik

22.6k5 gold badges21 silver badges44 bronze badges

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

2 Comments

skbmoony Over a year ago

In the map function the value of s["Size"]/s["Length"] is equal to (50+90+0) ? I want (50+90) / (5+10) value on this. Because I have too many 0 value on the Size column in my dataframe.

skbmoony Over a year ago

If I do s = df["Size" != 0].groupby("Loc").sum() is this fix my problem?

Collectives™ on Stack Overflow

Replacing values based on other columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related