Mutate each day and each hour using tidyverse functions in R

Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
Currently I have code returns each a tibble of events that occur each day using the following:
online_toy_purchases %>%
mutate(interval = lubridate::date(date)) %>%
group_by(interval) %>%
summarise(count = n())
This currently returns the following:
# A tibble: 31 x 2
interval count
2018-12-01 500
2018-12-02 300
2018-12-03 400
2018-12-04 200
2018-12-05 600
...
2018-12-31 100
I would like my code to group by each hour and each day for a more granular view of the data, which would return the following:
# A tibble: 744 x 2
interval count
2018-12-01 01:00:00 50
2018-12-01 02:00:00 60
2018-12-01 03:00:00 20
2018-12-01 04:00:00 80
...
2018-12-31 24:00:00 10
online_toy_purchases is a tibble that contains, among other features, the ID of the transaction and a timestamp containing the date and the hour, minute and second of the purchase (i.e -> "2018-12-01 01:20:58")
r datetime time-series tidyverse tidyr
add a comment |
Currently I have code returns each a tibble of events that occur each day using the following:
online_toy_purchases %>%
mutate(interval = lubridate::date(date)) %>%
group_by(interval) %>%
summarise(count = n())
This currently returns the following:
# A tibble: 31 x 2
interval count
2018-12-01 500
2018-12-02 300
2018-12-03 400
2018-12-04 200
2018-12-05 600
...
2018-12-31 100
I would like my code to group by each hour and each day for a more granular view of the data, which would return the following:
# A tibble: 744 x 2
interval count
2018-12-01 01:00:00 50
2018-12-01 02:00:00 60
2018-12-01 03:00:00 20
2018-12-01 04:00:00 80
...
2018-12-31 24:00:00 10
online_toy_purchases is a tibble that contains, among other features, the ID of the transaction and a timestamp containing the date and the hour, minute and second of the purchase (i.e -> "2018-12-01 01:20:58")
r datetime time-series tidyverse tidyr
3
It's easier to help you if you include a simple reproducible example with sample input and desired output that can be used to test and verify possible solutions.
– MrFlick
Jan 3 at 22:07
1
What if you just didgroup_by(interval, lubridate::hour(date))
? Unable to test because there is no reproducible example.
– MrFlick
Jan 3 at 22:08
This returns a tibble with interval, 'lubridate::hour(date)', count as features, with the middle feature displaying the hours. This is really close to what I want, but wouldn't be suitable for plotting. Working on getting some reproducible data to this post.
– Sepa
Jan 3 at 22:19
add a comment |
Currently I have code returns each a tibble of events that occur each day using the following:
online_toy_purchases %>%
mutate(interval = lubridate::date(date)) %>%
group_by(interval) %>%
summarise(count = n())
This currently returns the following:
# A tibble: 31 x 2
interval count
2018-12-01 500
2018-12-02 300
2018-12-03 400
2018-12-04 200
2018-12-05 600
...
2018-12-31 100
I would like my code to group by each hour and each day for a more granular view of the data, which would return the following:
# A tibble: 744 x 2
interval count
2018-12-01 01:00:00 50
2018-12-01 02:00:00 60
2018-12-01 03:00:00 20
2018-12-01 04:00:00 80
...
2018-12-31 24:00:00 10
online_toy_purchases is a tibble that contains, among other features, the ID of the transaction and a timestamp containing the date and the hour, minute and second of the purchase (i.e -> "2018-12-01 01:20:58")
r datetime time-series tidyverse tidyr
Currently I have code returns each a tibble of events that occur each day using the following:
online_toy_purchases %>%
mutate(interval = lubridate::date(date)) %>%
group_by(interval) %>%
summarise(count = n())
This currently returns the following:
# A tibble: 31 x 2
interval count
2018-12-01 500
2018-12-02 300
2018-12-03 400
2018-12-04 200
2018-12-05 600
...
2018-12-31 100
I would like my code to group by each hour and each day for a more granular view of the data, which would return the following:
# A tibble: 744 x 2
interval count
2018-12-01 01:00:00 50
2018-12-01 02:00:00 60
2018-12-01 03:00:00 20
2018-12-01 04:00:00 80
...
2018-12-31 24:00:00 10
online_toy_purchases is a tibble that contains, among other features, the ID of the transaction and a timestamp containing the date and the hour, minute and second of the purchase (i.e -> "2018-12-01 01:20:58")
r datetime time-series tidyverse tidyr
r datetime time-series tidyverse tidyr
asked Jan 3 at 22:06


SepaSepa
123
123
3
It's easier to help you if you include a simple reproducible example with sample input and desired output that can be used to test and verify possible solutions.
– MrFlick
Jan 3 at 22:07
1
What if you just didgroup_by(interval, lubridate::hour(date))
? Unable to test because there is no reproducible example.
– MrFlick
Jan 3 at 22:08
This returns a tibble with interval, 'lubridate::hour(date)', count as features, with the middle feature displaying the hours. This is really close to what I want, but wouldn't be suitable for plotting. Working on getting some reproducible data to this post.
– Sepa
Jan 3 at 22:19
add a comment |
3
It's easier to help you if you include a simple reproducible example with sample input and desired output that can be used to test and verify possible solutions.
– MrFlick
Jan 3 at 22:07
1
What if you just didgroup_by(interval, lubridate::hour(date))
? Unable to test because there is no reproducible example.
– MrFlick
Jan 3 at 22:08
This returns a tibble with interval, 'lubridate::hour(date)', count as features, with the middle feature displaying the hours. This is really close to what I want, but wouldn't be suitable for plotting. Working on getting some reproducible data to this post.
– Sepa
Jan 3 at 22:19
3
3
It's easier to help you if you include a simple reproducible example with sample input and desired output that can be used to test and verify possible solutions.
– MrFlick
Jan 3 at 22:07
It's easier to help you if you include a simple reproducible example with sample input and desired output that can be used to test and verify possible solutions.
– MrFlick
Jan 3 at 22:07
1
1
What if you just did
group_by(interval, lubridate::hour(date))
? Unable to test because there is no reproducible example.– MrFlick
Jan 3 at 22:08
What if you just did
group_by(interval, lubridate::hour(date))
? Unable to test because there is no reproducible example.– MrFlick
Jan 3 at 22:08
This returns a tibble with interval, 'lubridate::hour(date)', count as features, with the middle feature displaying the hours. This is really close to what I want, but wouldn't be suitable for plotting. Working on getting some reproducible data to this post.
– Sepa
Jan 3 at 22:19
This returns a tibble with interval, 'lubridate::hour(date)', count as features, with the middle feature displaying the hours. This is really close to what I want, but wouldn't be suitable for plotting. Working on getting some reproducible data to this post.
– Sepa
Jan 3 at 22:19
add a comment |
1 Answer
1
active
oldest
votes
This will count the number of rows within each hour of the data.
library(tidyverse)
online_toy_purchases %>%
# assuming that "date" is formatted as a datetime variable already
count(time = lubridate::floor_date(date, "1 hour")) %>%
# additional step using padr::pad to add missing hours and
# tidyr::replace_na to make NAs into zeroes
padr::pad() %>%
replace_na(list(n=0))
For visualization and further analysis, it will be helpful to have rows recording periods with no data. You might alternatively accomplish something similar by converting to a tsibble
.
Thanks! This gets me very close. How would I use padr or tsibble to return a "0" value for unmentioned hours? Those exist in this data.
– Sepa
Jan 3 at 22:25
updated answer to include padr step.
– Jon Spring
Jan 3 at 23:50
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
});
}
});
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%2f54030451%2fmutate-each-day-and-each-hour-using-tidyverse-functions-in-r%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This will count the number of rows within each hour of the data.
library(tidyverse)
online_toy_purchases %>%
# assuming that "date" is formatted as a datetime variable already
count(time = lubridate::floor_date(date, "1 hour")) %>%
# additional step using padr::pad to add missing hours and
# tidyr::replace_na to make NAs into zeroes
padr::pad() %>%
replace_na(list(n=0))
For visualization and further analysis, it will be helpful to have rows recording periods with no data. You might alternatively accomplish something similar by converting to a tsibble
.
Thanks! This gets me very close. How would I use padr or tsibble to return a "0" value for unmentioned hours? Those exist in this data.
– Sepa
Jan 3 at 22:25
updated answer to include padr step.
– Jon Spring
Jan 3 at 23:50
add a comment |
This will count the number of rows within each hour of the data.
library(tidyverse)
online_toy_purchases %>%
# assuming that "date" is formatted as a datetime variable already
count(time = lubridate::floor_date(date, "1 hour")) %>%
# additional step using padr::pad to add missing hours and
# tidyr::replace_na to make NAs into zeroes
padr::pad() %>%
replace_na(list(n=0))
For visualization and further analysis, it will be helpful to have rows recording periods with no data. You might alternatively accomplish something similar by converting to a tsibble
.
Thanks! This gets me very close. How would I use padr or tsibble to return a "0" value for unmentioned hours? Those exist in this data.
– Sepa
Jan 3 at 22:25
updated answer to include padr step.
– Jon Spring
Jan 3 at 23:50
add a comment |
This will count the number of rows within each hour of the data.
library(tidyverse)
online_toy_purchases %>%
# assuming that "date" is formatted as a datetime variable already
count(time = lubridate::floor_date(date, "1 hour")) %>%
# additional step using padr::pad to add missing hours and
# tidyr::replace_na to make NAs into zeroes
padr::pad() %>%
replace_na(list(n=0))
For visualization and further analysis, it will be helpful to have rows recording periods with no data. You might alternatively accomplish something similar by converting to a tsibble
.
This will count the number of rows within each hour of the data.
library(tidyverse)
online_toy_purchases %>%
# assuming that "date" is formatted as a datetime variable already
count(time = lubridate::floor_date(date, "1 hour")) %>%
# additional step using padr::pad to add missing hours and
# tidyr::replace_na to make NAs into zeroes
padr::pad() %>%
replace_na(list(n=0))
For visualization and further analysis, it will be helpful to have rows recording periods with no data. You might alternatively accomplish something similar by converting to a tsibble
.
edited Jan 3 at 23:48
answered Jan 3 at 22:16


Jon SpringJon Spring
7,9532929
7,9532929
Thanks! This gets me very close. How would I use padr or tsibble to return a "0" value for unmentioned hours? Those exist in this data.
– Sepa
Jan 3 at 22:25
updated answer to include padr step.
– Jon Spring
Jan 3 at 23:50
add a comment |
Thanks! This gets me very close. How would I use padr or tsibble to return a "0" value for unmentioned hours? Those exist in this data.
– Sepa
Jan 3 at 22:25
updated answer to include padr step.
– Jon Spring
Jan 3 at 23:50
Thanks! This gets me very close. How would I use padr or tsibble to return a "0" value for unmentioned hours? Those exist in this data.
– Sepa
Jan 3 at 22:25
Thanks! This gets me very close. How would I use padr or tsibble to return a "0" value for unmentioned hours? Those exist in this data.
– Sepa
Jan 3 at 22:25
updated answer to include padr step.
– Jon Spring
Jan 3 at 23:50
updated answer to include padr step.
– Jon Spring
Jan 3 at 23:50
add a comment |
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.
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%2f54030451%2fmutate-each-day-and-each-hour-using-tidyverse-functions-in-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
g06N l1L KgBVl e VoqC 1 Vt
3
It's easier to help you if you include a simple reproducible example with sample input and desired output that can be used to test and verify possible solutions.
– MrFlick
Jan 3 at 22:07
1
What if you just did
group_by(interval, lubridate::hour(date))
? Unable to test because there is no reproducible example.– MrFlick
Jan 3 at 22:08
This returns a tibble with interval, 'lubridate::hour(date)', count as features, with the middle feature displaying the hours. This is really close to what I want, but wouldn't be suitable for plotting. Working on getting some reproducible data to this post.
– Sepa
Jan 3 at 22:19