Error in fix.by(by.x, x) : 'by' must match numbers of columns Error in fix.by(by.x, x) : 'by' must match numbers of columns

user_13877190 0
Viewed 407

I have a data frame df_forecast, contains consumption data by date. Some dates are missing. So I thought to create another data frame considering the start date & end date of df_forecast & fill it with consecutive date then do left join with df_forecast & consider the last date value for the missing dates. So I wrote following code

startDate=min(df_forecast$Date1)
endDate=max(df_forecast$Date1)
dateSeq=seq(as.Date(startDate), as.Date(endDate), by="days")
df_date=data.frame(dateSeq)

But while I'm trying to merge df_date with df_forecast using below code

merge(df_date,df_forecast,by.x=dateSeq,by.y=Date1,all.x=True)

I'm getting error message

Error in fix.by(by.x, x) : 'by' must match numbers of columns Error in fix.by(by.x, x) : 'by' must  match numbers of columns
Error in fix.by(by.x, x) : 'by' must match numbers of columns

Both the variables in merge by are Date data type. Can you suggest me how to solve this issue or any alternative approach?

r
2 Answers

That was my first attempt at the same problem, but I found the "complete" function in tidyverse that does what you want (blog description by Kan Nishida here: https://blog.exploratory.io/populating-missing-dates-with-complete-and-fill-functions-in-r-and-exploratory-79f2a321e6b5). Here's the code to set up a replicable example and demo the function.

# Setting up replicable example
start_date <- Sys.Date()
end_date <- start_date + 24
date_vec <- seq.Date(from = start_date, to = end_date, by = "day")
set.seed(101)
other_data <- sample(1:25, 25)
data1 <- as.data.frame(cbind(date_vec, other_data))
data1$date_vec <- as.Date(data1$date_vec, origin = "1970-01-01")
set.seed(101)
n <- 5
# Remove 5 random rows
to_remove <- sample(data1$date_vec, n)
data_incomplete <- data1[!data1$date_vec %in% to_remove, ]
# Check it
data_incomplete
# Add rows back, other_data will be NA
data_incomplete %>% 
  mutate(date_vec = as.Date(date_vec)) %>% 
  complete(date_vec = seq.Date(from = min(date_vec), to = max(date_vec), by = "day"))
user_126428890

You need to refer to the columns as strings. For merge, if you add the column itself, it interprets it as an array of column names.

Columns to merge on can be specified by name, number or by a logical vector: the name "row.names" or the number 0 specifies the row names. If specified by name it must correspond uniquely to a named column in the input.

merge(df_date,df_forecast,by.x='dateSeq',by.y='Date1',all.x=True)

R tends to be very inconsistent in this regard, so always debug by trying a coupled different ways of referencing the column.

user_100237320
Related