1

I have data like this:

df <- data.frame(V1=c("stuff", "2nd June 2018", "otherstuff1", "baseball","", "142", "otherstuff2", "football","", "150", "4th June 2018", "otherstuff99", "hockey","", "160", "otherstuff100", "baseball", "", "190", "otherstuff5", "lacrosse", "200", "9th June 2018"), stringsAsFactors = F)

I want to insert a row by a condition, new cells "date" on bookends of any date value. There are random numbers of cells of otherstuff between the dates:

df.desired <- data.frame(V1=c("stuff","date", "2nd June 2018","date" ,"otherstuff1", "baseball","", "142", "otherstuff2", "football","", "150","date", "4th June 2018","date", "otherstuff99", "hockey","", "160", "otherstuff100", "baseball", "", "190", "otherstuff5", "lacrosse", "200", "date", "9th June 2018","date"), stringsAsFactors=F)
pogibas
  • 27,303
  • 19
  • 84
  • 117
Neal Barsch
  • 2,810
  • 2
  • 13
  • 39
  • Is date formate always like that (ie: day(num) Month (chr) year (num))? – pogibas Jun 02 '18 at 05:51
  • Yes, but in text string not date format – Neal Barsch Jun 02 '18 at 06:00
  • 1
    The use of a data frame adds unnecessary complexity to the question. Essentially you are only working with the vector of strings that happens to be part of a data frame. See these two questions (https://stackoverflow.com/questions/18178451/is-there-a-way-to-check-if-a-column-is-a-date-in-r and https://stackoverflow.com/questions/1493969/how-to-insert-elements-into-a-vector) – J. Win. Jun 02 '18 at 06:56

2 Answers2

3

There are three steps that you need to do:

  • find date positions (using grep)
  • create new data.frame with space for date rows
  • add date to new data.frame

Code:

# Find position of `month year`
foo <- grep(paste(month.name, "\\d+$", collapse = "|"), df$V1)
# Expand original data.frame with space for data
dfDesired <- data.frame(x = df$V1[sort(c(1:nrow(df), foo, foo))], stringsAsFactors = FALSE)
# Find position for date in expanded data.frame
bar <- foo + seq(by = 2, length.out = length(foo))
# Add date
dfDesired$x[c(bar - 1, bar + 1)] <- "date"

Notes:

grep is done with string: paste(month.name, "\\d+$", collapse = "|")

"January \d+$|February \d+$|March \d+$|April \d+$|May \d+$|June \d+$|July \d+$|August \d+$|September \d+$|October \d+$|November \d+$|December \d+$"

We need bar positions as rows in new data.frame are moved by: 1,3,5,+

pogibas
  • 27,303
  • 19
  • 84
  • 117
1

I'd do it like this; it looks like the dmy function from lubridate package succeeds to recognize all the date formats in your example, but if you have a wider variety of date strings that might not always hold: 

# lubridate parses your dates in dmy function
df$date_try <- dmy(df$V1) 
# the ones that are not NA must be dates
ind <- c(which(!is.na(df$date_try)))
# insert some bookends at the index locations before and after your dates
new_ind <- c(seq_along(df$date_try), ind + 0.5, ind - 0.5)
new_V1 <- c(df$V1, rep("date", length(ind) * 2))

# currently the bookends are at the end of the list,
# we must re-order them to insert at the proper locations
# create your desired output dataframe
df.new <- data.frame(V1 = new_V1[order(new_ind)])

> head(df.new)
             V1
1         stuff
2          date
3 2nd June 2018
4          date
5   otherstuff1
6      baseball
J. Win.
  • 6,662
  • 7
  • 34
  • 52