Reputation: 1609

Combine dataframes with "one column name in common with different number of elements in that common column" into one

Assume we have 5 dataframes with one column of them is based on the same timestamp ("year"); each has possibly a different number of rows and columns, but each has a first column called year. Each start and end in a different year, so there is no common starting or ending date among all the dataframes. We want to combine all of the dataframes into one based on the year those data were collected (i.e., the data collected in one matrix in a particular year, correspond to the data in other dataframes for that same year). For those dataframes that do not have a corresponding year, we want to fill those blanks with NA.

How can we line the data up and combine them into one dataframe?

Assume for the sake of argument, we have the following dataframes:

M1 <- data.frame(year=2000:2010, v1=16:26, v2=25:35)
M1; dim(M1) # 11x3
M2 <- data.frame(year=2005:2018, v3=6:19, v4=5:18, v5=3:16)
M2; dim(M2) #14x4
M3 <- data.frame(year=2002:2016, v3=3:17, v6=2:16, v7=0:14)
M3; dim(M3) # 15x4
M4 <- data.frame(year=2008:2020, v3=9:21, v6=8:20, v8=6:18)
M4; dim(M4) # 13x4
M5 <- data.frame(year=2018:2020, v9=19:21, v10=18:20, v11=16:18, v12=29:31)
M5; dim(M5) # 3x5

Note: A very similar question was asked by another useR, and closed by the reasoning "not clear". I clarified his question neatly.

Upvotes: 0

Answers (4)

Uwe

Reputation: 42544

Some of the data frames do have identical column names. So, it is possible that there might be different values for the same year and column name.

Therefore, I suggest to rbindlist() all data frames and use melt() and dcast() with an appropriate aggregation function which will make those "duplicate" entries visible:

df_list <- mget(paste0("M", 1:5))

library(datat.table)
rbindlist(df_list, use.names = TRUE, fill = TRUE, idcol = "df")[
  , melt(.SD, id.vars = c("df", "year"), na.rm = TRUE)][
    , dcast(.SD, year ~ variable, function(x) toString(unique(x)))]

    year v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12
 1: 2000 16 25                                 
 2: 2001 17 26                                 
 3: 2002 18 27  3        2  0                  
 4: 2003 19 28  4        3  1                  
 5: 2004 20 29  5        4  2                  
 6: 2005 21 30  6  5  3  5  3                  
 7: 2006 22 31  7  6  4  6  4                  
 8: 2007 23 32  8  7  5  7  5                  
 9: 2008 24 33  9  8  6  8  6  6               
10: 2009 25 34 10  9  7  9  7  7               
11: 2010 26 35 11 10  8 10  8  8               
12: 2011       12 11  9 11  9  9               
13: 2012       13 12 10 12 10 10               
14: 2013       14 13 11 13 11 11               
15: 2014       15 14 12 14 12 12               
16: 2015       16 15 13 15 13 13               
17: 2016       17 16 14 16 14 14               
18: 2017       18 17 15 17    15               
19: 2018       19 18 16 18    16 19  18  16  29
20: 2019       20       19    17 20  19  17  30
21: 2020       21       20    18 21  20  18  31
    year v1 v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12

Alternatively, the name of the source data frame can be used to make the column names unique:

rbindlist(df_list, use.names = TRUE, fill = TRUE, idcol = "df")[
  , melt(.SD, id.vars = c("df", "year"), na.rm = TRUE)][
    , dcast(.SD, year ~ paste(variable, df, sep = "_"))]

    year v10_M5 v11_M5 v12_M5 v1_M1 v2_M1 v3_M2 v3_M3 v3_M4 v4_M2 v5_M2 v6_M3 v6_M4 v7_M3 v8_M4 v9_M5
 1: 2000     NA     NA     NA    16    25    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA
 2: 2001     NA     NA     NA    17    26    NA    NA    NA    NA    NA    NA    NA    NA    NA    NA
 3: 2002     NA     NA     NA    18    27    NA     3    NA    NA    NA     2    NA     0    NA    NA
 4: 2003     NA     NA     NA    19    28    NA     4    NA    NA    NA     3    NA     1    NA    NA
 5: 2004     NA     NA     NA    20    29    NA     5    NA    NA    NA     4    NA     2    NA    NA
 6: 2005     NA     NA     NA    21    30     6     6    NA     5     3     5    NA     3    NA    NA
 7: 2006     NA     NA     NA    22    31     7     7    NA     6     4     6    NA     4    NA    NA
 8: 2007     NA     NA     NA    23    32     8     8    NA     7     5     7    NA     5    NA    NA
 9: 2008     NA     NA     NA    24    33     9     9     9     8     6     8     8     6     6    NA
10: 2009     NA     NA     NA    25    34    10    10    10     9     7     9     9     7     7    NA
11: 2010     NA     NA     NA    26    35    11    11    11    10     8    10    10     8     8    NA
12: 2011     NA     NA     NA    NA    NA    12    12    12    11     9    11    11     9     9    NA
13: 2012     NA     NA     NA    NA    NA    13    13    13    12    10    12    12    10    10    NA
14: 2013     NA     NA     NA    NA    NA    14    14    14    13    11    13    13    11    11    NA
15: 2014     NA     NA     NA    NA    NA    15    15    15    14    12    14    14    12    12    NA
16: 2015     NA     NA     NA    NA    NA    16    16    16    15    13    15    15    13    13    NA
17: 2016     NA     NA     NA    NA    NA    17    17    17    16    14    16    16    14    14    NA
18: 2017     NA     NA     NA    NA    NA    18    NA    18    17    15    NA    17    NA    15    NA
19: 2018     18     16     29    NA    NA    19    NA    19    18    16    NA    18    NA    16    19
20: 2019     19     17     30    NA    NA    NA    NA    20    NA    NA    NA    19    NA    17    20
21: 2020     20     18     31    NA    NA    NA    NA    21    NA    NA    NA    20    NA    18    21
    year v10_M5 v11_M5 v12_M5 v1_M1 v2_M1 v3_M2 v3_M3 v3_M4 v4_M2 v5_M2 v6_M3 v6_M4 v7_M3 v8_M4 v9_M5

Upvotes: 2

Sotos

Reputation: 51592

You can simply do,

Reduce(function(x, y)merge(x, y, by = 'year', all = TRUE), mget(ls(pattern = 'M[0-9]+')))

which gives,

   year v1 v2 v3.x v4 v5 v3.y v6.x v7 v3 v6.y v8 v9 v10 v11 v12
1  2000 16 25   NA NA NA   NA   NA NA NA   NA NA NA  NA  NA  NA
2  2001 17 26   NA NA NA   NA   NA NA NA   NA NA NA  NA  NA  NA
3  2002 18 27   NA NA NA    3    2  0 NA   NA NA NA  NA  NA  NA
4  2003 19 28   NA NA NA    4    3  1 NA   NA NA NA  NA  NA  NA
5  2004 20 29   NA NA NA    5    4  2 NA   NA NA NA  NA  NA  NA
6  2005 21 30    6  5  3    6    5  3 NA   NA NA NA  NA  NA  NA
7  2006 22 31    7  6  4    7    6  4 NA   NA NA NA  NA  NA  NA
8  2007 23 32    8  7  5    8    7  5 NA   NA NA NA  NA  NA  NA
9  2008 24 33    9  8  6    9    8  6  9    8  6 NA  NA  NA  NA
10 2009 25 34   10  9  7   10    9  7 10    9  7 NA  NA  NA  NA
11 2010 26 35   11 10  8   11   10  8 11   10  8 NA  NA  NA  NA
12 2011 NA NA   12 11  9   12   11  9 12   11  9 NA  NA  NA  NA
13 2012 NA NA   13 12 10   13   12 10 13   12 10 NA  NA  NA  NA
14 2013 NA NA   14 13 11   14   13 11 14   13 11 NA  NA  NA  NA
15 2014 NA NA   15 14 12   15   14 12 15   14 12 NA  NA  NA  NA
16 2015 NA NA   16 15 13   16   15 13 16   15 13 NA  NA  NA  NA
17 2016 NA NA   17 16 14   17   16 14 17   16 14 NA  NA  NA  NA
18 2017 NA NA   18 17 15   NA   NA NA 18   17 15 NA  NA  NA  NA
19 2018 NA NA   19 18 16   NA   NA NA 19   18 16 19  18  16  29
20 2019 NA NA   NA NA NA   NA   NA NA 20   19 17 20  19  17  30
21 2020 NA NA   NA NA NA   NA   NA NA 21   20 18 21  20  18  31

Upvotes: 2

AntoniosK

Reputation: 16121

I think it's more efficient to use the full_join command of dplyr. You can use full_join every time a new dataframe needs to be joined, or use it once within a reduce function that will work sequentially. See both methods below:

# create example datasets
M1 <- data.frame(year=2000:2010, v1=16:26, v2=25:35)
M2 <- data.frame(year=2005:2018, v3=6:19, v4=5:18, v5=3:16)
M3 <- data.frame(year=2002:2016, v3=3:17, v6=2:16, v7=0:14)
M4 <- data.frame(year=2008:2020, v3=9:21, v6=8:20, v8=6:18)
M5 <- data.frame(year=2018:2020, v9=19:21, v10=18:20, v11=16:18, v12=29:31)

First method:

library(dplyr)

# use the full_join command
# you have to "manually" use a full_join command for every new dataset you want to join 
full_join(M1, M2, by="year") %>%
  full_join(M3, by="year") %>%
  full_join(M4, by="year") %>%
  full_join(M5, by="year")

#    year v1 v2 v3.x v4 v5 v3.y v6.x v7 v3 v6.y v8 v9 v10 v11 v12
# 1  2000 16 25   NA NA NA   NA   NA NA NA   NA NA NA  NA  NA  NA
# 2  2001 17 26   NA NA NA   NA   NA NA NA   NA NA NA  NA  NA  NA
# 3  2002 18 27   NA NA NA    3    2  0 NA   NA NA NA  NA  NA  NA
# 4  2003 19 28   NA NA NA    4    3  1 NA   NA NA NA  NA  NA  NA
# 5  2004 20 29   NA NA NA    5    4  2 NA   NA NA NA  NA  NA  NA
# 6  2005 21 30    6  5  3    6    5  3 NA   NA NA NA  NA  NA  NA
# 7  2006 22 31    7  6  4    7    6  4 NA   NA NA NA  NA  NA  NA
# 8  2007 23 32    8  7  5    8    7  5 NA   NA NA NA  NA  NA  NA
# 9  2008 24 33    9  8  6    9    8  6  9    8  6 NA  NA  NA  NA
# 10 2009 25 34   10  9  7   10    9  7 10    9  7 NA  NA  NA  NA
# 11 2010 26 35   11 10  8   11   10  8 11   10  8 NA  NA  NA  NA
# 12 2011 NA NA   12 11  9   12   11  9 12   11  9 NA  NA  NA  NA
# 13 2012 NA NA   13 12 10   13   12 10 13   12 10 NA  NA  NA  NA
# 14 2013 NA NA   14 13 11   14   13 11 14   13 11 NA  NA  NA  NA
# 15 2014 NA NA   15 14 12   15   14 12 15   14 12 NA  NA  NA  NA
# 16 2015 NA NA   16 15 13   16   15 13 16   15 13 NA  NA  NA  NA
# 17 2016 NA NA   17 16 14   17   16 14 17   16 14 NA  NA  NA  NA
# 18 2017 NA NA   18 17 15   NA   NA NA 18   17 15 NA  NA  NA  NA
# 19 2018 NA NA   19 18 16   NA   NA NA 19   18 16 19  18  16  29
# 20 2019 NA NA   NA NA NA   NA   NA NA 20   19 17 20  19  17  30
# 21 2020 NA NA   NA NA NA   NA   NA NA 21   20 18 21  20  18  31

Second method:

library(purrr)

# apply full join sequentially to the datasets in your list
list(M1,M2,M3,M4,M5) %>%
  reduce(full_join, by="year")

#    year v1 v2 v3.x v4 v5 v3.y v6.x v7 v3 v6.y v8 v9 v10 v11 v12
# 1  2000 16 25   NA NA NA   NA   NA NA NA   NA NA NA  NA  NA  NA
# 2  2001 17 26   NA NA NA   NA   NA NA NA   NA NA NA  NA  NA  NA
# 3  2002 18 27   NA NA NA    3    2  0 NA   NA NA NA  NA  NA  NA
# 4  2003 19 28   NA NA NA    4    3  1 NA   NA NA NA  NA  NA  NA
# 5  2004 20 29   NA NA NA    5    4  2 NA   NA NA NA  NA  NA  NA
# 6  2005 21 30    6  5  3    6    5  3 NA   NA NA NA  NA  NA  NA
# 7  2006 22 31    7  6  4    7    6  4 NA   NA NA NA  NA  NA  NA
# 8  2007 23 32    8  7  5    8    7  5 NA   NA NA NA  NA  NA  NA
# 9  2008 24 33    9  8  6    9    8  6  9    8  6 NA  NA  NA  NA
# 10 2009 25 34   10  9  7   10    9  7 10    9  7 NA  NA  NA  NA
# 11 2010 26 35   11 10  8   11   10  8 11   10  8 NA  NA  NA  NA
# 12 2011 NA NA   12 11  9   12   11  9 12   11  9 NA  NA  NA  NA
# 13 2012 NA NA   13 12 10   13   12 10 13   12 10 NA  NA  NA  NA
# 14 2013 NA NA   14 13 11   14   13 11 14   13 11 NA  NA  NA  NA
# 15 2014 NA NA   15 14 12   15   14 12 15   14 12 NA  NA  NA  NA
# 16 2015 NA NA   16 15 13   16   15 13 16   15 13 NA  NA  NA  NA
# 17 2016 NA NA   17 16 14   17   16 14 17   16 14 NA  NA  NA  NA
# 18 2017 NA NA   18 17 15   NA   NA NA 18   17 15 NA  NA  NA  NA
# 19 2018 NA NA   19 18 16   NA   NA NA 19   18 16 19  18  16  29
# 20 2019 NA NA   NA NA NA   NA   NA NA 20   19 17 20  19  17  30
# 21 2020 NA NA   NA NA NA   NA   NA NA 21   20 18 21  20  18  31

Upvotes: 3

Erdogan CEVHER

Reputation: 1609

Step 1: Find the minimum and maximum year taking into account all the dataframes:

min(M1["year"], M2["year"], M3["year"], M4["year"], M5["year"]) # 2000
max(M1["year"], M2["year"], M3["year"], M4["year"], M5["year"]) # 2020

Step 2: Extend M1 through M5 by pumping relevant NAs, and taking care the missing years at the beginning and at the end

M1NA <- rbind(M1, data.frame(year=2011:2020, v1=NA, v2=NA))
M1NA
M2NA <- rbind(data.frame(year=2000:2004, v3=NA, v4=NA, v5=NA), M2, data.frame(year=2019:2020, v3=NA, v4=NA, v5=NA))
M2NA
M3NA <- rbind(data.frame(year=2000:2001, v3=NA, v6=NA, v7=NA), M3, data.frame(year=2017:2020, v3=NA, v6=NA, v7=NA))
M3NA
M4NA <- rbind(data.frame(year=2000:2007, v3=NA, v6=NA, v8=NA), M4)
M4NA
M5NA <- rbind(data.frame(year=2000:2017, v9=NA, v10=NA, v11=NA, v12=NA), M5)
M5NA

Step 3: Combine NA'ed dataframes in the final dataframe; no need to repeat year column in the other dataframes, hence delete them.

CombinedFrame <- cbind(M1NA, M2NA[-1], M3NA[-1], M4NA[-1], M5NA[-1])
CombinedFrame

In case of matrices, do the following:
Convert resultant dataframe to matrix by either of the following ways:

CombinedMatrix <- as.matrix(sapply(CombinedFrame, as.numeric))
CombinedMatrix
CombinedMatrix <- matrix(as.numeric(unlist(CombinedFrame)),nrow=nrow(CombinedFrame))
CombinedMatrix

Note: The above conversions take into account the possibility of the existence of strings in dataframe (i.e., the matrix at hand at the beginning)

This produces the following (just as desired):

     year v1 v2 v3 v4 v5 v3 v6 v7 v3 v6 v8 v9 v10 v11 v12
 [1,] 2000 16 25 NA NA NA NA NA NA NA NA NA NA  NA  NA  NA
 [2,] 2001 17 26 NA NA NA NA NA NA NA NA NA NA  NA  NA  NA
 [3,] 2002 18 27 NA NA NA  3  2  0 NA NA NA NA  NA  NA  NA
 [4,] 2003 19 28 NA NA NA  4  3  1 NA NA NA NA  NA  NA  NA
 [5,] 2004 20 29 NA NA NA  5  4  2 NA NA NA NA  NA  NA  NA
 [6,] 2005 21 30  6  5  3  6  5  3 NA NA NA NA  NA  NA  NA
 [7,] 2006 22 31  7  6  4  7  6  4 NA NA NA NA  NA  NA  NA
 [8,] 2007 23 32  8  7  5  8  7  5 NA NA NA NA  NA  NA  NA
 [9,] 2008 24 33  9  8  6  9  8  6  9  8  6 NA  NA  NA  NA
[10,] 2009 25 34 10  9  7 10  9  7 10  9  7 NA  NA  NA  NA
[11,] 2010 26 35 11 10  8 11 10  8 11 10  8 NA  NA  NA  NA
[12,] 2011 NA NA 12 11  9 12 11  9 12 11  9 NA  NA  NA  NA
[13,] 2012 NA NA 13 12 10 13 12 10 13 12 10 NA  NA  NA  NA
[14,] 2013 NA NA 14 13 11 14 13 11 14 13 11 NA  NA  NA  NA
[15,] 2014 NA NA 15 14 12 15 14 12 15 14 12 NA  NA  NA  NA
[16,] 2015 NA NA 16 15 13 16 15 13 16 15 13 NA  NA  NA  NA
[17,] 2016 NA NA 17 16 14 17 16 14 17 16 14 NA  NA  NA  NA
[18,] 2017 NA NA 18 17 15 NA NA NA 18 17 15 NA  NA  NA  NA
[19,] 2018 NA NA 19 18 16 NA NA NA 19 18 16 19  18  16  29
[20,] 2019 NA NA NA NA NA NA NA NA 20 19 17 20  19  17  30
[21,] 2020 NA NA NA NA NA NA NA NA 21 20 18 21  20  18  31

Upvotes: 0

Combine dataframes with &quot;one column name in common with different number of elements in that common column&quot; into one

Answers (4)

Related Questions

Combine dataframes with "one column name in common with different number of elements in that common column" into one