Challenge 3

challenge_3

Tidy Data: Pivoting

Author

Yoshita Varma Annam

Published

August 17, 2022

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 organicpoultry.xls ⭐⭐⭐
australian_marriage*.xlsx ⭐⭐⭐
USA Households*.xlsx ⭐⭐⭐⭐
sce_labor_chart_data_public.csv 🌟🌟🌟🌟🌟

Code

eggs_tidy <- read_csv('_data/eggs_tidy.csv')
eggs_tidy

# 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

summary(eggs_tidy)

    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

summary(eggs_tidy)

    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

Briefly describe the data

Egg_tidy.csv has 120 rows and 6 columns where it explain the purchase price of the eggs for different sizes. From the summary it is observed that data is dated from the year 2004 to 2013. Here in the columns large_half_dozen mean size of the eggs is large and price is given only for half dozen. As it can be observed as a signle column for one size and one quantity. To tidy this I have choose to store the size and quantities separately. In that way it will be easy to store the analyze the prices for different sizes over the years. And can predicting the price based on the quantity will be easy.

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    1397.    2704.
2 USA      1990 NAFTA     361.     671.
3 France   1980 EU        722.    1636.
4 Mexico   1990 NAFTA    1178.    2645.
5 USA      1980 NAFTA    1123.     464.
6 France   1990 EU       1107.     562.

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

In the tidy version of the egg data set we will have cost for the respective size and quantity. That means the final data set will have month, year, size_of_the_egg, quantity_of_the_eggs, and price_of_the_eggs as columns. This will have one column less than the existing columns but 4 times the rows.

Code

# existing rows and columns
nrow(eggs_tidy)

[1] 120

Code

ncol(eggs_tidy)

[1] 6

Code

# expected rows and columns
nrow(eggs_tidy) * (ncol(eggs_tidy)-2)

[1] 480

Code

ncol(eggs_tidy) -1

[1] 5

Any additional comments?

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              1397.
 2 Mexico   1980 NAFTA incoming              2704.
 3 USA      1990 NAFTA outgoing               361.
 4 USA      1990 NAFTA incoming               671.
 5 France   1980 EU    outgoing               722.
 6 France   1980 EU    incoming              1636.
 7 Mexico   1990 NAFTA outgoing              1178.
 8 Mexico   1990 NAFTA incoming              2645.
 9 USA      1980 NAFTA outgoing              1123.
10 USA      1980 NAFTA incoming               464.
11 France   1990 EU    outgoing              1107.
12 France   1990 EU    incoming               562.

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

Challenge: Pivot the Chosen Data

To pivot the chosen data I have renamed columns names of large_half_dozen, large_dozen, extra_large_half_dozen, and extra_large_dozen to Large_HalfDozen, Large_Dozen ExtraLarge_HalfDozen, and ExtraLarge_Dozen. This way it will be easy to acess the dataset and pivot it appropriately. Also, in future it will be easy to read the data and group based on monthb and year.

Code

#Renaming the column names 
eggs2<-rename(eggs_tidy,
        "Large_HalfDozen" = large_half_dozen, 
       "Large_Dozen" =  large_dozen,
       "ExtraLarge_HalfDozen"= extra_large_half_dozen, 
      "ExtraLarge_Dozen" =  extra_large_dozen )

Code

eggs_longer <- eggs2%>%
  pivot_longer(cols=contains("large"),
               names_to = c("size_of_the_egg", "quantity_of_the_eggs"),
               names_sep="_",
               values_to = "price_of_the_eggs"
  )
eggs_longer

# A tibble: 480 × 5
   month     year size_of_the_egg quantity_of_the_eggs price_of_the_eggs
   <chr>    <dbl> <chr>           <chr>                            <dbl>
 1 January   2004 Large           HalfDozen                         126 
 2 January   2004 Large           Dozen                             230 
 3 January   2004 ExtraLarge      HalfDozen                         132 
 4 January   2004 ExtraLarge      Dozen                             230 
 5 February  2004 Large           HalfDozen                         128.
 6 February  2004 Large           Dozen                             226.
 7 February  2004 ExtraLarge      HalfDozen                         134.
 8 February  2004 ExtraLarge      Dozen                             230 
 9 March     2004 Large           HalfDozen                         131 
10 March     2004 Large           Dozen                             225 
# … with 470 more rows

Any additional comments?