Splitting data on data.frame column

user_16474878 0
Viewed 55

I am currently working on a project where I am trying to calculate various data, however, the CSV file I am working with has an anomaly in the column. It contains a date in the format "%d/%m/%y" format followed immediately by a string.

This is repeated throughout the entire column (column is headerless just in case) and what I am currently trying to achieve is replace the date throughout the column with leaving the remaining string only.

My current approach is to use gsub function, which looks as follows:

gsub(".[/]|[/]|[[:digit:]].", " ", dataset column)

This seems to work initially, however when running a head command, it appears to apply this only for the first 6-7 fields and the rest are appearing as NA values.

Is there any limitations to the GSub function if I am working with a column of 3000+ entries or is there something wrong with the logic behind the code to achieve this.

Here is the sample data used for the code:

structure(list(V1 = c("3/3/2005Mitsubishi", "3/4/2006Jaguar", 
"13/2/2007Land Rover", "12/12/2009Ferrari", "4/4/2008Jeep", "3/3/2005Honda"
), V2 = c("Mitsubish", "Jaguar", "Land Rover", "Ferrari", "Jeep", 
"Honda")), row.names = c(NA, 6L), class = "data.frame")
analytics dataframe r
2 Answers

In case you would like to keep all three columns. Just bear in mind that we defined two captured groups, one to capture date and the other to capture the rest. So in regex argument of tidyr::extract we defind how are strings are separated based on our defined captured groups and sit into as many columns:

library(tidyr)

df %>%
  extract(V1, c("V1", "V3"), "(\\d+\\/\\d+\\/\\d+)(.*)")

          V1         V3         V2
1   3/3/2005 Mitsubishi  Mitsubish
2   3/4/2006     Jaguar     Jaguar
3  13/2/2007 Land Rover Land Rover
4 12/12/2009    Ferrari    Ferrari
5   4/4/2008       Jeep       Jeep
6   3/3/2005      Honda      Honda

In case there are multiple columns you would like to separate, one way is to write a custom function and apply in on every column with data-string combination:

library(rlang)

fn <- function(...) {
  dots <- dots_list(...)
  extract(..., into = sprintf("%s_col_%d", dots[[2]], 1:2), regex = "(\\d+\\/\\d+\\/\\d+)(.*)")
}

# Now imagine we have a data set called df2 which has two columns we 
# would like to separate

df %>%
  mutate(V3 = V1) %>%
  select(V1, V3) -> df2

                   V1                  V3
1  3/3/2005Mitsubishi  3/3/2005Mitsubishi
2      3/4/2006Jaguar      3/4/2006Jaguar
3 13/2/2007Land Rover 13/2/2007Land Rover
4   12/12/2009Ferrari   12/12/2009Ferrari
5        4/4/2008Jeep        4/4/2008Jeep
6       3/3/2005Honda       3/3/2005Honda

Then we apply it on our data set choosing only columns that we would like to be separated:

# Instead of `names(df2)` you can choose a character vector containing the
# name of the columns you would like to separate

library(purrr)
names(df2) %>%
  reduce(~ fn(.x, .y), .init = df2)

    V1_col_1   V1_col_2   V3_col_1   V3_col_2
1   3/3/2005 Mitsubishi   3/3/2005 Mitsubishi
2   3/4/2006     Jaguar   3/4/2006     Jaguar
3  13/2/2007 Land Rover  13/2/2007 Land Rover
4 12/12/2009    Ferrari 12/12/2009    Ferrari
5   4/4/2008       Jeep   4/4/2008       Jeep
6   3/3/2005      Honda   3/3/2005      Honda

I found this post very helpful for this solution.

user_143145200

strsplit at a (emulated) lookbehind.

r <- data.frame(do.call(rbind, 
                        strsplit(dat[, 1], '\\d{4}\\K', perl=TRUE)), dat[-1])
r[, 1] <- as.Date(r[, 1], '%D')  ## transform the date
r
#           X1         X2         V2
# 1 2003-03-20 Mitsubishi  Mitsubish
# 2 2003-04-20     Jaguar     Jaguar
# 3 2013-02-20 Land Rover Land Rover
# 4 2012-12-20    Ferrari    Ferrari
# 5 2004-04-20       Jeep       Jeep
# 6 2003-03-20      Honda      Honda

This answer explains the \K well.

Edit

Or leave out the "transform the date" step if you don't need the date, and do r <- r[-1].

Data:

dat <- structure(list(V1 = c("3/3/2005Mitsubishi", "3/4/2006Jaguar", 
"13/2/2007Land Rover", "12/12/2009Ferrari", "4/4/2008Jeep", "3/3/2005Honda"
), V2 = c("Mitsubish", "Jaguar", "Land Rover", "Ferrari", "Jeep", 
"Honda")), row.names = c(NA, 6L), class = "data.frame")
user_65740380
Related