CODEWITHSUNDEEP
pythonpandasnumpydataframereplace
skbmoony
skbmoony

Reputation: 27

Replacing values based on other columns

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!

Upvotes: 1

Views: 40

Answers (1)

Henry Yik
Henry Yik

Reputation: 22493

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

Upvotes: 2

Related Questions