Skip to content
R for the Rest of Us Logo

Going Deeper with R

Downloading and Importing Data

Transcript

Click on the transcript to go to that point in the video. Please note that transcripts are auto generated and may contain minor inaccuracies.

View code shown in video 
# Load Packages -----------------------------------------------------------

library(tidyverse)
library(fs)
library(readxl)
library(janitor)

# Create Directories ------------------------------------------------------

dir_create("data-raw")

# Download Data -----------------------------------------------------------

# https://www.oregon.gov/ode/educator-resources/assessment/Pages/Assessment-Group-Reports.aspx

download.file("https://www.oregon.gov/ode/educator-resources/assessment/Documents/TestResults2122/pagr_schools_math_tot_raceethnicity_2122.xlsx",
              mode = "wb",
              destfile = "data-raw/pagr_schools_math_tot_raceethnicity_2122.xlsx")

download.file("https://www.oregon.gov/ode/educator-resources/assessment/Documents/TestResults2122/TestResults2019/pagr_schools_math_tot_raceethnicity_1819.xlsx",
              mode = "wb",
              destfile = "data-raw/pagr_schools_math_tot_raceethnicity_1819.xlsx")

download.file("https://www.oregon.gov/ode/educator-resources/assessment/TestResults2018/pagr_schools_math_raceethnicity_1718.xlsx",
              mode = "wb",
              destfile = "data-raw/pagr_schools_math_raceethnicity_1718.xlsx")

download.file("https://www.oregon.gov/ode/educator-resources/assessment/TestResults2017/pagr_schools_math_raceethnicity_1617.xlsx",
              mode = "wb",
              destfile = "data-raw/pagr_schools_math_raceethnicity_1617.xlsx")

download.file("https://www.oregon.gov/ode/educator-resources/assessment/TestResults2016/pagr_schools_math_raceethnicity_1516.xlsx",
              mode = "wb",
              destfile = "data-raw/pagr_schools_math_raceethnicity_1516.xlsx")


# Import Data -------------------------------------------------------------

math_scores_2021_2022 <-
  read_excel(path = "data-raw/pagr_schools_math_tot_raceethnicity_2122.xlsx") |> 
  clean_names()

Your Turn

You’ll be working with data on Oregon school enrollment by race/ethnicity.

  1. Create a new project. Make sure you put it somewhere you’ll be able to find it again later!

  2. Create a new R script file where you’ll do all of your data downloading, cleaning, and importing work.

  3. Download the five most recent Fall Membership Report files using the download.file() function into a data-raw folder (which you’ll need to create).

  4. Import the 2022-2023 spreadsheet into a data frame called enrollment_2022_2023, using the clean_names() function from the janitor package to make our variable names easy to work with.

Learn More

You can read about all of the arguments for the download.file() function here. If you're very interested in reading in data from the internet, June Choe has put together a very comprehensive article called Read files on the web into R.

To learn about the fs package, check out its documentation.

To learn more about importing Excel files, check out the readxl package documentation. You’ll see, for example, ways to download only certain ranges of cells, which can be helpful when you have messy Excel data!

Other packages I mention in the video are:

I’ve also written an article about cleaning messy data in R. There are many packages to deal with messy data (which often comes in the form of Excel spreadsheets), and I go through several in the post.

And, finally, if you want a really deep dive on data cleaning, check out the course Data Cleaning with R.

Have any questions? Put them below and we will help you out!

You need to be signed-in to comment on this post. Login.

Brian Slattery

Brian Slattery • October 25, 2023

Is there a way that you can give read_excel an index for the sheet, rather than a name? Or is that not possible? (I tried looking on the help page and on Stack Overflow and couldn't find the answer)

David Keyes

David Keyes Founder • October 25, 2023

Yes, you can give it the number of the sheet like so:

read_excel(path = "data.xlsx", sheet = 1)

That would read in the first sheet. I actually show this in the Functions lesson solutions video.

The only danger with doing this is that, if you change the order of sheets, your code will fail. Something to keep in mind!

Eric Juskewitz

Eric Juskewitz • January 17, 2024

Hello there!

It seems like the provided URL/link for the data is timed out ("server not found" for oregon.gov). Could I get the raw files in another way?

David Keyes

David Keyes Founder • January 18, 2024

Do you mind trying again? I checked right now and it is working for me. I'm wondering if it was maybe just down earlier today?

Eric Juskewitz

Eric Juskewitz • January 18, 2024

It worked now :)

Edith Namuleme

Edith Namuleme • April 17, 2024

hi David, On following the instructions of this lesson and after downloading the data (fall membership report) and creating an object, it is only showing the first sheet of the excel document which has no data of interest. how can i create an object that gets all the sheets needed or that of my interest?

David Keyes

David Keyes Founder • April 17, 2024

Take a look at the solution code. :) You need to use the sheet argument to ensure you get the right data.

Edith Namuleme

Edith Namuleme • April 18, 2024

Got it, thanks

Gabby Bachhuber

Gabby Bachhuber • April 27, 2024

Do you recommend downloading data one file at a time, or alternatively, write code to download all files and then run once to add them into the directory?

I downloaded all at once, but I had a few errors in my code, so a few didn't download until I fixed the code. Perhaps I could have avoided that by downloading one at a time?

David Keyes

David Keyes Founder • April 29, 2024

If it were me, I'd write code to download them all. This might involve testing along the way and running code file by file to make sure it works, but in the end, I'd much rather have code that I can run to download everything.

David Keyes

David Keyes • September 3, 2024

Pending approval

Just testing

Vivian KAWANAMI

Vivian KAWANAMI • October 20, 2024

Hi! Hmm it was funny that when I added sheet = "School 2022-23" as in the solution, I received the message below:

Error: Sheet 'School 2022-23' not found

But it's ok since sheet=4 worked when loading the data ;)

Gracielle Higino

Gracielle Higino Coach • October 22, 2024

Hi Vivian! That odd... I couldn't reproduce your error, the code is working here on my end. Did this happen when importing the fallmembershipreport_20222023.xlsx file?

Raouf Kilada

Raouf Kilada • October 24, 2024

The oregon site does not look like the one that you are using in the video..... I have hard time to find the files that you downloded

Gracielle Higino

Gracielle Higino Coach • October 25, 2024

Hi Raouf! The link in the "Your turn" section is different from the one in the lesson demonstration. You're supposed to use a different dataset for the assignment. In the "View code shown in video" section above, you can get the link to the dataset that David is using in the video.

Tosan Okome

Tosan Okome • November 7, 2024

Hi. I need some help, please.

I ran this code for the final part of the Your Turn instructions:

enrollment_2022_2023 <- read_excel(path = "data-raw/fallmembershipreport_20222023.xlsx") |> 
  clean_names()

And I keep getting this error message:

Error in utils::unzip(zip_path, list = TRUE) : 
  error -103 with zipfile in unzGetCurrentFileInfo

Any idea why?

Gracielle Higino

Gracielle Higino Coach • November 8, 2024

Hi Tosan! This could be many things... Sometimes you get this error if the Excel file is open, but it can also be a path problem. Do you want to book a time to debug together?

Tosan Okome

Tosan Okome • November 11, 2024

Sure. I'll be in your mailbox. Thank you!

gene trevino

gene trevino • January 9, 2025

when I run the code I get download had nonzero exit status

what is the problem ?

David Keyes

David Keyes Founder • January 14, 2025

Can you post the exact code you ran and the exact error message you received?

Mike LeVan

Mike LeVan • April 9, 2025

There is ethnicity information on sheets 2, 3, and 4. Do you have a preference as to which one should be used?

Gracielle Higino

Gracielle Higino Coach • April 9, 2025

Hi Mike! For this exercise we'll be using the spreadsheet with a name starting with "School" followed by the corresponding years (e.g., "School 20242025"). It seems to be the spreadsheet number 4.

Haifa Haroon

Haifa Haroon • April 10, 2025

Hi there! What would be the best way to pull data from data portals from within R?

In the portal, for example, I can directly download the file as a csv/excel format, but the data on the web is not currently in that format, so unsure how to use this with the package Rio.

Example: https://data.detroitmi.gov/datasets/detroitmi::daycare-water-testing-results/explore

Haifa Haroon

Haifa Haroon • April 10, 2025

Thanks David! I was able to re-create this in chrome. This seems great especially for a dataset that may update over time.

In general, am I right in understanding that this would still require you to download a dataset first (to grab the link) before pulling into R?

David Keyes

David Keyes Founder • April 10, 2025

Yes, you do need to figure out the URL, but once you do that, it should stay the same so your code should run and grab the newest data.

Gracielle Higino

Gracielle Higino Coach • April 10, 2025

To add to David's response, sometimes you don't even need to download the data. If you get a link that ends with ".csv", very often you can use that URL directly. It's exactly what David did, it's just that in some cases you have more straightforward ways to get the URL, and in his example the URL was "hidden" behind a button and it was not easy to get.

One example is the data on our dataset collection. If you get this one, for example, and paste the URL into your read_csv function, it should read in the data without exactly downloading it.

Myles Kwitny

Myles Kwitny • April 17, 2025

Is there a package that allows you to read a dat file?

Gracielle Higino

Gracielle Higino Coach • April 17, 2025

If you know the structure of your .dat file (which is a generic file that can contain all sorts of data), then you can use the {readr} functions or other packages. For example, if your .dat file contains a whitespace-separated table, you can use the read_table() function from {readr}. Here you can read how {readr} deals with different types of data: https://readr.tidyverse.org/

If you meant ".dta" (from STATA), then you can use the {haven} package: https://haven.tidyverse.org/reference/read_dta.html

Course Content

44 Lessons