Code
library(tidyverse)
::opts_chunk$set(echo = TRUE, warning=FALSE, message=FALSE) knitr
Prajakti Kapade
August 17, 2022
Today’s challenge is to:
pivot_longer
Read in one (or more) of the following datasets, using the correct R package and command.
IPCC.Area Cattle...dairy Cattle...non.dairy Buffaloes
1 Indian Subcontinent 275 110 295
2 Eastern Europe 550 391 380
3 Africa 275 173 380
4 Oceania 500 330 380
5 Western Europe 600 420 380
6 Latin America 400 305 380
7 Asia 350 391 380
8 Middle east 275 173 380
9 Northern America 604 389 380
Swine...market Swine...breeding Chicken...Broilers Chicken...Layers Ducks
1 28 28 0.9 1.8 2.7
2 50 180 0.9 1.8 2.7
3 28 28 0.9 1.8 2.7
4 45 180 0.9 1.8 2.7
5 50 198 0.9 1.8 2.7
6 28 28 0.9 1.8 2.7
7 50 180 0.9 1.8 2.7
8 28 28 0.9 1.8 2.7
9 46 198 0.9 1.8 2.7
Turkeys Sheep Goats Horses Asses Mules Camels Llamas
1 6.8 28.0 30.0 238 130 130 217 217
2 6.8 48.5 38.5 377 130 130 217 217
3 6.8 28.0 30.0 238 130 130 217 217
4 6.8 48.5 38.5 377 130 130 217 217
5 6.8 48.5 38.5 377 130 130 217 217
6 6.8 28.0 30.0 238 130 130 217 217
7 6.8 48.5 38.5 377 130 130 217 217
8 6.8 28.0 30.0 238 130 130 217 217
9 6.8 48.5 38.5 377 130 130 217 217
Describe the data, and be sure to comment on why you are planning to pivot it to make it “tidy”
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!
Lets see if this works with a simple example.
# A tibble: 6 × 5
country year trade outgoing incoming
<chr> <dbl> <chr> <dbl> <dbl>
1 Mexico 1980 NAFTA 2272. 1668.
2 USA 1990 NAFTA 724. 844.
3 France 1980 EU 765. 1069.
4 Mexico 1990 NAFTA 866. 1587.
5 USA 1980 NAFTA 952. 922.
6 France 1990 EU 1680. -134.
[1] 6
[1] 5
[1] 12
[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.
Document your work here.
The area is the only variable that is used to decsribe the case, other values are just weights of the animals which need to be pivoted and converted to a single column, so that it can be neat.
IPCC.Area Cattle...dairy Cattle...non.dairy Buffaloes
1 Indian Subcontinent 275 110 295
2 Eastern Europe 550 391 380
3 Africa 275 173 380
4 Oceania 500 330 380
5 Western Europe 600 420 380
6 Latin America 400 305 380
7 Asia 350 391 380
8 Middle east 275 173 380
9 Northern America 604 389 380
Swine...market Swine...breeding Chicken...Broilers Chicken...Layers Ducks
1 28 28 0.9 1.8 2.7
2 50 180 0.9 1.8 2.7
3 28 28 0.9 1.8 2.7
4 45 180 0.9 1.8 2.7
5 50 198 0.9 1.8 2.7
6 28 28 0.9 1.8 2.7
7 50 180 0.9 1.8 2.7
8 28 28 0.9 1.8 2.7
9 46 198 0.9 1.8 2.7
Turkeys Sheep Goats Horses Asses Mules Camels Llamas
1 6.8 28.0 30.0 238 130 130 217 217
2 6.8 48.5 38.5 377 130 130 217 217
3 6.8 28.0 30.0 238 130 130 217 217
4 6.8 48.5 38.5 377 130 130 217 217
5 6.8 48.5 38.5 377 130 130 217 217
6 6.8 28.0 30.0 238 130 130 217 217
7 6.8 48.5 38.5 377 130 130 217 217
8 6.8 28.0 30.0 238 130 130 217 217
9 6.8 48.5 38.5 377 130 130 217 217
[1] 9
[1] 17
[1] 144
Any additional comments?
The table will change into a cleaner table, though there will be very high number of values to be moved.
Now we will pivot the data, and compare our pivoted data dimensions to the dimensions calculated above as a “sanity” check.
# A tibble: 12 × 5
country year trade trade_direction trade_value
<chr> <dbl> <chr> <chr> <dbl>
1 Mexico 1980 NAFTA outgoing 2272.
2 Mexico 1980 NAFTA incoming 1668.
3 USA 1990 NAFTA outgoing 724.
4 USA 1990 NAFTA incoming 844.
5 France 1980 EU outgoing 765.
6 France 1980 EU incoming 1069.
7 Mexico 1990 NAFTA outgoing 866.
8 Mexico 1990 NAFTA incoming 1587.
9 USA 1980 NAFTA outgoing 952.
10 USA 1980 NAFTA incoming 922.
11 France 1990 EU outgoing 1680.
12 France 1990 EU incoming -134.
Yes, once it is pivoted long, our resulting data are \(12x5\) - exactly what we expected!
Document your work here. What will a new “case” be once you have pivoted the data? How does it meet requirements for tidy data?
[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"
# A tibble: 144 × 3
IPCC.Area animal_name weight_value
<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
Any additional comments? The table looks like tidier and has lesser columns but more descriptive of the weight each animal has in every area.
---
title: "Challenge 3 Instructions"
author: "Prajakti Kapade"
desription: "Tidy Data: Pivoting"
date: "08/17/2022"
format:
html:
toc: true
code-fold: true
code-copy: true
code-tools: true
categories:
- challenge_3
- animal_weights
- eggs
- australian_marriage
- usa_households
- sce_labor
---
```{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_weights.csv ⭐
- eggs_tidy.csv ⭐⭐ or organiceggpoultry.xls ⭐⭐⭐
- australian_marriage\*.xls ⭐⭐⭐
- USA Households\*.xlsx ⭐⭐⭐⭐
- sce_labor_chart_data_public.xlsx 🌟🌟🌟🌟🌟
```{r}
data <-read.csv('_data/animal_weight.csv')
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"
## 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.
The area is the only variable that is used to decsribe the case, other values are just weights of the animals which need to be pivoted and converted to a single column, so that it can be neat.
```{r}
data
nrow(data)
ncol(data)
#no of changes to be made i.e. values to be moved
nrow(data) * (ncol(data)-1)
```
Any additional comments?
The table will change into a cleaner table, though there will be very high number of values to be moved.
## 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}
names(data)
data<-pivot_longer(data, col = c("Cattle...dairy","Cattle...non.dairy","Buffaloes","Swine...market","Swine...breeding","Chicken...Broilers","Chicken...Layers","Ducks","Turkeys","Sheep","Goats","Horses","Asses","Mules","Camels","Llamas"),
names_to="animal_name",
values_to = "weight_value")
data
```
Any additional comments?
The table looks like tidier and has lesser columns but more descriptive of the weight each animal has in every area.