Activity

Coding Activity

Step 1: Load the packages and data

Load the tidyverse and dslabs packages.
Load the murders dataset from the dslabs package.
Import the state-level CSV dataset directly from the following URL using read_csv():

https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv
Display the first six rows of each dataset

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(dslabs)
data(murders)

read.csv("https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv")

   Rank                State Postal Population
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
7     7          Connecticut     CT    3596677
8     8             Delaware     DE     935614
9     9 District of Columbia     DC     658893
10   10              Florida     FL   19893297
11   11              Georgia     GA   10097343
12   12               Hawaii     HI    1419561
13   13                Idaho     ID    1634464
14   14             Illinois     IL   12880580
15   15              Indiana     IN    6596855
16   16                 Iowa     IA    3107126
17   17               Kansas     KS    2904021
18   18             Kentucky     KY    4413457
19   19            Louisiana     LA    4649676
20   20                Maine     ME    1330089
21   21             Maryland     MD    5976407
22   22        Massachusetts     MA    6745408
23   23             Michigan     MI    9909877
24   24            Minnesota     MN    5457173
25   25          Mississippi     MS    2994079
26   26             Missouri     MO    6063589
27   27              Montana     MT    1023579
28   28             Nebraska     NE    1881503
29   29               Nevada     NV    2839098
30   30        New Hampshire     NH    1326813
31   31           New Jersey     NJ    8938175
32   32           New Mexico     NM    2085572
33   33             New York     NY   19746227
34   34       North Carolina     NC    9943964
35   35         North Dakota     ND     739482
36   36                 Ohio     OH   11594163
37   37             Oklahoma     OK    3878051
38   38               Oregon     OR    3970239
39   39         Pennsylvania     PA   12787209
40   40          Puerto Rico     PR    3548397
41   41         Rhode Island     RI    1055173
42   42       South Carolina     SC    4832482
43   43         South Dakota     SD     853175
44   44            Tennessee     TN    6549352
45   45                Texas     TX   26956958
46   46                 Utah     UT    2942902
47   47              Vermont     VT     626562
48   48             Virginia     VA    8326289
49   49           Washington     WA    7061530
50   50        West Virginia     WV    1850326
51   51            Wisconsin     WI    5757564
52   52              Wyoming     WY     584153

usa_states<-read.csv("https://raw.githubusercontent.com/plotly/datasets/master/2014_usa_states.csv")

head(usa_states)

  Rank      State Postal Population
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

data(murders)
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

#5. How many rows and columns does each data set contain?

The usa_states data set has 4 Columns and 52 Rows, and the murders data set has 5 Columns and 51 Rows
Step 2: Explore the data sets

Use glimpse() to examine the structure of both data sets.

glimpse(murders)

Rows: 51
Columns: 5
$ state      <chr> "Alabama", "Alaska", "Arizona", "Arkansas", "California", "…
$ abb        <chr> "AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "DC", "FL",…
$ region     <fct> South, West, West, South, West, West, Northeast, South, Sou…
$ population <dbl> 4779736, 710231, 6392017, 2915918, 37253956, 5029196, 35740…
$ total      <dbl> 135, 19, 232, 93, 1257, 65, 97, 38, 99, 669, 376, 7, 12, 36…

glimpse(usa_states)

Rows: 52
Columns: 4
$ Rank       <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, …
$ State      <chr> "Alabama", "Alaska", "Arizona", "Arkansas", "California", "…
$ Postal     <chr> "AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", "DC", "FL",…
$ Population <dbl> 4849377, 736732, 6731484, 2966369, 38802500, 5355866, 35966…

Display the column names of each dataset using names().

names(murders)

[1] "state"      "abb"        "region"     "population" "total"

names(usa_states)

[1] "Rank"       "State"      "Postal"     "Population"

Identify the variable that represents the state in each data set.
Are the state variable names identical in both data sets?

No they are different the murders data set has “state” with the s in lowercase and the usa_states data set has “State” with the first s in upper case.
Identify the variables that are common to both data sets.

The variables that are common in both data sets are STATE and POPULATION
Why is it important to inspect the data sets before joining them?

Because we need to know which variables we will use or if we need something specific of that data set.

3. Prepare the state names

The murders data set has 51 observations, including Washington, D.C. The second data set contains 52 rows, so not every row necessarily represents a state.

murders <- murders |>
  mutate(state = tolower(state))

scores <- ________________________

murders <- murders |>
  mutate(state= tolower(state))
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
7           connecticut  CT     Northeast    3574097    97
8              delaware  DE         South     897934    38
9  district of columbia  DC         South     601723    99
10              florida  FL         South   19687653   669
11              georgia  GA         South    9920000   376
12               hawaii  HI          West    1360301     7
13                idaho  ID          West    1567582    12
14             illinois  IL North Central   12830632   364
15              indiana  IN North Central    6483802   142
16                 iowa  IA North Central    3046355    21
17               kansas  KS North Central    2853118    63
18             kentucky  KY         South    4339367   116
19            louisiana  LA         South    4533372   351
20                maine  ME     Northeast    1328361    11
21             maryland  MD         South    5773552   293
22        massachusetts  MA     Northeast    6547629   118
23             michigan  MI North Central    9883640   413
24            minnesota  MN North Central    5303925    53
25          mississippi  MS         South    2967297   120
26             missouri  MO North Central    5988927   321
27              montana  MT          West     989415    12
28             nebraska  NE North Central    1826341    32
29               nevada  NV          West    2700551    84
30        new hampshire  NH     Northeast    1316470     5
31           new jersey  NJ     Northeast    8791894   246
32           new mexico  NM          West    2059179    67
33             new york  NY     Northeast   19378102   517
34       north carolina  NC         South    9535483   286
35         north dakota  ND North Central     672591     4
36                 ohio  OH North Central   11536504   310
37             oklahoma  OK         South    3751351   111
38               oregon  OR          West    3831074    36
39         pennsylvania  PA     Northeast   12702379   457
40         rhode island  RI     Northeast    1052567    16
41       south carolina  SC         South    4625364   207
42         south dakota  SD North Central     814180     8
43            tennessee  TN         South    6346105   219
44                texas  TX         South   25145561   805
45                 utah  UT          West    2763885    22
46              vermont  VT     Northeast     625741     2
47             virginia  VA         South    8001024   250
48           washington  WA          West    6724540    93
49        west virginia  WV         South    1852994    27
50            wisconsin  WI North Central    5686986    97
51              wyoming  WY          West     563626     5

state_level <- usa_states |>
  mutate(state = tolower(State))
state_level

   Rank                State Postal Population                state
1     1              Alabama     AL    4849377              alabama
2     2               Alaska     AK     736732               alaska
3     3              Arizona     AZ    6731484              arizona
4     4             Arkansas     AR    2966369             arkansas
5     5           California     CA   38802500           california
6     6             Colorado     CO    5355866             colorado
7     7          Connecticut     CT    3596677          connecticut
8     8             Delaware     DE     935614             delaware
9     9 District of Columbia     DC     658893 district of columbia
10   10              Florida     FL   19893297              florida
11   11              Georgia     GA   10097343              georgia
12   12               Hawaii     HI    1419561               hawaii
13   13                Idaho     ID    1634464                idaho
14   14             Illinois     IL   12880580             illinois
15   15              Indiana     IN    6596855              indiana
16   16                 Iowa     IA    3107126                 iowa
17   17               Kansas     KS    2904021               kansas
18   18             Kentucky     KY    4413457             kentucky
19   19            Louisiana     LA    4649676            louisiana
20   20                Maine     ME    1330089                maine
21   21             Maryland     MD    5976407             maryland
22   22        Massachusetts     MA    6745408        massachusetts
23   23             Michigan     MI    9909877             michigan
24   24            Minnesota     MN    5457173            minnesota
25   25          Mississippi     MS    2994079          mississippi
26   26             Missouri     MO    6063589             missouri
27   27              Montana     MT    1023579              montana
28   28             Nebraska     NE    1881503             nebraska
29   29               Nevada     NV    2839098               nevada
30   30        New Hampshire     NH    1326813        new hampshire
31   31           New Jersey     NJ    8938175           new jersey
32   32           New Mexico     NM    2085572           new mexico
33   33             New York     NY   19746227             new york
34   34       North Carolina     NC    9943964       north carolina
35   35         North Dakota     ND     739482         north dakota
36   36                 Ohio     OH   11594163                 ohio
37   37             Oklahoma     OK    3878051             oklahoma
38   38               Oregon     OR    3970239               oregon
39   39         Pennsylvania     PA   12787209         pennsylvania
40   40          Puerto Rico     PR    3548397          puerto rico
41   41         Rhode Island     RI    1055173         rhode island
42   42       South Carolina     SC    4832482       south carolina
43   43         South Dakota     SD     853175         south dakota
44   44            Tennessee     TN    6549352            tennessee
45   45                Texas     TX   26956958                texas
46   46                 Utah     UT    2942902                 utah
47   47              Vermont     VT     626562              vermont
48   48             Virginia     VA    8326289             virginia
49   49           Washington     WA    7061530           washington
50   50        West Virginia     WV    1850326        west virginia
51   51            Wisconsin     WI    5757564            wisconsin
52   52              Wyoming     WY     584153              wyoming

Step 4: Join the datasets

Use left_join() to join the murders dataset with the state-level dataset.

combined_data <- left_join(x=murders,y=state_level,by="state")
combined_data

                  state abb        region population total Rank
1               alabama  AL         South    4779736   135    1
2                alaska  AK          West     710231    19    2
3               arizona  AZ          West    6392017   232    3
4              arkansas  AR         South    2915918    93    4
5            california  CA          West   37253956  1257    5
6              colorado  CO          West    5029196    65    6
7           connecticut  CT     Northeast    3574097    97    7
8              delaware  DE         South     897934    38    8
9  district of columbia  DC         South     601723    99    9
10              florida  FL         South   19687653   669   10
11              georgia  GA         South    9920000   376   11
12               hawaii  HI          West    1360301     7   12
13                idaho  ID          West    1567582    12   13
14             illinois  IL North Central   12830632   364   14
15              indiana  IN North Central    6483802   142   15
16                 iowa  IA North Central    3046355    21   16
17               kansas  KS North Central    2853118    63   17
18             kentucky  KY         South    4339367   116   18
19            louisiana  LA         South    4533372   351   19
20                maine  ME     Northeast    1328361    11   20
21             maryland  MD         South    5773552   293   21
22        massachusetts  MA     Northeast    6547629   118   22
23             michigan  MI North Central    9883640   413   23
24            minnesota  MN North Central    5303925    53   24
25          mississippi  MS         South    2967297   120   25
26             missouri  MO North Central    5988927   321   26
27              montana  MT          West     989415    12   27
28             nebraska  NE North Central    1826341    32   28
29               nevada  NV          West    2700551    84   29
30        new hampshire  NH     Northeast    1316470     5   30
31           new jersey  NJ     Northeast    8791894   246   31
32           new mexico  NM          West    2059179    67   32
33             new york  NY     Northeast   19378102   517   33
34       north carolina  NC         South    9535483   286   34
35         north dakota  ND North Central     672591     4   35
36                 ohio  OH North Central   11536504   310   36
37             oklahoma  OK         South    3751351   111   37
38               oregon  OR          West    3831074    36   38
39         pennsylvania  PA     Northeast   12702379   457   39
40         rhode island  RI     Northeast    1052567    16   41
41       south carolina  SC         South    4625364   207   42
42         south dakota  SD North Central     814180     8   43
43            tennessee  TN         South    6346105   219   44
44                texas  TX         South   25145561   805   45
45                 utah  UT          West    2763885    22   46
46              vermont  VT     Northeast     625741     2   47
47             virginia  VA         South    8001024   250   48
48           washington  WA          West    6724540    93   49
49        west virginia  WV         South    1852994    27   50
50            wisconsin  WI North Central    5686986    97   51
51              wyoming  WY          West     563626     5   52
                  State Postal Population
1               Alabama     AL    4849377
2                Alaska     AK     736732
3               Arizona     AZ    6731484
4              Arkansas     AR    2966369
5            California     CA   38802500
6              Colorado     CO    5355866
7           Connecticut     CT    3596677
8              Delaware     DE     935614
9  District of Columbia     DC     658893
10              Florida     FL   19893297
11              Georgia     GA   10097343
12               Hawaii     HI    1419561
13                Idaho     ID    1634464
14             Illinois     IL   12880580
15              Indiana     IN    6596855
16                 Iowa     IA    3107126
17               Kansas     KS    2904021
18             Kentucky     KY    4413457
19            Louisiana     LA    4649676
20                Maine     ME    1330089
21             Maryland     MD    5976407
22        Massachusetts     MA    6745408
23             Michigan     MI    9909877
24            Minnesota     MN    5457173
25          Mississippi     MS    2994079
26             Missouri     MO    6063589
27              Montana     MT    1023579
28             Nebraska     NE    1881503
29               Nevada     NV    2839098
30        New Hampshire     NH    1326813
31           New Jersey     NJ    8938175
32           New Mexico     NM    2085572
33             New York     NY   19746227
34       North Carolina     NC    9943964
35         North Dakota     ND     739482
36                 Ohio     OH   11594163
37             Oklahoma     OK    3878051
38               Oregon     OR    3970239
39         Pennsylvania     PA   12787209
40         Rhode Island     RI    1055173
41       South Carolina     SC    4832482
42         South Dakota     SD     853175
43            Tennessee     TN    6549352
44                Texas     TX   26956958
45                 Utah     UT    2942902
46              Vermont     VT     626562
47             Virginia     VA    8326289
48           Washington     WA    7061530
49        West Virginia     WV    1850326
50            Wisconsin     WI    5757564
51              Wyoming     WY     584153

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.

head(combined_data)

       state abb region population total Rank      State Postal Population
1    alabama  AL  South    4779736   135    1    Alabama     AL    4849377
2     alaska  AK   West     710231    19    2     Alaska     AK     736732
3    arizona  AZ   West    6392017   232    3    Arizona     AZ    6731484
4   arkansas  AR  South    2915918    93    4   Arkansas     AR    2966369
5 california  CA   West   37253956  1257    5 California     CA   38802500
6   colorado  CO   West    5029196    65    6   Colorado     CO    5355866

How many rows does the joined data set contain?

It has 51 rows and 9 columns

Export the final dataset
Use write_csv() to export combined_data as a CSV file named combined_murders.csv
```
write.csv(combined_data, file = "combinded_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?

Because there are one extra state in the state_level data set, that are not in murders data set

Use anti_join() to identify the states in murders that do not have a matching state in the second dataset.

not_combined <- anti_join(x=state_level,y=murders,by="state")
not_combined

  Rank       State Postal Population       state
1   40 Puerto Rico     PR    3548397 puerto rico

Coding Activity

Step 1: Load the packages and data

Step 2: Explore the data sets

3. Prepare the state names

Step 4: Join the datasets

Export the final dataset

Step 5: Investigate unmatched states