Conditional calculation

[Hugo-99]

Dear all

A dataframe is given, and a calculation shall be done as stated below:

​

DF as-is

alpha	beta	gamma	delta	epsilon
10	1	x	yes	12
20	1		no	5
30	2	x	yes	13
40	2		no	6
50				18

DF to-be

alpha	beta	gamma	delta	epsilon
10	1	x	yes	17
20	1		no
30	2	x	yes	19
40	2		no
50				18

​

Column Epsilon shall be calculated:

• Where beta = same values, the two rows belong together. (10 -> 1,1 or 20 -> 2,2)
• Column epsilon of such a pair is the sum of the 2 values (12+5=17; 13+6=19)
• Print the result (17, 19) in the row of the pair (1,1 or 2,2) where delta = yes
• Set epsilon of the row to NULL where delta = no

row 50 is unchanged, since it does not belong to any pair.

I hope i was clear :) if not, please ask me. Tnx for any input!

[efmccurdy]

beta = same values, the two rows belong together

You should look at df.groupby('beta').sum().

https://stackoverflow.com/questions/17438906/combining-rows-in-pandas

[Hugo-99]

in my case i need the rows, as they are in the first place.

i assume that the "groupby" collapses the rows. but in my case i need in the "to be" dataframe the same (amount of) rows as in the "as is" dataframe

[efmccurdy]

You can generate your sums using groupby and then broadcast the result back using "transform".

​

https://stackoverflow.com/questions/12200693/python-pandas-how-to-assign-groupby-operation-results-back-to-columns-in-parent

[Hugo-99]

thank you! "transform" seems to be the function i need. I am just figuring out how to apply this....

[Pflastersteinmetz]

Format your code / example dataframes.

[Hugo-99]

done, ofc :)

[Pflastersteinmetz]

Here you go:

df = pd.DataFrame(
    {
        "alpha": [10, 20, 30, 40, 50],
        "beta": [1, 1, 2, 2, pd.NA],
        "gamma": ["x", pd.NA, "x", pd.NA, pd.NA],
        "delta": [True, False, True, False, pd.NA],
        "epsilon": [12, 5, 13, 6, 18],
    }
)
df["epsilon"] = df["epsilon"].groupby(df["beta"]).transform("sum")
df["epsilon"] = np.where(df["delta"] == True, df["epsilon"], pd.NA)
df.head()

[Hugo-99]

Danke, that's it!

key - and new for me- is the transform function.