5.3 Exporting and Importing Data from Excel Files
Now, you know how to export and import data from delimited files. In this section, you will learn how to export and import data from Excel files with extensions .xls and .xlxs.
5.3.1 Export data into Excel files
To export data into an Excel file, you can use the writexl package. Let’s first install the package.
install.packages("writexl")
Now, we can load the writexl package and use the write.xlsx()
function to write data into an Excel file with extension .xlsx. Let’s first load the data from “my_animals.csv,” and write it to “my_animals.xlsx.”
library(writexl)
library(readr)
<- read_csv("my_animals.csv")
my_animals write_xlsx(my_animals, "my_animals.xlsx")
By default, the column names of the data frame/tibble will be written to the first row of the Excel file. To skip the column names, you can set col_names = FALSE
in the write_xlsx()
function.
In addition to writing a single data frame to an Excel file, write.xlsx()
can also write multiple data frames into a single Excel file, with each sheet containing each data frame. To do that, you need to supply a list of data frames as the first argument in write_xlsx()
. Let’s take a look at the following example which write both my_animals
and sahp
(a tibble in the r02pro package) into an Excel file named “two_data.xlsx.”
library(r02pro)
<- list(my_animals = my_animals, sahp = sahp)
two_data write_xlsx(two_data, "two_data.xlsx")
You can open this file with Excel and verify its contents.
5.3.2 Import Excel Files (.xls and .xlsx ) using read_excel()
Having learned how to export data into an Excel file, let’s see how to read an existing Excel file into R. We can use the read_excel()
function in the readxl
package to import Excel files. Here, readxl
is another subpackage in the tidyverse package. Thus we can directly load the package if the tidyverse package is already installed.
Let’s import the Excel file “two_data.xlsx” we just created into R.
library(readxl)
<- read_excel("two_data.xlsx")
my_df head(my_df)
#> # A tibble: 6 × 3
#> dig_num ani_char conditions
#> <dbl> <chr> <chr>
#> 1 7 sheep Excellent
#> 2 6 pig Good
#> 3 5 monkey N
#> 4 4 pig Fair
#> 5 3 monkey Good
#> 6 2 <NA> Good
We can see from the result that although the file contains two sheets, the function will import the first sheet by default. To import the second sheet, you can set the sheet
argument to the sheet name (sahp
) or the position of the sheet (2).
<- read_excel("two_data.xlsx", sheet = "sahp")
shap_1 #shap_1 <- read_excel("two_data.xlsx", sheet = 2) #same result as the previous line
head(shap_1)
#> # A tibble: 6 × 13
#> dt_sold bedroom bathroom gar_car oa_qual liv_area lot_area
#> <dttm> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2010-03-25 00:00:00 3 2.5 2 6 1479 13517
#> 2 2009-04-10 00:00:00 4 3.5 2 7 2122 11492
#> 3 2010-01-15 00:00:00 3 2 1 5 1057 7922
#> 4 2010-04-19 00:00:00 3 2.5 2 5 1444 9802
#> 5 2010-03-22 00:00:00 3 2 2 6 1445 14235
#> 6 2010-06-06 00:00:00 2 2.5 2 6 1888 16492
#> # … with 6 more variables: house_style <chr>, kit_qual <chr>, heat_qual <chr>,
#> # central_air <chr>, sale_price <dbl>, good_qual <lgl>
If we only want to import a portion of the data, say the first 5 rows and the first 4 columns, then we can set the argument range = "A1:D5"
, just like the range in an excel file.
<- read_excel("two_data.xlsx", sheet = "sahp", range = "A1:D5")
shap_2
shap_2#> # A tibble: 4 × 4
#> dt_sold bedroom bathroom gar_car
#> <dttm> <dbl> <dbl> <dbl>
#> 1 2010-03-25 00:00:00 3 2.5 2
#> 2 2009-04-10 00:00:00 4 3.5 2
#> 3 2010-01-15 00:00:00 3 2 1
#> 4 2010-04-19 00:00:00 3 2.5 2
Note that read_excel()
can read both .xls and xlsx file types.
5.3.4 Exercises
Use R to create the following data frame and assign it to the name df1
.
#> word1 number1
#> 1 one 1
#> 2 two NA
#> 3 <NA> 3
Then, use R to create the following data frame and assign it to the name df2
.
#> word2 number2
#> 1 three 3
#> 2 <NA> 4
#> 3 five 5
Then create a list named my_list
with df1
as the first element and df2
as the second element.
Write R code to set working directory to the desktop, then export
my_list
into an excel file namedlist.xlsx
. How many sheets are there in the excel file? What’s in each sheet?Write R code to import the first two rows and the first column of the second sheet from the excel file you just created.