Generating a variable to count consecutive entries
up vote
1
down vote
favorite
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,782155692
add a comment |
up vote
1
down vote
favorite
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,782155692
add a comment |
up vote
1
down vote
favorite
1
up vote
1
down vote
favorite
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,782155692
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,782155692
asked Nov 11 at 16:37
Parseltongue
2,782155692
edited Nov 11 at 18:35
asked Nov 11 at 16:37
Parseltongue
2,782155692
asked Nov 11 at 16:37
Parseltongue
2,782155692
asked Nov 11 at 16:37
Parseltongue
2,782155692
2,782155692
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
up vote
2
down vote
accepted
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,657312
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 |
up vote
5
down vote
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.2k31730
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 |
up vote
1
down vote
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
914219
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 |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
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,657312
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 |
up vote
2
down vote
accepted
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,657312
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 |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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,657312
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,657312
edited Nov 11 at 22:22
answered Nov 11 at 19:30
arg0naut
1,657312
answered Nov 11 at 19:30
arg0naut
1,657312
answered Nov 11 at 19:30
arg0naut
1,657312
1,657312
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 |
up vote
5
down vote
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.2k31730
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 |
up vote
5
down vote
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.2k31730
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 |
up vote
5
down vote
up vote
5
down vote
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.2k31730
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.2k31730
answered Nov 11 at 16:56
Rui Barradas
15.2k31730
answered Nov 11 at 16:56
Rui Barradas
15.2k31730
answered Nov 11 at 16:56
Rui Barradas
15.2k31730
15.2k31730
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 |
up vote
1
down vote
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
914219
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 |
up vote
1
down vote
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
914219
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 |
up vote
1
down vote
up vote
1
down vote
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
914219
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
914219
edited Nov 11 at 22:10
answered Nov 11 at 21:26
prosoitos
914219
answered Nov 11 at 21:26
prosoitos
914219
answered Nov 11 at 21:26
prosoitos
914219
914219
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
