How many rows and columns does each dataset contain?
Step 2: Explore the datasets
Use glimpse() to examine the structure of both datasets.
Display the column names of each dataset using names().
Identify the variable that represents the state in each dataset.
Are the state variable names identical in both datasets?
Identify the variables that are common to both datasets.
Why is it important to inspect the datasets before joining them?
3. Prepare the state names
The murders dataset has 51 observations, including Washington, D.C. The second dataset contains 52 rows, so not every row necessarily represents a state.
Use left_join() to join the murders dataset with the state-level dataset.
Use the state variable as the joining key.
Store the joined dataset in a new object called combined_data.
Display the first six rows of combined_data.
How many rows does the joined dataset contain?
Export the final dataset
Use write_csv() to export combined_data as a CSV file named combined_murders.csv
Step 5: Investigate unmatched states
The murders dataset contains 51 observations, while the second dataset contains 52 rows. Why might the number of rows differ?
Use anti_join() to identify the states in murders that do not have a matching state in the second dataset.
STEP-1
# Loading required librarylibrary(tidyverse)library(dslabs)library(readr)# Loading data murdersdata(murders)# Importing the url and storing as usa_states_2014usa_states_2014 <-read_csv("https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv")# Showing first 6 rows of both data-set.head(murders)
state abb region population total
1 Alabama AL South 4779736 135
2 Alaska AK West 710231 19
3 Arizona AZ West 6392017 232
4 Arkansas AR South 2915918 93
5 California CA West 37253956 1257
6 Colorado CO West 5029196 65
head(usa_states_2014)
# A tibble: 6 × 4
Rank State Postal Population
<dbl> <chr> <chr> <dbl>
1 1 Alabama AL 4849377
2 2 Alaska AK 736732
3 3 Arizona AZ 6731484
4 4 Arkansas AR 2966369
5 5 California CA 38802500
6 6 Colorado CO 5355866
# Displaying the column names of each dataset.names(murders)
[1] "state" "abb" "region" "population" "total"
names(usa_states_2014)
[1] "Rank" "State" "Postal" "Population"
# Both of the data-set has state but usa_states_2014 has the upper-cased state. So, making everything lower-cased.names(usa_states_2014) <-tolower(names(usa_states_2014))head(usa_states_2014)
# A tibble: 6 × 4
rank state postal population
<dbl> <chr> <chr> <dbl>
1 1 Alabama AL 4849377
2 2 Alaska AK 736732
3 3 Arizona AZ 6731484
4 4 Arkansas AR 2966369
5 5 California CA 38802500
6 6 Colorado CO 5355866
#head(murders)# state and population are common in both data-sets# It is important to inspect the datasets before joining them because as above, the variables could be lower cased and upper cased respectively in each data-set which creates error while joining. It could also have different names eg, state could be named as us_state in other data-set, which if we want to join is not going to work.
STEP-3
# Lower casing every state in both data-sets to avoid problems.murders <- murders |>mutate(state =tolower(state))usa_states_2014 <- usa_states_2014 |>mutate(state =tolower(state))
STEP-4
# Joining both the data-set using "left_join" function by state.combined_data <-left_join(murders, usa_states_2014, by ="state")# Viewing some observations of the combined data-set.head(combined_data)
state abb region population.x total rank postal population.y
1 alabama AL South 4779736 135 1 AL 4849377
2 alaska AK West 710231 19 2 AK 736732
3 arizona AZ West 6392017 232 3 AZ 6731484
4 arkansas AR South 2915918 93 4 AR 2966369
5 california CA West 37253956 1257 5 CA 38802500
6 colorado CO West 5029196 65 6 CO 5355866
# The joined data-set has 51 rows.# Exporting the final data-set.write_csv(combined_data, "combined_data.csv")
STEP-5
# It differs because there is no particular observation as "puerto rico" in the "murders" data-set.# Checking which state in murders left out.anti_join(murders, usa_states_2014, by ="state")
[1] state abb region population total
<0 rows> (or 0-length row.names)
anti_join(usa_states_2014, murders, by ="state")
# A tibble: 1 × 4
rank state postal population
<dbl> <chr> <chr> <dbl>
1 40 puerto rico PR 3548397
# While using "anti_join" in "usa_states_2014" data-set, the result shows "puerto rico" is not mentioned in 'murders' data-set. However, when using the function in "murders" data-set, the result shows zero(0).