Split characters of a column in a dataframe to insert an underscore between values in R

user_14266849 0
Viewed 116

I have a dataframe with a column of dates in YYYYMMDD format. I would like to convert this column of values to YYYY_MM_DD to match the format in another database.

Solutions I have found primarily focus on splitting a strings such as comma-delimited.

I essentially want a solution that indexes YYYYMMDD and inserts an '_' after the 4th character and the 6th character.

Thanks

UPDATE:

After posting I almost immediately found a solution that worked for me:

library(tidyr)

# create names for new columns
newCol <- c("YEAR", "MONTH", "DAY")

# separate existing column at 4 and 6 character
newData <- separate(table, old_column, newCol, sep = c(4,6))

# combining the three columns to one delimited by '_'
table$newColumn <- paste(table$YEAR, table$MONTH, table$DAY, sep = "_")
character r indexing date split
3 Answers

You can try gsub + as.Date, e.g.,

> gsub("-","_",as.Date(s,"%Y%m%d"))
[1] "2020_10_30" "2020_09_22"

Data

s <- c("20201030", "20200922")
user_121587570

You can use the format function in R to achive the required, like this:

year = format(as.Date("20200913", "%Y%m%d"), "%Y")
month = format(as.Date("20200913", "%Y%m%d"), "%m")
day = format(as.Date("20200913", "%Y%m%d"), "%d")
dt = paste0(year, "_", month, "_", day)
print(dt)

This prints the following result:

[1] "2020_09_13"
user_142600790

Another way you can try

library(stringr)
date1 <- c("20201030", "20200922")
date2 <- paste0(str_sub(date1, 1,4), "_", str_sub(date1, 5,6), "_", str_sub(date1, 7,8))
#[1] "2020_10_30" "2020_09_22"
user_123363050
Related