Challenge 3

challenge_3

FNU Avinesh Krishnan

animal_weight

Tidy Data: Pivoting

Author

FNU Avinesh Krishnan

Published

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

library(readr)
weightdata <- read_csv("~/Desktop/601_Spring_2023/posts/_data/animal_weight.csv")

Briefly describe the data

Describe the data, and be sure to comment on why you are planning to pivot it to make it “tidy”

Code

head(weightdata)

# A tibble: 6 × 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
# … 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

dim(weightdata)

[1]  9 17

Code

str(weightdata)

spc_tbl_ [9 × 17] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
 $ IPCC Area         : chr [1:9] "Indian Subcontinent" "Eastern Europe" "Africa" "Oceania" ...
 $ Cattle - dairy    : num [1:9] 275 550 275 500 600 400 350 275 604
 $ Cattle - non-dairy: num [1:9] 110 391 173 330 420 305 391 173 389
 $ Buffaloes         : num [1:9] 295 380 380 380 380 380 380 380 380
 $ Swine - market    : num [1:9] 28 50 28 45 50 28 50 28 46
 $ Swine - breeding  : num [1:9] 28 180 28 180 198 28 180 28 198
 $ Chicken - Broilers: num [1:9] 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9
 $ Chicken - Layers  : num [1:9] 1.8 1.8 1.8 1.8 1.8 1.8 1.8 1.8 1.8
 $ Ducks             : num [1:9] 2.7 2.7 2.7 2.7 2.7 2.7 2.7 2.7 2.7
 $ Turkeys           : num [1:9] 6.8 6.8 6.8 6.8 6.8 6.8 6.8 6.8 6.8
 $ Sheep             : num [1:9] 28 48.5 28 48.5 48.5 28 48.5 28 48.5
 $ Goats             : num [1:9] 30 38.5 30 38.5 38.5 30 38.5 30 38.5
 $ Horses            : num [1:9] 238 377 238 377 377 238 377 238 377
 $ Asses             : num [1:9] 130 130 130 130 130 130 130 130 130
 $ Mules             : num [1:9] 130 130 130 130 130 130 130 130 130
 $ Camels            : num [1:9] 217 217 217 217 217 217 217 217 217
 $ Llamas            : num [1:9] 217 217 217 217 217 217 217 217 217
 - attr(*, "spec")=
  .. cols(
  ..   `IPCC Area` = col_character(),
  ..   `Cattle - dairy` = col_double(),
  ..   `Cattle - non-dairy` = col_double(),
  ..   Buffaloes = col_double(),
  ..   `Swine - market` = col_double(),
  ..   `Swine - breeding` = col_double(),
  ..   `Chicken - Broilers` = col_double(),
  ..   `Chicken - Layers` = col_double(),
  ..   Ducks = col_double(),
  ..   Turkeys = col_double(),
  ..   Sheep = col_double(),
  ..   Goats = col_double(),
  ..   Horses = col_double(),
  ..   Asses = col_double(),
  ..   Mules = col_double(),
  ..   Camels = col_double(),
  ..   Llamas = col_double()
  .. )
 - attr(*, "problems")=<externalptr>

Code

colnames(weightdata)

 [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

weightdata %>% 
  select(`IPCC Area`) %>% 
  distinct(.)

# A tibble: 9 × 1
  `IPCC Area`        
  <chr>              
1 Indian Subcontinent
2 Eastern Europe     
3 Africa             
4 Oceania            
5 Western Europe     
6 Latin America      
7 Asia               
8 Middle east        
9 Northern America

The data represents information on livestock populations across different regions. The data includes the following cases and variables:

Each row represents a specific region or area. Variables: IPCC Area: The geographical area or region under consideration. Cattle - dairy: Population count of dairy cattle in the region. Cattle - non-dairy: Population count of non-dairy cattle in the region. Buffaloes: Population count of buffaloes in the region. Swine - market: Population count of market swine (pigs) in the region. Swine - breeding: Population count of breeding swine (pigs) in the region. Chicken - Broilers: Population count of broiler chickens in the region. Chicken - Layers: Population count of layer chickens in the region. Ducks: Population count of ducks in the region. Turkeys: Population count of turkeys in the region. Sheep: Population count of sheep in the region. Goats: Population count of goats in the region. Horses: Population count of horses in the region. Asses: Population count of asses (donkeys) in the region. Mules: Population count of mules in the region. Camels: Population count of camels in the region. Llamas: Population count of llamas in the region. The data provides an overview of livestock populations in various regions, including Western Europe, Oceania, Northern America, Middle East, Latin America, Indian Subcontinent, Eastern Europe, Asia, and Africa. It presents the population counts for different types of livestock in each region.

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!

We will utilize the function pivot_longer() to transform the data. Each of the 16 animal types will be listed under a column named “Animals,” and their corresponding weights will be listed under a column named “Weight.” This transformation will result in a longer version of the current tibble, where each IPCC area (geographic region) will appear 16 times, representing each animal type. With the current 9 IPCC areas, the resulting tibble will have 144 rows and 3 columns: IPCC area, Animals, and Weight.

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     653.    852. 
2 USA      1990 NAFTA    1432.    886. 
3 France   1980 EU       1421.   1260. 
4 Mexico   1990 NAFTA     697.    764. 
5 USA      1980 NAFTA    1855.    -57.5
6 France   1990 EU        933.  -1187.

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.

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              653. 
 2 Mexico   1980 NAFTA incoming              852. 
 3 USA      1990 NAFTA outgoing             1432. 
 4 USA      1990 NAFTA incoming              886. 
 5 France   1980 EU    outgoing             1421. 
 6 France   1980 EU    incoming             1260. 
 7 Mexico   1990 NAFTA outgoing              697. 
 8 Mexico   1990 NAFTA incoming              764. 
 9 USA      1980 NAFTA outgoing             1855. 
10 USA      1980 NAFTA incoming              -57.5
11 France   1990 EU    outgoing              933. 
12 France   1990 EU    incoming            -1187.

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?

We will perform a pivot operation on columns 2 through 17 that describe animal weights. This will create a new column called “Animals” which will contain the names of each animal column (e.g., Cattle - dairy, Cattle - non-dairy, etc.). The original column values will be moved to a new column titled “Weight”.

Code

pivoted_data<-weightdata %>% 
pivot_longer(col = c(2:17),names_to="Animals",values_to = "Weight")
pivoted_data

# A tibble: 144 × 3
   `IPCC Area`         Animals            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_data)

[1] 144   3

After performing the pivot_longer operation on the data, the resulting tibble has dimensions of 144 rows and 3 columns, which aligns with our expectations.

Any additional comments?