R Notebook

Daniel Lefevre

Tidying Untidy Datasets

library(tidyverse)

Registered S3 methods overwritten by 'dbplyr':
  method         from
  print.tbl_lazy     
  print.tbl_sql      
── Attaching packages ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse 1.3.0 ──
✓ ggplot2 3.3.3     ✓ purrr   0.3.4
✓ tibble  3.1.0     ✓ dplyr   1.0.4
✓ tidyr   1.1.2     ✓ stringr 1.4.0
✓ readr   1.4.0     ✓ forcats 0.5.0
── Conflicts ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
x dplyr::filter() masks stats::filter()
x dplyr::lag()    masks stats::lag()

Untidy dataset 1

Source: https://github.com/kwstat/untidydata2/tree/master/inst/messydata

data <- read.csv("/Users/daniellefevre/Documents/DATA101/untidydata2-master/inst/messydata/agcensus-chapter1-table1-US.csv")

head(data)

This dataset has a lot of information, I am going to pare it down to some of the produce items in order to practice the pivot commands more effectively. If we wanted to work with the dataset, it would be easy enough to repeat the similar steps for the rest of the data in this file.

produce <- data %>%
  filter(commodity %in% c("OATS", "BARLEY",
                          "SOYBEANS", "BEANS", "COTTON", "TOBACCO", 
                          "HAY & HAYLAGE", "RICE", "SUNFLOWER", 
                          "SUGARBEETS", "SUGARCANE", "PEANUTS",
                          "POTATOES", "SWEET POTATOES"))

produce <- produce %>% select(c("commodity", "data.item", "X2017", "X2012",
                                "X2007", "X2002", "X1997"))

head(produce)

We’ll call this the ‘original’ data.

View(produce)

Rename the year columns:

names(produce)

[1] "commodity" "data.item" "X2017"     "X2012"     "X2007"     "X2002"     "X1997"    

names(produce) <- c("commodity", "data.item", "2017", "2012", "2007", "2002",
                    "1997")

Now we need to clean up the redundant information in the data.item column

produce[grepl("OPERATIONS", produce$data.item), ]$data.item = 
  "OPERATIONS WITH AREA HARVESTED"
produce[grepl("ACRES", produce$data.item), ]$data.item = 
  "ACRES HARVESTED"
# We could turn the production unit into a factor column, but we'll just assume
# a common unit for now
produce[grepl("PRODUCTION", produce$data.item), ]$data.item = 
  "PRODUCTION"

head(produce)

In order to “tidy” the data, “Year”, “OPERATIONS WITH AREA HARVESTED”, “ACRES HARVESTED”, and “PRODUCTION” should be columns.

view(produce)

produce %>%
  pivot_wider(names_from = data.item, values_from = value)

We now have the produce data arranged in a “tidy” format, where each row corresponds to a specific measurement and each column corresponds to a single variable.

Untidy dataset 2

Source: https://github.com/kwstat/untidydata2/tree/master/inst/messydata

data = read_csv("/Users/daniellefevre/Documents/DATA101/untidydata2-master/inst/messydata/gdp_by_county.csv")

view(data)

str(data)

names(data) = c("FIPS", "Countyname", "Postal", "LineCode", "IndustryName", "2012", "2013", "2014", "2015")

names(data)

data <- slice(data, 4:12459)

view(data)

data <- pivot_longer(data, cols=c("2012", "2013", "2014", "2015"), names_to = "year")

head(data)

By moving the data for years into a single column, we now have the data in a tidy format where each row represents a single measurement and each variable is represented by one column

Tidy datasets

Tidy dataset 1

The mpg dataset

head(mpg)

The mpg dataset is tidy; each variable is represented by a single column, and each row represents a specific data item (a car)

Tidy dataset 2

The Kaggle Netflix Movie dataset

Source: https://www.kaggle.com/shivamb/netflix-shows

movies <- read.csv("/Users/daniellefevre/Documents/DATA101/netflix_titles.csv")

This dataset is already tidy; each row represents a single media item (Movie or TV show). Each column represents a specific variable.

Tidy dataset 3

The US Food Assistance Dataset

Source: https://www.kaggle.com/jpmiller/publicassistance

snap <- read.csv("/Users/daniellefevre/Documents/DATA101/snap/SNAP_history_1969_2019.csv")

This dataset is already tidy; each row represents collected statistics for the SNAP program for a single year.