Expand data frame based on new column
I have the following framework:
df <- data.frame(
month=c("July", "August", "August"),
day=c(31, 1, 2),
time=c(12, 12, 12))
month day time
1 July 31 12
2 August 1 12
3 August 2 12
I have a text file times (decimal) and I want to replace the "time" column at all times from the text file. There are several days in the text file with over 300 entries for each.
7-31-2016 #the days are all concatenated together, this line represents the beginning of one day (July 31)
13.12344
13.66445
13.76892
...
8-1-2016 #here is another day (August 1)
14.50333
14.52000
14.53639
...
However, the text file is much longer than the current data frame - it contains 393 entries. So I would like the resulting data file to look something like this:
month day time
5 July 31 13.12344
6 July 31 13.66445
7 July 31 13.76892
.....
393 August 1 14.50333
394 August 1 14.52000
394 August 1 14.53639
Basically, I just need to expand my current dataframe to match the number of records from the new file, keeping the same day. Hope this makes sense.
+3
source to share
3 answers
# Create txt data
txt <- data.frame(x = c('7-31-2016', '13.12344', '13.66445', '13.76892', '8-1-2016', '14.50333', '14.52000', '14.53639'))
# Load Your data
df <- data.frame(
month=c("July", "August", "August"),
day=c(31, 1, 2),
time=c(12, 12, 12))
# Need a year to join dates
df$year <- 2016
# Create date column
df$date <- as.Date(paste0(df$month, "/", df$day, "/", df$year), format = "%B/%d/%Y")
# Find values with dashes, then replaces with /
txt$dash <- grepl('-', txt$x)
txt$x <- gsub("-", "/", txt$x)
# Adds new columns
library(dplyr)
txt <- mutate(txt, date = ifelse(dash==TRUE, as.Date(x, format = "%m/%d/%Y"), NA))
txt <- mutate(txt, time = ifelse(dash==FALSE, as.numeric(x), NA))
# Fill down values
library(zoo)
txt$date <- na.locf(txt$date)
# Removes NA and keeps necessary columns
txt <- txt[!is.na(txt$time),]
txt <- txt[c("date", "time")]
# Merge
output <- merge(df, txt, by = "date")
+2
source to share