Bind rows together

You have > 1 excel sheet with same variable names and you want to merge them to 1 dataframe. Here is what you do!

I have three data sheets. Let’s import them all.

Libraries required

library(readxl) #for importing excel sheets
library(plyr) #for merging

Import data sheets

dat1 <- read_excel("data_1.xlsx")
dat2 <- read_excel("data_2.xlsx")
dat3 <- read_excel("data_3.xlsx")
dat1_extra <- read_excel("data_1_extra.xlsx") # this is to demonstrate extra variable, we will deal with this later

Check data

dat1

# A tibble: 2 x 2
  subject marks
  <chr>   <dbl>
1 tamil      95
2 english    96

dat2

# A tibble: 2 x 2
  subject marks
  <chr>   <dbl>
1 maths     100
2 science    86

dat3

# A tibble: 2 x 2
  subject marks
  <chr>   <dbl>
1 social     96
2 physics    NA

Let’s merge them all

Merge

rbind(dat1,dat2,dat3)

# A tibble: 6 x 2
  subject marks
  <chr>   <dbl>
1 tamil      95
2 english    96
3 maths     100
4 science    86
5 social     96
6 physics    NA

rbind binds the rows together provided they all have same variable names.

Dealing with extra columns (unmatched)

Let us say one data sheet had an extra column

dat1_extra

# A tibble: 2 x 3
  subject marks previous_exam
  <chr>   <dbl>         <dbl>
1 tamil      95            45
2 english    96            52

Binding this with unmatched data sheets will lead to error. See here

“rbind(dat1_extra,dat2,dat3)” gives the following error.

Error screenshot

Instead, use “rbind_fill”

rbind.fill(dat1_extra,dat2,dat3)

  subject marks previous_exam
1   tamil    95            45
2 english    96            52
3   maths   100            NA
4 science    86            NA
5  social    96            NA
6 physics    NA            NA

This adds that extra variable and fills the missing values with NAs.

Happy merging!