How can I convert a non-numeric variable column into two numeric variable columns?

Question

Using R, I need help converting a non-numeric column into two numeric ones. I want to split the non-numeric data in column x, with the value before the dash going into one column (Start) and the value after the dash going into another column (End). Then, I want to create a new numeric column containing the difference between the Start and End columns with 1 added to the difference. (The Diff column contains a year count, so from 2011 to 2018 will be eight years.)

I encountered unexpected problems when I tried to do it. First, the x variable displayed as a factor. Second, the data in the Start and End columns were not numeric and when I tried to make them numeric so the Diff calculation could occur, I got a coercion error. Third, I could not get strsplit to work.

I checked stackoverflow solutions for comparable problems, but was unable to find one that presented a solution that worked for me.

The input data is just a very small sample of what is in the actual file

I would prefer a solution that uses dplyr, but am open to other ones.

Input

dput(df)
structure(list(x = c(NA, "1950-1960", "1975-1986", "2011-2018"
)), class = "data.frame", row.names = c(NA, -4L))

Output

x          Start  End   Diff
1950-1960  1950   1960  11
1975-1986  1975   1986  12
2011-2018  2011   2018   8

Related: [Convert comma separated string to numeric columns](https://stackoverflow.com/questions/8464312/convert-comma-separated-string-to-numeric-columns) — Henrik, Jun 16 '20 at 19:34
I'm partial to `splitstackshape`. `library(splitstackshape); cSplit(df, "x", "-", drop = FALSE)[, diff := x_2 - x_1 + 1][]`. — A5C1D2H2I1M1N2O1R2T1, Jun 17 '20 at 03:36

score 4 · Answer 1 · answered Jun 16 '20 at 17:21

df$Start = as.numeric(unlist(lapply(strsplit(df$x, "-"), `[`, 1)))
df$End   = as.numeric(unlist(lapply(strsplit(df$x, "-"), `[`, 2)))
df$Diff  = df$End - df$Start + 1
df
          x Start  End Diff
1      <NA>    NA   NA   NA
2 1950-1960  1950 1960   11
3 1975-1986  1975 1986   12
4 2011-2018  2011 2018    8

r2evans · Accepted Answer · 2020-06-16T17:28:21.063

3

G5W's is great for base R, here's a "tidyverse" version:

library(dplyr)
library(tidyr) # separate
df %>%
  filter(!is.na(x)) %>%
  tidyr::separate(x, into = c("Start", "End"), sep = "-", remove = FALSE, convert = TRUE) %>%
  mutate(Diff = End - Start + 1L)
#           x Start  End Diff
# 1 1950-1960  1950 1960   11
# 2 1975-1986  1975 1986   12
# 3 2011-2018  2011 2018    8

edited Jun 16 '20 at 17:28

answered Jun 16 '20 at 17:23

r2evans

141,215
6
77
149

score 2 · Answer 3 · answered Jun 16 '20 at 17:24

A quick but inflexible solutions is to grab the years by position using substr():

df$Start <- as.numeric(substr(df$x, 1, 4))
df$End <- as.numeric(substr(df$x, 6, 10))
df$Diff <- df$End - df$Start + 1

df[!is.na(df$Diff), ]
          x Start  End Diff
2 1950-1960  1950 1960   11
3 1975-1986  1975 1986   12
4 2011-2018  2011 2018    8

score 1 · Answer 4 · answered Jun 16 '20 at 18:49

Yet another baseR solution:

df1[, c("Start", "End")] <- do.call(rbind, strsplit(df1$x, "-"))
df1 <- transform(type.convert(df1), Diff = End - Start + 1)

Result

df1
#          x Start  End Diff
#1      <NA>    NA   NA   NA
#2 1950-1960  1950 1960   11
#3 1975-1986  1975 1986   12
#4 2011-2018  2011 2018    8

data

df1 <- structure(list(x = c(NA, "1950-1960", "1975-1986", "2011-2018"
)), class = "data.frame", row.names = c(NA, -4L))

score 0 · Answer 5 · answered Jun 16 '20 at 17:38

base R, easy to read

#your data
x <- c("1950-1960", "1975-1986", "2011-2018")
df <- as.data.frame(x)

#code
df_list <- unlist(apply(df, MARGIN = 1, strsplit, "-"))
new_data <- matrix(df_list, ncol = 2,byrow = T)

#output
output <- cbind(df,new_data)

Output:

          x    1    2
1 1950-1960 1950 1960
2 1975-1986 1975 1986
3 2011-2018 2011 2018

How can I convert a non-numeric variable column into two numeric variable columns?

5 Answers5