How to replicate an SQL groupby query with summing of columns based on conditions using Pandas?

Asked Sep 03 '19 at 09:51

Active Sep 03 '19 at 10:37

Viewed 105 times

I'm trying to replicate the following SQL code using Pandas functions. This SQL code block contains a groupby and summation of columns based on different conditions.

I tried using pandas groupby and apply functions but couldn't crack the logic.

select id, lcode,
sum(case when type='A' and code='$' AND sub_code='B' then
amount else 0 end) as A_amount,
sum(case when type='P' and code='$' AND sub_code='B' then
amount else 0 end) as P_amount,
sum(case when type='W' and code='$' AND sub_code='B' then
amount else 0 end) as W_amount
from TRANSACTIONS
where id in ('1235','2456','4231','2341','9624')
GROUP BY id, lcode

The requirement is to replicate this logic in Python using Pandas functions.

asked Sep 03 '19 at 09:51

Nelwin Jacob

1

This might help you: https://stackoverflow.com/a/57763038/11610186 – Grzegorz Skibinski Sep 03 '19 at 09:53
2

Can you add some sample data ? – Vikas Periyadath Sep 03 '19 at 09:55
What does your pandas' dataframe look like, and are the 'id', 'code', 'sub_code' fields already filtered? – Aryerez Sep 03 '19 at 09:56
@GrzegorzSkibinski That solved my issue. Thank you! – Nelwin Jacob Sep 03 '19 at 10:47
@VikasP It's some confidential data. Skibinski's answer helped. Thanks! – Nelwin Jacob Sep 03 '19 at 10:49

How to replicate an SQL groupby query with summing of columns based on conditions using Pandas?

0 Answers0