Week 9: Apply it to your data 8

Import your data

data <- read_excel("../00_data/Datasimpler.xlsx")
data

## # A tibble: 45 × 4
##    States      Colony_Loss Colony_Added `Colony_Gain/Loss`
##    <chr>             <dbl>        <dbl>              <dbl>
##  1 Alabama            2700         2800                100
##  2 Arizona            4200         8500               4300
##  3 Arkansas           1600         9000               7400
##  4 California        64000       200000             136000
##  5 Colorado           5500         4800               -700
##  6 Connecticut          60          960                900
##  7 Florida           33000        52000              19000
##  8 Georgia           20000        25000               5000
##  9 Hawaii              630          460               -170
## 10 Idaho             19500        40000              20500
## # … with 35 more rows

Chapter 13

What are primary keys in your data?

Primary keys in my column are States

Can you divide your data into two?

Divide it using dplyr::select in a way the two have a common variable, which you could use to join the two.

data_1 <- data %>% select(States, Colony_Loss, Colony_Added)
data_1

## # A tibble: 45 × 3
##    States      Colony_Loss Colony_Added
##    <chr>             <dbl>        <dbl>
##  1 Alabama            2700         2800
##  2 Arizona            4200         8500
##  3 Arkansas           1600         9000
##  4 California        64000       200000
##  5 Colorado           5500         4800
##  6 Connecticut          60          960
##  7 Florida           33000        52000
##  8 Georgia           20000        25000
##  9 Hawaii              630          460
## 10 Idaho             19500        40000
## # … with 35 more rows

data_2 <- data %>% select(States, `Colony_Gain/Loss`)

data_1 %>%
    left_join(data_2)

## Joining with `by = join_by(States)`

## # A tibble: 45 × 4
##    States      Colony_Loss Colony_Added `Colony_Gain/Loss`
##    <chr>             <dbl>        <dbl>              <dbl>
##  1 Alabama            2700         2800                100
##  2 Arizona            4200         8500               4300
##  3 Arkansas           1600         9000               7400
##  4 California        64000       200000             136000
##  5 Colorado           5500         4800               -700
##  6 Connecticut          60          960                900
##  7 Florida           33000        52000              19000
##  8 Georgia           20000        25000               5000
##  9 Hawaii              630          460               -170
## 10 Idaho             19500        40000              20500
## # … with 35 more rows

Can you join the two together?

Use tidyr::left_join or other joining functions.

data_1 %>%
    left_join(data_2)

## Joining with `by = join_by(States)`

## # A tibble: 45 × 4
##    States      Colony_Loss Colony_Added `Colony_Gain/Loss`
##    <chr>             <dbl>        <dbl>              <dbl>
##  1 Alabama            2700         2800                100
##  2 Arizona            4200         8500               4300
##  3 Arkansas           1600         9000               7400
##  4 California        64000       200000             136000
##  5 Colorado           5500         4800               -700
##  6 Connecticut          60          960                900
##  7 Florida           33000        52000              19000
##  8 Georgia           20000        25000               5000
##  9 Hawaii              630          460               -170
## 10 Idaho             19500        40000              20500
## # … with 35 more rows