Generating a variable to count consecutive entries
1
Let's say I have the following dataset
df <- read.table(text="UTCDate UTCTime White Black
2018.01.01 03:49:40 JL XN
2018.01.01 03:52:01 XN JL
2018.01.01 03:54:16 JL XN
2018.01.01 03:55:58 XN JL
2018.01.01 03:57:59 JL XN
2018.01.01 04:00:27 XN JL
2018.01.01 04:01:48 JL XN
2018.01.01 04:03:43 XN JL
2018.01.01 04:06:12 JL XN
2018.01.01 09:21:16 JL OC
2018.01.01 09:22:28 OC JL
2018.01.01 09:24:16 JL OC
2018.01.01 09:24:58 OC JL", header=T)
I am trying to create a column that tracks "consecutive games" where a consecutive game is defined as, say, any entry that is less than 10 minutes from the preceding entry. Here, consecutive game is defined from the perspective of some player (e.g. JL) so rows 1 through 9 would read (1:9) in consec_games column and rows 10 through 13 would be (1:4).
The column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. This is a large dataset where multiple player may be playing others at the same time. Therefore, the consecutive games column has to apply only to some pre-specified player (in this case, JL).
I have seen answers like this: Consecutive count by groups which take advantage of the lag function, but I'm not sure how to apply a lag to this dataset.
r dplyr
asked Nov 11 at 16:37
Parseltongue
2,844155693
add a comment |
1
Let's say I have the following dataset
df <- read.table(text="UTCDate UTCTime White Black
2018.01.01 03:49:40 JL XN
2018.01.01 03:52:01 XN JL
2018.01.01 03:54:16 JL XN
2018.01.01 03:55:58 XN JL
2018.01.01 03:57:59 JL XN
2018.01.01 04:00:27 XN JL
2018.01.01 04:01:48 JL XN
2018.01.01 04:03:43 XN JL
2018.01.01 04:06:12 JL XN
2018.01.01 09:21:16 JL OC
2018.01.01 09:22:28 OC JL
2018.01.01 09:24:16 JL OC
2018.01.01 09:24:58 OC JL", header=T)
I am trying to create a column that tracks "consecutive games" where a consecutive game is defined as, say, any entry that is less than 10 minutes from the preceding entry. Here, consecutive game is defined from the perspective of some player (e.g. JL) so rows 1 through 9 would read (1:9) in consec_games column and rows 10 through 13 would be (1:4).
The column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. This is a large dataset where multiple player may be playing others at the same time. Therefore, the consecutive games column has to apply only to some pre-specified player (in this case, JL).
I have seen answers like this: Consecutive count by groups which take advantage of the lag function, but I'm not sure how to apply a lag to this dataset.
r dplyr
asked Nov 11 at 16:37
Parseltongue
2,844155693
add a comment |
1
1
1
1
Let's say I have the following dataset
df <- read.table(text="UTCDate UTCTime White Black
2018.01.01 03:49:40 JL XN
2018.01.01 03:52:01 XN JL
2018.01.01 03:54:16 JL XN
2018.01.01 03:55:58 XN JL
2018.01.01 03:57:59 JL XN
2018.01.01 04:00:27 XN JL
2018.01.01 04:01:48 JL XN
2018.01.01 04:03:43 XN JL
2018.01.01 04:06:12 JL XN
2018.01.01 09:21:16 JL OC
2018.01.01 09:22:28 OC JL
2018.01.01 09:24:16 JL OC
2018.01.01 09:24:58 OC JL", header=T)
I am trying to create a column that tracks "consecutive games" where a consecutive game is defined as, say, any entry that is less than 10 minutes from the preceding entry. Here, consecutive game is defined from the perspective of some player (e.g. JL) so rows 1 through 9 would read (1:9) in consec_games column and rows 10 through 13 would be (1:4).
The column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. This is a large dataset where multiple player may be playing others at the same time. Therefore, the consecutive games column has to apply only to some pre-specified player (in this case, JL).
I have seen answers like this: Consecutive count by groups which take advantage of the lag function, but I'm not sure how to apply a lag to this dataset.
r dplyr
asked Nov 11 at 16:37
Parseltongue
2,844155693
Let's say I have the following dataset
df <- read.table(text="UTCDate UTCTime White Black
2018.01.01 03:49:40 JL XN
2018.01.01 03:52:01 XN JL
2018.01.01 03:54:16 JL XN
2018.01.01 03:55:58 XN JL
2018.01.01 03:57:59 JL XN
2018.01.01 04:00:27 XN JL
2018.01.01 04:01:48 JL XN
2018.01.01 04:03:43 XN JL
2018.01.01 04:06:12 JL XN
2018.01.01 09:21:16 JL OC
2018.01.01 09:22:28 OC JL
2018.01.01 09:24:16 JL OC
2018.01.01 09:24:58 OC JL", header=T)
I am trying to create a column that tracks "consecutive games" where a consecutive game is defined as, say, any entry that is less than 10 minutes from the preceding entry. Here, consecutive game is defined from the perspective of some player (e.g. JL) so rows 1 through 9 would read (1:9) in consec_games column and rows 10 through 13 would be (1:4).
The column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. This is a large dataset where multiple player may be playing others at the same time. Therefore, the consecutive games column has to apply only to some pre-specified player (in this case, JL).
I have seen answers like this: Consecutive count by groups which take advantage of the lag function, but I'm not sure how to apply a lag to this dataset.
r dplyr
r dplyr
asked Nov 11 at 16:37
Parseltongue
2,844155693
asked Nov 11 at 16:37
Parseltongue
2,844155693
edited Nov 11 at 18:35
asked Nov 11 at 16:37
Parseltongue
2,844155693
asked Nov 11 at 16:37
Parseltongue
2,844155693
asked Nov 11 at 16:37
Parseltongue
2,844155693
2,844155693
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
2
What about a convenience data.table function:
consecutive_plays <- function(df, player, date_var = "UTCDate", time_var = "UTCTime", white_var = "White", black_var = "Black", diff_time = 10, unit_time = "mins") {
require(data.table)
setDT(df)[, `:=` (TimeDimension = as.POSIXct(paste(get(date_var), get(time_var)), format = "%Y.%m.%d %H:%M:%S"),
Player_Present = get(white_var) == player | get(black_var) == player)][
, time_diff := as.numeric(difftime(TimeDimension, shift(TimeDimension), units = unit_time) >= diff_time), by = .(Player_Present)][
is.na(time_diff), time_diff := 0][
get(white_var) == player | get(black_var) == player, paste0(player, "_consecutive_games") := seq_len(.N), by = .(Player_Present, cumsum(time_diff))][
, c("TimeDimension", "time_diff", "Player_Present") := NULL
]
}
That you can call for any desired player:
df <- consecutive_plays(df, player = "JL")
And get the output with the corresponding column:
UTCDate UTCTime White Black JL_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1
2: 2018.01.01 03:52:01 XN JL 2
3: 2018.01.01 03:54:16 JL XN 3
4: 2018.01.01 03:55:58 XN JL 4
5: 2018.01.01 03:57:59 JL XN 5
6: 2018.01.01 04:00:27 XN JL 6
7: 2018.01.01 04:01:48 JL XN 7
8: 2018.01.01 04:03:43 XN JL 8
9: 2018.01.01 04:06:12 JL XN 9
10: 2018.01.01 09:21:16 JL OC 1
11: 2018.01.01 09:22:28 OC JL 2
12: 2018.01.01 09:24:16 JL OC 3
13: 2018.01.01 09:24:58 OC JL 4
If the player is not there for any of the games, the rows would be set to NA:
df <- consecutive_plays(df, player = "XN")
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1
2: 2018.01.01 03:52:01 XN JL 2 2
3: 2018.01.01 03:54:16 JL XN 3 3
4: 2018.01.01 03:55:58 XN JL 4 4
5: 2018.01.01 03:57:59 JL XN 5 5
6: 2018.01.01 04:00:27 XN JL 6 6
7: 2018.01.01 04:01:48 JL XN 7 7
8: 2018.01.01 04:03:43 XN JL 8 8
9: 2018.01.01 04:06:12 JL XN 9 9
10: 2018.01.01 09:21:16 JL OC 1 NA
11: 2018.01.01 09:22:28 OC JL 2 NA
12: 2018.01.01 09:24:16 JL OC 3 NA
13: 2018.01.01 09:24:58 OC JL 4 NA
It doesn't matter much how many players you have. You can easily quickly get the columns for all of them:
players <- unique(c(as.character(df$White), as.character(df$Black)))
for (player in players) { df <- consecutive_plays(df, player = player) }
Output:
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games OC_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1 NA
2: 2018.01.01 03:52:01 XN JL 2 2 NA
3: 2018.01.01 03:54:16 JL XN 3 3 NA
4: 2018.01.01 03:55:58 XN JL 4 4 NA
5: 2018.01.01 03:57:59 JL XN 5 5 NA
6: 2018.01.01 04:00:27 XN JL 6 6 NA
7: 2018.01.01 04:01:48 JL XN 7 7 NA
8: 2018.01.01 04:03:43 XN JL 8 8 NA
9: 2018.01.01 04:06:12 JL XN 9 9 NA
10: 2018.01.01 09:21:16 JL OC 1 NA 1
11: 2018.01.01 09:22:28 OC JL 2 NA 2
12: 2018.01.01 09:24:16 JL OC 3 NA 3
13: 2018.01.01 09:24:58 OC JL 4 NA 4
Eventually, you can also then configure other parameters, e.g. if you would like to change the diff_time to more than 10 minutes, or if you would like to change the unit_time to hours, days or weeks, etc.
answered Nov 11 at 19:30
arg0naut
1,987314
Wow, this is absolutely incredible. Thanks a lot. I really need to invest the time to learn data.table -- it looks extremely powerful, but so much harder to read than dplyr.
– Parseltongue
Nov 11 at 22:06
Thanks, glad it's helpful! I find both have their pros and con;dplyris certainly on the side of readability, sometimes it's also more practical (when it comes tocase_whenfor instance), however due to speed I generally preferdata.tablewhen writing functions or working with larger dataframes. P.S. There was a bug in the argument for desired time difference (it wouldn't work with anything else than 10 as it hasn't been transferred in the body of the function), I've fixed that in my edit, now it's calleddiff_time.
– arg0naut
Nov 11 at 22:15
1
Great! Thanks so much!
– Parseltongue
Nov 11 at 22:34
add a comment |
5
In base R only.
First make a data/time object with the two separate columns UTCDate and UTCTime. Then use a cumsum trick to get the groups. Finally ave applies a function (seq_along) to each of the vectors defined by d.
UTC <- paste(df$UTCDate, df$UTCTime)
UTC <- as.POSIXct(UTC, format = "%Y.%m.%d %H:%M:%S")
d <- c(0, difftime(UTC[-1], UTC[-length(UTC)], units = "mins"))
d <- cumsum(d > 10)
ave(seq_len(nrow(df)), d, FUN = seq_along)
#[1] 1 2 3 4 5 6 7 8 9 1 2 3 4
df$Games <- ave(seq_len(nrow(df)), d, FUN = seq_along)
rm(UTC, d) # Tidy up
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
Does this handle the "perspective of some player" aspect properly? Tho I don't really grok what the OP wants when they say that.
– hrbrmstr
Nov 11 at 17:05
@hrbrmstr Good question. The example given, playerJLis in all rows so I really don't know what the OP means by that and left it out.
– Rui Barradas
Nov 11 at 17:08
What I meant is that the column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. The values in this column would be different if it were from the perspective of another player (e.g. OB) if OB had been playing with some other player other than JL, and then immediately (within 10 minutes) began playing with JL.
– Parseltongue
Nov 11 at 18:28
add a comment |
1
Here is a solution using the tidyverse that gives you the result for all players:
library(tidyverse)
library(magrittr)
library(lubridate)
df %<>%
gather("color", "player", 3:4) %>%
unite(datetime, 1:2) %>%
arrange(datetime) %>%
mutate(name = player)
df$datetime %<>% parse_datetime("%Y.%m.%d_%H:%M:%S")
nested <- df %>%
mutate(cond = difftime(df$datetime, lag(df$datetime)) < 600) %>%
group_by(player) %>%
nest()
get_cons <- function(df) {
df$consecutive_games[1] <- 1
for(i in 2:nrow(df)) {
if(df$cond[i] == T) {
df$consecutive_games[i] <- df$consecutive_games[i - 1] + 1
}
df$cond[i] <- 1
}
df %>%
select(- cond)
}
options(tibble.print_max = Inf) # to show entire tibble
map_df(nested$data, get_cons)
# A tibble: 26 x 4
datetime color name consecutive_games
<dttm> <chr> <chr> <dbl>
1 2018-01-01 03:49:40 White JL 1
2 2018-01-01 03:52:01 Black JL 2
3 2018-01-01 03:54:16 White JL 3
4 2018-01-01 03:55:58 Black JL 4
5 2018-01-01 03:57:59 White JL 5
6 2018-01-01 04:00:27 Black JL 6
7 2018-01-01 04:01:48 White JL 7
8 2018-01-01 04:03:43 Black JL 8
9 2018-01-01 04:06:12 White JL 9
10 2018-01-01 09:21:16 White JL 1
11 2018-01-01 09:22:28 Black JL 2
12 2018-01-01 09:24:16 White JL 3
13 2018-01-01 09:24:58 Black JL 4
14 2018-01-01 03:49:40 Black XN 1
15 2018-01-01 03:52:01 White XN 2
16 2018-01-01 03:54:16 Black XN 3
17 2018-01-01 03:55:58 White XN 4
18 2018-01-01 03:57:59 Black XN 5
19 2018-01-01 04:00:27 White XN 6
20 2018-01-01 04:01:48 Black XN 7
21 2018-01-01 04:03:43 White XN 8
22 2018-01-01 04:06:12 Black XN 9
23 2018-01-01 09:21:16 Black OC 1
24 2018-01-01 09:22:28 White OC 2
25 2018-01-01 09:24:16 Black OC 3
26 2018-01-01 09:24:58 White OC 4
And if you prefer it in wide format:
map_df(nested$data, get_cons) %>%
rownames_to_column(var = "id") %>%
mutate_at(vars(id), funs(stringi::stri_pad_left(., width = 2, pad = "0"))) %>%
spread(name, consecutive_games)
# A tibble: 26 x 6
id datetime color JL OC XN
<chr> <dttm> <chr> <dbl> <dbl> <dbl>
1 01 2018-01-01 03:49:40 White 1 NA NA
2 02 2018-01-01 03:52:01 Black 2 NA NA
3 03 2018-01-01 03:54:16 White 3 NA NA
4 04 2018-01-01 03:55:58 Black 4 NA NA
5 05 2018-01-01 03:57:59 White 5 NA NA
6 06 2018-01-01 04:00:27 Black 6 NA NA
7 07 2018-01-01 04:01:48 White 7 NA NA
8 08 2018-01-01 04:03:43 Black 8 NA NA
9 09 2018-01-01 04:06:12 White 9 NA NA
10 10 2018-01-01 09:21:16 White 1 NA NA
11 11 2018-01-01 09:22:28 Black 2 NA NA
12 12 2018-01-01 09:24:16 White 3 NA NA
13 13 2018-01-01 09:24:58 Black 4 NA NA
14 14 2018-01-01 03:49:40 Black NA NA 1
15 15 2018-01-01 03:52:01 White NA NA 2
16 16 2018-01-01 03:54:16 Black NA NA 3
17 17 2018-01-01 03:55:58 White NA NA 4
18 18 2018-01-01 03:57:59 Black NA NA 5
19 19 2018-01-01 04:00:27 White NA NA 6
20 20 2018-01-01 04:01:48 Black NA NA 7
21 21 2018-01-01 04:03:43 White NA NA 8
22 22 2018-01-01 04:06:12 Black NA NA 9
23 23 2018-01-01 09:21:16 Black NA 1 NA
24 24 2018-01-01 09:22:28 White NA 2 NA
25 25 2018-01-01 09:24:16 Black NA 3 NA
26 26 2018-01-01 09:24:58 White NA 4 NA
answered Nov 11 at 21:26
prosoitos
910219
1
This is an extremely instructive and great answer, but I accepted argonaut because he was a bit faster, and it's a bit more flexible. Thanks so much for showing me how it's done in dplyr... so much left to learn!
– Parseltongue
Nov 11 at 22:34
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%2f53250860%2fgenerating-a-variable-to-count-consecutive-entries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
What about a convenience data.table function:
consecutive_plays <- function(df, player, date_var = "UTCDate", time_var = "UTCTime", white_var = "White", black_var = "Black", diff_time = 10, unit_time = "mins") {
require(data.table)
setDT(df)[, `:=` (TimeDimension = as.POSIXct(paste(get(date_var), get(time_var)), format = "%Y.%m.%d %H:%M:%S"),
Player_Present = get(white_var) == player | get(black_var) == player)][
, time_diff := as.numeric(difftime(TimeDimension, shift(TimeDimension), units = unit_time) >= diff_time), by = .(Player_Present)][
is.na(time_diff), time_diff := 0][
get(white_var) == player | get(black_var) == player, paste0(player, "_consecutive_games") := seq_len(.N), by = .(Player_Present, cumsum(time_diff))][
, c("TimeDimension", "time_diff", "Player_Present") := NULL
]
}
That you can call for any desired player:
df <- consecutive_plays(df, player = "JL")
And get the output with the corresponding column:
UTCDate UTCTime White Black JL_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1
2: 2018.01.01 03:52:01 XN JL 2
3: 2018.01.01 03:54:16 JL XN 3
4: 2018.01.01 03:55:58 XN JL 4
5: 2018.01.01 03:57:59 JL XN 5
6: 2018.01.01 04:00:27 XN JL 6
7: 2018.01.01 04:01:48 JL XN 7
8: 2018.01.01 04:03:43 XN JL 8
9: 2018.01.01 04:06:12 JL XN 9
10: 2018.01.01 09:21:16 JL OC 1
11: 2018.01.01 09:22:28 OC JL 2
12: 2018.01.01 09:24:16 JL OC 3
13: 2018.01.01 09:24:58 OC JL 4
If the player is not there for any of the games, the rows would be set to NA:
df <- consecutive_plays(df, player = "XN")
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1
2: 2018.01.01 03:52:01 XN JL 2 2
3: 2018.01.01 03:54:16 JL XN 3 3
4: 2018.01.01 03:55:58 XN JL 4 4
5: 2018.01.01 03:57:59 JL XN 5 5
6: 2018.01.01 04:00:27 XN JL 6 6
7: 2018.01.01 04:01:48 JL XN 7 7
8: 2018.01.01 04:03:43 XN JL 8 8
9: 2018.01.01 04:06:12 JL XN 9 9
10: 2018.01.01 09:21:16 JL OC 1 NA
11: 2018.01.01 09:22:28 OC JL 2 NA
12: 2018.01.01 09:24:16 JL OC 3 NA
13: 2018.01.01 09:24:58 OC JL 4 NA
It doesn't matter much how many players you have. You can easily quickly get the columns for all of them:
players <- unique(c(as.character(df$White), as.character(df$Black)))
for (player in players) { df <- consecutive_plays(df, player = player) }
Output:
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games OC_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1 NA
2: 2018.01.01 03:52:01 XN JL 2 2 NA
3: 2018.01.01 03:54:16 JL XN 3 3 NA
4: 2018.01.01 03:55:58 XN JL 4 4 NA
5: 2018.01.01 03:57:59 JL XN 5 5 NA
6: 2018.01.01 04:00:27 XN JL 6 6 NA
7: 2018.01.01 04:01:48 JL XN 7 7 NA
8: 2018.01.01 04:03:43 XN JL 8 8 NA
9: 2018.01.01 04:06:12 JL XN 9 9 NA
10: 2018.01.01 09:21:16 JL OC 1 NA 1
11: 2018.01.01 09:22:28 OC JL 2 NA 2
12: 2018.01.01 09:24:16 JL OC 3 NA 3
13: 2018.01.01 09:24:58 OC JL 4 NA 4
Eventually, you can also then configure other parameters, e.g. if you would like to change the diff_time to more than 10 minutes, or if you would like to change the unit_time to hours, days or weeks, etc.
answered Nov 11 at 19:30
arg0naut
1,987314
Wow, this is absolutely incredible. Thanks a lot. I really need to invest the time to learn data.table -- it looks extremely powerful, but so much harder to read than dplyr.
– Parseltongue
Nov 11 at 22:06
Thanks, glad it's helpful! I find both have their pros and con;dplyris certainly on the side of readability, sometimes it's also more practical (when it comes tocase_whenfor instance), however due to speed I generally preferdata.tablewhen writing functions or working with larger dataframes. P.S. There was a bug in the argument for desired time difference (it wouldn't work with anything else than 10 as it hasn't been transferred in the body of the function), I've fixed that in my edit, now it's calleddiff_time.
– arg0naut
Nov 11 at 22:15
1
Great! Thanks so much!
– Parseltongue
Nov 11 at 22:34
add a comment |
2
What about a convenience data.table function:
consecutive_plays <- function(df, player, date_var = "UTCDate", time_var = "UTCTime", white_var = "White", black_var = "Black", diff_time = 10, unit_time = "mins") {
require(data.table)
setDT(df)[, `:=` (TimeDimension = as.POSIXct(paste(get(date_var), get(time_var)), format = "%Y.%m.%d %H:%M:%S"),
Player_Present = get(white_var) == player | get(black_var) == player)][
, time_diff := as.numeric(difftime(TimeDimension, shift(TimeDimension), units = unit_time) >= diff_time), by = .(Player_Present)][
is.na(time_diff), time_diff := 0][
get(white_var) == player | get(black_var) == player, paste0(player, "_consecutive_games") := seq_len(.N), by = .(Player_Present, cumsum(time_diff))][
, c("TimeDimension", "time_diff", "Player_Present") := NULL
]
}
That you can call for any desired player:
df <- consecutive_plays(df, player = "JL")
And get the output with the corresponding column:
UTCDate UTCTime White Black JL_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1
2: 2018.01.01 03:52:01 XN JL 2
3: 2018.01.01 03:54:16 JL XN 3
4: 2018.01.01 03:55:58 XN JL 4
5: 2018.01.01 03:57:59 JL XN 5
6: 2018.01.01 04:00:27 XN JL 6
7: 2018.01.01 04:01:48 JL XN 7
8: 2018.01.01 04:03:43 XN JL 8
9: 2018.01.01 04:06:12 JL XN 9
10: 2018.01.01 09:21:16 JL OC 1
11: 2018.01.01 09:22:28 OC JL 2
12: 2018.01.01 09:24:16 JL OC 3
13: 2018.01.01 09:24:58 OC JL 4
If the player is not there for any of the games, the rows would be set to NA:
df <- consecutive_plays(df, player = "XN")
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1
2: 2018.01.01 03:52:01 XN JL 2 2
3: 2018.01.01 03:54:16 JL XN 3 3
4: 2018.01.01 03:55:58 XN JL 4 4
5: 2018.01.01 03:57:59 JL XN 5 5
6: 2018.01.01 04:00:27 XN JL 6 6
7: 2018.01.01 04:01:48 JL XN 7 7
8: 2018.01.01 04:03:43 XN JL 8 8
9: 2018.01.01 04:06:12 JL XN 9 9
10: 2018.01.01 09:21:16 JL OC 1 NA
11: 2018.01.01 09:22:28 OC JL 2 NA
12: 2018.01.01 09:24:16 JL OC 3 NA
13: 2018.01.01 09:24:58 OC JL 4 NA
It doesn't matter much how many players you have. You can easily quickly get the columns for all of them:
players <- unique(c(as.character(df$White), as.character(df$Black)))
for (player in players) { df <- consecutive_plays(df, player = player) }
Output:
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games OC_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1 NA
2: 2018.01.01 03:52:01 XN JL 2 2 NA
3: 2018.01.01 03:54:16 JL XN 3 3 NA
4: 2018.01.01 03:55:58 XN JL 4 4 NA
5: 2018.01.01 03:57:59 JL XN 5 5 NA
6: 2018.01.01 04:00:27 XN JL 6 6 NA
7: 2018.01.01 04:01:48 JL XN 7 7 NA
8: 2018.01.01 04:03:43 XN JL 8 8 NA
9: 2018.01.01 04:06:12 JL XN 9 9 NA
10: 2018.01.01 09:21:16 JL OC 1 NA 1
11: 2018.01.01 09:22:28 OC JL 2 NA 2
12: 2018.01.01 09:24:16 JL OC 3 NA 3
13: 2018.01.01 09:24:58 OC JL 4 NA 4
Eventually, you can also then configure other parameters, e.g. if you would like to change the diff_time to more than 10 minutes, or if you would like to change the unit_time to hours, days or weeks, etc.
answered Nov 11 at 19:30
arg0naut
1,987314
Wow, this is absolutely incredible. Thanks a lot. I really need to invest the time to learn data.table -- it looks extremely powerful, but so much harder to read than dplyr.
– Parseltongue
Nov 11 at 22:06
Thanks, glad it's helpful! I find both have their pros and con;dplyris certainly on the side of readability, sometimes it's also more practical (when it comes tocase_whenfor instance), however due to speed I generally preferdata.tablewhen writing functions or working with larger dataframes. P.S. There was a bug in the argument for desired time difference (it wouldn't work with anything else than 10 as it hasn't been transferred in the body of the function), I've fixed that in my edit, now it's calleddiff_time.
– arg0naut
Nov 11 at 22:15
1
Great! Thanks so much!
– Parseltongue
Nov 11 at 22:34
add a comment |
2
2
2
What about a convenience data.table function:
consecutive_plays <- function(df, player, date_var = "UTCDate", time_var = "UTCTime", white_var = "White", black_var = "Black", diff_time = 10, unit_time = "mins") {
require(data.table)
setDT(df)[, `:=` (TimeDimension = as.POSIXct(paste(get(date_var), get(time_var)), format = "%Y.%m.%d %H:%M:%S"),
Player_Present = get(white_var) == player | get(black_var) == player)][
, time_diff := as.numeric(difftime(TimeDimension, shift(TimeDimension), units = unit_time) >= diff_time), by = .(Player_Present)][
is.na(time_diff), time_diff := 0][
get(white_var) == player | get(black_var) == player, paste0(player, "_consecutive_games") := seq_len(.N), by = .(Player_Present, cumsum(time_diff))][
, c("TimeDimension", "time_diff", "Player_Present") := NULL
]
}
That you can call for any desired player:
df <- consecutive_plays(df, player = "JL")
And get the output with the corresponding column:
UTCDate UTCTime White Black JL_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1
2: 2018.01.01 03:52:01 XN JL 2
3: 2018.01.01 03:54:16 JL XN 3
4: 2018.01.01 03:55:58 XN JL 4
5: 2018.01.01 03:57:59 JL XN 5
6: 2018.01.01 04:00:27 XN JL 6
7: 2018.01.01 04:01:48 JL XN 7
8: 2018.01.01 04:03:43 XN JL 8
9: 2018.01.01 04:06:12 JL XN 9
10: 2018.01.01 09:21:16 JL OC 1
11: 2018.01.01 09:22:28 OC JL 2
12: 2018.01.01 09:24:16 JL OC 3
13: 2018.01.01 09:24:58 OC JL 4
If the player is not there for any of the games, the rows would be set to NA:
df <- consecutive_plays(df, player = "XN")
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1
2: 2018.01.01 03:52:01 XN JL 2 2
3: 2018.01.01 03:54:16 JL XN 3 3
4: 2018.01.01 03:55:58 XN JL 4 4
5: 2018.01.01 03:57:59 JL XN 5 5
6: 2018.01.01 04:00:27 XN JL 6 6
7: 2018.01.01 04:01:48 JL XN 7 7
8: 2018.01.01 04:03:43 XN JL 8 8
9: 2018.01.01 04:06:12 JL XN 9 9
10: 2018.01.01 09:21:16 JL OC 1 NA
11: 2018.01.01 09:22:28 OC JL 2 NA
12: 2018.01.01 09:24:16 JL OC 3 NA
13: 2018.01.01 09:24:58 OC JL 4 NA
It doesn't matter much how many players you have. You can easily quickly get the columns for all of them:
players <- unique(c(as.character(df$White), as.character(df$Black)))
for (player in players) { df <- consecutive_plays(df, player = player) }
Output:
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games OC_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1 NA
2: 2018.01.01 03:52:01 XN JL 2 2 NA
3: 2018.01.01 03:54:16 JL XN 3 3 NA
4: 2018.01.01 03:55:58 XN JL 4 4 NA
5: 2018.01.01 03:57:59 JL XN 5 5 NA
6: 2018.01.01 04:00:27 XN JL 6 6 NA
7: 2018.01.01 04:01:48 JL XN 7 7 NA
8: 2018.01.01 04:03:43 XN JL 8 8 NA
9: 2018.01.01 04:06:12 JL XN 9 9 NA
10: 2018.01.01 09:21:16 JL OC 1 NA 1
11: 2018.01.01 09:22:28 OC JL 2 NA 2
12: 2018.01.01 09:24:16 JL OC 3 NA 3
13: 2018.01.01 09:24:58 OC JL 4 NA 4
Eventually, you can also then configure other parameters, e.g. if you would like to change the diff_time to more than 10 minutes, or if you would like to change the unit_time to hours, days or weeks, etc.
answered Nov 11 at 19:30
arg0naut
1,987314
What about a convenience data.table function:
consecutive_plays <- function(df, player, date_var = "UTCDate", time_var = "UTCTime", white_var = "White", black_var = "Black", diff_time = 10, unit_time = "mins") {
require(data.table)
setDT(df)[, `:=` (TimeDimension = as.POSIXct(paste(get(date_var), get(time_var)), format = "%Y.%m.%d %H:%M:%S"),
Player_Present = get(white_var) == player | get(black_var) == player)][
, time_diff := as.numeric(difftime(TimeDimension, shift(TimeDimension), units = unit_time) >= diff_time), by = .(Player_Present)][
is.na(time_diff), time_diff := 0][
get(white_var) == player | get(black_var) == player, paste0(player, "_consecutive_games") := seq_len(.N), by = .(Player_Present, cumsum(time_diff))][
, c("TimeDimension", "time_diff", "Player_Present") := NULL
]
}
That you can call for any desired player:
df <- consecutive_plays(df, player = "JL")
And get the output with the corresponding column:
UTCDate UTCTime White Black JL_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1
2: 2018.01.01 03:52:01 XN JL 2
3: 2018.01.01 03:54:16 JL XN 3
4: 2018.01.01 03:55:58 XN JL 4
5: 2018.01.01 03:57:59 JL XN 5
6: 2018.01.01 04:00:27 XN JL 6
7: 2018.01.01 04:01:48 JL XN 7
8: 2018.01.01 04:03:43 XN JL 8
9: 2018.01.01 04:06:12 JL XN 9
10: 2018.01.01 09:21:16 JL OC 1
11: 2018.01.01 09:22:28 OC JL 2
12: 2018.01.01 09:24:16 JL OC 3
13: 2018.01.01 09:24:58 OC JL 4
If the player is not there for any of the games, the rows would be set to NA:
df <- consecutive_plays(df, player = "XN")
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1
2: 2018.01.01 03:52:01 XN JL 2 2
3: 2018.01.01 03:54:16 JL XN 3 3
4: 2018.01.01 03:55:58 XN JL 4 4
5: 2018.01.01 03:57:59 JL XN 5 5
6: 2018.01.01 04:00:27 XN JL 6 6
7: 2018.01.01 04:01:48 JL XN 7 7
8: 2018.01.01 04:03:43 XN JL 8 8
9: 2018.01.01 04:06:12 JL XN 9 9
10: 2018.01.01 09:21:16 JL OC 1 NA
11: 2018.01.01 09:22:28 OC JL 2 NA
12: 2018.01.01 09:24:16 JL OC 3 NA
13: 2018.01.01 09:24:58 OC JL 4 NA
It doesn't matter much how many players you have. You can easily quickly get the columns for all of them:
players <- unique(c(as.character(df$White), as.character(df$Black)))
for (player in players) { df <- consecutive_plays(df, player = player) }
Output:
UTCDate UTCTime White Black JL_consecutive_games XN_consecutive_games OC_consecutive_games
1: 2018.01.01 03:49:40 JL XN 1 1 NA
2: 2018.01.01 03:52:01 XN JL 2 2 NA
3: 2018.01.01 03:54:16 JL XN 3 3 NA
4: 2018.01.01 03:55:58 XN JL 4 4 NA
5: 2018.01.01 03:57:59 JL XN 5 5 NA
6: 2018.01.01 04:00:27 XN JL 6 6 NA
7: 2018.01.01 04:01:48 JL XN 7 7 NA
8: 2018.01.01 04:03:43 XN JL 8 8 NA
9: 2018.01.01 04:06:12 JL XN 9 9 NA
10: 2018.01.01 09:21:16 JL OC 1 NA 1
11: 2018.01.01 09:22:28 OC JL 2 NA 2
12: 2018.01.01 09:24:16 JL OC 3 NA 3
13: 2018.01.01 09:24:58 OC JL 4 NA 4
Eventually, you can also then configure other parameters, e.g. if you would like to change the diff_time to more than 10 minutes, or if you would like to change the unit_time to hours, days or weeks, etc.
answered Nov 11 at 19:30
arg0naut
1,987314
edited Nov 11 at 22:22
answered Nov 11 at 19:30
arg0naut
1,987314
answered Nov 11 at 19:30
arg0naut
1,987314
answered Nov 11 at 19:30
arg0naut
1,987314
1,987314
Wow, this is absolutely incredible. Thanks a lot. I really need to invest the time to learn data.table -- it looks extremely powerful, but so much harder to read than dplyr.
– Parseltongue
Nov 11 at 22:06
Thanks, glad it's helpful! I find both have their pros and con;dplyris certainly on the side of readability, sometimes it's also more practical (when it comes tocase_whenfor instance), however due to speed I generally preferdata.tablewhen writing functions or working with larger dataframes. P.S. There was a bug in the argument for desired time difference (it wouldn't work with anything else than 10 as it hasn't been transferred in the body of the function), I've fixed that in my edit, now it's calleddiff_time.
– arg0naut
Nov 11 at 22:15
1
Great! Thanks so much!
– Parseltongue
Nov 11 at 22:34
add a comment |
Wow, this is absolutely incredible. Thanks a lot. I really need to invest the time to learn data.table -- it looks extremely powerful, but so much harder to read than dplyr.
– Parseltongue
Nov 11 at 22:06
Thanks, glad it's helpful! I find both have their pros and con;dplyris certainly on the side of readability, sometimes it's also more practical (when it comes tocase_whenfor instance), however due to speed I generally preferdata.tablewhen writing functions or working with larger dataframes. P.S. There was a bug in the argument for desired time difference (it wouldn't work with anything else than 10 as it hasn't been transferred in the body of the function), I've fixed that in my edit, now it's calleddiff_time.
– arg0naut
Nov 11 at 22:15
1
Great! Thanks so much!
– Parseltongue
Nov 11 at 22:34
Wow, this is absolutely incredible. Thanks a lot. I really need to invest the time to learn data.table -- it looks extremely powerful, but so much harder to read than dplyr.
– Parseltongue
Nov 11 at 22:06
Wow, this is absolutely incredible. Thanks a lot. I really need to invest the time to learn data.table -- it looks extremely powerful, but so much harder to read than dplyr.
– Parseltongue
Nov 11 at 22:06
Thanks, glad it's helpful! I find both have their pros and con;
dplyr is certainly on the side of readability, sometimes it's also more practical (when it comes to case_when for instance), however due to speed I generally prefer data.table when writing functions or working with larger dataframes. P.S. There was a bug in the argument for desired time difference (it wouldn't work with anything else than 10 as it hasn't been transferred in the body of the function), I've fixed that in my edit, now it's called diff_time.– arg0naut
Nov 11 at 22:15
Thanks, glad it's helpful! I find both have their pros and con;
dplyr is certainly on the side of readability, sometimes it's also more practical (when it comes to case_when for instance), however due to speed I generally prefer data.table when writing functions or working with larger dataframes. P.S. There was a bug in the argument for desired time difference (it wouldn't work with anything else than 10 as it hasn't been transferred in the body of the function), I've fixed that in my edit, now it's called diff_time.– arg0naut
Nov 11 at 22:15
1
1
Great! Thanks so much!
– Parseltongue
Nov 11 at 22:34
Great! Thanks so much!
– Parseltongue
Nov 11 at 22:34
add a comment |
5
In base R only.
First make a data/time object with the two separate columns UTCDate and UTCTime. Then use a cumsum trick to get the groups. Finally ave applies a function (seq_along) to each of the vectors defined by d.
UTC <- paste(df$UTCDate, df$UTCTime)
UTC <- as.POSIXct(UTC, format = "%Y.%m.%d %H:%M:%S")
d <- c(0, difftime(UTC[-1], UTC[-length(UTC)], units = "mins"))
d <- cumsum(d > 10)
ave(seq_len(nrow(df)), d, FUN = seq_along)
#[1] 1 2 3 4 5 6 7 8 9 1 2 3 4
df$Games <- ave(seq_len(nrow(df)), d, FUN = seq_along)
rm(UTC, d) # Tidy up
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
Does this handle the "perspective of some player" aspect properly? Tho I don't really grok what the OP wants when they say that.
– hrbrmstr
Nov 11 at 17:05
@hrbrmstr Good question. The example given, playerJLis in all rows so I really don't know what the OP means by that and left it out.
– Rui Barradas
Nov 11 at 17:08
What I meant is that the column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. The values in this column would be different if it were from the perspective of another player (e.g. OB) if OB had been playing with some other player other than JL, and then immediately (within 10 minutes) began playing with JL.
– Parseltongue
Nov 11 at 18:28
add a comment |
5
In base R only.
First make a data/time object with the two separate columns UTCDate and UTCTime. Then use a cumsum trick to get the groups. Finally ave applies a function (seq_along) to each of the vectors defined by d.
UTC <- paste(df$UTCDate, df$UTCTime)
UTC <- as.POSIXct(UTC, format = "%Y.%m.%d %H:%M:%S")
d <- c(0, difftime(UTC[-1], UTC[-length(UTC)], units = "mins"))
d <- cumsum(d > 10)
ave(seq_len(nrow(df)), d, FUN = seq_along)
#[1] 1 2 3 4 5 6 7 8 9 1 2 3 4
df$Games <- ave(seq_len(nrow(df)), d, FUN = seq_along)
rm(UTC, d) # Tidy up
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
Does this handle the "perspective of some player" aspect properly? Tho I don't really grok what the OP wants when they say that.
– hrbrmstr
Nov 11 at 17:05
@hrbrmstr Good question. The example given, playerJLis in all rows so I really don't know what the OP means by that and left it out.
– Rui Barradas
Nov 11 at 17:08
What I meant is that the column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. The values in this column would be different if it were from the perspective of another player (e.g. OB) if OB had been playing with some other player other than JL, and then immediately (within 10 minutes) began playing with JL.
– Parseltongue
Nov 11 at 18:28
add a comment |
5
5
5
In base R only.
First make a data/time object with the two separate columns UTCDate and UTCTime. Then use a cumsum trick to get the groups. Finally ave applies a function (seq_along) to each of the vectors defined by d.
UTC <- paste(df$UTCDate, df$UTCTime)
UTC <- as.POSIXct(UTC, format = "%Y.%m.%d %H:%M:%S")
d <- c(0, difftime(UTC[-1], UTC[-length(UTC)], units = "mins"))
d <- cumsum(d > 10)
ave(seq_len(nrow(df)), d, FUN = seq_along)
#[1] 1 2 3 4 5 6 7 8 9 1 2 3 4
df$Games <- ave(seq_len(nrow(df)), d, FUN = seq_along)
rm(UTC, d) # Tidy up
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
In base R only.
First make a data/time object with the two separate columns UTCDate and UTCTime. Then use a cumsum trick to get the groups. Finally ave applies a function (seq_along) to each of the vectors defined by d.
UTC <- paste(df$UTCDate, df$UTCTime)
UTC <- as.POSIXct(UTC, format = "%Y.%m.%d %H:%M:%S")
d <- c(0, difftime(UTC[-1], UTC[-length(UTC)], units = "mins"))
d <- cumsum(d > 10)
ave(seq_len(nrow(df)), d, FUN = seq_along)
#[1] 1 2 3 4 5 6 7 8 9 1 2 3 4
df$Games <- ave(seq_len(nrow(df)), d, FUN = seq_along)
rm(UTC, d) # Tidy up
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
answered Nov 11 at 16:56
Rui Barradas
15.9k41730
15.9k41730
Does this handle the "perspective of some player" aspect properly? Tho I don't really grok what the OP wants when they say that.
– hrbrmstr
Nov 11 at 17:05
@hrbrmstr Good question. The example given, playerJLis in all rows so I really don't know what the OP means by that and left it out.
– Rui Barradas
Nov 11 at 17:08
What I meant is that the column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. The values in this column would be different if it were from the perspective of another player (e.g. OB) if OB had been playing with some other player other than JL, and then immediately (within 10 minutes) began playing with JL.
– Parseltongue
Nov 11 at 18:28
add a comment |
Does this handle the "perspective of some player" aspect properly? Tho I don't really grok what the OP wants when they say that.
– hrbrmstr
Nov 11 at 17:05
@hrbrmstr Good question. The example given, playerJLis in all rows so I really don't know what the OP means by that and left it out.
– Rui Barradas
Nov 11 at 17:08
What I meant is that the column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. The values in this column would be different if it were from the perspective of another player (e.g. OB) if OB had been playing with some other player other than JL, and then immediately (within 10 minutes) began playing with JL.
– Parseltongue
Nov 11 at 18:28
Does this handle the "perspective of some player" aspect properly? Tho I don't really grok what the OP wants when they say that.
– hrbrmstr
Nov 11 at 17:05
Does this handle the "perspective of some player" aspect properly? Tho I don't really grok what the OP wants when they say that.
– hrbrmstr
Nov 11 at 17:05
@hrbrmstr Good question. The example given, player
JL is in all rows so I really don't know what the OP means by that and left it out.– Rui Barradas
Nov 11 at 17:08
@hrbrmstr Good question. The example given, player
JL is in all rows so I really don't know what the OP means by that and left it out.– Rui Barradas
Nov 11 at 17:08
What I meant is that the column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. The values in this column would be different if it were from the perspective of another player (e.g. OB) if OB had been playing with some other player other than JL, and then immediately (within 10 minutes) began playing with JL.
– Parseltongue
Nov 11 at 18:28
What I meant is that the column "consecutive_games" should only apply to a single player (e.g. JL). So it would be JL_consecutive_games. The values in this column would be different if it were from the perspective of another player (e.g. OB) if OB had been playing with some other player other than JL, and then immediately (within 10 minutes) began playing with JL.
– Parseltongue
Nov 11 at 18:28
add a comment |
1
Here is a solution using the tidyverse that gives you the result for all players:
library(tidyverse)
library(magrittr)
library(lubridate)
df %<>%
gather("color", "player", 3:4) %>%
unite(datetime, 1:2) %>%
arrange(datetime) %>%
mutate(name = player)
df$datetime %<>% parse_datetime("%Y.%m.%d_%H:%M:%S")
nested <- df %>%
mutate(cond = difftime(df$datetime, lag(df$datetime)) < 600) %>%
group_by(player) %>%
nest()
get_cons <- function(df) {
df$consecutive_games[1] <- 1
for(i in 2:nrow(df)) {
if(df$cond[i] == T) {
df$consecutive_games[i] <- df$consecutive_games[i - 1] + 1
}
df$cond[i] <- 1
}
df %>%
select(- cond)
}
options(tibble.print_max = Inf) # to show entire tibble
map_df(nested$data, get_cons)
# A tibble: 26 x 4
datetime color name consecutive_games
<dttm> <chr> <chr> <dbl>
1 2018-01-01 03:49:40 White JL 1
2 2018-01-01 03:52:01 Black JL 2
3 2018-01-01 03:54:16 White JL 3
4 2018-01-01 03:55:58 Black JL 4
5 2018-01-01 03:57:59 White JL 5
6 2018-01-01 04:00:27 Black JL 6
7 2018-01-01 04:01:48 White JL 7
8 2018-01-01 04:03:43 Black JL 8
9 2018-01-01 04:06:12 White JL 9
10 2018-01-01 09:21:16 White JL 1
11 2018-01-01 09:22:28 Black JL 2
12 2018-01-01 09:24:16 White JL 3
13 2018-01-01 09:24:58 Black JL 4
14 2018-01-01 03:49:40 Black XN 1
15 2018-01-01 03:52:01 White XN 2
16 2018-01-01 03:54:16 Black XN 3
17 2018-01-01 03:55:58 White XN 4
18 2018-01-01 03:57:59 Black XN 5
19 2018-01-01 04:00:27 White XN 6
20 2018-01-01 04:01:48 Black XN 7
21 2018-01-01 04:03:43 White XN 8
22 2018-01-01 04:06:12 Black XN 9
23 2018-01-01 09:21:16 Black OC 1
24 2018-01-01 09:22:28 White OC 2
25 2018-01-01 09:24:16 Black OC 3
26 2018-01-01 09:24:58 White OC 4
And if you prefer it in wide format:
map_df(nested$data, get_cons) %>%
rownames_to_column(var = "id") %>%
mutate_at(vars(id), funs(stringi::stri_pad_left(., width = 2, pad = "0"))) %>%
spread(name, consecutive_games)
# A tibble: 26 x 6
id datetime color JL OC XN
<chr> <dttm> <chr> <dbl> <dbl> <dbl>
1 01 2018-01-01 03:49:40 White 1 NA NA
2 02 2018-01-01 03:52:01 Black 2 NA NA
3 03 2018-01-01 03:54:16 White 3 NA NA
4 04 2018-01-01 03:55:58 Black 4 NA NA
5 05 2018-01-01 03:57:59 White 5 NA NA
6 06 2018-01-01 04:00:27 Black 6 NA NA
7 07 2018-01-01 04:01:48 White 7 NA NA
8 08 2018-01-01 04:03:43 Black 8 NA NA
9 09 2018-01-01 04:06:12 White 9 NA NA
10 10 2018-01-01 09:21:16 White 1 NA NA
11 11 2018-01-01 09:22:28 Black 2 NA NA
12 12 2018-01-01 09:24:16 White 3 NA NA
13 13 2018-01-01 09:24:58 Black 4 NA NA
14 14 2018-01-01 03:49:40 Black NA NA 1
15 15 2018-01-01 03:52:01 White NA NA 2
16 16 2018-01-01 03:54:16 Black NA NA 3
17 17 2018-01-01 03:55:58 White NA NA 4
18 18 2018-01-01 03:57:59 Black NA NA 5
19 19 2018-01-01 04:00:27 White NA NA 6
20 20 2018-01-01 04:01:48 Black NA NA 7
21 21 2018-01-01 04:03:43 White NA NA 8
22 22 2018-01-01 04:06:12 Black NA NA 9
23 23 2018-01-01 09:21:16 Black NA 1 NA
24 24 2018-01-01 09:22:28 White NA 2 NA
25 25 2018-01-01 09:24:16 Black NA 3 NA
26 26 2018-01-01 09:24:58 White NA 4 NA
answered Nov 11 at 21:26
prosoitos
910219
1
This is an extremely instructive and great answer, but I accepted argonaut because he was a bit faster, and it's a bit more flexible. Thanks so much for showing me how it's done in dplyr... so much left to learn!
– Parseltongue
Nov 11 at 22:34
add a comment |
1
Here is a solution using the tidyverse that gives you the result for all players:
library(tidyverse)
library(magrittr)
library(lubridate)
df %<>%
gather("color", "player", 3:4) %>%
unite(datetime, 1:2) %>%
arrange(datetime) %>%
mutate(name = player)
df$datetime %<>% parse_datetime("%Y.%m.%d_%H:%M:%S")
nested <- df %>%
mutate(cond = difftime(df$datetime, lag(df$datetime)) < 600) %>%
group_by(player) %>%
nest()
get_cons <- function(df) {
df$consecutive_games[1] <- 1
for(i in 2:nrow(df)) {
if(df$cond[i] == T) {
df$consecutive_games[i] <- df$consecutive_games[i - 1] + 1
}
df$cond[i] <- 1
}
df %>%
select(- cond)
}
options(tibble.print_max = Inf) # to show entire tibble
map_df(nested$data, get_cons)
# A tibble: 26 x 4
datetime color name consecutive_games
<dttm> <chr> <chr> <dbl>
1 2018-01-01 03:49:40 White JL 1
2 2018-01-01 03:52:01 Black JL 2
3 2018-01-01 03:54:16 White JL 3
4 2018-01-01 03:55:58 Black JL 4
5 2018-01-01 03:57:59 White JL 5
6 2018-01-01 04:00:27 Black JL 6
7 2018-01-01 04:01:48 White JL 7
8 2018-01-01 04:03:43 Black JL 8
9 2018-01-01 04:06:12 White JL 9
10 2018-01-01 09:21:16 White JL 1
11 2018-01-01 09:22:28 Black JL 2
12 2018-01-01 09:24:16 White JL 3
13 2018-01-01 09:24:58 Black JL 4
14 2018-01-01 03:49:40 Black XN 1
15 2018-01-01 03:52:01 White XN 2
16 2018-01-01 03:54:16 Black XN 3
17 2018-01-01 03:55:58 White XN 4
18 2018-01-01 03:57:59 Black XN 5
19 2018-01-01 04:00:27 White XN 6
20 2018-01-01 04:01:48 Black XN 7
21 2018-01-01 04:03:43 White XN 8
22 2018-01-01 04:06:12 Black XN 9
23 2018-01-01 09:21:16 Black OC 1
24 2018-01-01 09:22:28 White OC 2
25 2018-01-01 09:24:16 Black OC 3
26 2018-01-01 09:24:58 White OC 4
And if you prefer it in wide format:
map_df(nested$data, get_cons) %>%
rownames_to_column(var = "id") %>%
mutate_at(vars(id), funs(stringi::stri_pad_left(., width = 2, pad = "0"))) %>%
spread(name, consecutive_games)
# A tibble: 26 x 6
id datetime color JL OC XN
<chr> <dttm> <chr> <dbl> <dbl> <dbl>
1 01 2018-01-01 03:49:40 White 1 NA NA
2 02 2018-01-01 03:52:01 Black 2 NA NA
3 03 2018-01-01 03:54:16 White 3 NA NA
4 04 2018-01-01 03:55:58 Black 4 NA NA
5 05 2018-01-01 03:57:59 White 5 NA NA
6 06 2018-01-01 04:00:27 Black 6 NA NA
7 07 2018-01-01 04:01:48 White 7 NA NA
8 08 2018-01-01 04:03:43 Black 8 NA NA
9 09 2018-01-01 04:06:12 White 9 NA NA
10 10 2018-01-01 09:21:16 White 1 NA NA
11 11 2018-01-01 09:22:28 Black 2 NA NA
12 12 2018-01-01 09:24:16 White 3 NA NA
13 13 2018-01-01 09:24:58 Black 4 NA NA
14 14 2018-01-01 03:49:40 Black NA NA 1
15 15 2018-01-01 03:52:01 White NA NA 2
16 16 2018-01-01 03:54:16 Black NA NA 3
17 17 2018-01-01 03:55:58 White NA NA 4
18 18 2018-01-01 03:57:59 Black NA NA 5
19 19 2018-01-01 04:00:27 White NA NA 6
20 20 2018-01-01 04:01:48 Black NA NA 7
21 21 2018-01-01 04:03:43 White NA NA 8
22 22 2018-01-01 04:06:12 Black NA NA 9
23 23 2018-01-01 09:21:16 Black NA 1 NA
24 24 2018-01-01 09:22:28 White NA 2 NA
25 25 2018-01-01 09:24:16 Black NA 3 NA
26 26 2018-01-01 09:24:58 White NA 4 NA
answered Nov 11 at 21:26
prosoitos
910219
1
This is an extremely instructive and great answer, but I accepted argonaut because he was a bit faster, and it's a bit more flexible. Thanks so much for showing me how it's done in dplyr... so much left to learn!
– Parseltongue
Nov 11 at 22:34
add a comment |
1
1
1
Here is a solution using the tidyverse that gives you the result for all players:
library(tidyverse)
library(magrittr)
library(lubridate)
df %<>%
gather("color", "player", 3:4) %>%
unite(datetime, 1:2) %>%
arrange(datetime) %>%
mutate(name = player)
df$datetime %<>% parse_datetime("%Y.%m.%d_%H:%M:%S")
nested <- df %>%
mutate(cond = difftime(df$datetime, lag(df$datetime)) < 600) %>%
group_by(player) %>%
nest()
get_cons <- function(df) {
df$consecutive_games[1] <- 1
for(i in 2:nrow(df)) {
if(df$cond[i] == T) {
df$consecutive_games[i] <- df$consecutive_games[i - 1] + 1
}
df$cond[i] <- 1
}
df %>%
select(- cond)
}
options(tibble.print_max = Inf) # to show entire tibble
map_df(nested$data, get_cons)
# A tibble: 26 x 4
datetime color name consecutive_games
<dttm> <chr> <chr> <dbl>
1 2018-01-01 03:49:40 White JL 1
2 2018-01-01 03:52:01 Black JL 2
3 2018-01-01 03:54:16 White JL 3
4 2018-01-01 03:55:58 Black JL 4
5 2018-01-01 03:57:59 White JL 5
6 2018-01-01 04:00:27 Black JL 6
7 2018-01-01 04:01:48 White JL 7
8 2018-01-01 04:03:43 Black JL 8
9 2018-01-01 04:06:12 White JL 9
10 2018-01-01 09:21:16 White JL 1
11 2018-01-01 09:22:28 Black JL 2
12 2018-01-01 09:24:16 White JL 3
13 2018-01-01 09:24:58 Black JL 4
14 2018-01-01 03:49:40 Black XN 1
15 2018-01-01 03:52:01 White XN 2
16 2018-01-01 03:54:16 Black XN 3
17 2018-01-01 03:55:58 White XN 4
18 2018-01-01 03:57:59 Black XN 5
19 2018-01-01 04:00:27 White XN 6
20 2018-01-01 04:01:48 Black XN 7
21 2018-01-01 04:03:43 White XN 8
22 2018-01-01 04:06:12 Black XN 9
23 2018-01-01 09:21:16 Black OC 1
24 2018-01-01 09:22:28 White OC 2
25 2018-01-01 09:24:16 Black OC 3
26 2018-01-01 09:24:58 White OC 4
And if you prefer it in wide format:
map_df(nested$data, get_cons) %>%
rownames_to_column(var = "id") %>%
mutate_at(vars(id), funs(stringi::stri_pad_left(., width = 2, pad = "0"))) %>%
spread(name, consecutive_games)
# A tibble: 26 x 6
id datetime color JL OC XN
<chr> <dttm> <chr> <dbl> <dbl> <dbl>
1 01 2018-01-01 03:49:40 White 1 NA NA
2 02 2018-01-01 03:52:01 Black 2 NA NA
3 03 2018-01-01 03:54:16 White 3 NA NA
4 04 2018-01-01 03:55:58 Black 4 NA NA
5 05 2018-01-01 03:57:59 White 5 NA NA
6 06 2018-01-01 04:00:27 Black 6 NA NA
7 07 2018-01-01 04:01:48 White 7 NA NA
8 08 2018-01-01 04:03:43 Black 8 NA NA
9 09 2018-01-01 04:06:12 White 9 NA NA
10 10 2018-01-01 09:21:16 White 1 NA NA
11 11 2018-01-01 09:22:28 Black 2 NA NA
12 12 2018-01-01 09:24:16 White 3 NA NA
13 13 2018-01-01 09:24:58 Black 4 NA NA
14 14 2018-01-01 03:49:40 Black NA NA 1
15 15 2018-01-01 03:52:01 White NA NA 2
16 16 2018-01-01 03:54:16 Black NA NA 3
17 17 2018-01-01 03:55:58 White NA NA 4
18 18 2018-01-01 03:57:59 Black NA NA 5
19 19 2018-01-01 04:00:27 White NA NA 6
20 20 2018-01-01 04:01:48 Black NA NA 7
21 21 2018-01-01 04:03:43 White NA NA 8
22 22 2018-01-01 04:06:12 Black NA NA 9
23 23 2018-01-01 09:21:16 Black NA 1 NA
24 24 2018-01-01 09:22:28 White NA 2 NA
25 25 2018-01-01 09:24:16 Black NA 3 NA
26 26 2018-01-01 09:24:58 White NA 4 NA
answered Nov 11 at 21:26
prosoitos
910219
Here is a solution using the tidyverse that gives you the result for all players:
library(tidyverse)
library(magrittr)
library(lubridate)
df %<>%
gather("color", "player", 3:4) %>%
unite(datetime, 1:2) %>%
arrange(datetime) %>%
mutate(name = player)
df$datetime %<>% parse_datetime("%Y.%m.%d_%H:%M:%S")
nested <- df %>%
mutate(cond = difftime(df$datetime, lag(df$datetime)) < 600) %>%
group_by(player) %>%
nest()
get_cons <- function(df) {
df$consecutive_games[1] <- 1
for(i in 2:nrow(df)) {
if(df$cond[i] == T) {
df$consecutive_games[i] <- df$consecutive_games[i - 1] + 1
}
df$cond[i] <- 1
}
df %>%
select(- cond)
}
options(tibble.print_max = Inf) # to show entire tibble
map_df(nested$data, get_cons)
# A tibble: 26 x 4
datetime color name consecutive_games
<dttm> <chr> <chr> <dbl>
1 2018-01-01 03:49:40 White JL 1
2 2018-01-01 03:52:01 Black JL 2
3 2018-01-01 03:54:16 White JL 3
4 2018-01-01 03:55:58 Black JL 4
5 2018-01-01 03:57:59 White JL 5
6 2018-01-01 04:00:27 Black JL 6
7 2018-01-01 04:01:48 White JL 7
8 2018-01-01 04:03:43 Black JL 8
9 2018-01-01 04:06:12 White JL 9
10 2018-01-01 09:21:16 White JL 1
11 2018-01-01 09:22:28 Black JL 2
12 2018-01-01 09:24:16 White JL 3
13 2018-01-01 09:24:58 Black JL 4
14 2018-01-01 03:49:40 Black XN 1
15 2018-01-01 03:52:01 White XN 2
16 2018-01-01 03:54:16 Black XN 3
17 2018-01-01 03:55:58 White XN 4
18 2018-01-01 03:57:59 Black XN 5
19 2018-01-01 04:00:27 White XN 6
20 2018-01-01 04:01:48 Black XN 7
21 2018-01-01 04:03:43 White XN 8
22 2018-01-01 04:06:12 Black XN 9
23 2018-01-01 09:21:16 Black OC 1
24 2018-01-01 09:22:28 White OC 2
25 2018-01-01 09:24:16 Black OC 3
26 2018-01-01 09:24:58 White OC 4
And if you prefer it in wide format:
map_df(nested$data, get_cons) %>%
rownames_to_column(var = "id") %>%
mutate_at(vars(id), funs(stringi::stri_pad_left(., width = 2, pad = "0"))) %>%
spread(name, consecutive_games)
# A tibble: 26 x 6
id datetime color JL OC XN
<chr> <dttm> <chr> <dbl> <dbl> <dbl>
1 01 2018-01-01 03:49:40 White 1 NA NA
2 02 2018-01-01 03:52:01 Black 2 NA NA
3 03 2018-01-01 03:54:16 White 3 NA NA
4 04 2018-01-01 03:55:58 Black 4 NA NA
5 05 2018-01-01 03:57:59 White 5 NA NA
6 06 2018-01-01 04:00:27 Black 6 NA NA
7 07 2018-01-01 04:01:48 White 7 NA NA
8 08 2018-01-01 04:03:43 Black 8 NA NA
9 09 2018-01-01 04:06:12 White 9 NA NA
10 10 2018-01-01 09:21:16 White 1 NA NA
11 11 2018-01-01 09:22:28 Black 2 NA NA
12 12 2018-01-01 09:24:16 White 3 NA NA
13 13 2018-01-01 09:24:58 Black 4 NA NA
14 14 2018-01-01 03:49:40 Black NA NA 1
15 15 2018-01-01 03:52:01 White NA NA 2
16 16 2018-01-01 03:54:16 Black NA NA 3
17 17 2018-01-01 03:55:58 White NA NA 4
18 18 2018-01-01 03:57:59 Black NA NA 5
19 19 2018-01-01 04:00:27 White NA NA 6
20 20 2018-01-01 04:01:48 Black NA NA 7
21 21 2018-01-01 04:03:43 White NA NA 8
22 22 2018-01-01 04:06:12 Black NA NA 9
23 23 2018-01-01 09:21:16 Black NA 1 NA
24 24 2018-01-01 09:22:28 White NA 2 NA
25 25 2018-01-01 09:24:16 Black NA 3 NA
26 26 2018-01-01 09:24:58 White NA 4 NA
answered Nov 11 at 21:26
prosoitos
910219
edited Nov 11 at 22:10
answered Nov 11 at 21:26
prosoitos
910219
answered Nov 11 at 21:26
prosoitos
910219
answered Nov 11 at 21:26
prosoitos
910219
910219
1
This is an extremely instructive and great answer, but I accepted argonaut because he was a bit faster, and it's a bit more flexible. Thanks so much for showing me how it's done in dplyr... so much left to learn!
– Parseltongue
Nov 11 at 22:34
add a comment |
1
This is an extremely instructive and great answer, but I accepted argonaut because he was a bit faster, and it's a bit more flexible. Thanks so much for showing me how it's done in dplyr... so much left to learn!
– Parseltongue
Nov 11 at 22:34
1
1
This is an extremely instructive and great answer, but I accepted argonaut because he was a bit faster, and it's a bit more flexible. Thanks so much for showing me how it's done in dplyr... so much left to learn!
– Parseltongue
Nov 11 at 22:34
This is an extremely instructive and great answer, but I accepted argonaut because he was a bit faster, and it's a bit more flexible. Thanks so much for showing me how it's done in dplyr... so much left to learn!
– Parseltongue
Nov 11 at 22:34
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%2f53250860%2fgenerating-a-variable-to-count-consecutive-entries%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
