I am working with a dataset that lays out some of it’s data in ways that are not the most useful to later work on. For example:
ID Group timestamp location
1 2 12 secs c(50,120)
2 1 3 secs c(20,45)
3 1 7 secs c(12,30)
4 2 18 secs c(45,100)
5 3 4 secs c(0,80)
I want to separate the location column into 2 numeric columns, and have the timestamp column become numeric in order to work on them as such.
Tried to remove characters and use as.numeric but upon starting any mutate ork with the columns I get an non-numeric argument to binary operator error.
data= data %>%
mutate(timestamp = gsub("\secs", "", timestamp)) %>%
mutate(location = gsub("\c()", "", location)) %>%
separate(location, c("location.x", "location.y"), sep = ",") %>%
drop_na(timestamp,
location.y)
as.numeric(data$timestamp)
as.numeric(data&location.y)
data = data %>%
group_by(Group) %>%
mutate(av_location.y = mean(location.y),
av_time = max(timestamp) - min(timestamp))
If anyone know how I might get around this character vector issue it would be appreciated.
2
We assume that the data is as shown reproducibly in the Note at the end. It either looks like data where the location column is character or like data2 where the location column is a list of numeric vectors. The code handles both but if it is a character vector then the {…} line could be optionally omitted without changing anything.
Extract the timestamp using separate. This will also create a junk column which we eliminate using the NA shown. convert=TRUE causes the character numbers to be converted to numeric.
The next line checks if location is a list column and if so converts it to a character column. This line could be omitted if we knew that location were character.
Finally use separate again on location.
library(dplyr)
library(tidyr)
data %>%
separate(timestamp, c("timestamp", NA), convert = TRUE) %>%
{ if (is.list(.$location)) mutate(., location = paste(location)) else . } %>%
separate(location, c(NA,"location1", "location2", NA), convert = TRUE)
giving
ID Group timestamp location1 location2
1 1 2 12 50 120
2 2 1 3 20 45
3 3 1 7 12 30
4 4 2 18 45 100
5 5 3 4 0 80
Note
data <- data.frame(
ID = 1:5,
Group = c(2L, 1L, 1L, 2L, 3L),
timestamp = c("12 secs", "3 secs", "7 secs", "18 secs", "4 secs"),
location = c("c(50,120)", "c(20,45)", "c(12,30)", "c(45,100)", "c(0,80)")
data2 <- data %>%
mutate(location = lapply(location, (x) eval(parse(text = x))))
1
Assuming that you indeed deal with character columns:
library(dplyr, warn.conflicts = FALSE)
data <- tribble(
~ID, ~Group, ~timestamp, ~location,
1, 2, "12 secs", "c(50,120)",
2, 1, "3 secs" , "c(20,45)",
3, 1, "7 secs" , "c(12,30)",
4, 2, "18 secs", "c(45,100)",
5, 3, "4 secs" , "c(0,80)")
data |>
mutate(timestamp = readr::parse_number(timestamp),
location = purrr::map(location, (loc) textConnection(loc) |> dget())) |>
tidyr::unnest_wider(location, names_sep = ".")
#> # A tibble: 5 × 5
#> ID Group timestamp location.1 location.2
#> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 1 2 12 50 120
#> 2 2 1 3 20 45
#> 3 3 1 7 12 30
#> 4 4 2 18 45 100
#> 5 5 3 4 0 80
Expressions like as.numeric(data$timestamp) on their own do not really store any changes, you’d need assign that result, i.e.
data$timestamp <- as.numeric(data$timestamp)
Just in case anyone is interested, here is how I would do this using only base R functions. It would be a little bit odd using base R to work with a tibble but it does work and perhaps this will help someone else with a similar question.
I use gsub() to remove non-numeric characters, I use strsplit() to separate the two locations from each other, and I use lapply() to get just the first or just the second element of each list element. See comments in the code!
Thank you to user margusl (from the other answer) for the code to create the data.
library(dplyr)
## stack overflow user margusl's code to create your data:
data <- tribble(
~ID, ~Group, ~timestamp, ~location,
1, 2, "12 secs", "c(50,120)",
2, 1, "3 secs" , "c(20,45)",
3, 1, "7 secs" , "c(12,30)",
4, 2, "18 secs", "c(45,100)",
5, 3, "4 secs" , "c(0,80)")
## Make a new column that removes non-numeric characters from the
## timestamps and converts the type to numeric
data$time <- as.numeric(gsub("\D", "", data$timestamp))
## Split the strings containing the two locations by the comma so we
## have a list of vectors each of length 2 where the first element has
## the first location and the second has the second
split_by_comma <- strsplit(data$location, ',')
## Then get the first element from each list element
data$loc1 <- lapply(split_by_comma, '[', 1)
## And remove all non-numeric characters and convert to numeric type
data$loc1 <- as.numeric(gsub("\D", "", data$loc1))
## Repeat for the second element of each list element
data$loc2 <- lapply(split_by_comma, '[', 2)
data$loc2 <- as.numeric(gsub("\D", "", data$loc2))
For df0 representing your data, an un-optimized base R approach,
lapply(df0[sapply(df0, is.character)], (a) {
lapply(regmatches(a, gregexpr("[[:digit:]]+", a)) , strtoi) |>
list2DF() |> t() }) |> do.call(what="cbind") |>
`colnames<-`(c("timestamp", "location.1" ,"location.2"))
which does not include (length) checks, gives
timestamp location.1 location.2
12 50 120
3 20 45
7 12 30
18 45 100
4 0 80
Another option might be
cbind(strtoi(sub("\D+", "", df0$timestamp)),
t(vapply(df0$location, (i) eval(str2expression(i)), numeric(2L), USE.NAMES=FALSE))) |>
`colnames<-`(c("timestamp", "location.1" ,"location.2"))
(where , USE.NAMES=FALSE))) |> `colnames<-`(c("timestamp", "location.1" ,"location.2")) is cosmetics and might be replaced with a single closing ).)
This is very uncommon. I suspect we are dealing with an xy-problem. Where does location = c("c(50,120)", "c(20,45)", ...) come from?