Challenge 3 Instructions

challenge_3

animal_weights

eggs

australian_marriage

usa_households

sce_labor

Tidy Data: Pivoting

Author

Gabrielle Roman

Published

May 30, 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

eggs_tidy <- read.csv("_data/eggs_tidy.csv")
view(eggs_tidy)
tibble(eggs_tidy)

# A tibble: 120 × 6
   month      year large_half_dozen large_dozen extra_large_half_dozen
   <chr>     <int>            <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 
 7 July       2004             134.        234.                   137 
 8 August     2004             134.        234.                   137 
 9 September  2004             130.        234.                   136.
10 October    2004             128.        234.                   136.
# ℹ 110 more rows
# ℹ 1 more variable: extra_large_dozen <dbl>

Briefly describe the data

The data set includes 126 observations and 6 variables. It describes what looks to be the price of eggs sold by month/year between 2004 and 2013.

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     606.     836.
2 USA      1990 NAFTA     508.     488.
3 France   1980 EU        964.     467.
4 Mexico   1990 NAFTA     535.    2490.
5 USA      1980 NAFTA    1599.     198.
6 France   1990 EU       1654.     779.

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

nrow(eggs_tidy)

[1] 120

Code

ncol(eggs_tidy)

[1] 6

Code

nrow(eggs_tidy)*(ncol(eggs_tidy)-2)

[1] 480

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               606.
 2 Mexico   1980 NAFTA incoming               836.
 3 USA      1990 NAFTA outgoing               508.
 4 USA      1990 NAFTA incoming               488.
 5 France   1980 EU    outgoing               964.
 6 France   1980 EU    incoming               467.
 7 Mexico   1990 NAFTA outgoing               535.
 8 Mexico   1990 NAFTA incoming              2490.
 9 USA      1980 NAFTA outgoing              1599.
10 USA      1980 NAFTA incoming               198.
11 France   1990 EU    outgoing              1654.
12 France   1990 EU    incoming               779.

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

eggs_tidy %>%
  pivot_longer(cols = c(large_half_dozen, large_dozen, extra_large_half_dozen, extra_large_dozen),
  names_to = "egg type",
  values_to = "egg price")

# A tibble: 480 × 4
   month     year `egg type`             `egg price`
   <chr>    <int> <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 
# ℹ 470 more rows