Merge dataframes with unequal rows, and no matching column names R

0
votes

I am trying to take df1 (a summary table), and merge it into df2 (master summary table). This is a snapshot of df2, ignore the random 42, just the answer to the ultimate question. df2 This is an example of what df1, looks like.

df1

Lastly, I have a vector called Dates. This matches the dates that are the column names for df2. I am trying to cycle through 20 file, and gather the summary statistics of that file. I then want to enter that data into df2 to be stored permanently. I only need to enter the Earned column. I have tried to use merge but since they do not have shared column names, I am unable to. My next attempt was to try this. But it gave an error, because of unequal row numbers. 

df2[,paste(Dates[i])] <- cbind(df2,df1)

Then I thought that maybe if I specified the exact location, it might work.

df2[1:length(df1$Earned),Dates[i]] <- df1$Earned

But that gave and error "New columns would leave holes after existing columns" So then I thought of trying that again, but with cbind.

df2[1:length(df1$Earned),Dates[i]] <- cbind(df2, df1$Earned)
##This gave an error for differing row numbers
df2 <- cbind(df2[1:length(df1$Earned),Dates[i]],df1$earned)
## This "worked" but it replaced all of df2 with df1$earned, so I basically lost the rest of the master table

Any ideas would be greatly appreciated. Thank you.

2
rdataframemerge
If they have uneven lengths and no common variables, it makes more sense to store them inside a list. - André Oliveira
The issue with a list, is that I am running it through a for loop, and the end product of df2 will be a triangular dataframe. If I were to use a list, how would I keep track of what year and date the value corresponds to. - Sam Edeus
You can store dataframes inside the list. If said dataframes have the collums you want, you just need to stack square brackets and use double brackets to unlist elements - for instance list_1["DF_1"][["Collum_1"]] would a vector - André Oliveira

2 Answers

1
votes

Something like this might work:

df1[df1$TreatyYear %in% df2$TreatyYear, Dates] <- df2$Earned

Example

df <- data.frame(matrix(NA,4,4))
df$X1 <- 1:4

df[df$X1 %in% c(1,2),c("X3","X4")] <- c(1,2)
0
votes

The only solution that I have found so far is to force df1$Earned into a vector. Then append the vector to be the exact length of the df2. Then I am able to insert the values into df2 by the specific column. 

temp_values <- append(df1$Earned,rep(0,(length(df2$TreatyYear)-length(df1$TreatyYear))),after=length(df1$Earned))
df2[,paste(Dates[i])] <- temp_values

This is kind of a roundabout way to fix it, but not a very pleasant way. Any better ideas would be appreciated.