Merge Dataframes with different number of rows

Question

I am trying to merge together 8 dataframes into one, matching against the row names.

Examples of the dataframes:

DF1

	Arable and Horticulture
Acer	100
Achillea	90
Aesculus	23
Alliaria	3
Allium	56
Anchusa	299

DF2

	Improved Grassland
Acer	12
Alliaria	3
Allium	50
Brassica	23
Calystegia	299
Campanula	29

And so on for a few hundred rows for different plants and 8 columns of different habitats. What I want the merged frame to look like:

	Arable and Horticulture	Improved Grassland
Acer	100	12
Achillea	90	0
Aesculus	23	0
Alliaria	3	3
Allium	56	50
Anchusa	299	0
Brassica	0	23
Calystegia	0	299
Campanula	0	29

I tried merging

PolPerGen <- merge(DF1, DF2, all=TRUE)

But that does not match up the row name and dropped them entirely in the output

	Arable and Horticulture	Improved Grassland
1	100	NA
2	90	NA
3	23	NA
4	2	NA
5	56	NA
6	299	NA
7	NA	12
8	NA	3
9	NA	50
10	NA	23
11	NA	299
12	NA	29

I am completely out of ideas, any thoughts?

The first column is variable? If not, create one with rownames_to_column() and the join should work — Vinícius Félix
– Vinícius Félix, Commented Aug 30, 2021 at 16:05

Nad Pat · Accepted Answer · 2021-08-30 17:06:21Z

Your dataset is,

dat1 = data.frame("Arable and Horticulture" = c(100, 90,23, 3, 56, 299), 
                  row.names = c("Acer", "Achillea", "Aesculus", "Alliaria", "Allium", "Anchusa"))

dat2 = data.frame("Improved Grassland" = c(12, 3, 50, 23, 299, 29), 
                  row.names = c("Acer", "Achillea", "Allium", "Brassica", "Calystegia", "Campanula"))

As @Vinícius Félix suggested first convert rownames to column.

library(tibble)
dat1 = rownames_to_column(dat1, "Plants")
dat2 = rownames_to_column(dat2, "Plants")

Then lets join both the datasets,

library(dplyr)
dat = full_join(dat1, dat2, )

And replace the NA with 0

dat = dat %>% replace(is.na(.), 0)

      Plants Arable.and.Horticulture Improved.Grassland
1       Acer                     100                 12
2   Achillea                      90                  3
3   Aesculus                      23                  0
4   Alliaria                       3                  0
5     Allium                      56                 50
6    Anchusa                     299                  0
7   Brassica                       0                 23
8 Calystegia                       0                299
9  Campanula                       0                 29

Collectives™ on Stack Overflow

Merge Dataframes with different number of rows

1 Answer 1

Comments

Hot Network Questions

	Arable and Horticulture	Improved Grassland
1	100	NA
2	90	NA
3	23	NA
4	2	NA
5	56	NA
6	299	NA
7	NA	12
8	NA	3
9	NA	50
10	NA	23
11	NA	299
12	NA	29

	Arable and Horticulture	Improved Grassland
1	100	NA
2	90	NA
3	23	NA
4	2	NA
5	56	NA
6	299	NA
7	NA	12
8	NA	3
9	NA	50
10	NA	23
11	NA	299
12	NA	29

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related

	Arable and Horticulture	Improved Grassland
1	100	NA
2	90	NA
3	23	NA
4	2	NA
5	56	NA
6	299	NA
7	NA	12
8	NA	3
9	NA	50
10	NA	23
11	NA	299
12	NA	29