Pivoting dataset using pivot_longer: eggs_tidy.csv

challenge_3

eggs

readr

Tidy Data: Pivoting

Author

Susmita Madineni

Published

March 16, 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

# Reading eggs_tidy.csv dataset

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

Code

# Preview the first few rows of the dataset
head(eggs_tidy_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

Code

# Understanding the dimensions of the dataset 
dim(eggs_tidy_data)

[1] 120   6

Code

# Identifying the column names of the dataset 
colnames(eggs_tidy_data)

[1] "month"                  "year"                   "large_half_dozen"      
[4] "large_dozen"            "extra_large_half_dozen" "extra_large_dozen"

Code

# Identifying the data types of the columns
table(sapply(eggs_tidy_data, function(x) typeof(x)))


character    double 
        1         5

Code

# Summary of the dataset
summary(eggs_tidy_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

Briefly describe the data

This dataset gives the information about prices of different quantities and sizes of eggs in every month from the year 2004 to 2013.. It has 120 rows and 6 columns. Based on the above, we can infer that there are 1 column(variables) is of character datatype and 5 variables are of double datatype. All the columns are readable for the user. Each observation in the dataset is providing information about - month, year, prices of different quantities and sizes of eggs in a particular month and year. Based on the summary, we can say that average price for large_half_dozen is around 155.2, for large_dozen is around 254.2, for extra_large_half_dozen is 164.2 and extra_large_dozen is around 266.8. The dataset is put together in a way that size, quantity and respective price of the eggs are included in the same column. I want to pivot the data longer to make it more readable for analysis. I want to make different columns for size and quantity and mark the price for that month, year, size and quantity respectively.

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!

Code

#existing rows/cases in the dataset
nrow(eggs_tidy_data)

[1] 120

Code

#existing columns in the dataset
ncol(eggs_tidy_data)

[1] 6

Code

#expected rows/cases in the dataset
nrow(eggs_tidy_data) * (ncol(eggs_tidy_data)-2)

[1] 480

Code

# expected columns in the dataset
3 + 2

[1] 5

Challenge: Describe the final dimensions

From the above, we can say that the dataset has 120 rows(observations) and 6 columns in the dataset. After pivoting the dataset, I expect 480 rows/observations in the dataset with 5 columns. After pivoting, the observation will contain month, year, size, quantity and price of the eggs columns. With this we will be easily able to analyze the data. We can understand how the prices of different types, quantities of the eggs across different months, years are changing.

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_pivot_data<-pivot_longer(eggs_tidy_data, cols = contains("dozen"),
                              names_to= c("size", "quantity"),
                              names_sep = "_",
                              values_to = "price")
View(eggs_pivot_data)

Code

# Preview the first few rows of the pivoted dataset
head(eggs_pivot_data)

# A tibble: 6 × 5
  month     year size  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.

Code

# number of rows/cases in the pivot dataset
nrow(eggs_pivot_data)

[1] 480

Code

# number of columns in the pivot dataset
ncol(eggs_pivot_data)

[1] 5

As expected, we can observe that the pivoted dataset has 480 observations with 5 columns(month, year, size, quantity and price of the eggs). This helps us to easily analyze the data. We can mutate the price of the eggs(from cents to USD).

Code

# Mutating the price of the eggs
eggs_price_mutated <-mutate(eggs_pivot_data, price_USD = price/100)%>%
  select(!contains("cost"))

View(eggs_price_mutated)

# Identifying the column names of the dataset 
colnames(eggs_price_mutated)

[1] "month"     "year"      "size"      "quantity"  "price"     "price_USD"

Code

# Identifying the data types of the columns
table(sapply(eggs_price_mutated, function(x) typeof(x)))


character    double 
        3         3

The mutated data has an additional column price in USD for every observation. There are 3 character datatypes(month, size, quantity) columns and 3 columns are of double datatype(year, price, price_USD).