Shopping Cart

No products in the cart.

Back to Course

Going Deeper with R

0% Complete
0/0 Steps
  1. Welcome to Going Deeper with R
  2. Advanced Data Wrangling and Analysis
    Overview
  3. Importing Data
  4. Tidy Data
  5. Reshaping Data
  6. Dealing with Missing Data
  7. Changing Variable Types
  8. Advanced Variable Creation
  9. Advanced Summarizing
  10. Binding Data Frames
  11. Functions
  12. Merging Data
  13. Renaming Variables
  14. Quick Interlude to Reorganize our Code
  15. Exporting Data
  16. Advanced Data Visualization
    Data Visualization Best Practices
  17. Tidy Data
  18. Pipe Data Into ggplot
  19. Reorder Plots to Highlight Findings
  20. Line Charts
  21. Use Color to Highlight Findings
  22. Declutter
  23. Use the scales Package for Nicely Formatted Values
  24. Use Direct Labeling
  25. Use Axis Text Wisely
  26. Use Titles to Highlight Findings
  27. Use Color in Titles to Highlight Findings
  28. Use Annotations to Explain
  29. Tweak Spacing
  30. Customize Your Theme
  31. Customize Your Fonts
  32. Try New Plot Types
  33. Advanced RMarkdown
    Advanced Markdown Text Formatting
  34. Tables
  35. Advanced YAML
  36. Inline R Code
  37. Making Your Reports Shine: Word Edition
  38. Making Your Reports Shine: HTML Edition
  39. Making Your Reports Shine: PDF Edition
  40. Presentations
  41. Dashboards
  42. Other Formats
  43. Wrapping Up
    You Did It!
Going Deeper with R Importing Data
Lesson 3 of 43
In Progress

Importing Data

Your Turn

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

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

Download the two files using the download.file() function into a data-raw folder (which you’ll need to create).

Create a new R script file where you’ll do all of your data cleaning work

Import the two spreadsheets into two data frames (enrollment_17_18 and enrollment_18_19)

Heads Up!

If you have issues opening the spreadsheet, note that you may need to add the argument mode = "wb" to the download.file() function, as follows (read more about why here). You can see the full code in the solutions section below if necessary.

Solutions
Learn More

You can read about all of the arguments for the download.file() function here.

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!

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.

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

  1. Jody Oconnor April 19, 2021

    fyi – I was able to download both .xlsx files, but when creating objects with the 17-18 data I got this error message:
    > download.file(url = “https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-17-18.xlsx”,
    + destfile = “data-raw/enrollment-17-18.xlsx”)
    trying URL ‘https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-17-18.xlsx’
    Content type ‘application/octet-stream’ length 47088 bytes (45 KB)
    downloaded 45 KB

    > enrollment_17_18 %
    + clean_names()
    Error: Evaluation error: zip file ‘data-raw/enrollment-17-18.xlsx’ cannot be opened.

    I was able to download the file to my computer, then copy it into the ‘data-raw’ folder manually and turn it into an object with R script successfully. So I’m moving on with the assignment but thought you would want to know.

  2. Abby Isaacson April 19, 2021

    I am having a problem just downloading the files. My code (with or without the mode = “wb”,):

    download.file(url = “https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx”,
    destfile = “data-raw-gd/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx”)

    and I get these two errors:

    Warning messages:
    1: In download.file(url = “https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx”, :
    URL https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx: cannot open destfile ‘data-raw/enrollment-18-19.xlsx’, reason ‘No such file or directory’
    2: In download.file(url = “https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx”, :
    download had nonzero exit status

    This also happens with my own project file links (from Tidy Tuesday).

    1. David Keyes April 19, 2021

      Do you have a folder called data-raw-gd? That’s where the download.file() function is trying to put the file it downloads, but if that folder doesn’t exist, it won’t work.

        1. David Keyes April 19, 2021

          Actually, take a look back at your destfile argument:

          destfile = “data-raw-gd/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx”

          It’s going to try to put the data in the data-raw-gd/going-deeper/raw/master/data-raw/ folder. Try this instead and let me know if it works:

          download.file(url = "https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx",
          destfile = "enrollment-18-19.xlsx")

          That will put the file in the root of the project you’re working in. You did create a completely new project for this course, correct?

          2. Abby Isaacson April 19, 2021

            this is what worked:
            download.file(url=”https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx”,
            destfile= “data-raw-gd/enrollment-18-19.xlsx”)

          3. Abby Isaacson April 19, 2021

            And I went through the steps to create a new project in a new Directory, but I am still saving everything in an “R in 3 mo” folder. I think I have some troubleshooting yet to do about keeping projects truly separate.

          4. David Keyes April 20, 2021

            Yeah, I would recommend one RStudio project for each distinct project. It’s a bit artificial now because you’re working on “projects” that are actually just materials for each course. We can discuss more this week in our live session!

  3. Nithin Pradeep October 7, 2021

    The excel file I am downloading is not getting opened in excel. The following dialog box appears-“file format or file extension not valid. Verify the file has not been corrupted and that file extension matches the format”.

  4. Matt M November 1, 2021

    You recommended data cleaning in R script but reporting in RMarkdown. What if I want my report to be 100% reproducible by someone else? Should I then do all my data cleaning within RMarkdown?

    1. David Keyes November 1, 2021

      Either an R script file or an RMarkdown file will be reproducible because other people can run your code so it shouldn’t matter on that front. Others do their data cleaning in RMarkdown and that works fine as well. I just have a preference to do it in an R script file. In the end, it’s up to you!

  5. Matt M November 1, 2021

    At about 9:38 you said “let me highlight all of these” because keyboard shortcuts are faster. Did you use CTRL A to select all or something else to select just that chunk/those lines?

  7. Niger Sultana April 29, 2022

    Hi I was trying to import data from two excels(field_data_AA and predictor season13July) where 4 spread sheets (name are Autumn, Spring, Winter and Summer. I found following messages. I first run code with Spring and data imported then run code with all seasons? It did not work. Can you suggest me please how can I fix cell reference in excel files ?

    > field_data_2018
    > field_data_2019 <- read_excel(path = "Data-raw/predictor season13July.xlsx",
    + sheet = "Autumn","Spring","Winter","Summer")
    Error: Can't guess format of this cell reference:
    Spring
    In addition: Warning message:
    Cell reference follows neither the A1 nor R1C1 format. Example:
    Spring
    NAs generated.

    Cheers
    Niger

    1. Charlie Hadley May 3, 2022

      Hello Niger,

      The read_excel() function will only allow you to read in one sheet at a time, eg


      read_excel(path = "Data-raw/predictor season13July.xlsx", sheet = "Autumn")

      You would need to import each of these sheets separately and to then combine them together with bind_rows(). As you’re a member of R in 3 Months I’d recommend asking about how to combine these in a submission and I can help you.

      Thanks,

      Charlie

  8. Esther Okoye May 5, 2022

    Hello whenever i try to run my code it keeps giving me an error mesage;

    source(“~/.active-rstudio-document”, echo=TRUE)
    Error in source(“~/.active-rstudio-document”, echo = TRUE) :
    ~/.active-rstudio-document:6:107: unexpected symbol
    5:
    6: download.file(url=”https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-mode = “wb
    ^

      1. Esther Okoye May 11, 2022

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

        download.file(url=”https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-mode = “wb”,
        destfile = “data-raw/enrollment-18-19.xlsx”)

        download.file(url= “https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-mode = “wb”,
        destfile = “data-raw/enrollment-17-18.xlsx”)

        #import data

        enrollment_18_19 <- read_excel(path = "data-raw/enrollment-18-19.xlsx",
        sheet = "Sheet 1")

        enrollment_17_18 <- read_excel(path= "data-raw/enrollment-17-18.xlsx",
        sheet = "Sheet 1")

        enrollment_18_19 <- read_excel(path = "data-raw/enrollment-18-19.xlsx",
        sheet = "Sheet 1")

        enrollment_17_18 <- read_excel(path = "data-raw/enrollment-17-18.xlsx",
        sheet = "Sheet 1")

        1. Charlie Hadley May 11, 2022

          Hello Esther,

          Ah! Okay, this is a slightly difficult thing to explain.

          There are two types of double quotation characters, the curly ones you’re using “ and the straight one ” which are what you need to use in R code. Almost without exception the curly bracket cannot be used in programming languages.

          The next issue is a missing closing quotation mark at the end of the URL and the argument “mode”. The code you need to write is as follows:


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

          download.file(url = "https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-18-19.xlsx",
          mode = "wb",
          destfile = "data-raw/enrollment-18-19.xlsx")

          download.file(url = "https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-17-18.xlsx",
          mode = "wb",
          destfile = "data-raw/enrollment-17-18.xlsx")

          #import data

          enrollment_18_19 <- read_excel(path = "data-raw/enrollment-18-19.xlsx", sheet = "Sheet 1") enrollment_17_18 <- read_excel(path= "data-raw/enrollment-17-18.xlsx", sheet = "Sheet 1") enrollment_18_19 <- read_excel(path = "data-raw/enrollment-18-19.xlsx", sheet = "Sheet 1") enrollment_17_18 <- read_excel(path = "data-raw/enrollment-17-18.xlsx", sheet = "Sheet 1")

          Thanks,

          Charlie

  9. Julia Nee October 31, 2022

    What’s the difference between the “destfile” argument and “dest” in read_excel, which seems to have also put my downloads into the folder I directed them to? Are they two ways of doing the same thing?

      1. Julia Nee October 31, 2022

        Yes! I used this code:
        download.file(url = “https://github.com/rfortherestofus/going-deeper/raw/master/data-raw/enrollment-17-18.xlsx”,
        dest = “data-raw/enrollment-17-18.xlsx”)
        …and it downloaded the data to inside my “data-raw” folder. I was confused then when I watched the solutions video, because yeah, it seems like “dest” should not be accepted because it’s not an allowed argument; only “destfile” should work I would think.

  10. Kirstin O'Dell November 1, 2022

    I’m having trouble reading in the files. This is the error message:

    > enrollment_18_19 <- read_excel(path = "data-raw/enrollment-18-19.xlsx",
    + sheet = "Sheet 1")
    Error in utils::unzip(zip_path, list = TRUE) :
    error -103 with zipfile in unzGetCurrentFileInfo

  11. Amanda Krantz November 2, 2022

    I was able to download the files, but when I go to view them before import, I receive an error that “the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.”