Challenge 3_PriyankaThatikonda

challenge_3

animal_weights

eggs

australian_marriage

usa_households

sce_labor

Tidy Data: Pivoting

Author

Meredith Rolfe

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

Code

library(readr)
data <- read_csv("_data/eggs_tidy.csv",show_col_types = FALSE)
head(data)

# A tibble: 6 × 6
  month     year large_half_dozen large_dozen extra_large_half_dozen extra_lar…¹
  <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 
# … with abbreviated variable name ¹extra_large_dozen

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 consists of egg prices observed on a monthly basis between 2004 and 2013. It has dimensions of 120 rows and 6 columns.The data is aggregated based on different egg carton sizes, including large_half_dozen, large_dozen, extra_large_half_dozen, and extra_large_dozen. According to the analysis, the average price for large_half_dozen egg cartons is $155.2, while the average price for large_dozen egg cartons is $254.2. Furthermore, the average price for extra_large_half_dozen egg cartons is $164.2, and the average price for extra_large_dozen egg cartons is $266.8. These figures provide insights into the average prices associated with each specific egg carton size categor. However, the current format of the dataset violates the “tidy” data principle, which requires each observation to be represented in a separate row. To rectify this, we can pivot the columns representing different sizes and quantities of eggs (e.g., large_half_dozen, large_dozen, extra_large_half_dozen, extra_large_dozen). By doing so, we will create two new columns - “type” with values “large” and “extra_large”, and “quantity” with values “dozen” and “half_dozen”. This transformation will convert the dataset into a tidy format, adhering to the principles of tidy data.

Code

summary(data)

    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

dim(data)

[1] 120   6

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    766.     -10.4
2 USA      1990 NAFTA   1240.    1370. 
3 France   1980 EU       -55.0     77.6
4 Mexico   1990 NAFTA    628.    1767. 
5 USA      1980 NAFTA    919.    1543. 
6 France   1990 EU       877.     981.

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

dim(data)

[1] 120   6

Code

cat(paste("The number of rows in the dataset are", nrow(data)))

The number of rows in the dataset are 120

Any additional comments?

Here, we are pivoting four columns, and the number of rows in the original dataset (data) is 120. After pivoting, the resulting number of rows would be equal to `nrows(df) * 4 = 120 * 4 = 480`. Additionally, the number of columns after pivoting would be 3 + 2 = 5. Therefore, the final dimension of the dataset would be 480 rows by 5 columns.

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              766. 
 2 Mexico   1980 NAFTA incoming              -10.4
 3 USA      1990 NAFTA outgoing             1240. 
 4 USA      1990 NAFTA incoming             1370. 
 5 France   1980 EU    outgoing              -55.0
 6 France   1980 EU    incoming               77.6
 7 Mexico   1990 NAFTA outgoing              628. 
 8 Mexico   1990 NAFTA incoming             1767. 
 9 USA      1980 NAFTA outgoing              919. 
10 USA      1980 NAFTA incoming             1543. 
11 France   1990 EU    outgoing              877. 
12 France   1990 EU    incoming              981.

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

EggsPivot <- data %>% 
  pivot_longer(
    cols = -c(month, year),
    names_to = c("size", "quantity"),
    names_pattern = "(.+)_(.+)",
    values_to = "price"
  )

EggsPivot

# A tibble: 480 × 5
   month     year size             quantity price
   <chr>    <dbl> <chr>            <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

Any additional comments?