Carlos Carlos - 1 year ago 59
R Question

Grab string from table and append as column in R

I have the following .csv file:

And I would like to be able to take the date and agent name(pasting its constituent parts) and append them as columns to the right of the table, up until it finds a different name and date, doing the same for the remaining name and date items, to get the following result:

enter image description here

The only thing I have been able to do with the dplyr package is the following:


report <- read.csv(file ="test15.csv", head=TRUE, sep=",")

date_pattern <- "(\\d+/\\d+/\\d+)"
date <- str_extract(report[,2], date_pattern)

report <- mutate(report, date = date)

Which gives me the following result:

enter image description here

The difficulty I am finding is probably using conditionals in order make the script get the appropriate string and append it as a column at the end of the table.

Answer Source

This might be crude, but I think it illustrates several things: a) setting stringsAsFactors=F; b) "pre-allocating" the columns in the data frame; and c) using the column name instead of column number to set the value.

report<-read.csv('test15.csv', header=T, stringsAsFactors=F)

# first, allocate the two additional columns (with NAs)
report$date <- rep(NA, nrow(report))
report$agent <- rep(NA, nrow(report))

# step through the rows
for (i in 1:nrow(report)) {
  # grab current name and date if "Agent:"
  if (report[i,1] == 'Agent:') {
    currDate <- report[i+1,2]
    currName=paste(report[i,2:5], collapse=' ')
  # otherwise append the name/date
  } else {
    report[i,'date'] <- currDate
    report[i,'agent'] <- currName
write.csv(report, 'test15a.csv')

Output file