To aggregate a csv file having day wise data into month
1
I have a csv file named crime.csv as below:-
OFFENSE_CODE OFFENSE_TYPE OFFENSE_DESCRIPTION DISTRICT DAY YEAR MONTH STREET NO_OF_CRIME
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD B2 1 2018 2 WASHINGTON ST 1
3201 Property Lost PROPERTY - LOST B2 15 2018 3 ELM HILL AVE 1
1001 Counterfeiting FORGERY / COUNTERFEITING D4 9 2018 1 TREMONT ST 1
2629 Harassment HARASSMENT E5 1 2018 1 CROWN POINT DR 1
1001 Counterfeiting FORGERY / COUNTERFEITING E5 8 2018 4 REDGATE RD 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD D4 22 2018 2 BOYLSTON ST 1
2629 Harassment HARASSMENT B2 9 2017 10 AKRON ST 1
1102 Fraud FRAUD - FALSE PRETENSE / SCHEME A7 25 2018 4 LIVERPOOL ST 1
3201 Property Lost PROPERTY - LOST D14 1 2018 1 FIDELIS WAY 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD E5 12 2018 4 SPRING ST 1
3201 Property Lost PROPERTY - LOST A1 30 2018 4 NASHUA ST 1
I need to aggregate this data into monthly data based on OFFENCE_CODE. So that NO_OF_CRIMES gets aggregated for that particular month. Any help would be really great.
r csv date aggregate
edited Nov 12 at 19:04
12b345b6b78
767115
asked Nov 12 at 18:55
supriya singh
234
add a comment |
1
I have a csv file named crime.csv as below:-
OFFENSE_CODE OFFENSE_TYPE OFFENSE_DESCRIPTION DISTRICT DAY YEAR MONTH STREET NO_OF_CRIME
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD B2 1 2018 2 WASHINGTON ST 1
3201 Property Lost PROPERTY - LOST B2 15 2018 3 ELM HILL AVE 1
1001 Counterfeiting FORGERY / COUNTERFEITING D4 9 2018 1 TREMONT ST 1
2629 Harassment HARASSMENT E5 1 2018 1 CROWN POINT DR 1
1001 Counterfeiting FORGERY / COUNTERFEITING E5 8 2018 4 REDGATE RD 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD D4 22 2018 2 BOYLSTON ST 1
2629 Harassment HARASSMENT B2 9 2017 10 AKRON ST 1
1102 Fraud FRAUD - FALSE PRETENSE / SCHEME A7 25 2018 4 LIVERPOOL ST 1
3201 Property Lost PROPERTY - LOST D14 1 2018 1 FIDELIS WAY 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD E5 12 2018 4 SPRING ST 1
3201 Property Lost PROPERTY - LOST A1 30 2018 4 NASHUA ST 1
I need to aggregate this data into monthly data based on OFFENCE_CODE. So that NO_OF_CRIMES gets aggregated for that particular month. Any help would be really great.
r csv date aggregate
edited Nov 12 at 19:04
12b345b6b78
767115
asked Nov 12 at 18:55
supriya singh
234
add a comment |
1
1
1
I have a csv file named crime.csv as below:-
OFFENSE_CODE OFFENSE_TYPE OFFENSE_DESCRIPTION DISTRICT DAY YEAR MONTH STREET NO_OF_CRIME
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD B2 1 2018 2 WASHINGTON ST 1
3201 Property Lost PROPERTY - LOST B2 15 2018 3 ELM HILL AVE 1
1001 Counterfeiting FORGERY / COUNTERFEITING D4 9 2018 1 TREMONT ST 1
2629 Harassment HARASSMENT E5 1 2018 1 CROWN POINT DR 1
1001 Counterfeiting FORGERY / COUNTERFEITING E5 8 2018 4 REDGATE RD 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD D4 22 2018 2 BOYLSTON ST 1
2629 Harassment HARASSMENT B2 9 2017 10 AKRON ST 1
1102 Fraud FRAUD - FALSE PRETENSE / SCHEME A7 25 2018 4 LIVERPOOL ST 1
3201 Property Lost PROPERTY - LOST D14 1 2018 1 FIDELIS WAY 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD E5 12 2018 4 SPRING ST 1
3201 Property Lost PROPERTY - LOST A1 30 2018 4 NASHUA ST 1
I need to aggregate this data into monthly data based on OFFENCE_CODE. So that NO_OF_CRIMES gets aggregated for that particular month. Any help would be really great.
r csv date aggregate
edited Nov 12 at 19:04
12b345b6b78
767115
asked Nov 12 at 18:55
supriya singh
234
I have a csv file named crime.csv as below:-
OFFENSE_CODE OFFENSE_TYPE OFFENSE_DESCRIPTION DISTRICT DAY YEAR MONTH STREET NO_OF_CRIME
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD B2 1 2018 2 WASHINGTON ST 1
3201 Property Lost PROPERTY - LOST B2 15 2018 3 ELM HILL AVE 1
1001 Counterfeiting FORGERY / COUNTERFEITING D4 9 2018 1 TREMONT ST 1
2629 Harassment HARASSMENT E5 1 2018 1 CROWN POINT DR 1
1001 Counterfeiting FORGERY / COUNTERFEITING E5 8 2018 4 REDGATE RD 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD D4 22 2018 2 BOYLSTON ST 1
2629 Harassment HARASSMENT B2 9 2017 10 AKRON ST 1
1102 Fraud FRAUD - FALSE PRETENSE / SCHEME A7 25 2018 4 LIVERPOOL ST 1
3201 Property Lost PROPERTY - LOST D14 1 2018 1 FIDELIS WAY 1
1106 Confidence Games FRAUD - CREDIT CARD / ATM FRAUD E5 12 2018 4 SPRING ST 1
3201 Property Lost PROPERTY - LOST A1 30 2018 4 NASHUA ST 1
I need to aggregate this data into monthly data based on OFFENCE_CODE. So that NO_OF_CRIMES gets aggregated for that particular month. Any help would be really great.
r csv date aggregate
r csv date aggregate
edited Nov 12 at 19:04
12b345b6b78
767115
asked Nov 12 at 18:55
supriya singh
234
edited Nov 12 at 19:04
12b345b6b78
767115
asked Nov 12 at 18:55
supriya singh
234
edited Nov 12 at 19:04
12b345b6b78
767115
edited Nov 12 at 19:04
12b345b6b78
767115
edited Nov 12 at 19:04
12b345b6b78
767115
767115
asked Nov 12 at 18:55
supriya singh
234
asked Nov 12 at 18:55
supriya singh
234
asked Nov 12 at 18:55
supriya singh
234
234
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
Assuming x is the name of the dataframe:
aggregate(x$NO_OF_CRIME, by = list(x$OFFENCE_CODE, x$MONTH), FUN = sum)
answered Nov 12 at 19:01
12b345b6b78
767115
add a comment |
0
We can use tidyverse
library(tidyverse)
df1 %>%
group_by(OFFENCE_CODE, YEAR, MONTH) %>%
mutate(SUM_NO_OF_CRIME = sum(NO_OF_CRIME))
answered Nov 12 at 19:04
akrun
397k13187260
This is giving the sum of all NO_OF_CRIMES. I need my csv file to be aggregated by month and stored in the same table. I need monthwise data as I need to join this table with another table based on month and year.
– supriya singh
Nov 12 at 19:20
@supriyasingh If you need to create a column, usemutate. updated
– akrun
Nov 12 at 19:22
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%2f53268403%2fto-aggregate-a-csv-file-having-day-wise-data-into-month%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
0
Assuming x is the name of the dataframe:
aggregate(x$NO_OF_CRIME, by = list(x$OFFENCE_CODE, x$MONTH), FUN = sum)
answered Nov 12 at 19:01
12b345b6b78
767115
add a comment |
0
Assuming x is the name of the dataframe:
aggregate(x$NO_OF_CRIME, by = list(x$OFFENCE_CODE, x$MONTH), FUN = sum)
answered Nov 12 at 19:01
12b345b6b78
767115
add a comment |
0
0
0
Assuming x is the name of the dataframe:
aggregate(x$NO_OF_CRIME, by = list(x$OFFENCE_CODE, x$MONTH), FUN = sum)
answered Nov 12 at 19:01
12b345b6b78
767115
Assuming x is the name of the dataframe:
aggregate(x$NO_OF_CRIME, by = list(x$OFFENCE_CODE, x$MONTH), FUN = sum)
answered Nov 12 at 19:01
12b345b6b78
767115
answered Nov 12 at 19:01
12b345b6b78
767115
answered Nov 12 at 19:01
12b345b6b78
767115
answered Nov 12 at 19:01
12b345b6b78
767115
767115
add a comment |
add a comment |
0
We can use tidyverse
library(tidyverse)
df1 %>%
group_by(OFFENCE_CODE, YEAR, MONTH) %>%
mutate(SUM_NO_OF_CRIME = sum(NO_OF_CRIME))
answered Nov 12 at 19:04
akrun
397k13187260
This is giving the sum of all NO_OF_CRIMES. I need my csv file to be aggregated by month and stored in the same table. I need monthwise data as I need to join this table with another table based on month and year.
– supriya singh
Nov 12 at 19:20
@supriyasingh If you need to create a column, usemutate. updated
– akrun
Nov 12 at 19:22
add a comment |
0
We can use tidyverse
library(tidyverse)
df1 %>%
group_by(OFFENCE_CODE, YEAR, MONTH) %>%
mutate(SUM_NO_OF_CRIME = sum(NO_OF_CRIME))
answered Nov 12 at 19:04
akrun
397k13187260
This is giving the sum of all NO_OF_CRIMES. I need my csv file to be aggregated by month and stored in the same table. I need monthwise data as I need to join this table with another table based on month and year.
– supriya singh
Nov 12 at 19:20
@supriyasingh If you need to create a column, usemutate. updated
– akrun
Nov 12 at 19:22
add a comment |
0
0
0
We can use tidyverse
library(tidyverse)
df1 %>%
group_by(OFFENCE_CODE, YEAR, MONTH) %>%
mutate(SUM_NO_OF_CRIME = sum(NO_OF_CRIME))
answered Nov 12 at 19:04
akrun
397k13187260
We can use tidyverse
library(tidyverse)
df1 %>%
group_by(OFFENCE_CODE, YEAR, MONTH) %>%
mutate(SUM_NO_OF_CRIME = sum(NO_OF_CRIME))
answered Nov 12 at 19:04
akrun
397k13187260
edited Nov 12 at 19:22
answered Nov 12 at 19:04
akrun
397k13187260
answered Nov 12 at 19:04
akrun
397k13187260
answered Nov 12 at 19:04
akrun
397k13187260
397k13187260
This is giving the sum of all NO_OF_CRIMES. I need my csv file to be aggregated by month and stored in the same table. I need monthwise data as I need to join this table with another table based on month and year.
– supriya singh
Nov 12 at 19:20
@supriyasingh If you need to create a column, usemutate. updated
– akrun
Nov 12 at 19:22
add a comment |
This is giving the sum of all NO_OF_CRIMES. I need my csv file to be aggregated by month and stored in the same table. I need monthwise data as I need to join this table with another table based on month and year.
– supriya singh
Nov 12 at 19:20
@supriyasingh If you need to create a column, usemutate. updated
– akrun
Nov 12 at 19:22
This is giving the sum of all NO_OF_CRIMES. I need my csv file to be aggregated by month and stored in the same table. I need monthwise data as I need to join this table with another table based on month and year.
– supriya singh
Nov 12 at 19:20
This is giving the sum of all NO_OF_CRIMES. I need my csv file to be aggregated by month and stored in the same table. I need monthwise data as I need to join this table with another table based on month and year.
– supriya singh
Nov 12 at 19:20
@supriyasingh If you need to create a column, use
mutate. updated– akrun
Nov 12 at 19:22
@supriyasingh If you need to create a column, use
mutate. updated– akrun
Nov 12 at 19:22
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%2f53268403%2fto-aggregate-a-csv-file-having-day-wise-data-into-month%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
