Challenge 3 Instructions

challenge_3

animal_weights

eggs

australian_marriage

usa_households

sce_labor

Author

Meredith Rolfe

Published

August 17, 2022

Code

library(tidyverse)
library(readxl)

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 animal_weight.csv into a dataframe
animal_wt = read_csv("_data/animal_weight.csv")


# Displaying the top 5 rows in the dataframe
head(animal_wt, 5)

# A tibble: 5 × 17
  IPCC A…¹ Cattl…² Cattl…³ Buffa…⁴ Swine…⁵ Swine…⁶ Chick…⁷ Chick…⁸ Ducks Turkeys
  <chr>      <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl> <dbl>   <dbl>
1 Indian …     275     110     295      28      28     0.9     1.8   2.7     6.8
2 Eastern…     550     391     380      50     180     0.9     1.8   2.7     6.8
3 Africa       275     173     380      28      28     0.9     1.8   2.7     6.8
4 Oceania      500     330     380      45     180     0.9     1.8   2.7     6.8
5 Western…     600     420     380      50     198     0.9     1.8   2.7     6.8
# … with 7 more variables: Sheep <dbl>, Goats <dbl>, Horses <dbl>, Asses <dbl>,
#   Mules <dbl>, Camels <dbl>, Llamas <dbl>, and abbreviated variable names
#   ¹`IPCC Area`, ²`Cattle - dairy`, ³`Cattle - non-dairy`, ⁴Buffaloes,
#   ⁵`Swine - market`, ⁶`Swine - breeding`, ⁷`Chicken - Broilers`,
#   ⁸`Chicken - Layers`

Code

# Checking for any NaN values in columns
apply(animal_wt, 2, anyNA)

         IPCC Area     Cattle - dairy Cattle - non-dairy          Buffaloes 
             FALSE              FALSE              FALSE              FALSE 
    Swine - market   Swine - breeding Chicken - Broilers   Chicken - Layers 
             FALSE              FALSE              FALSE              FALSE 
             Ducks            Turkeys              Sheep              Goats 
             FALSE              FALSE              FALSE              FALSE 
            Horses              Asses              Mules             Camels 
             FALSE              FALSE              FALSE              FALSE 
            Llamas 
             FALSE

Code

# Column names of the dataframe
colnames(animal_wt)

 [1] "IPCC Area"          "Cattle - dairy"     "Cattle - non-dairy"
 [4] "Buffaloes"          "Swine - market"     "Swine - breeding"  
 [7] "Chicken - Broilers" "Chicken - Layers"   "Ducks"             
[10] "Turkeys"            "Sheep"              "Goats"             
[13] "Horses"             "Asses"              "Mules"             
[16] "Camels"             "Llamas"

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 data looks to be sourced to collect data for different breeds of farm-bred animals, both animals and poultry (cattle, chicken, buffaloes, etc.) and their corresponding weights. It also contains the area that these animals are native to. Pivoting this data will tidy it up, as the only differentiator for all the rows is the weight value, which can be converted to a single column.

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    1012.    1850.
2 USA      1990 NAFTA     735.    1075.
3 France   1980 EU        188.    1190.
4 Mexico   1990 NAFTA    1449.    1194.
5 USA      1980 NAFTA    1314.     311.
6 France   1990 EU        190.     554.

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

head(animal_wt, 9)

# A tibble: 9 × 17
  IPCC A…¹ Cattl…² Cattl…³ Buffa…⁴ Swine…⁵ Swine…⁶ Chick…⁷ Chick…⁸ Ducks Turkeys
  <chr>      <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl> <dbl>   <dbl>
1 Indian …     275     110     295      28      28     0.9     1.8   2.7     6.8
2 Eastern…     550     391     380      50     180     0.9     1.8   2.7     6.8
3 Africa       275     173     380      28      28     0.9     1.8   2.7     6.8
4 Oceania      500     330     380      45     180     0.9     1.8   2.7     6.8
5 Western…     600     420     380      50     198     0.9     1.8   2.7     6.8
6 Latin A…     400     305     380      28      28     0.9     1.8   2.7     6.8
7 Asia         350     391     380      50     180     0.9     1.8   2.7     6.8
8 Middle …     275     173     380      28      28     0.9     1.8   2.7     6.8
9 Norther…     604     389     380      46     198     0.9     1.8   2.7     6.8
# … with 7 more variables: Sheep <dbl>, Goats <dbl>, Horses <dbl>, Asses <dbl>,
#   Mules <dbl>, Camels <dbl>, Llamas <dbl>, and abbreviated variable names
#   ¹`IPCC Area`, ²`Cattle - dairy`, ³`Cattle - non-dairy`, ⁴Buffaloes,
#   ⁵`Swine - market`, ⁶`Swine - breeding`, ⁷`Chicken - Broilers`,
#   ⁸`Chicken - Layers`

Code

# Existing rows
nrow(animal_wt)

[1] 9

Code

# Existing cols
ncol(animal_wt)

[1] 17

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              1012.
 2 Mexico   1980 NAFTA incoming              1850.
 3 USA      1990 NAFTA outgoing               735.
 4 USA      1990 NAFTA incoming              1075.
 5 France   1980 EU    outgoing               188.
 6 France   1980 EU    incoming              1190.
 7 Mexico   1990 NAFTA outgoing              1449.
 8 Mexico   1990 NAFTA incoming              1194.
 9 USA      1980 NAFTA outgoing              1314.
10 USA      1980 NAFTA incoming               311.
11 France   1990 EU    outgoing               190.
12 France   1990 EU    incoming               554.

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?

Every row is uniquely identified by 1 variable i.e. the country column which represents the area that the particular animal belongs to. Thus we have k-1 = 17-1 = 16 variables that are being pivoted. 16 columns consist of the animal weights of animals of different breeds belonging to a particular sub-region, which will all be pivoted and transformed to a single weight column, which will make the data neat. The new dataframe will be expected to consist of n * (k-1) rows = 9 * (17 - 1) rows = 144 rows

Code

# Fetching column names of the animal_wt dataframe
col_names <- names(animal_wt)

# Printing column names
col_names

 [1] "IPCC Area"          "Cattle - dairy"     "Cattle - non-dairy"
 [4] "Buffaloes"          "Swine - market"     "Swine - breeding"  
 [7] "Chicken - Broilers" "Chicken - Layers"   "Ducks"             
[10] "Turkeys"            "Sheep"              "Goats"             
[13] "Horses"             "Asses"              "Mules"             
[16] "Camels"             "Llamas"

Code

# Pivoting the dataframe
pivoted_animal_wt <- pivot_longer(animal_wt, cols=col_names[-1], 
                          names_to = "animal_breed",
                          values_to = "weight")
pivoted_animal_wt

# A tibble: 144 × 3
   `IPCC Area`         animal_breed       weight
   <chr>               <chr>               <dbl>
 1 Indian Subcontinent Cattle - dairy      275  
 2 Indian Subcontinent Cattle - non-dairy  110  
 3 Indian Subcontinent Buffaloes           295  
 4 Indian Subcontinent Swine - market       28  
 5 Indian Subcontinent Swine - breeding     28  
 6 Indian Subcontinent Chicken - Broilers    0.9
 7 Indian Subcontinent Chicken - Layers      1.8
 8 Indian Subcontinent Ducks                 2.7
 9 Indian Subcontinent Turkeys               6.8
10 Indian Subcontinent Sheep                28  
# … with 134 more rows

Code

dim(pivoted_animal_wt)

[1] 144   3

Any additional comments?

The pivoted dataframe looks much tidier with a more descriptive view of the animal weight of each animal belonging to a sub-region.

Reading and analysing the file eggs_tidy.csv. Every row is uniquely identified by 2 variables i.e. the month and year columns which represent the month and year for the cost of different egg brackets. Thus we have k-2 = 6-2 = 4 variables that are being pivoted. 4 columns consist of the different egg bracket costs for the particular moth and year, which will all be pivoted and transformed to a single cost column, which will make the data neat. The new dataframe will be expected to consist of n * (k-1) rows = 120 * (6 - 2) rows = 480 rows

Code

# Read the csv file
egg_data <- read_csv("_data/eggs_tidy.csv", show_col_types = FALSE)

# Displaying top 5 rows in the dataframe
tail(egg_data, 5)

# A tibble: 5 × 6
  month      year large_half_dozen large_dozen extra_large_half_dozen extra_la…¹
  <chr>     <dbl>            <dbl>       <dbl>                  <dbl>      <dbl>
1 August     2013              178        268.                   188.        290
2 September  2013              178        268.                   188.        290
3 October    2013              178        268.                   188.        290
4 November   2013              178        268.                   188.        290
5 December   2013              178        268.                   188.        290
# … with abbreviated variable name ¹extra_large_dozen

Code

# Dimensions of the dataframe
dim(egg_data)

[1] 120   6

Code

# Get column names of the dataframe
col_names <- names(egg_data)

# Exclude month and year columns from pivoting, as they uniquely identify each row case
col_names <- col_names[!col_names %in% c("month","year")]

# Pivoting longer for tidier dataframe
pivoted_egg_data <- pivot_longer(egg_data, cols=col_names, 
                          names_to = "egg_qty",
                          values_to = "cost")

pivoted_egg_data

# A tibble: 480 × 4
   month     year egg_qty                 cost
   <chr>    <dbl> <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

Code

# Dimensions of pivoted dataframe
dim(pivoted_egg_data)

[1] 480   4

As above, the pivoted dataframe looks much tidier with a more descriptive view of the egg quantity (carton type) and their corresponding costs.