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(ggplot2)
library(dplyr)
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

rawData <- read_excel("_data/USA Households by Total Money Income, Race, and Hispanic Origin of Householder 1967 to 2019.xlsx", 
    skip = 2)
View(rawData)

#Trimming top and bottom

trimmedData <- rawData %>%
                slice(3:354)

#renaming cols
columnNames <- c('Year', 'Count','totDistSum','Percentdist_under15','Percentdist15_25','Percentdist25_35','Percentdist35_50','Percentdist50_75','Percentdist75_100','Percentdist100_150','Percentdist150_200', 'Percentdist200andover','MedianIncome_est', 'MedianIncome_MOE', 'MeanIncome_est', 'MeanIncome_MOE')
colnames(trimmedData)<- columnNames





mutatedData <- trimmedData %>% 
                  mutate(Race =NA, .before =1 )


# hate that i am doing this...

mutatedData$Race[1+1] <- "ALL RACES"
mutatedData$Race[57+1] <- "WHITE ALONE"
mutatedData$Race[78+1] <- "WHITE"
mutatedData$Race[114+1] <- "WHITE ALONE, NOT HISPANIC"
mutatedData$Race[135+1] <- "WHITE, NOT HISPANIC"
mutatedData$Race[166+1] <- "BLACK ALONE OR IN COMBINATION"
mutatedData$Race[187+1] <- "BLACK ALONE"
mutatedData$Race[208+1] <- "BLACK"
mutatedData$Race[244+1] <- "ASIAN ALONE OR IN COMBINATION"
mutatedData$Race[265+1] <- "ASIAN ALONE"
mutatedData$Race[286+1] <- "ASIAN AND PACIFIC ISLANDER"
mutatedData$Race[302+1] <- "HISPANIC (ANY RACE)"
mutatedData$Race[265+1] <- "HISPANIC (ANY RACE)"



mutatedData <- mutatedData %>%
  filter(!is.na(Count)) %>%
  fill(Race)


# now cleaning values

mutatedData[mutatedData == 'N']<- NA


mutatedData <- mutatedData %>% 
  mutate(across(Count:MeanIncome_MOE, as.double))



mutatedData <- mutatedData %>%
  mutate(Year =substr(Year,1,4))

round(mutatedData$Percentdist15_25 ,2)

  [1]  8.0  8.8  9.1  9.1  9.0 10.0 10.5 10.3 10.4 10.6 10.2 10.7 10.0 10.0 10.0
 [16]  9.5  9.8  9.7  9.7  9.8  9.6  9.3  9.7  9.8 10.3 10.5 10.4 10.8 10.6 10.6
 [31] 10.3 10.1 10.0  9.7 10.0 10.0 10.6 10.8 10.9 10.8 10.9 10.7 10.2 10.5 11.2
 [46] 11.3 11.4 10.7 10.8 10.3 10.2 10.1  9.9 10.1 10.2  7.5  8.3  8.6  8.7  8.6
 [61]  9.5 10.1 10.0  9.9 10.2  9.6 10.4  9.6  9.7  9.6  9.1  9.3  9.4  9.3  9.4
 [76]  9.4  9.0  9.3  9.3  9.8 10.1 10.0 10.3 10.1 10.1  9.9  9.7  9.6  9.0  9.5
 [91]  9.5 10.0 10.2 10.3 10.2 10.2 10.1  9.5  9.9 10.5 10.5 10.8 10.0 10.0  9.6
[106]  9.5  9.4  9.3  9.3  9.4  7.3  7.8  8.3  8.5  8.2  9.1  9.5  9.4  9.5  9.8
[121]  9.3 10.0  9.0  9.2  9.2  8.8  9.0  9.1  9.0  9.1  9.0  8.7  8.9  8.8  9.4
[136]  9.6  9.5 10.0  9.7  9.8  9.6  9.2  9.4  8.7  9.1  9.2  9.6  9.9 10.0  9.9
[151] 10.0  9.9  9.4  9.8 10.3 10.3 10.6  9.8  9.9  9.3 11.5 12.6 12.5 12.1 12.3
[166] 13.5 13.9 13.3 14.4 14.2 14.0 13.6 13.5 12.9 13.1 12.9 13.7 12.1 12.8 13.0
[181] 11.5 12.6 12.6 12.2 12.4 13.6 14.0 13.5 14.5 14.3 14.0 13.5 13.6 13.0 13.1
[196] 12.9 13.7 12.1 12.8 13.0 12.3 11.9 12.8 13.5 13.6 14.3 14.0 14.0 14.4 14.1
[211] 13.3 13.9 13.4 14.2 14.2 14.2 15.2 16.1 15.9 15.6 16.0 16.3 15.8 15.4 17.5
[226] 17.6 16.9 16.4 16.9 16.5 16.2 15.8 16.1 17.0 17.7  5.1  6.3  6.4  6.3  6.1
[241]  6.5  6.5  7.2  6.2  6.3  8.1  7.7  6.4  6.9  6.6  6.0  6.6  6.6  7.4  6.6
[256]  5.0  6.2  6.4  6.4  6.0  6.4  6.5  7.3  6.4  6.4  8.0  7.6  6.4  6.9  6.7
[271]  6.1  6.9  6.6  7.4  6.6  6.5  6.0  7.0  7.5  7.9  7.4  8.5  8.9  8.6  8.1
[286]  7.4  7.5  7.9  8.7  9.0  8.8 10.9 10.4 10.3 10.8 11.9 13.2 13.4 12.7 13.0
[301] 12.1 12.8 12.9 13.0 12.3 11.5 12.0 12.0 12.0 11.8 12.6 11.9 13.4 13.7 13.9
[316] 15.1 14.6 14.4 14.8 14.0 13.8 15.0 12.4 12.4 14.0 14.3 15.7 14.3 14.8 15.2
[331] 13.8 14.0 12.9 13.3 14.8 15.3 14.7 15.4 14.2 16.0

Code

#final tidy DF
mutatedData

# A tibble: 340 × 17
   Race     Year   Count totDi…¹ Perce…² Perce…³ Perce…⁴ Perce…⁵ Perce…⁶ Perce…⁷
   <chr>    <chr>  <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
 1 ALL RAC… 2019  128451     100     9.1     8       8.3    11.7    16.5    12.3
 2 ALL RAC… 2018  128579     100    10.1     8.8     8.7    12      17      12.5
 3 ALL RAC… 2017  127669     100    10       9.1     9.2    12      16.4    12.4
 4 ALL RAC… 2017  127586     100    10.1     9.1     9.2    11.9    16.3    12.6
 5 ALL RAC… 2016  126224     100    10.4     9       9.2    12.3    16.7    12.2
 6 ALL RAC… 2015  125819     100    10.6    10       9.6    12.1    16.1    12.4
 7 ALL RAC… 2014  124587     100    11.4    10.5     9.6    12.6    16.4    12.1
 8 ALL RAC… 2013  123931     100    11.4    10.3     9.5    12.5    16.8    12  
 9 ALL RAC… 2013  122952     100    11.3    10.4     9.7    13.1    17      12.5
10 ALL RAC… 2012  122459     100    11.4    10.6    10.1    12.5    17.4    12  
# … with 330 more rows, 7 more variables: Percentdist100_150 <dbl>,
#   Percentdist150_200 <dbl>, Percentdist200andover <dbl>,
#   MedianIncome_est <dbl>, MedianIncome_MOE <dbl>, MeanIncome_est <dbl>,
#   MeanIncome_MOE <dbl>, and abbreviated variable names ¹totDistSum,
#   ²Percentdist_under15, ³Percentdist15_25, ⁴Percentdist25_35,
#   ⁵Percentdist35_50, ⁶Percentdist50_75, ⁷Percentdist75_100

Code

# trying to copy over year column to a new column 'Race" conditionally based on the Count column
  # I then planned to fill the race column NAs with previous values


# trimmedData %>%
#   filter(is.na(Count))
# 
# 
# 
# mutatedData <- trimmedData%>%
#                   mutate(Race = 
#                            case_when(Count == NA ~ trimmedData$Year[1]), .before =1)
# 
# mutatedData <- trimmedData%>%
#                   mutate(Race = 
#                            case_when(Count == NA ~ 1), .before =1)
# 
# mutatedData

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 contains information on income, grouped by race for American households from as early as 1967 to 2019.

My plan to make this data “tidy” is to rename all the columns so that they can be represented in one header. I will then add a race column which will contain the race for that particular observation. Year will also have its own column.

Anticipate the End Result

Code

# i dont include the percent distribution column
race <- c("value1","value2","value3")
year <- c("value1","value2","value3")
Count <- c("value1","value2","value3")
Percentdist_under15 <- c("value1","value2","value3")
Percentdist15_25 <- c("value1","value2","value3")
Percentdist25_35 <- c("value1","value2","value3")
Percentdist35_50 <- c("value1","value2","value3")
Percentdist50_75 <- c("value1","value2","value3")
Percentdist75_100 <- c("value1","value2","value3")
Percentdist100_150 <- c("value1","value2","value3")
Percentdist150_200 <- c("value1","value2","value3")
Percentdist200andover <- c("value1","value2","value3")

MedianIncome_est <- c("value1","value2","value3")
MedianIncome_MOE <- c("value1","value2","value3")

MeanIncome_est <- c("value1","value2","value3")
MeanIncome_MOE <- c("value1","value2","value3")

sampleDF <- data.frame(race,year,Count,Percentdist_under15,Percentdist15_25,Percentdist25_35,Percentdist35_50,Percentdist50_75,Percentdist75_100,Percentdist100_150,Percentdist150_200,
                       Percentdist200andover,MedianIncome_est,MedianIncome_MOE,MeanIncome_est,MeanIncome_MOE)



sampleDF

    race   year  Count Percentdist_under15 Percentdist15_25 Percentdist25_35
1 value1 value1 value1              value1           value1           value1
2 value2 value2 value2              value2           value2           value2
3 value3 value3 value3              value3           value3           value3
  Percentdist35_50 Percentdist50_75 Percentdist75_100 Percentdist100_150
1           value1           value1            value1             value1
2           value2           value2            value2             value2
3           value3           value3            value3             value3
  Percentdist150_200 Percentdist200andover MedianIncome_est MedianIncome_MOE
1             value1                value1           value1           value1
2             value2                value2           value2           value2
3             value3                value3           value3           value3
  MeanIncome_est MeanIncome_MOE
1         value1         value1
2         value2         value2
3         value3         value3

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    1091.     845.
2 USA      1990 NAFTA    1446.    1164.
3 France   1980 EU        377.     841.
4 Mexico   1990 NAFTA    1466.     699.
5 USA      1980 NAFTA     530.     644.
6 France   1990 EU       1152.     714.

Code

#updated with my expected values

#existing rows/cases
nrow(rawData)

[1] 385

Code

#existing columns/cases
ncol(rawData)

[1] 16

Code

#expected rows/cases
nrow(rawData)-11 * (ncol(rawData))+1

[1] 210

Code

# expected columns 
(ncol(rawData))+1

[1] 17

Challenge: Describe the final dimensions

Document your work here.

Code

sampleDF

    race   year  Count Percentdist_under15 Percentdist15_25 Percentdist25_35
1 value1 value1 value1              value1           value1           value1
2 value2 value2 value2              value2           value2           value2
3 value3 value3 value3              value3           value3           value3
  Percentdist35_50 Percentdist50_75 Percentdist75_100 Percentdist100_150
1           value1           value1            value1             value1
2           value2           value2            value2             value2
3           value3           value3            value3             value3
  Percentdist150_200 Percentdist200andover MedianIncome_est MedianIncome_MOE
1             value1                value1           value1           value1
2             value2                value2           value2           value2
3             value3                value3           value3           value3
  MeanIncome_est MeanIncome_MOE
1         value1         value1
2         value2         value2
3         value3         value3

Any additional comments?

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?

If you wanted to see a side by side of the mean and median income estimates and MOE you could pivot your data as follows.

Code

mutatedData <- mutatedData %>% pivot_longer(c(MedianIncome_est,MedianIncome_MOE), names_to = "Median",values_to = "Values_med")

mutatedData <- mutatedData %>% pivot_longer(c(MeanIncome_est,MeanIncome_MOE), names_to = "Mean",values_to = "Values_mean")

mutatedData

# A tibble: 1,360 × 17
   Race     Year   Count totDi…¹ Perce…² Perce…³ Perce…⁴ Perce…⁵ Perce…⁶ Perce…⁷
   <chr>    <chr>  <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
 1 ALL RAC… 2019  128451     100     9.1     8       8.3    11.7    16.5    12.3
 2 ALL RAC… 2019  128451     100     9.1     8       8.3    11.7    16.5    12.3
 3 ALL RAC… 2019  128451     100     9.1     8       8.3    11.7    16.5    12.3
 4 ALL RAC… 2019  128451     100     9.1     8       8.3    11.7    16.5    12.3
 5 ALL RAC… 2018  128579     100    10.1     8.8     8.7    12      17      12.5
 6 ALL RAC… 2018  128579     100    10.1     8.8     8.7    12      17      12.5
 7 ALL RAC… 2018  128579     100    10.1     8.8     8.7    12      17      12.5
 8 ALL RAC… 2018  128579     100    10.1     8.8     8.7    12      17      12.5
 9 ALL RAC… 2017  127669     100    10       9.1     9.2    12      16.4    12.4
10 ALL RAC… 2017  127669     100    10       9.1     9.2    12      16.4    12.4
# … with 1,350 more rows, 7 more variables: Percentdist100_150 <dbl>,
#   Percentdist150_200 <dbl>, Percentdist200andover <dbl>, Median <chr>,
#   Values_med <dbl>, Mean <chr>, Values_mean <dbl>, and abbreviated variable
#   names ¹totDistSum, ²Percentdist_under15, ³Percentdist15_25,
#   ⁴Percentdist25_35, ⁵Percentdist35_50, ⁶Percentdist50_75, ⁷Percentdist75_100