Vfrdtyky

I have a set of data in which the date, time, and speed have been merged into a single column, desciptio:

  coordinates     Name                                descriptio

1 (-123.3397, 50.07757) HAR07(0) Timestamp: 08/16/2018 03:44:00 Speed: 0.8

2 (-123.3396, 50.07787) HAR07(1) Timestamp: 08/16/2018 07:46:00 Speed: 0.1

3 (-123.3397, 50.07755) HAR07(2) Timestamp: 08/16/2018 11:50:00 Speed: 0.0

4 (-123.3616, 50.11495) HAR07(3) Timestamp: 08/17/2018 04:01:00 Speed: 0.1

5 (-123.3289, 50.10053) HAR07(4) Timestamp: 08/18/2018 04:22:00 Speed: 0.4

6 (-123.3514, 50.10265) HAR07(5) Timestamp: 08/19/2018 04:44:00 Speed: 0.1

I am looking for a way to extract these values and add them to the data frame as separate columns, date, time, and speed. I've seen a few methods for extracting date, and maybe time, but I'm really stumped on the speed. I did find this question, which seems similar, but I'm not familiar enough with regex to adapt it to my needs. Any advice?

Thanks in advance!

Edit: these data are in a shapefile, not a data frame. I think I can read them into a data frame, edit them, and then re-save them as a shapefile, but I'd prefer to keep them as a spatial data throughout, if possible.

There you go :

df <- read.table(header=TRUE,stringsAsFactors=FALSE,text="  coordinates     Name                                descriptio

1 '(-123.3397, 50.07757)' HAR07(0) 'Timestamp: 08/16/2018 03:44:00 Speed: 0.8'

2 '(-123.3396, 50.07787)' HAR07(1) 'Timestamp: 08/16/2018 07:46:00 Speed: 0.1'

3 '(-123.3397, 50.07755)' HAR07(2) 'Timestamp: 08/16/2018 11:50:00 Speed: 0.0'

4 '(-123.3616, 50.11495)' HAR07(3) 'Timestamp: 08/17/2018 04:01:00 Speed: 0.1'

5 '(-123.3289, 50.10053)' HAR07(4) 'Timestamp: 08/18/2018 04:22:00 Speed: 0.4'

6 '(-123.3514, 50.10265)' HAR07(5) 'Timestamp: 08/19/2018 04:44:00 Speed: 0.1'")



transform(df,

  date = as.Date(substr(descriptio,12,21),"%M/%d/%Y"),

  time = substr(descriptio,23,30),

  speed = as.numeric(substr(descriptio,39,41)))

#             coordinates     Name                                descriptio       date     time speed

# 1 (-123.3397, 50.07757) HAR07(0) Timestamp: 08/16/2018 03:44:00 Speed: 0.8 2018-11-16 03:44:00   0.8

# 2 (-123.3396, 50.07787) HAR07(1) Timestamp: 08/16/2018 07:46:00 Speed: 0.1 2018-11-16 07:46:00   0.1

# 3 (-123.3397, 50.07755) HAR07(2) Timestamp: 08/16/2018 11:50:00 Speed: 0.0 2018-11-16 11:50:00   0.0

# 4 (-123.3616, 50.11495) HAR07(3) Timestamp: 08/17/2018 04:01:00 Speed: 0.1 2018-11-17 04:01:00   0.1

# 5 (-123.3289, 50.10053) HAR07(4) Timestamp: 08/18/2018 04:22:00 Speed: 0.4 2018-11-18 04:22:00   0.4

# 6 (-123.3514, 50.10265) HAR07(5) Timestamp: 08/19/2018 04:44:00 Speed: 0.1 2018-11-19 04:44:00   0.1

There's no native type/class for time in R so I left it as character.

The solution turned out to be quite simple, if a bit wordier than I wanted:

# Split column into 5 parts at each space

split <- str_split_fixed(raw.shp.data$descriptio, ' ', 5)



# Add the relevant columns back to the original data frame

raw.shp.data$time <- paste(split[,2], split[,3])

raw.shp.data$speed <- split[,5]



# Delete no-longer-needed descriptio column

raw.shp.data$descriptio <- NULL

This keeps the spatial format intact.