Challenge 3

challenge_3

animal_weights

eggs

australian_marriage

usa_households

sce_labor

Author

Michaela Bowen

Published

September 26, 2022

Code

library(tidyverse)
library(readxl)
library(readr)

knitr::opts_chunk$set(echo = TRUE, warning=FALSE, message=FALSE)

Challenge Overview

Today’s challenge is to:

read in a data set, and describe the data set using both words and any supporting information (e.g., tables, etc)
identify what needs to be done to tidy the current data
anticipate the shape of pivoted data
pivot the data into tidy format using pivot_longer

Read in data

Read in one (or more) of the following datasets, using the correct R package and command.

animal_weights.csv ⭐
eggs_tidy.csv ⭐⭐ or organiceggpoultry.xls ⭐⭐⭐
australian_marriage*.xls ⭐⭐⭐
USA Households*.xlsx ⭐⭐⭐⭐
sce_labor_chart_data_public.xlsx 🌟🌟🌟🌟🌟

Code

library(readr)
eggs <- read_csv("_data/eggs_tidy.csv")
  
eggs

# A tibble: 120 × 6
   month      year large_half_dozen large_dozen extra_large_half_dozen extra_l…¹
   <chr>     <dbl>            <dbl>       <dbl>                  <dbl>     <dbl>
 1 January    2004             126         230                    132       230 
 2 February   2004             128.        226.                   134.      230 
 3 March      2004             131         225                    137       230 
 4 April      2004             131         225                    137       234.
 5 May        2004             131         225                    137       236 
 6 June       2004             134.        231.                   137       241 
 7 July       2004             134.        234.                   137       241 
 8 August     2004             134.        234.                   137       241 
 9 September  2004             130.        234.                   136.      241 
10 October    2004             128.        234.                   136.      241 
# … with 110 more rows, and abbreviated variable name ¹extra_large_dozen

Briefly describe the data

Above I have read in the eggs_tidy dataset. Upon observsation, I noticed there were 6 variables. We can see that the amount and size of eggs(L,XL, Dozen or Half Dozen) are determined by the independent variables month and year. The data ranges from January of 2004 to December of 2013. I plant to pivot the data set longer in order to group by month, year or type of product. This way I can get means, averages, standard deviations, etc on the amount produces based on a certain timeframe.

Anticipate the End Result

The first step in pivoting the data is to try to come up with a concrete vision of what the end product should look like - that way you will know whether or not your pivoting was successful.

One easy way to do this is to think about the dimensions of your current data (tibble, dataframe, or matrix), and then calculate what the dimensions of the pivoted data should be.

Suppose you have a dataset with \(n\) rows and \(k\) variables. In our example, 3 of the variables are used to identify a case, so you will be pivoting \(k-3\) variables into a longer format where the \(k-3\) variable names will move into the names_to variable and the current values in each of those columns will move into the values_to variable. Therefore, we would expect \(n * (k-3)\) rows in the pivoted dataframe!

Challenge: Pivot the Chosen Data

Document your work here. What will a new “case” be once you have pivoted the data? How does it meet requirements for tidy data?

Below are the expected dimensions after pivoting the eggs_tidy data. The new variables “l/xl_half/whole_dozens”, and “dozens_quantity” are more helpful in determining information from this dataset. Each case is now determined by year and month, and the size and quantity of the produce depend on those specific variables. It is now easier to group by time, and type of product as well.

Code

#existing rows
nrow(eggs)

[1] 120

Code

#existing columns
ncol(eggs)

[1] 6

Code

#dataframe dimensions algorithm
#expected rows
nrow(eggs) * ((ncol(eggs)-2))

[1] 480

Code

#expected columns
2+4

[1] 6

Code

#pivoting data
pivot_longer(eggs, cols = c("large_half_dozen","large_dozen","extra_large_half_dozen","extra_large_dozen"),
               names_to = "l/xl_half/whole_dozens",
               values_to = "dozens_quantity"
               )

# A tibble: 480 × 4
   month     year `l/xl_half/whole_dozens` dozens_quantity
   <chr>    <dbl> <chr>                              <dbl>
 1 January   2004 large_half_dozen                    126 
 2 January   2004 large_dozen                         230 
 3 January   2004 extra_large_half_dozen              132 
 4 January   2004 extra_large_dozen                   230 
 5 February  2004 large_half_dozen                    128.
 6 February  2004 large_dozen                         226.
 7 February  2004 extra_large_half_dozen              134.
 8 February  2004 extra_large_dozen                   230 
 9 March     2004 large_half_dozen                    131 
10 March     2004 large_dozen                         225 
# … with 470 more rows

Code

eggs

# A tibble: 120 × 6
   month      year large_half_dozen large_dozen extra_large_half_dozen extra_l…¹
   <chr>     <dbl>            <dbl>       <dbl>                  <dbl>     <dbl>
 1 January    2004             126         230                    132       230 
 2 February   2004             128.        226.                   134.      230 
 3 March      2004             131         225                    137       230 
 4 April      2004             131         225                    137       234.
 5 May        2004             131         225                    137       236 
 6 June       2004             134.        231.                   137       241 
 7 July       2004             134.        234.                   137       241 
 8 August     2004             134.        234.                   137       241 
 9 September  2004             130.        234.                   136.      241 
10 October    2004             128.        234.                   136.      241 
# … with 110 more rows, and abbreviated variable name ¹extra_large_dozen

Code

#new dimensions of the eggs data
dim(eggs)

[1] 120   6

Any additional comments?

After reading in the tidy eggs data I attempted to read in the excel eggs data. Below I have renamed the columns, separated month and year into separate columns and pivoted the data longer to create the new columns: size, quantity and price. After visually analyzing the data I noted there was a typo in the “date” column. I removed this typo and proceeded with pivoting the data.

Code

organiceggpoultry <- read_excel("_data/organiceggpoultry.xls",
                                sheet = "Data",
                                range = cell_limits(c(6,2),c(NA,6)),
                                col_names = c("date", "extralarge_dozen", "extralarge_halfdozen","large_dozen", "large_halfdozen "))%>%
  mutate(
    date = str_remove(date, "/1")
  )%>%
  #split month/year variables
  separate("date", into = c("month","year"))%>%
  fill("year")%>%
  pivot_longer(cols = contains("dozen"),
              names_to = c("size","quantity"),
              names_sep = "_",
              values_to = "price")
summary(organiceggpoultry)

    month               year               size             quantity        
 Length:480         Length:480         Length:480         Length:480        
 Class :character   Class :character   Class :character   Class :character  
 Mode  :character   Mode  :character   Mode  :character   Mode  :character  
                                                                            
                                                                            
                                                                            
     price      
 Min.   :126.0  
 1st Qu.:174.5  
 Median :206.6  
 Mean   :210.1  
 3rd Qu.:267.5  
 Max.   :290.0

Code

View(organiceggpoultry)