Btukfyl

Is there any packages either in R (data.table/dplyr) or in Python, that can do SQL code below in some consistent and straightforward way?

Can you share some examples of doing it?

Example of what I need:

My input data-frame (CSV, sep - ";", headers - True) :

articule;group;is_new;ammount

1;fruits;1;100

2;fruits;1;200

3;fruits;1;300

4;fruits;0;400

5;frozen;0;500

6;frozen;0;600

7;frozen;0;700

8;frozen;1;800

My expected output (CSV, sep - ";", headers - True):

articule;group;is_new;ammount;sum_by_group;sum_by_group_is_new;result

1;fruits;1;100;1000;600;0.60

2;fruits;1;200;1000;600;0.60

3;fruits;1;300;1000;600;0.60

4;fruits;0;400;1000;400;0.40

5;frozen;0;500;2600;1800;0.69

6;frozen;0;600;2600;1800;0.69

7;frozen;0;700;2600;1800;0.69

8;frozen;1;800;2600;800;0.31

My code in SQL:

select a.*, sum_by_group_is_new / sum_by_group result from (



select a.*, 

sum(ammount) over (partition by group) sum_by_group,

sum(ammount) over(partition by group, is_new) sum_by_group_is_new 

from input_data_frame a



) a;

Best regards

You can use the transform method with groupby in this case. It sort of works like SQL's partition by

df['sum_by_group'] = df.groupby('group').ammount.transform(sum)



df['sum_by_group_is_new'] = df.groupby(['group', 'is_new']).ammount.transform(sum)



df['result'] = df.sum_by_group_is_new / df.sum_by_group

this gave me the following output data frame.

   articule   group  is_new  ammount  sum_by_group  sum_by_group_is_new    result

0         1  fruits       1      100          1000                  600  0.600000

1         2  fruits       1      200          1000                  600  0.600000

2         3  fruits       1      300          1000                  600  0.600000

3         4  fruits       0      400          1000                  400  0.400000

4         5  frozen       0      500          2600                 1800  0.692308

5         6  frozen       0      600          2600                 1800  0.692308

6         7  frozen       0      700          2600                 1800  0.692308

7         8  frozen       1      800          2600                  800  0.307692

Welcome to SO!

Here is what you could do with R:

library(data.table)



DT <- data.table(

  articule = seq(8),

  group = rep(c("fruits", "frozen"), each = 4),

  is_new = c(rep(c(1, 0), each = 3), 0, 1),

  ammount = seq(100, 800, by = 100)

)



DT[, sum_by_group := sum(ammount), by = group]

DT[, sum_by_group_is_new := sum(ammount), by = .(group, is_new)]

DT[, result := sum_by_group_is_new / sum_by_group]



print(DT)