Turning Character columns containing numbers into Numeric in R

Question

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.

Answer 1

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))))