SQL “partition by” similar feature in Python/R
1
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
python sql r pandas dplyr
asked Nov 22 at 17:59
tarne_xrd
62
add a comment |
1
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
python sql r pandas dplyr
asked Nov 22 at 17:59
tarne_xrd
62
Pandastransformmight be helpful. Can you add some sample input and output data to your question?
– Dan
Nov 22 at 18:02
I've added one example. May you look at this, please?
– tarne_xrd
Nov 22 at 18:23
Please add the example as text (not an image) so we can copy it into python (or sql fiddle) and get the data frame / sql table. Also, please post both input AND expected output.
– Dan
Nov 22 at 18:24
I've added input and expected data-frames, please look at this
– tarne_xrd
Nov 22 at 18:34
add a comment |
1
1
1
0
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
python sql r pandas dplyr
asked Nov 22 at 17:59
tarne_xrd
62
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
python sql r pandas dplyr
python sql r pandas dplyr
asked Nov 22 at 17:59
tarne_xrd
62
asked Nov 22 at 17:59
tarne_xrd
62
edited Nov 22 at 18:33
asked Nov 22 at 17:59
tarne_xrd
62
asked Nov 22 at 17:59
tarne_xrd
62
asked Nov 22 at 17:59
tarne_xrd
62
62
Pandastransformmight be helpful. Can you add some sample input and output data to your question?
– Dan
Nov 22 at 18:02
I've added one example. May you look at this, please?
– tarne_xrd
Nov 22 at 18:23
Please add the example as text (not an image) so we can copy it into python (or sql fiddle) and get the data frame / sql table. Also, please post both input AND expected output.
– Dan
Nov 22 at 18:24
I've added input and expected data-frames, please look at this
– tarne_xrd
Nov 22 at 18:34
add a comment |
Pandastransformmight be helpful. Can you add some sample input and output data to your question?
– Dan
Nov 22 at 18:02
I've added one example. May you look at this, please?
– tarne_xrd
Nov 22 at 18:23
Please add the example as text (not an image) so we can copy it into python (or sql fiddle) and get the data frame / sql table. Also, please post both input AND expected output.
– Dan
Nov 22 at 18:24
I've added input and expected data-frames, please look at this
– tarne_xrd
Nov 22 at 18:34
Pandas
transform might be helpful. Can you add some sample input and output data to your question?– Dan
Nov 22 at 18:02
Pandas
transform might be helpful. Can you add some sample input and output data to your question?– Dan
Nov 22 at 18:02
I've added one example. May you look at this, please?
– tarne_xrd
Nov 22 at 18:23
I've added one example. May you look at this, please?
– tarne_xrd
Nov 22 at 18:23
Please add the example as text (not an image) so we can copy it into python (or sql fiddle) and get the data frame / sql table. Also, please post both input AND expected output.
– Dan
Nov 22 at 18:24
Please add the example as text (not an image) so we can copy it into python (or sql fiddle) and get the data frame / sql table. Also, please post both input AND expected output.
– Dan
Nov 22 at 18:24
I've added input and expected data-frames, please look at this
– tarne_xrd
Nov 22 at 18:34
I've added input and expected data-frames, please look at this
– tarne_xrd
Nov 22 at 18:34
add a comment |
2 Answers
2
active
oldest
votes
1
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
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
Thanks a lot! I wrote this in Dplyr and got : df %>% group_by(group) %>% mutate(by_group = sum(ammount)) %>% group_by(group, is_new) %>% mutate(by_group_is_new = sum(ammount)) %>% mutate(result = by_group_is_new / by_group)
– tarne_xrd
Nov 22 at 19:57
add a comment |
1
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)
answered Nov 22 at 20:23
ismirsehregal
1,1761210
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436182%2fsql-partition-by-similar-feature-in-python-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
Thanks a lot! I wrote this in Dplyr and got : df %>% group_by(group) %>% mutate(by_group = sum(ammount)) %>% group_by(group, is_new) %>% mutate(by_group_is_new = sum(ammount)) %>% mutate(result = by_group_is_new / by_group)
– tarne_xrd
Nov 22 at 19:57
add a comment |
1
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
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
Thanks a lot! I wrote this in Dplyr and got : df %>% group_by(group) %>% mutate(by_group = sum(ammount)) %>% group_by(group, is_new) %>% mutate(by_group_is_new = sum(ammount)) %>% mutate(result = by_group_is_new / by_group)
– tarne_xrd
Nov 22 at 19:57
add a comment |
1
1
1
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
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
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
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
answered Nov 22 at 18:40
Haleemur Ali
12.1k21738
12.1k21738
Thanks a lot! I wrote this in Dplyr and got : df %>% group_by(group) %>% mutate(by_group = sum(ammount)) %>% group_by(group, is_new) %>% mutate(by_group_is_new = sum(ammount)) %>% mutate(result = by_group_is_new / by_group)
– tarne_xrd
Nov 22 at 19:57
add a comment |
Thanks a lot! I wrote this in Dplyr and got : df %>% group_by(group) %>% mutate(by_group = sum(ammount)) %>% group_by(group, is_new) %>% mutate(by_group_is_new = sum(ammount)) %>% mutate(result = by_group_is_new / by_group)
– tarne_xrd
Nov 22 at 19:57
Thanks a lot! I wrote this in Dplyr and got : df %>% group_by(group) %>% mutate(by_group = sum(ammount)) %>% group_by(group, is_new) %>% mutate(by_group_is_new = sum(ammount)) %>% mutate(result = by_group_is_new / by_group)
– tarne_xrd
Nov 22 at 19:57
Thanks a lot! I wrote this in Dplyr and got : df %>% group_by(group) %>% mutate(by_group = sum(ammount)) %>% group_by(group, is_new) %>% mutate(by_group_is_new = sum(ammount)) %>% mutate(result = by_group_is_new / by_group)
– tarne_xrd
Nov 22 at 19:57
add a comment |
1
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)
answered Nov 22 at 20:23
ismirsehregal
1,1761210
add a comment |
1
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)
answered Nov 22 at 20:23
ismirsehregal
1,1761210
add a comment |
1
1
1
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)
answered Nov 22 at 20:23
ismirsehregal
1,1761210
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)
answered Nov 22 at 20:23
ismirsehregal
1,1761210
edited Nov 22 at 21:23
answered Nov 22 at 20:23
ismirsehregal
1,1761210
answered Nov 22 at 20:23
ismirsehregal
1,1761210
answered Nov 22 at 20:23
ismirsehregal
1,1761210
1,1761210
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436182%2fsql-partition-by-similar-feature-in-python-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Pandas
transformmight be helpful. Can you add some sample input and output data to your question?– Dan
Nov 22 at 18:02
I've added one example. May you look at this, please?
– tarne_xrd
Nov 22 at 18:23
Please add the example as text (not an image) so we can copy it into python (or sql fiddle) and get the data frame / sql table. Also, please post both input AND expected output.
– Dan
Nov 22 at 18:24
I've added input and expected data-frames, please look at this
– tarne_xrd
Nov 22 at 18:34