Splitting the contents of a dataframe column into different columns based on values

Question

I am trying to split the next data column into 3 columns depending on the content. I tried using dplyr and mutating because I wanted to know them better, but any suggestions would be welcome.

exampledf<-data.frame(c("Argentina","2005/12","2005/11","Bolivia","2006/12"),stringsAsFactors=F)
mutate(exampledf,month=strsplit(exampledf[,1],"/")[1],month=strsplit(exampledf[,1],"/")[2])

My goal:

Year     Month    Country
2005     12       Argentina
2005     11       Argentina
2006     12       Bolivia

This is very close to this SO post, but it does not concern my recurring country issue.

Answer 1

, ('i1'), , split , "", "" (sub), "" - , data.frame rbind list.

 i1 <- grepl('^[^0-9]+$', exampledf$Col1)
 lst <- lapply(split(exampledf, cumsum(i1)), function(x) 
   data.frame(year= as.numeric(sub('\\/.*', '',   x[-1,1])), 
              month = as.numeric(sub('.*\\/', '', x[-1,1])),
              Country = x[1,1] ) )
 res <- do.call(rbind, lst)
 row.names(res) <- NULL

 res
 # year month   Country
 #1 2005    12 Argentina
 #2 2005    11 Argentina
 #3 2006    12   Bolivia

---

data.table, 'data.frame' 'data.table' (setDT(exampledf)), cumsum (), (tstrsplit) "Col1" ( ) (/). . setnames. , (:=) NULL.

library(data.table)
res1 <- setDT(exampledf)[, c(tstrsplit(Col1[-1], 
        '/'),Country = Col1[1L]), .(i2=cumsum(i1))][,i2:= NULL][]
setnames(res1, 1:2, c('year', 'month'))

 exampledf<-data.frame(Col1=c("Argentina","2005/12","2005/11",
          "Bolivia","2006/12"),stringsAsFactors=FALSE)

Answer 2

, ...

edf<-data.frame(c("Argentina","2005/12","2005/11","Bolivia","2006/12"),
                stringsAsFactors=F)

names(edf) <- "x"  # just to give a concise name

# flag if the row shows the month or not
edf$isMonth <- (regexpr("^[0-9]+/[0-9]+$", edf$x) > 0)

# expand the country 
# (i.e. if the row is month, reuse the country from the previous row)
edf$country <- edf$x
for (i in seq(2, nrow(edf))) {
  if (edf$isMonth[i]) {
    edf$country[i] <- edf$country[i-1]
  }
}

# now only the rows with month are relevant
edf <- edf[edf$isMonth,]

:

     x isMonth   country
2005/12    TRUE Argentina
2005/11    TRUE Argentina
2006/12    TRUE   Bolivia

- year-month . strsplit , strsplit , mutate , .

stringr::str_match .

library(stringr)
matched <- str_match(edf$x, "([0-9]+)/([0-9]+)")
edf$year <- matched[, 2]
edf$month <- matched[, 3]

:

      x isMonth   country year month    
2005/12    TRUE Argentina 2005    12
2005/11    TRUE Argentina 2005    11
2006/12    TRUE   Bolivia 2006    12

Answer 3

. , .

library(tidyr)
df <-data.frame(Country = c("Argentina","2005/12","2005/11","Bolivia","2006/12"),stringsAsFactors=F)
df$dates[grep("[0-9]",df$Country)] <- df$Country[grep("[0-9]",df$Country)]
df$Country[grep("[0-9]",df$Country)] <- NA

replace_with <- 1
for(i in 1:length(df$Country)) {
  if(!is.na(df$Country[i])) {
    replace_with <- df$Country[i]
    next
  } else {
    x[i] <- replace_with
  }
}
df$Country <- x
df <- separate(df, dates, c("Year", "Month"), "/")
df <- na.omit(df)
df
    Country Year Month
2 Argentina 2005    12
3 Argentina 2005    11
5   Bolivia 2006    12

Answer 4

. read.mtable "SOfun" cSplit "splitstackshape" rbindlist "data.table".

Assuming you have downloaded at least a function read.mtable(if you do not want to install the package), this approach will look like this:

library(SOfun)
library(splitstackshape)

rbindlist(lapply(read.mtable(textConnection(exampledf[[1]]), "[a-z]"), 
                 cSplit, "V1", "/"), idcol = TRUE)
#          .id V1_1 V1_2
# 1: Argentina 2005   12
# 2: Argentina 2005   11
# 3:   Bolivia 2006   12

Alternatively, you can split the data with read.mtable(although I suspect it cSplitmight be faster). So the approach would be:

# library(SOfun)
# library(data.table)
rbindlist(read.mtable(textConnection(exampledf[[1]]), "[a-z]", 
                      sep = "/", col.names = c("Year", "Month")), idcol = TRUE)
#          .id Year Month
# 1: Argentina 2005    12
# 2: Argentina 2005    11
# 3:   Bolivia 2006    12

With this approach, you have the added benefit of naming columns in the process.