Challenge 3 Instructions

challenge_3

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

Code

animal_weight<-read_csv("_data/animal_weight.csv",
                        show_col_types = FALSE)

Briefly describe the data

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

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     738.    1211.
2 USA      1990 NAFTA    1268.    1224.
3 France   1980 EU       1856.     594.
4 Mexico   1990 NAFTA    1768.     982.
5 USA      1980 NAFTA     696.    1078.
6 France   1990 EU       1012.    2294.

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 data frame to have \(n * 2 = 12\) rows x \(3 + 2 = 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 = "abc")
df

# A tibble: 12 × 5
   country  year trade trade_direction   abc
   <chr>   <dbl> <chr> <chr>           <dbl>
 1 Mexico   1980 NAFTA outgoing         738.
 2 Mexico   1980 NAFTA incoming        1211.
 3 USA      1990 NAFTA outgoing        1268.
 4 USA      1990 NAFTA incoming        1224.
 5 France   1980 EU    outgoing        1856.
 6 France   1980 EU    incoming         594.
 7 Mexico   1990 NAFTA outgoing        1768.
 8 Mexico   1990 NAFTA incoming         982.
 9 USA      1980 NAFTA outgoing         696.
10 USA      1980 NAFTA incoming        1078.
11 France   1990 EU    outgoing        1012.
12 France   1990 EU    incoming        2294.

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

animal_weights <- read_csv('_data/animal_weight.csv')
animal_weights

# 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`
# ℹ Use `colnames()` to see all variable names

Observed many animals have designated columns. So i will try pivot_longer to compress them into individual rows. lets see how long the dataset will be.

I’ve used the pivot_longer to convert multiple animal rows to a single column with all the animal names, and all their values are in the ‘values’ column

Code

animal_longer <- animal_weights %>% 
  pivot_longer("Cattle - dairy" : 'Llamas', names_to='All_Animals', values_to='values')

animal_longer

# A tibble: 144 × 3
   `IPCC Area`         All_Animals        values
   <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
# ℹ Use `print(n = ...)` to see more rows

Code

# dim(animal_longer)

To the converted dataset, I want to apply pivot_wider to spread out the country names to multiple columns, with its values. Now I ended up with a 16 * 10 tibble table.

Code

animal_longer %>% pivot_wider(names_from='IPCC Area', values_from='values')

# A tibble: 16 × 10
   All_An…¹ India…² Easte…³ Africa Oceania Weste…⁴ Latin…⁵  Asia Middl…⁶ North…⁷
   <chr>      <dbl>   <dbl>  <dbl>   <dbl>   <dbl>   <dbl> <dbl>   <dbl>   <dbl>
 1 Cattle …   275     550    275     500     600     400   350     275     604  
 2 Cattle …   110     391    173     330     420     305   391     173     389  
 3 Buffalo…   295     380    380     380     380     380   380     380     380  
 4 Swine -…    28      50     28      45      50      28    50      28      46  
 5 Swine -…    28     180     28     180     198      28   180      28     198  
 6 Chicken…     0.9     0.9    0.9     0.9     0.9     0.9   0.9     0.9     0.9
 7 Chicken…     1.8     1.8    1.8     1.8     1.8     1.8   1.8     1.8     1.8
 8 Ducks        2.7     2.7    2.7     2.7     2.7     2.7   2.7     2.7     2.7
 9 Turkeys      6.8     6.8    6.8     6.8     6.8     6.8   6.8     6.8     6.8
10 Sheep       28      48.5   28      48.5    48.5    28    48.5    28      48.5
11 Goats       30      38.5   30      38.5    38.5    30    38.5    30      38.5
12 Horses     238     377    238     377     377     238   377     238     377  
13 Asses      130     130    130     130     130     130   130     130     130  
14 Mules      130     130    130     130     130     130   130     130     130  
15 Camels     217     217    217     217     217     217   217     217     217  
16 Llamas     217     217    217     217     217     217   217     217     217  
# … with abbreviated variable names ¹All_Animals, ²`Indian Subcontinent`,
#   ³`Eastern Europe`, ⁴`Western Europe`, ⁵`Latin America`, ⁶`Middle east`,
#   ⁷`Northern America`

Eggs_tidy.csv

Code

eggs_tidy <- read_csv('_data/eggs_tidy.csv')
eggs_tidy

# A tibble: 120 × 6
   month      year large_half_dozen large_dozen extra_large_half_dozen extra_l…¹
   <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 
 7 July       2004             134.        234.                   137       241 
 8 August     2004             134.        234.                   137       241 
 9 September  2004             130.        234.                   136.      241 
10 October    2004             128.        234.                   136.      241 
# … with 110 more rows, and abbreviated variable name ¹extra_large_dozen
# ℹ Use `print(n = ...)` to see more rows

Code

colnames(eggs_tidy)

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

I want to compress everything from 3rd column to the last in a single column with its values included

Code

eggs_longer <- eggs_tidy %>% 
  pivot_longer('large_half_dozen':'extra_large_dozen', values_to='Values')
eggs_longer

# A tibble: 480 × 4
   month     year name                   Values
   <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
# ℹ Use `print(n = ...)` to see more rows

I want to have individual columns for months, hence using pivot_wider()

Code

eggs_wider_month <- eggs_longer %>% 
  pivot_wider(names_from='month', values_from = 'Values')
eggs_wider

Error in eval(expr, envir, enclos): object 'eggs_wider' not found

pivot_wider() with year

Code

eggs_wider_year <- eggs_longer %>% 
  pivot_wider(names_from='year', values_from='Values')
eggs_wider_year

# A tibble: 48 × 12
   month    name  `2004` `2005` `2006` `2007` `2008` `2009` `2010` `2011` `2012`
   <chr>    <chr>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 January  larg…   126    128.   128.   128.    132   174.   174.   174.   174.
 2 January  larg…   230    234.   234.   234.    237   278.   272.   268.   268.
 3 January  extr…   132    136.   136.   136.    139   186.   186.   186.   186.
 4 January  extr…   230    241    241    242.    245   286.   286.   286.   286.
 5 February larg…   128.   128.   128.   131.    132   174.   174.   174.   174.
 6 February larg…   226.   234.   234.   236.    237   278.   272.   268.   268.
 7 February extr…   134.   136.   136.   138.    139   186.   186.   186.   186.
 8 February extr…   230    241    241    244.    245   286.   286.   286.   288.
 9 March    larg…   131    128.   128.   132     132   174.   174.   174.   174.
10 March    larg…   225    234.   234.   237     237   278.   268    268.   268.
# … with 38 more rows, and 1 more variable: `2013` <dbl>
# ℹ Use `print(n = ...)` to see more rows, and `colnames()` to see all variable names

USA Households

Code

usa_households<-read_excel('_data/USA Households by Total Money Income, Race, and Hispanic Origin of Householder 1967 to 2019.xlsx', skip=5)
usa_households

# A tibble: 382 × 16
   `ALL RACES` ...2   ...3  ...4  ...5  ...6  ...7  ...8  ...9 ...10 ...11 ...12
   <chr>       <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1 2019        1284…   100   9.1   8     8.3  11.7  16.5  12.3  15.5   8.3  10.3
 2 2018        1285…   100  10.1   8.8   8.7  12    17    12.5  15     7.2   8.8
 3 2017 2      1276…   100  10     9.1   9.2  12    16.4  12.4  14.7   7.3   8.9
 4 2017        1275…   100  10.1   9.1   9.2  11.9  16.3  12.6  14.8   7.5   8.5
 5 2016        1262…   100  10.4   9     9.2  12.3  16.7  12.2  15     7.2   8  
 6 2015        1258…   100  10.6  10     9.6  12.1  16.1  12.4  14.9   7.1   7.2
 7 2014        1245…   100  11.4  10.5   9.6  12.6  16.4  12.1  14     6.6   6.8
 8 2013 3      1239…   100  11.4  10.3   9.5  12.5  16.8  12    13.9   6.7   6.9
 9 2013 4      1229…   100  11.3  10.4   9.7  13.1  17    12.5  13.6   6.3   6  
10 2012        1224…   100  11.4  10.6  10.1  12.5  17.4  12    13.9   6.3   5.9
# … with 372 more rows, and 4 more variables: ...13 <dbl>, ...14 <dbl>,
#   ...15 <chr>, ...16 <chr>
# ℹ Use `print(n = ...)` to see more rows, and `colnames()` to see all variable names

Code

ushh_orig <- read_excel("_data/USA Households by Total Money Income, Race, and Hispanic Origin of Householder 1967 to 2019.xlsx",
         skip=5,
         n_max = 352,
         col_names = c("year", "hholds", "del",
                       str_c("income",1:9,sep="_i"),
                       "median_inc", "median_se", 
                       "mean_inc","mean_se")) %>% 
  select(-del)
  ushh_orig

# A tibble: 352 × 15
   year   hholds incom…¹ incom…² incom…³ incom…⁴ incom…⁵ incom…⁶ incom…⁷ incom…⁸
   <chr>  <chr>    <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
 1 ALL R… <NA>      NA      NA      NA      NA      NA      NA      NA      NA  
 2 2019   128451     9.1     8       8.3    11.7    16.5    12.3    15.5     8.3
 3 2018   128579    10.1     8.8     8.7    12      17      12.5    15       7.2
 4 2017 2 127669    10       9.1     9.2    12      16.4    12.4    14.7     7.3
 5 2017   127586    10.1     9.1     9.2    11.9    16.3    12.6    14.8     7.5
 6 2016   126224    10.4     9       9.2    12.3    16.7    12.2    15       7.2
 7 2015   125819    10.6    10       9.6    12.1    16.1    12.4    14.9     7.1
 8 2014   124587    11.4    10.5     9.6    12.6    16.4    12.1    14       6.6
 9 2013 3 123931    11.4    10.3     9.5    12.5    16.8    12      13.9     6.7
10 2013 4 122952    11.3    10.4     9.7    13.1    17      12.5    13.6     6.3
# … with 342 more rows, 5 more variables: income_i9 <dbl>, median_inc <dbl>,
#   median_se <dbl>, mean_inc <chr>, mean_se <chr>, and abbreviated variable
#   names ¹income_i1, ²income_i2, ³income_i3, ⁴income_i4, ⁵income_i5,
#   ⁶income_i6, ⁷income_i7, ⁸income_i8
# ℹ Use `print(n = ...)` to see more rows, and `colnames()` to see all variable names

Still figuring out how to read excel sheets.