Challenge 3

challenge_3

maanusri balasubramanian

eggs_tidy

Tidy Data: Pivoting

Author

Maanusri Balasubramanian

Published

May 4, 2023

Code

library(tidyverse)

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

# reading the CSV file
eggs <- read_csv("_data/eggs_tidy.csv")

# taking a peek into the data 
head(eggs)

# A tibble: 6 × 6
  month     year large_half_dozen large_dozen extra_large_half_dozen
  <chr>    <dbl>            <dbl>       <dbl>                  <dbl>
1 January   2004             126         230                    132 
2 February  2004             128.        226.                   134.
3 March     2004             131         225                    137 
4 April     2004             131         225                    137 
5 May       2004             131         225                    137 
6 June      2004             134.        231.                   137 
# ℹ 1 more variable: extra_large_dozen <dbl>

Code

# summary
summary(eggs)

    month                year      large_half_dozen  large_dozen   
 Length:120         Min.   :2004   Min.   :126.0    Min.   :225.0  
 Class :character   1st Qu.:2006   1st Qu.:129.4    1st Qu.:233.5  
 Mode  :character   Median :2008   Median :174.5    Median :267.5  
                    Mean   :2008   Mean   :155.2    Mean   :254.2  
                    3rd Qu.:2011   3rd Qu.:174.5    3rd Qu.:268.0  
                    Max.   :2013   Max.   :178.0    Max.   :277.5  
 extra_large_half_dozen extra_large_dozen
 Min.   :132.0          Min.   :230.0    
 1st Qu.:135.8          1st Qu.:241.5    
 Median :185.5          Median :285.5    
 Mean   :164.2          Mean   :266.8    
 3rd Qu.:185.5          3rd Qu.:285.5    
 Max.   :188.1          Max.   :290.0

Code

# dimensions of the dataset
dim(eggs)

[1] 120   6

Briefly describe the data

Describe the data, and be sure to comment on why you are planning to pivot it to make it “tidy”

The dataset contains egg prices over all the years, from 2004 to 2013 month-wise. We can see that the dataset has 120 rows and 6 columns. Each row in the dataset contains the prices of different types(large/extra_large) and quantities(half_dozen/dozen) of eggs in a particular month of a particular year. This violates the “tidy” data guideline that each observation must have a separate row/entry. So pivoting the columns large_half_dozen, large_dozen, extra_large_half_dozen and extra_large_dozen would make the data “tidy”. We can create two new columns “type” (which will take large and extra_large as values) and “quantity” (which will take dozen and half_dozen as values), pivoting the unnecessary columns and making the data “tidy”.

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!

Example: find current and future data dimensions

Lets see if this works with a simple example.

Code

df<-tibble(country = rep(c("Mexico", "USA", "France"),2),
           year = rep(c(1980,1990), 3), 
           trade = rep(c("NAFTA", "NAFTA", "EU"),2),
           outgoing = rnorm(6, mean=1000, sd=500),
           incoming = rlogis(6, location=1000, 
                             scale = 400))
df

# A tibble: 6 × 5
  country  year trade outgoing incoming
  <chr>   <dbl> <chr>    <dbl>    <dbl>
1 Mexico   1980 NAFTA    1182.    1100.
2 USA      1990 NAFTA    1096.    1070.
3 France   1980 EU       1322.    2168.
4 Mexico   1990 NAFTA     712.     864.
5 USA      1980 NAFTA     938.    1084.
6 France   1990 EU        890.     747.

Code

#existing rows/cases
nrow(df)

[1] 6

Code

#existing columns/cases
ncol(df)

[1] 5

Code

#expected rows/cases
nrow(df) * (ncol(df)-3)

[1] 12

Code

# expected columns 
3 + 2

[1] 5

Or simple example has \(n = 6\) rows and \(k - 3 = 2\) variables being pivoted, so we expect a new dataframe to have \(n * 2 = 12\) rows x \(3 + 2 = 5\) columns.

Challenge: Describe the final dimensions

Document your work here.

Code

# existing rows
nrow(eggs)

[1] 120

Code

# existing columns
ncol(eggs)

[1] 6

Code

# expected rows
nrow(eggs) * (ncol(eggs)-2)

[1] 480

Code

# expected columns
ncol(eggs) - 4 + 3

[1] 5

Any additional comments?

The current dimension of the dataset is 120 x 6. As we’ll be changing 4 of the columns into rows, the number of rows would be 120 * 4 = 480. And the expected number of columns would be 4.

Pivot the Data

Now we will pivot the data, and compare our pivoted data dimensions to the dimensions calculated above as a “sanity” check.

Example

Code

df<-pivot_longer(df, col = c(outgoing, incoming),
                 names_to="trade_direction",
                 values_to = "trade_value")
df

# A tibble: 12 × 5
   country  year trade trade_direction trade_value
   <chr>   <dbl> <chr> <chr>                 <dbl>
 1 Mexico   1980 NAFTA outgoing              1182.
 2 Mexico   1980 NAFTA incoming              1100.
 3 USA      1990 NAFTA outgoing              1096.
 4 USA      1990 NAFTA incoming              1070.
 5 France   1980 EU    outgoing              1322.
 6 France   1980 EU    incoming              2168.
 7 Mexico   1990 NAFTA outgoing               712.
 8 Mexico   1990 NAFTA incoming               864.
 9 USA      1980 NAFTA outgoing               938.
10 USA      1980 NAFTA incoming              1084.
11 France   1990 EU    outgoing               890.
12 France   1990 EU    incoming               747.

Yes, once it is pivoted long, our resulting data are \(12x5\) - exactly what we expected!

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?

Code

tidy_eggs <- eggs %>%
  pivot_longer(cols = contains("dozen"),
               names_to= c("type", "quantity"),
               names_sep = '_',
               values_to = "price")

tidy_eggs

# A tibble: 480 × 5
   month     year type  quantity price
   <chr>    <dbl> <chr> <chr>    <dbl>
 1 January   2004 large half      126 
 2 January   2004 large dozen     230 
 3 January   2004 extra large     132 
 4 January   2004 extra large     230 
 5 February  2004 large half      128.
 6 February  2004 large dozen     226.
 7 February  2004 extra large     134.
 8 February  2004 extra large     230 
 9 March     2004 large half      131 
10 March     2004 large dozen     225 
# ℹ 470 more rows

As expected the pivoted dataset has the dimensions 480 x 5. Yes, this new dataset meets all the requirements for tidy data, as each unique case has a separate entry/row in the dataset.

Any additional comments?