Challenge 3 Submission

challenge_3

Tidy Data: Pivoting

Author

Tanmay Agrawal

Published

January 11, 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_weight.csv ⭐
eggs_tidy.csv ⭐⭐ or organicpoultry.xls ⭐⭐⭐
australian_marriage*.xlsx ⭐⭐⭐
USA Households*.xlsx ⭐⭐⭐⭐
sce_labor_chart_data_public.csv 🌟🌟🌟🌟🌟

Code

data <- read_csv("_data/animal_weight.csv")
head(data)

Briefly describe the data

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

Answer: As the name suggests this dataset presents the weights of animals/livestock in a tabular format. The IPCC Area column contains the name of the region and rest of the columns contain the weight values in KGs. The main idea is to have columns be the categories and not the elements of a category. It makes a wide dataset longer and easier to use and read. For e.g.: here we would benefit greatly from pivoting along the IPCC Area column since that column can’t be clubbed with the rest of the columns, whereas all the other columns can be one category that describes one thing which is the type of the animal. We can add two new columns namely animals and weight where the former contains all the other columns (types of animals) and their values could go in the latter. This makes it tidier because now it’s clear that the numbers are weights of the animals.

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

Example

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

#existing rows/cases
nrow(data)

[1] 9

Code

#existing columns/cases
ncol(data)

[1] 17

Code

#expected rows/cases
# we use k=1 since we're only pivoting along the IPPC Area column
nrow(data) * (ncol(data)-1)

[1] 144

Code

# expected cols
1 + 2

[1] 3

Any additional comments?

So expected result should have 144 rows and 3 columns namely IPCC Area, Animal, Weight.

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

Pivoted Example

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

data_longer <- pivot_longer(data, col=-`IPCC Area`, names_to="Animals", values_to="Weight") 

data_longer

Any additional comments?

The dataset is now 144x3 which is exactly what we expected. It’s much cleaner, and more useful since weights are all in one column which makes it much easier to work with. We can sort animals by weight, get descriptive statistics, and have an easier time understanding it.

--- title: "Challenge 3 Submission" author: "Tanmay Agrawal" description: "Tidy Data: Pivoting" date: "01/11/2023" format: html: toc: true code-fold: true code-copy: true code-tools: true df-print: paged categories: - challenge_3 --- ```{r} #| label: setup #| warning: false #| message: false library(tidyverse) knitr::opts_chunk$set(echo = TRUE, warning=FALSE, message=FALSE) ``` ## Challenge Overview Today's challenge is to: 1. read in a data set, and describe the data set using both words and any supporting information (e.g., tables, etc) 2. identify what needs to be done to tidy the current data 3. anticipate the shape of pivoted data 4. 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_weight.csv ⭐ - eggs_tidy.csv ⭐⭐ or organicpoultry.xls ⭐⭐⭐ - australian_marriage\*.xlsx ⭐⭐⭐ - USA Households\*.xlsx ⭐⭐⭐⭐ - sce_labor_chart_data_public.csv 🌟🌟🌟🌟🌟 ```{r} data <- read_csv("_data/animal_weight.csv") head(data) ``` ### Briefly describe the data Describe the data, and be sure to comment on why you are planning to pivot it to make it "tidy" Answer: As the name suggests this dataset presents the weights of animals/livestock in a tabular format. The `IPCC Area` column contains the name of the region and rest of the columns contain the weight values in KGs. The main idea is to have columns be the categories and not the elements of a category. It makes a wide dataset longer and easier to use and read. For e.g.: here we would benefit greatly from pivoting along the `IPCC Area` column since that column can't be clubbed with the rest of the columns, whereas all the other columns can be one category that describes one thing which is the type of the animal. We can add two new columns namely `animals` and `weight` where the former contains all the other columns (types of animals) and their values could go in the latter. This makes it tidier because now it's clear that the numbers are weights of the animals. ## 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. ```{r} #| tbl-cap: Example 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 #existing rows/cases nrow(df) #existing columns/cases ncol(df) #expected rows/cases nrow(df) * (ncol(df)-3) # expected columns 3 + 2 ``` 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. ```{r} #existing rows/cases nrow(data) #existing columns/cases ncol(data) #expected rows/cases # we use k=1 since we're only pivoting along the IPPC Area column nrow(data) * (ncol(data)-1) # expected cols 1 + 2 ``` Any additional comments? So expected result should have 144 rows and 3 columns namely `IPCC Area`, `Animal`, `Weight`. ## 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 ```{r} #| tbl-cap: Pivoted Example df<-pivot_longer(df, col = c(outgoing, incoming), names_to="trade_direction", values_to = "trade_value") df ``` 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? ```{r} data_longer <- pivot_longer(data, col=-`IPCC Area`, names_to="Animals", values_to="Weight") data_longer ``` Any additional comments? The dataset is now 144x3 which is exactly what we expected. It's much cleaner, and more useful since weights are all in one column which makes it much easier to work with. We can sort animals by weight, get descriptive statistics, and have an easier time understanding it.