library(tidyverse)
library(readxl)
library(stringr)
library(rmarkdown)
library(here)
data_path <- paste(here(), "/_data/", sep="")
knitr::opts_chunk$set(echo = TRUE)
Our next data wrangling will be with the Australian Marriage Dataset. First we will just read in the excel sheet to see what this shows. Sheet 3 has the marriage data that we are interested in. This is hardcoded and can be adjusted later to be more flexible by referencing the name of the sheet instead of the position.
## Format this data this way:
## Case = division + town
## Value = only get the counts of 1. yes 2. no 3. no response 4. not clear
aus_data_path <- paste(data_path, "australian_marriage_law_postal_survey_2017_-_response_final.xls", sep="")
aus_data <- read_excel(aus_data_path, sheet=3)
paged_table(aus_data)
Australian Bureau of Statistics <chr> | |
|---|---|
| 1800.0 Australian Marriage Law Postal Survey, 2017 | |
| Released on 15 November 2017 | |
| Table 2 Response by Federal Electoral Division(a) | |
| NA | |
| NA | |
| NA | |
| New South Wales Divisions | |
| Banks | |
| Barton | |
| Bennelong |
Now that we are able to read in the raw data, we are going to rename the columns to something easier to read and manipulate. We will name the columns that we want to delete “D” plus some number. We can also see that we can skip 6 of the empty rows in the beginning of the data set to make room for these new column names.
aus_data <- read_excel(aus_data_path, sheet=3, skip=6,
col_names = c("Towns", "Yes", "D1",
"No", "D2", "D3", "D4",
"D5", "D6","D11", "Response_Not_Clear",
"D7", "No_Response", "D8", "D9", "D10"))
paged_table(aus_data)
Towns <chr> | Yes <chr> | D1 <chr> | |
|---|---|---|---|
| NA | no. | % | |
| New South Wales Divisions | NA | NA | |
| Banks | 37736 | 44.899999999999999 | |
| Barton | 37153 | 43.600000000000001 | |
| Bennelong | 42943 | 49.799999999999997 | |
| Berowra | 48471 | 54.600000000000001 | |
| Blaxland | 20406 | 26.100000000000001 | |
| Bradfield | 53681 | 60.600000000000001 | |
| Calare | 54091 | 60.200000000000003 | |
| Chifley | 32871 | 41.299999999999997 |
We then check to make sure that our column names align correctly with the correct data. Then we can skip one more row to replace the row that labelled the “no.” and “%” data.
aus_data <- read_excel(aus_data_path, sheet=3, skip=7,
col_names = c("Towns", "Yes", "D1",
"No", "D2", "D3", "D4",
"D5", "D6","D11", "Response_Not_Clear",
"D7", "No_Response", "D8", "D9", "D10"))
paged_table(aus_data)
Towns <chr> | Yes <dbl> | D1 <dbl> | No <dbl> | D2 <dbl> | |
|---|---|---|---|---|---|
| New South Wales Divisions | NA | NA | NA | NA | |
| Banks | 37736 | 44.9 | 46343 | 55.1 | |
| Barton | 37153 | 43.6 | 47984 | 56.4 | |
| Bennelong | 42943 | 49.8 | 43215 | 50.2 | |
| Berowra | 48471 | 54.6 | 40369 | 45.4 | |
| Blaxland | 20406 | 26.1 | 57926 | 73.9 | |
| Bradfield | 53681 | 60.6 | 34927 | 39.4 | |
| Calare | 54091 | 60.2 | 35779 | 39.8 | |
| Chifley | 32871 | 41.3 | 46702 | 58.7 | |
| Cook | 47505 | 55.0 | 38804 | 45.0 |
Now that we have properly labeled the data, we can remove the columns with the data that we will not use. As mentioned before, we labeled these columns as “D” to make it easier to delete. We will also remove any of the blank rows, the total rows, and the notes starting with “(a)” at the end of the dataset.
aus_data <- aus_data %>%
select(!starts_with("d", ignore.case = TRUE)) %>%
filter(!grepl("total", Towns, ignore.case = TRUE)) %>%
filter(grepl("^[A-Z]", Towns, ignore.case = TRUE)) %>%
filter(!is.na(Towns))
paged_table(aus_data)
Towns <chr> | Yes <dbl> | No <dbl> | Response_Not_Clear <dbl> | |
|---|---|---|---|---|
| New South Wales Divisions | NA | NA | NA | |
| Banks | 37736 | 46343 | 247 | |
| Barton | 37153 | 47984 | 226 | |
| Bennelong | 42943 | 43215 | 244 | |
| Berowra | 48471 | 40369 | 212 | |
| Blaxland | 20406 | 57926 | 220 | |
| Bradfield | 53681 | 34927 | 202 | |
| Calare | 54091 | 35779 | 285 | |
| Chifley | 32871 | 46702 | 263 | |
| Cook | 47505 | 38804 | 229 |
Something I learned with using grepl is that the singular * does not work to escape characters. So I had to use a double \* to escape the parentheses in my search for the notes at the bottom of the data set. I am not sure why this is at the moment, but now the dataset has filtered out the subnotes, the total rows, and the columns with the excessive information. Another lesson was that the is.na() takes the name of the columns, not as strings, but as objects.
Now, we should separate the Towns column that contains the divisions and the cities/towns. One approach would be to take any row that contains an NA in “Yes”, “No”, “Response_Not_Clear”, or “No_Response” column. A simpler approach will be to search for anything that contains the word “Divisions” within the Towns column.
aus_data <- aus_data %>%
mutate(Divisions = case_when(str_ends(Towns, "Divisions") ~ Towns, TRUE ~ NA_character_)) %>%
fill(Divisions) %>%
filter(!str_ends(Towns, "Divisions"))
paged_table(aus_data)
Towns <chr> | Yes <dbl> | No <dbl> | Response_Not_Clear <dbl> | No_Response <dbl> | |
|---|---|---|---|---|---|
| Banks | 37736 | 46343 | 247 | 20928 | |
| Barton | 37153 | 47984 | 226 | 24008 | |
| Bennelong | 42943 | 43215 | 244 | 19973 | |
| Berowra | 48471 | 40369 | 212 | 16038 | |
| Blaxland | 20406 | 57926 | 220 | 25883 | |
| Bradfield | 53681 | 34927 | 202 | 17261 | |
| Calare | 54091 | 35779 | 285 | 25342 | |
| Chifley | 32871 | 46702 | 263 | 28180 | |
| Cook | 47505 | 38804 | 229 | 18713 | |
| Cowper | 57493 | 38317 | 315 | 25197 |
Now we need to pivot the types of responses into one variable. A case is really defined by the type of response from Town and Division and the count of those responses.
aus_data <- aus_data %>%
pivot_longer(c(`Yes`, `No`, `Response_Not_Clear`, `No_Response`), names_to = "Response", values_to = "Count")
paged_table(aus_data)
Towns <chr> | Divisions <chr> | |
|---|---|---|
| Banks | New South Wales Divisions | |
| Banks | New South Wales Divisions | |
| Banks | New South Wales Divisions | |
| Banks | New South Wales Divisions | |
| Barton | New South Wales Divisions | |
| Barton | New South Wales Divisions | |
| Barton | New South Wales Divisions | |
| Barton | New South Wales Divisions | |
| Bennelong | New South Wales Divisions | |
| Bennelong | New South Wales Divisions |
Text and figures are licensed under Creative Commons Attribution CC BY-NC 4.0. The figures that have been reused from other sources don't fall under this license and can be recognized by a note in their caption: "Figure from ...".
For attribution, please cite this work as
Beach (2021, Oct. 4). DACSS 601 Fall 2021: HW 3 Continued with the Australian Marriage Dataset. Retrieved from https://mrolfe.github.io/DACSS601Fall21/posts/2021-10-04-hw3-continued-allyson-beach/
BibTeX citation
@misc{beach2021hw,
author = {Beach, Allyson},
title = {DACSS 601 Fall 2021: HW 3 Continued with the Australian Marriage Dataset},
url = {https://mrolfe.github.io/DACSS601Fall21/posts/2021-10-04-hw3-continued-allyson-beach/},
year = {2021}
}