Data Cleaning in R

With the Tidyverse

Josh Allen

Department of Political Science at Georgia State University

8/29/22

Research Data Services

Our Team

Also, we do not have the capacity to provide pre-scheduled frequent software tutoring that is divorced from a specific assignment or software troubleshooting task at hand – in other words, we are also not generalized software tutors.

Our one-on-one software assistance is available to help you troubleshoot specific tasks or assignments. If you are seeking generalized software help, we direct you to our live workshops and our recorded workshops to gain the foundational skills for using various analytical software. Then, when or if the time comes that you have targeted software questions or specific issues to tackle related to a course assignment or research project, please feel free to contact us for one-on-one support.

Get Ready Badges

https://research.library.gsu.edu/dataservices/data-ready

These are awesome to share on social media i.e. linkedin which is a good signal to potential employers that you know this stuff

How To Get the Badges

In our last workshop we covered

• Assignment

• Indexing

• Generating Descriptive Statistics

• Some data cleaning(subsetting, generating new variables)

• A Bit of Graphing

Today we will cover:

• The Tidyverse (minus ggplot2)

Cleaning Data in the Tidyverse

Like families, tidy datasets are all alike but every messy dataset is messy in its own way.

Hadley Wickham

Unless we are truly lucky and the data gods bless us with the perfect dataset that fits our needs we will need to do things to our data to make it suitable for our use. We often need to generate treatment indicators, produce subsets of our data add data from another dataset. Each dataset is unique which prompted this famous quote from the creator of the tidyverse.

I have a also included another set of slides that cover data.table. Which is a really awesome package.

Packages You Will Need

install.packages("tidyverse")
library(tidyverse)
starwars <- read_csv("starwars.csv")
penguins <- read_csv("penguins.csv")

What is in the Tidyverse?

tidyverse::tidyverse_packages()

 [1] "broom"         "cli"           "crayon"        "dbplyr"       
 [5] "dplyr"         "dtplyr"        "forcats"       "ggplot2"      
 [9] "googledrive"   "googlesheets4" "haven"         "hms"          
[13] "httr"          "jsonlite"      "lubridate"     "magrittr"     
[17] "modelr"        "pillar"        "purrr"         "readr"        
[21] "readxl"        "reprex"        "rlang"         "rstudioapi"   
[25] "rvest"         "stringr"       "tibble"        "tidyr"        
[29] "xml2"          "tidyverse"    

These are the packages that are considered a part of the tidyerse. These offer a whole host of awesome capabilities. These all work off of the same philosophy so once you get the hang of one the rest will make more sense.

Some of these are core tidyverse things others you have to load in seperetly like haven lubridate and broom.

What is loaded?

library(tidyverse)

── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2 ──
✔ ggplot2 3.4.0     ✔ purrr   1.0.1
✔ tibble  3.1.8     ✔ dplyr   1.1.0
✔ tidyr   1.3.0     ✔ stringr 1.5.0
✔ readr   2.1.3     ✔ forcats 1.0.0
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

The tidyverse is pretty verbose because it loads a lot of things and in the case of lag and filter actually makes its lag and filter function the one that R uses.

dplyr: is a grammar of data manipulation

tidyr: this helps make tidy data

forcats: helps us work with factors

stringr: helps us work with strings

purr: is basically just there to help you with iterating operations

tibble: adds an additional class to R called a tibble that creates a special tibble thing

readr: reads in data however if you get data in some non-standard format than you need to use haven which will let you read in stata dta files, sas data files and SPSS .sav files.

Namespace Conflicts

• Since R is open source you can name your functions just about anything

  • this results in lots of packages having similarly named functions or the same name

• Dplyr is just warning us that if we use filter or lag it will use dplyr’s version of the function

• Whenever R runs into a namespace conflict it will default to the last package that was loaded

• That is why it is generally best practice to load the most important package last

• You can also use packagename::function you want to use to get around it.

  • This is called a namespace call
  • explicitly tells R which function you are using

The actual dependency count for the tidyverse it self is pretty large so this is something you might run into and is kind of controversial. This is something to be aware of because dependencies can really be annoying to deal with.

Dplyr

Extract rows with filter()
Extract columns with select()
Arrange/sort rows with arrange()
Make new columns with mutate()
Make group summaries with group_by() %>% summarize()

Dplyr tries to be really human readable so we refer to each function as verbs. walk through table

note we will talk about the %>% for now do not worry about it

filter() allows you to keep rows based on the values of the columns1. The first argument is the data frame. The second and subsequent arguments are the conditions that must be true to keep the row.

select() allows you to rapidly zoom in on a useful subset using operations based on the names of the variables. select(). Our datasets today are pretty small

arrange() changes the order of the rows based on the value of the columns. It takes a data frame and a set of column names (or more complicated expressions) to order by

The job of mutate() is to add new columns that are calculated from the existing columns

group_by() doesn’t change the data but, if you look closely at the output, you’ll notice that it’s now “grouped by” month. This means subsequent operations will now work “by month”

How A Dplyr Verb Works

verb(.data, ...)

• All dplyr stuff works along these lines

• verb is one of the Dplyr verbs i.e select

• .data is the dataset you want to manipulate

  • this is true for the rest of the tidyverse

• ... is just a set of things that the verb does.

Important

There have been some significant additions to dplyr 1.1.0. This workshop was written under dplyr 1.0.10

Select()

 select(.data = penguins, species)

species
Adelie
Adelie
Adelie
Adelie
Adelie
Adelie
...

You can give select a range of columns with firstcolumn:thirdcolumn or omit stuff using -columnwedontwant

I will only partially cover select because it is a pretty intuitive verb. Just give it the name of our columns. But see that the first argument we feed our verb is data and in this case the thing that verb does is just select the column name we want.

Throughout the presentation you can copy and paste the code and run it yourself. You are probably going to get some different stuff than me. Thats because behind the scene you will see lots and lots of select calls just make sure everything fits on the screen! If you want to recreate the output your self all you would do is feed it the column names on the screen!

filter()

Subsetting by Rows

filter()

  filter(.data = penguins,
         species == "Adelie",
         body_mass_g < 6200,
         bill_length_mm < 59.6)

species	body_mass_g	bill_length_mm
Adelie	3750	39.1
Adelie	3800	39.5
Adelie	3250	40.3
Adelie	3450	36.7
Adelie	3650	39.3
Adelie	3625	38.9
…	…	…

The basics of filter are that you need to feed it a set of tests. Most often what you use filter for is to subset data based on a series of tests that make sense for your data. In this case I just took the quantiles for both variables. You can string together a set of tests in filter.

Notice that we can pass different operations with a comma. We could also plausibly use our & to only return a dataset as long as we have data that meets those tests

What Kind of Tests Can I Do?

Test	Meaning	Test	Meaning
x < y	Less than	x %in% y	In (group membership)
x > y	Greater than	is.na(x)	Is missing
==	Equal to	!is.na(x)	Is not missing
x <= y	Less than or equal to
x >= y	Greater than or equal to
x != y	Not equal to

The Default

filter(starwars, homeworld == "Naboo",
                 homeworld == "Coruscant")

name	height	homeworld

Filter is going to default to an and test. That is basically what the columns are telling it

These Do The Same Thing

filter(starwars, homeworld == "Naboo",
                 mass < 84.5)

filter(starwars, homeworld == "Naboo" &
                 mass < 84.5)

name	mass	homeworld
R2-D2	32	Naboo
Palpatine	75	Naboo

name	mass	homeworld
R2-D2	32	Naboo
Palpatine	75	Naboo

Getting Multiple Things From the Same Column

• To get a subset of homeworld we chain together multiple | tests together

filter(starwars, homeworld == "Naboo" |
                 homeworld == "Coruscant" |
                 homeworld == "Tatooine")

name	homeworld
Luke Skywalker	Tatooine
C-3PO	Tatooine
R2-D2	Naboo
Darth Vader	Tatooine
Owen Lars	Tatooine
Beru Whitesun lars	Tatooine
R5-D4	Tatooine
...	...

In this case we are just using lots of or tests to achieve what we want this is because filter defaults to using &

Getting Multiple Things From the Same Column(cont)

 filter(starwars,
  homeworld %in% c("Naboo",
                  "Tatooine",
                  "Coruscant"))

name	homeworld
Luke Skywalker	Tatooine
C-3PO	Tatooine
R2-D2	Naboo
Darth Vader	Tatooine
Owen Lars	Tatooine
Beru Whitesun lars	Tatooine
R5-D4	Tatooine
Biggs Darklighter	Tatooine
Anakin Skywalker	Tatooine
Palpatine	Naboo
Finis Valorum	Coruscant
...	...

Using lists instead of big long conditional statements will honestly make your life so much easier. Where you use set membership in filter is in place of lots of or tests. So in this case we are feeding

filter() mistakes we all make

filter(penguins, species = "Gentoo")

Error in `filter()`:
! We detected a named input.
ℹ This usually means that you've used `=` instead of `==`.
ℹ Did you mean `species == "Gentoo"`?

filter(starwars, homeworld == "Nabo")

name	height	mass	hair_color	skin_color	eye_color	birth_year	sex	gender	homeworld	species	films	vehicles	starships

We are likely to encounter some sets of errors. Luckily Dplyr has finally built in a friendly error message with when your tests are misspecified. The equals one is one that we all make. Which will just now throw an error.

What your are likely to encounter is that you have done a test and your object is now blank. It will return your something back to you but it is blank. All this means is that something is wrong in one of your tests. R will evaluate the test. If something meets the test than it will just return those results

In this case nothing in our data is named Nabo with one o so it is just returning the results of the test back to us.

Your Turn

• Try removing the missing values from bill_length_mm hint: use ! and is.na.

• Return a data dataset that only has data for the Dream island

• Using either the starwars or penguins data use %in% to get a set of homeworlds or islands

• Bonus: return a set of islands or homeworlds not in that set

• Subset the penguin data where body mass is less than 4202 and not on the Dream Island.

03:00

Mutate

Mutate

 mutate(penguins,
   log_bill_length = round(log(bill_length_mm)
    , digits = 2))

species	bill_length_mm	log_bill_length
Adelie	39.1	3.67
Adelie	39.5	3.68
Adelie	40.3	3.7
Adelie	36.7	3.6
Adelie	39.3	3.67
Adelie	38.9	3.66
...	...	...

Mutate

mutate(penguins,
      bill_length_mm = bill_length_mm / bill_depth_mm) 

bill_length_mm	bill_depth_mm
2.09090909090909	18.7
2.27011494252874	17.4
2.23888888888889	18
NA	NA
1.90155440414508	19.3
...	...

We can also just take the existing columns and create a variable. So in this case we can just make a column that divides things or multiplies or square roots or whatever to your column to create something that we need!

Logical Tests

• There are various ways to do this in mutate but they all follow the same logic

ifelse(test,
       Value_if_True,
       Value_if_False)

• Test is a logical test species == "Chinstrap", species == "Wookie", etc

• Value_if_True what does it do if the test returns true

• Value_if_FALSE what does it do if the test returns false

Mutate

mutate(penguins,
  big_penguin = ifelse(
  body_mass_g >= 4750,  # median(body_mass_g) aslo works
   TRUE,
   FALSE))

body_mass_g	big_penguin
3750	FALSE
3800	FALSE
3250	FALSE
NA	NA
3450	FALSE
3650	FALSE
3625	FALSE
4675	FALSE
3475	FALSE
4250	FALSE

You can put just about anything in this true false column generally thought true or false or a 1 or a zero are standard. All the stuff that we saw with the filter tests can work in there too! So if you need a variable requires multiple tests than you can do that as well.

You can also other functions in there as well

Mutate()

• Mutate is order aware so you don’t have to use a new mutate for each new variable you want to create

  mutate(penguins, long_bill = bill_length_mm * 2,
         long_bill_logical =
           ifelse(long_bill >= 100
                  & long_bill <= 119.20,
                  TRUE,
                  FALSE)) 

bill_length_mm	long_bill	long_bill_logical	species
46.1	92.2	FALSE	NA
50	100	TRUE	NA
48.7	97.4	FALSE	NA
50	100	TRUE	NA
47.6	95.2	FALSE	NA
46.5	93	FALSE	NA
...	...	...	...

Your Turn

• Write code to

• Add a column in your dataset that is TRUE if a penguin is an Adelie penguin

• Add a column in the starwars dataset that says Naboo or Tatooine, and Not Naboo or Tatooine if the character is not from there

• Add a column in your dataset that squares the body mass (hint: use ^)

03:00

Doing More Than One Thing at a Time

Data Cleaning

• Often requires lots of intermediary steps

  • replacing missing values
  • subsetting data
  • creating new variables

• We would ideally like to do this without assigning a new object each time

• If things start going it may be difficult to spot where it went wrong

• Enter dplyr and the pipe

Remember How A Dplyr Verb Works

verb(.data, ...)

• All dplyr stuff works along these lines

• verb is one of the Dplyr verbs

• .data is the dataset you want to manipulate

  • this is true for the rest of the tidyverse

• ... is just a set of things that the verb does.

Side By Side

• Filter
• Select
• Mutate

filter(.data = penguins, species == "Gentoo")

# A tibble: 124 × 8
   species island bill_length_mm bill_depth_mm flipper_len…¹ body_…² sex    year
   <fct>   <fct>           <dbl>         <dbl>         <int>   <int> <fct> <int>
 1 Gentoo  Biscoe           46.1          13.2           211    4500 fema…  2007
 2 Gentoo  Biscoe           50            16.3           230    5700 male   2007
 3 Gentoo  Biscoe           48.7          14.1           210    4450 fema…  2007
 4 Gentoo  Biscoe           50            15.2           218    5700 male   2007
 5 Gentoo  Biscoe           47.6          14.5           215    5400 male   2007
 6 Gentoo  Biscoe           46.5          13.5           210    4550 fema…  2007
 7 Gentoo  Biscoe           45.4          14.6           211    4800 fema…  2007
 8 Gentoo  Biscoe           46.7          15.3           219    5200 male   2007
 9 Gentoo  Biscoe           43.3          13.4           209    4400 fema…  2007
10 Gentoo  Biscoe           46.8          15.4           215    5150 male   2007
# … with 114 more rows, and abbreviated variable names ¹​flipper_length_mm,
#   ²​body_mass_g

select(.data = penguins, species:bill_length_mm)

# A tibble: 344 × 3
   species island    bill_length_mm
   <fct>   <fct>              <dbl>
 1 Adelie  Torgersen           39.1
 2 Adelie  Torgersen           39.5
 3 Adelie  Torgersen           40.3
 4 Adelie  Torgersen           NA  
 5 Adelie  Torgersen           36.7
 6 Adelie  Torgersen           39.3
 7 Adelie  Torgersen           38.9
 8 Adelie  Torgersen           39.2
 9 Adelie  Torgersen           34.1
10 Adelie  Torgersen           42  
# … with 334 more rows

mutate(.data = penguins, bill_length_mm_sq = bill_length_mm^2)

# A tibble: 344 × 9
   species island    bill_length_mm bill_d…¹ flipp…² body_…³ sex    year bill_…⁴
   <fct>   <fct>              <dbl>    <dbl>   <int>   <int> <fct> <int>   <dbl>
 1 Adelie  Torgersen           39.1     18.7     181    3750 male   2007   1529.
 2 Adelie  Torgersen           39.5     17.4     186    3800 fema…  2007   1560.
 3 Adelie  Torgersen           40.3     18       195    3250 fema…  2007   1624.
 4 Adelie  Torgersen           NA       NA        NA      NA <NA>   2007     NA 
 5 Adelie  Torgersen           36.7     19.3     193    3450 fema…  2007   1347.
 6 Adelie  Torgersen           39.3     20.6     190    3650 male   2007   1544.
 7 Adelie  Torgersen           38.9     17.8     181    3625 fema…  2007   1513.
 8 Adelie  Torgersen           39.2     19.6     195    4675 male   2007   1537.
 9 Adelie  Torgersen           34.1     18.1     193    3475 <NA>   2007   1163.
10 Adelie  Torgersen           42       20.2     190    4250 <NA>   2007   1764 
# … with 334 more rows, and abbreviated variable names ¹​bill_depth_mm,
#   ²​flipper_length_mm, ³​body_mass_g, ⁴​bill_length_mm_sq

Piping

• A pipe takes whats on the left hand side of the pipe and evaluates it as the first argument on the right hand side

• this leverages the common syntax effectively

  • this is not limited to just dplyr stuff

• If you look behind the curtain of the workshop slides you will see pipes everywhere!

Rather than starting with nested parenthesis we will start with piping. because it affords us a ton of advantages

Magrittr Piping

• The tidyverse has its own pipe %>% and used to be the only game in town.
  • %>% is just % followed by > followed by %
• You can see the advantages of the pipe when we need to do multiple things to a dataset

## These do the same thing
 filter(mutate(penguins,
  female = ifelse(sex == "female",
    TRUE, FALSE)),
     species == "Adelie")

# these do the same thing
penguins %>%  
filter(species == "Adelie") %>% 
mutate(female = ifelse(sex == "female", TRUE, FALSE))

This example an adaptation provided by Grant McDermot

Magrittr Piping(cont)

• When you use the pipe it is easier to think of the pipe as saying and then

I %>%
    wake_up(time = "8.00am") %>%
    get_out_of_bed(side = "correct") %>%
    get_dressed(pants = "TRUE", shirt = "TRUE") %>% 
    leave_house(car = TRUE, bike = FALSE, MARTA = FALSE) %>%
    am_late(traffic = TRUE)

The first thing in our pipe sequence is the object we are manipulating. In this case the object is me

Example derived from Andrew Heiss

Base R Pipe

• The pipe caught on and the team behind R added a native pipe |>

  • this is just | followed by >

• If you are have a version of R that is 4.2.0>= it should come with the native pipe

• The base versus magrittr pipe differ slightly and it is worth knowing some of the differences

• The base R pipe is pretty flexible and supports some cool computer sciency stuff for more check out this page

  • I tend to use the base R pipe
  • The differences between the two pipes are outlined in this post

If you are on a mac the keyboard shortcut is command shift m

Group_by() and Summarize()

group_by()

• group_by() simply puts rows into groups based on values of a column

• The grouped data frames will continue until you do ungroup

• Not necessarily the most useful function because nothing really happens when called by itself

• Unless you combine it with summarize()

  • Note: you will see summarise as well in people’s code because the creator and maintainer is from New Zealand

penguins |> 
  group_by(species) |>
  select(bill_length_mm, island) |> 
  head(5)

# A tibble: 5 × 3
# Groups:   species [1]
  species bill_length_mm island   
  <fct>            <dbl> <fct>    
1 Adelie            39.1 Torgersen
2 Adelie            39.5 Torgersen
3 Adelie            40.3 Torgersen
4 Adelie            NA   Torgersen
5 Adelie            36.7 Torgersen

Find the Number of Each Species

penguins |> 
  group_by(species) |> 
  summarize(n())

species	n()
Adelie	152
Chinstrap	68
Gentoo	124

Multiple Summary Statistics

penguins |> 
  group_by(species) |> 
  summarise(n(),
        mean_bill_length = mean(bill_length_mm,
                                na.rm = TRUE))

species	mean_bill_length	n()
Adelie	38.79	152
Chinstrap	48.83	68
Gentoo	47.5	124

Recent Features

• You can now add temporary groupings

penguins |> 
  summarise(n(),
            mean(bill_length_mm, na.rm = TRUE),
         .by = species)

species	mean_bill_length	n()
Adelie	38.79	152
Gentoo	47.5	124
Chinstrap	48.83	68

As with other dplyr stuff we can chain together multiple operations with a comma. So instead of doing multiple one of these we can just add them all in one summarise call. Thankfully we can also give our variables more informative names kind of like how we use mutate.

Your Turn

• Calculate the minimum, maximum, and median body_mass_g for each species of penguin

• What happens if you remove group_by()?

• Calculate the number of distinct penguin species per island

  • hint: type n_

03:00

Other useful dplyr stuff

Joins

• Often times we need to get data from another dataset

• In Dplyr we use join operations

  • What each of the below joins are doing are elaborated more in R for Data Science

• inner_join(df1, df2)

• left_join(df1, df2)

• right_join(df1, df2)

• full_join(df1, df2)

• semi_join(df1, df2)

• anti_join(df1, df2)

Joins

• The basic syntax for each join is the same _join(df1, df2, by = "var I want to join on)

• The by argument can take a list of variables or you can just let dplyr guess(bad idea)

• Each join does something different and some are more cautious than others

• I tend to use left_join the most and is handy when you are trying to fill in gaps in panel data

data1 = data.frame(ID = 1:2,                      ## Create first example data frame
                    X1 = c("a1", "a2"),
                    stringsAsFactors = FALSE)

ID	X1
1	a1
2	a2

data2 = data.frame(ID = 2:3,                      ## Create second example data frame
                    X2 = c("b1", "b2"),
                    stringsAsFactors = FALSE)

ID	X2
2	b1
3	b2

left_join()

left_join(data1, data2, join_by(ID))

ID	X1	X2
1	a1	NA
2	a2	b1

Using “Real” Data

national_data

state	year	unemployment	inflation	population
GA	2018	5.0	2.0	100
GA	2019	5.3	1.8	200
GA	2020	5.2	2.5	300
NC	2018	6.1	1.8	350
NC	2019	5.9	1.6	375
NC	2020	5.3	1.8	400
CO	2018	4.7	2.7	200
CO	2019	4.4	2.6	300
CO	2020	5.1	2.5	400

national_libraries

state	year	libraries	schools
CO	2018	230	470
CO	2019	240	440
CO	2020	270	510
NC	2018	200	610
NC	2019	210	590
NC	2020	220	530

Joins(cont)

national_combined = left_join(national_data, national_libraries, 
                                    join_by(state, year)) 

national_combined

state	year	unemployment	inflation	population	libraries	schools
GA	2018	5.0	2.0	100	NA	NA
GA	2019	5.3	1.8	200	NA	NA
GA	2020	5.2	2.5	300	NA	NA
NC	2018	6.1	1.8	350	200	610
NC	2019	5.9	1.6	375	210	590
NC	2020	5.3	1.8	400	220	530
CO	2018	4.7	2.7	200	230	470
CO	2019	4.4	2.6	300	240	440
CO	2020	5.1	2.5	400	270	510

Combined Data

national_combined = national_data |> 
  left_join(national_libraries, join_by(state, year))

national_combined

state	year	unemployment	inflation	population	libraries	schools
GA	2018	5.0	2.0	100	NA	NA
GA	2019	5.3	1.8	200	NA	NA
GA	2020	5.2	2.5	300	NA	NA
NC	2018	6.1	1.8	350	200	610
NC	2019	5.9	1.6	375	210	590
NC	2020	5.3	1.8	400	220	530
CO	2018	4.7	2.7	200	230	470
CO	2019	4.4	2.6	300	240	440
CO	2020	5.1	2.5	400	270	510

What if our Columns Have Different Names?

state	year	unemployment	inflation	population
GA	2018	5.0	2.0	100
GA	2019	5.3	1.8	200
GA	2020	5.2	2.5	300
NC	2018	6.1	1.8	350
NC	2019	5.9	1.6	375
NC	2020	5.3	1.8	400
CO	2018	4.7	2.7	200
CO	2019	4.4	2.6	300
CO	2020	5.1	2.5	400

statename	year	libraries	schools
CO	2018	230	470
CO	2019	240	440
CO	2020	270	510
NC	2018	200	610
NC	2019	210	590
NC	2020	220	530

Renaming Columns

• Renaming stuff in dplyr is easy

• we use the same syntax as dplyr::rename()

• rename(newvarname = oldvarname)

national_data |> 
  left_join(national_libraries, join_by(state == statename, year))

state	year	unemployment	inflation	population	libraries	schools
GA	2018	5.0	2.0	100	NA	NA
GA	2019	5.3	1.8	200	NA	NA
GA	2020	5.2	2.5	300	NA	NA
NC	2018	6.1	1.8	350	200	610
NC	2019	5.9	1.6	375	210	590

Tidyr

Reshaping Data

Each variable must have its own column.

Each observation must have its own row.

Each value must have its own cell.

What does this look like in practice?

religion	<$10k	$10-20k	$20-30k	$30-40k	$40-50k	$50-75k	$75-100k	$100-150k	>150k	Don't know/refused
Agnostic	27	34	60	81	76	137	122	109	84	96
Atheist	12	27	37	52	35	70	73	59	74	76
Buddhist	27	21	30	34	33	58	62	39	53	54
Catholic	418	617	732	670	638	1116	949	792	633	1489
Don’t know/refused	15	14	15	11	10	35	21	17	18	116
Evangelical Prot	575	869	1064	982	881	1486	949	723	414	1529
Hindu	1	9	7	9	11	34	47	48	54	37
Historically Black Prot	228	244	236	238	197	223	131	81	78	339
Jehovah's Witness	20	27	24	24	21	30	15	11	6	37
Jewish	19	19	25	25	30	95	69	87	151	162

Lets take a look at some wide data here we are looking at billboard top hits. I have truncated it a bit to fit on the slide but weeks goes to week 47. Instead of having each week be an individual column we want it to be in a tidy format where week is one column and the values from all those columns are in the week column we create

Making Data Longer

relig_income |> 
  pivot_longer(!religion, names_to = "income", values_to = "count" )

religion	income	count
Agnostic	<$10k	27
Agnostic	$10-20k	34
Agnostic	$20-30k	60
Agnostic	$30-40k	81
Agnostic	$40-50k	76
Agnostic	$50-75k	137
Agnostic	$75-100k	122
Agnostic	$100-150k	109
Agnostic	>150k	84
Agnostic	Don't know/refused	96

Separate

• Sometimes we need one variable to be two variables
• Enter Separate

library(lubridate) # for working with dates 
athlete_data =   tibble(forename = c("Lewis", "Tom", "Michael", "Joshua"),
                      surname = c("Hamilton", "Brady", "Jordan", "Allen"),
                      dob = ymd(c("1985-01-07", "1977-08-03","1963-02-17", "1996-05-21")))
athlete_data |>
separate(dob, c("year", "month", "day"))

# A tibble: 4 × 5
  forename surname  year  month day  
  <chr>    <chr>    <chr> <chr> <chr>
1 Lewis    Hamilton 1985  01    07   
2 Tom      Brady    1977  08    03   
3 Michael  Jordan   1963  02    17   
4 Joshua   Allen    1996  05    21   

Unite

• Other times we need to combine multiple columns to be one column
• enter unite

athlete_data |>
unite(name, c("forename", "surname"), sep = " ")

# A tibble: 4 × 2
  name           dob       
  <chr>          <date>    
1 Lewis Hamilton 1985-01-07
2 Tom Brady      1977-08-03
3 Michael Jordan 1963-02-17
4 Joshua Allen   1996-05-21

Getting up and Running Using helpers

Helpful Helpers

• Dplyr comes with really useful functions that help you when there are common patterns in your variable names

• the syntax usually goes

• select(contains("pattern"))

• select(starts_with("pattern"))

• select(ends_with("pattern"))

  select(starwars, name,
   ends_with("color"),  -eye_color)

name	hair_color	skin_color
Luke Skywalker	blond	fair
C-3PO	NA	gold
R2-D2	NA	white, blue
Darth Vader	none	white
...	...	...

Another Helper Example

penguins |>
select(starts_with("bill"))

bill_length_mm	bill_depth_mm
39.1	18.7
39.5	17.4
40.3	18.0
NA	NA
36.7	19.3

filter() with regular expressions

• regular expressions also work and can be pretty handy.

# base R regex
filter(starwars, grepl("Sky", name)|  # base r version
           grepl("Palp", name) |
           grepl("Obi", name))
# tidyverse regex
filter(starwars, str_detect(name, "Sky") | 
         str_detect(name, "Palp") |
        str_detect(name, "Obi"))

name	height	mass	homeworld
Luke Skywalker	172	77	Tatooine
Obi-Wan Kenobi	182	77	Stewjon
Anakin Skywalker	188	84	Tatooine
Palpatine	170	75	Naboo
Shmi Skywalker	163	NA	Tatooine
...	...	...	...

I will say that regular expressions are notoriously difficult to work with. I choose the simplest one to use. Here we are feeding filter a really simple regular expression

Mutate

Regular ifelse

mutate(penguins,
  big_penguin = ifelse(
  body_mass_g >= 4750,
   "Dats a big penguin",
   "SMOL penguin"))

# A tibble: 6 × 2
  body_mass_g big_penguin 
        <int> <chr>       
1        3750 SMOL penguin
2        3800 SMOL penguin
3        3250 SMOL penguin
4          NA <NA>        
5        3450 SMOL penguin
6        3650 SMOL penguin

Fancy ifelse

jedi = c("Luke Skywalker",
 "Yoda", "Obi-Wan Kenobi",
  "Rey", 
  "Mace Windu")

sith = c("Palpatine",
 "Darth Maul",
  "Dooku",
   "Darth Vader")

hero_villains <- filter(starwars, name %in% jedi |
 name %in% sith)  
 
mutate(hero_villains,
  what_are_they = case_when(
  name %in% jedi ~ "Hero",
  name %in% sith ~ "Evil Dooer")) 

# A tibble: 5 × 2
  name           what_are_they
  <chr>          <chr>        
1 Luke Skywalker Hero         
2 Darth Vader    Evil Dooer   
3 Obi-Wan Kenobi Hero         
4 Yoda           Hero         
5 Palpatine      Evil Dooer   

Generating Summary statistics

penguins |> 
select(-year) |>
group_by(species) |>
summarise(across(where(is.numeric),
 c(Mean = mean, Min = min, Max = max), na.rm = TRUE),
  .names = "{.cols}_{.fn}")

# A tibble: 3 × 14
  species   bill_lengt…¹ bill_…² bill_…³ bill_…⁴ bill_…⁵ bill_…⁶ flipp…⁷ flipp…⁸
  <fct>            <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <int>
1 Adelie            38.8    32.1    46      18.3    15.5    21.5    190.     172
2 Chinstrap         48.8    40.9    58      18.4    16.4    20.8    196.     178
3 Gentoo            47.5    40.9    59.6    15.0    13.1    17.3    217.     203
# … with 5 more variables: flipper_length_mm_Max <int>, body_mass_g_Mean <dbl>,
#   body_mass_g_Min <int>, body_mass_g_Max <int>, .names <chr>, and abbreviated
#   variable names ¹​bill_length_mm_Mean, ²​bill_length_mm_Min,
#   ³​bill_length_mm_Max, ⁴​bill_depth_mm_Mean, ⁵​bill_depth_mm_Min,
#   ⁶​bill_depth_mm_Max, ⁷​flipper_length_mm_Mean, ⁸​flipper_length_mm_Min

Pivoting With LOTS of columns

data("billboard")

artist	track	date.entered	wk1	wk2	wk3	wk4	wk5
2 Pac	Baby Don't Cry (Keep...	2000-02-26	87	82	72	77	87
2Ge+her	The Hardest Part Of ...	2000-09-02	91	87	92	NA	NA
3 Doors Down	Kryptonite	2000-04-08	81	70	68	67	66
3 Doors Down	Loser	2000-10-21	76	76	72	69	67
504 Boyz	Wobble Wobble	2000-04-15	57	34	25	17	17
98^0	Give Me Just One Nig...	2000-08-19	51	39	34	26	26
A*Teens	Dancing Queen	2000-07-08	97	97	96	95	100
Aaliyah	I Don't Wanna	2000-01-29	84	62	51	41	38
Aaliyah	Try Again	2000-03-18	59	53	38	28	21
Adams, Yolanda	Open My Heart	2000-08-26	76	76	74	69	68
Adkins, Trace	More	2000-04-29	84	84	75	73	73
Aguilera, Christina	Come On Over Baby (A...	2000-08-05	57	47	45	29	23
Aguilera, Christina	I Turn To You	2000-04-15	50	39	30	28	21
Aguilera, Christina	What A Girl Wants	1999-11-27	71	51	28	18	13
Alice Deejay	Better Off Alone	2000-04-08	79	65	53	48	45
Allan, Gary	Smoke Rings In The D...	2000-01-22	80	78	76	77	92
Amber	Sexual	1999-07-17	99	99	96	96	100
Anastacia	I'm Outta Love	2000-04-01	92	NA	NA	95	NA
Anthony, Marc	My Baby You	2000-09-16	82	76	76	70	82
Anthony, Marc	You Sang To Me	2000-02-26	77	54	50	43	30
Avant	My First Love	2000-11-04	70	62	56	43	39
Avant	Separated	2000-04-29	62	32	30	23	26
BBMak	Back Here	2000-04-29	99	86	60	52	38
Backstreet Boys, The	Shape Of My Heart	2000-10-14	39	25	24	15	12
Backstreet Boys, The	Show Me The Meaning ...	2000-01-01	74	62	55	25	16
Backstreet Boys, The	The One	2000-05-27	58	50	43	37	31
Badu, Erkyah	Bag Lady	2000-08-19	67	53	42	41	48
Baha Men	Who Let The Dogs Out	2000-07-22	99	92	85	76	65
Barenaked Ladies	Pinch Me	2000-09-09	77	76	69	45	51
Beenie Man	Girls Dem Sugar	2000-10-21	72	72	63	56	62
Before Dark	Monica	2000-05-20	95	87	80	80	77
Bega, Lou	Tricky Tricky	2000-01-29	75	74	87	NA	NA
Big Punisher	It's So Hard	2000-04-22	96	87	75	79	81
Black Rob	Whoa!	2000-03-04	78	59	53	52	47
Black, Clint	Been There	2000-02-19	87	73	62	58	58
Blaque	Bring It All To Me	1999-10-23	73	63	50	42	24
Blige, Mary J.	Deep Inside	1999-11-13	83	80	80	75	75
Blige, Mary J.	Give Me You	2000-04-15	97	94	77	76	68
Blink-182	All The Small Things	1999-12-04	89	76	69	59	59
Bloodhound Gang	The Bad Touch	2000-03-18	70	62	55	55	52
Bon Jovi	It's My Life	2000-08-12	64	58	51	51	48
Braxton, Toni	He Wasn't Man Enough	2000-03-18	63	55	48	39	35
Braxton, Toni	Just Be A Man About ...	2000-07-29	76	69	51	42	37
Braxton, Toni	Spanish Guitar	2000-12-02	98	98	98	NA	NA
Brock, Chad	A Country Boy Can Su...	2000-01-01	93	75	92	NA	NA
Brock, Chad	Yes!	2000-04-08	90	77	66	61	59
Brooks & Dunn	You'll Always Be Lov...	2000-06-10	95	85	85	85	83
Brooks, Garth	Do What You Gotta Do	2000-02-19	86	81	72	70	69
Byrd, Tracy	Put Your Hand In Min...	2000-01-29	81	77	76	76	86
Cagle, Chris	My Love Goes On And ...	2000-10-21	99	94	94	87	84
Cam'ron	What Means The World...	2000-10-14	94	94	96	91	84
Carey, Mariah	Crybaby	2000-06-24	28	34	48	62	77
Carey, Mariah	Thank God I Found Yo...	1999-12-11	82	68	50	50	41
Carter, Aaron	Aaron's Party (Come ...	2000-08-26	99	75	57	35	35
Carter, Torrey	Take That	2000-06-24	94	88	86	91	89
Changing Faces	That Other Woman	2000-09-30	80	72	66	66	64
Chesney, Kenny	I Lost It	2000-10-21	75	67	61	58	58
Chesney, Kenny	What I Need To Do	2000-04-01	79	74	68	72	69
Clark Family Experience	Meanwhile Back At Th...	2000-11-18	87	86	81	92	80
Clark, Terri	A Little Gasoline	2000-12-16	75	82	88	96	99
Common	The Light	2000-08-05	75	55	53	49	46
Counting Crows	Hanginaround	1999-11-06	84	70	66	60	46
Creed	Higher	1999-09-11	81	77	73	63	61
Creed	With Arms Wide Open	2000-05-13	84	78	76	74	70
Cyrus, Billy Ray	You Won't Be Lonely ...	2000-09-23	97	97	97	92	91
D'Angelo	Left & Right	1999-12-11	93	77	75	70	91
D'Angelo	Untitled (How Does I...	2000-01-22	77	56	35	26	25
DMX	Party Up (Up In Here...	2000-02-26	88	74	62	56	49
DMX	What You Want	2000-07-01	98	95	95	87	86
DMX	What's My Name	2000-01-15	98	76	69	69	67
Da Brat	That's What I'm Look...	2000-02-26	93	73	60	60	60
Da Brat	What'Chu Like	2000-06-03	71	65	54	54	51
Davidson, Clay	Unconditional	2000-03-25	97	97	97	90	83
De La Soul	All Good?	2000-12-23	96	96	100	NA	NA
Destiny's Child	Independent Women Pa...	2000-09-23	78	63	49	33	23
Destiny's Child	Jumpin' Jumpin'	2000-05-13	74	71	65	62	57
Destiny's Child	Say My Name	1999-12-25	83	83	44	38	16
Diffie, Joe	It's Always Somethin...	2000-08-12	81	78	67	63	60
Diffie, Joe	The Quittin' Kind	2000-01-01	98	100	100	90	93
Dion, Celine	That's The Way It Is	1999-11-13	74	68	65	49	44
Dixie Chicks, The	Cold Day In July	2000-06-24	80	79	76	72	68
Dixie Chicks, The	Cowboy Take Me Away	1999-11-27	79	72	70	61	52
Dixie Chicks, The	Goodbye Earl	2000-03-18	40	29	24	24	20
Dixie Chicks, The	Without You	2000-10-07	80	70	63	56	50
Dr. Dre	Forgot About Dre	2000-01-22	75	55	47	36	36
Dr. Dre	The Next Episode	2000-05-27	78	67	58	53	46
Drama	Left, Right, Left	2000-02-12	100	98	89	80	75
Dream	He Loves U Not	2000-09-30	99	92	81	59	47
Eastsidaz, The	G'D Up	2000-01-08	77	77	89	64	57
Eastsidaz, The	Got Beef	2000-07-01	99	99	NA	NA	NA
Eiffel 65	Blue	1999-12-11	67	29	16	16	13
Elliott, Missy "Misdemeanor"	Hot Boyz	1999-11-27	36	21	13	9	7
Eminem	Stan	2000-11-04	78	67	57	57	51
Eminem	The Real Slim Shady	2000-05-06	70	32	20	16	11
Eminem	The Way I Am	2000-08-26	87	74	59	65	59
En Vogue	Riddle	2000-06-17	92	92	97	100	NA
Estefan, Gloria	No Me Dejes De Quere...	2000-06-10	77	NA	NA	NA	NA
Evans, Sara	Born To Fly	2000-10-21	77	71	64	57	55
Eve	Got It All	2000-07-15	89	88	88	91	95
Eve	Love Is Blind	2000-01-08	94	91	57	46	46
Everclear	Wonderful	2000-07-08	77	69	53	37	33
Fabian, Lara	I Will Love Again	2000-06-10	91	80	75	61	60
Fatboy Slim	The Rockafeller Skan...	1999-11-13	94	94	94	87	77
Filter	Take A Picture	1999-11-27	91	74	64	52	38
Foo Fighters	Learn To Fly	1999-10-16	80	69	68	63	60
Fragma	Toca's Miracle	2000-10-28	99	NA	NA	NA	NA
Funkmaster Flex	Do You	2000-11-11	92	92	95	91	91
Ghostface Killah	Cherchez LaGhost	2000-08-05	98	NA	NA	NA	NA
Gill, Vince	Feels Like Love	2000-09-02	82	76	74	73	73
Gilman, Billy	One Voice	2000-06-17	86	86	82	72	65
Ginuwine	None Of Ur Friends B...	1999-12-11	94	84	71	71	60
Ginuwine	The Best Man I Can B...	2000-01-08	97	97	83	94	84
Goo Goo Dolls	Broadway	2000-04-22	74	58	53	42	35
Gray, Macy	I Try	2000-02-19	68	51	47	36	30
Griggs, Andy	She's More	2000-03-11	81	76	69	67	62
Guy	Dancin'	1999-12-18	46	29	19	22	36
Hanson	This Time Around	2000-04-22	22	22	20	45	87
Hart, Beth	L.A. Song	1999-11-27	99	100	98	99	99
Heatherly, Eric	Flowers On The Wall	2000-04-29	95	88	88	82	82
Henley, Don	Taking You Home	2000-06-24	79	77	74	73	66
Herndon, Ty	No Mercy	2000-03-18	100	99	99	NA	NA
Hill, Faith	Breathe	1999-11-06	81	68	62	51	42
Hill, Faith	Let's Make Love	2000-08-12	83	83	73	73	67
Hoku	Another Dumb Blonde	2000-02-19	69	49	49	34	34
Hollister, Dave	Can't Stay	2000-03-25	84	84	93	98	NA
Hot Boys	I Need A Hot Girl	2000-02-19	77	75	71	65	65
Houston, Whitney	Could I Have This Ki...	2000-06-17	74	68	68	67	59
Houston, Whitney	I Learned From The B...	2000-02-19	83	83	83	40	28
Houston, Whitney	My Love Is Your Love	1999-09-04	81	68	44	16	11
Houston, Whitney	Same Script, Differe...	2000-06-17	71	71	71	71	70
IMx	Stay The Night	1999-10-09	84	61	45	43	40
Ice Cube	You Can Do It	1999-12-04	86	66	50	42	42
Ideal	Whatever	2000-06-10	75	75	67	73	64
Iglesias, Enrique	Be With You	2000-04-01	63	45	34	23	17
Iglesias, Enrique	Rhythm Divine	1999-12-04	90	84	79	67	67
J-Shin	One Night Stand	1999-12-25	96	96	69	74	72
Ja Rule	Between Me And You	2000-09-16	85	74	61	37	27
Jackson, Alan	It Must Be Love	2000-06-24	76	74	68	63	57
Jackson, Alan	Pop A Top	1999-11-13	79	73	70	64	63
Jackson, Alan	www.memory	2000-11-04	75	59	59	54	50
Jagged Edge	He Can't Love U	1999-12-11	54	32	17	17	15
Jagged Edge	Let's Get Married	2000-05-06	77	66	55	45	38
Janet	Doesn't Really Matte...	2000-06-17	59	52	43	30	29
Jay-Z	Anything	2000-02-26	72	58	55	55	63
Jay-Z	Big Pimpin'	2000-04-22	69	52	39	33	28
Jay-Z	Do It Again (Put Ya ...	2000-01-15	95	68	65	65	74
Jay-Z	Hey Papi	2000-08-12	98	100	98	94	83
Jay-Z	I Just Wanna Love U ...	2000-10-28	58	45	35	26	23
Jean, Wyclef	911	2000-10-07	77	74	64	61	53
Joe	I Wanna Know	2000-01-01	94	86	69	50	41
Joe	Treat Her Like A Lad...	2000-08-05	77	75	63	63	69
John, Elton	Someday Out Of The B...	2000-04-22	56	56	49	59	67
Jones, Donell	Where I Wanna Be	2000-04-22	81	71	65	50	41
Jordan, Montell	Get It On.. Tonite	1999-10-23	92	80	72	69	67
Juvenile	U Understand	2000-02-05	85	83	100	98	97
Kandi	Don't Think I'm Not	2000-08-05	66	66	66	61	49
Keith, Toby	Country Comes To Tow...	2000-08-05	82	78	75	69	66
Keith, Toby	How Do You Like Me N...	2000-01-29	77	72	59	53	45
Kelis	Caught Out There	1999-12-04	84	68	67	63	63
Kenny G	Auld Lang Syne (The ...	1999-12-25	89	89	7	8	66
Kid Rock	Only God Knows Why	2000-02-19	63	47	46	39	35
Kravitz, Lenny	I Belong To You	2000-03-25	78	77	71	71	71
Kumbia Kings	U Don't Love Me	2000-03-04	81	64	62	67	70
LFO	I Don't Wanna Kiss Y...	2000-04-15	63	61	68	73	91
LFO	West Side Story	2000-08-05	96	84	88	96	NA
LL Cool J	Imagine That	2000-08-12	99	98	NA	NA	NA
Larrieux, Amel	Get Up	2000-03-04	100	97	97	NA	NA
Lawrence, Tracy	Lessons Learned	2000-01-29	80	73	61	61	48
Levert, Gerald	Baby U Are	2000-08-19	96	89	92	96	96
Levert, Gerald	Mr. Too Damn Good	2000-03-18	84	83	83	76	86
Lil Bow Wow	Bounce With Me	2000-08-19	48	35	24	24	20
Lil Wayne	Tha Block Is Hot	1999-12-04	99	89	92	84	84
Lil' Kim	How Many Licks?	2000-11-25	79	75	77	86	86
Lil' Kim	No Matter What They ...	2000-07-15	80	72	67	60	65
Lil' Mo	Ta Da	2000-08-12	100	99	97	97	100
Lil' Zane	Callin' Me	2000-07-29	83	89	57	40	34
Limp Bizkit	N 2 Gether Now	1999-12-04	94	88	85	78	78
Limp Bizkit	Re-Arranged	1999-12-04	91	91	90	95	95
Limp Bizkit	Rollin'	2000-11-11	77	73	72	66	65
Lonestar	Amazed	1999-06-05	81	54	44	39	38
Lonestar	Smile	1999-12-18	89	80	80	80	65
Lonestar	What About Now	2000-06-10	78	72	66	64	56
Lopez, Jennifer	Feelin' Good	2000-02-19	79	79	66	54	54
Loveless, Patty	That's The Kind Of M...	2000-09-16	98	93	93	93	88
Lox	Ryde or Die, Chick	2000-03-18	86	73	80	84	91
Lucy Pearl	Dance Tonight	2000-05-20	80	75	63	59	55
Ludacris	What's Your Fantasy	2000-09-30	89	83	63	55	49
M2M	Don't Say You Love M...	1999-11-20	72	53	62	46	54
M2M	Mirror Mirror	2000-04-01	87	87	94	91	75
Madison Avenue	Don't Call Me Baby	2000-07-08	98	96	93	93	93
Madonna	American Pie	2000-02-19	43	35	29	29	33
Madonna	Music	2000-08-12	41	23	18	14	2
Martin, Ricky	Private Emotion	2000-03-11	76	67	71	78	89
Martin, Ricky	Shake Your Bon-Bon	1999-11-20	74	66	52	39	39
Martin, Ricky	She Bangs	2000-10-07	38	28	21	21	18
Mary Mary	Shackles (Praise You...	2000-03-25	90	76	72	54	43
Master P	Souljas	2000-11-18	98	NA	NA	NA	NA
McBride, Martina	Love's The Only Hous...	2000-02-05	79	69	65	58	53
McBride, Martina	There You Are	2000-09-09	79	75	75	75	73
McEntire, Reba	I'll Be	2000-05-13	89	79	79	72	69
McEntire, Reba	What Do You Say	1999-10-30	88	76	71	71	69
McGraw, Tim	My Best Friend	1999-11-27	85	76	71	64	54
McGraw, Tim	My Next Thirty Years	2000-10-21	73	62	56	52	46
McGraw, Tim	Some Things Never Ch...	2000-05-13	76	66	66	65	63
McKnight, Brian	Stay Or Let It Go	2000-02-26	95	92	82	78	76
Messina, Jo Dee	Because You Love Me	2000-01-29	83	78	71	71	66
Messina, Jo Dee	That's The Way	2000-06-24	78	67	63	54	50
Metallica	I Disappear	2000-05-13	86	84	88	81	81
Metallica	No Leaf Clover (Live...	2000-02-12	86	81	78	76	74
Montgomery Gentry	Daddy Won't Sell The...	2000-03-04	87	83	81	79	81
Montgomery, John Michael	The Little Girl	2000-09-09	81	67	64	56	45
Moore, Chante	Straight Up	2000-10-28	98	98	97	90	85
Moore, Mandy	I Wanna Be With You	2000-06-17	69	63	54	50	45
Mumba, Samantha	Gotta Tell You	2000-09-09	85	72	65	49	39
Musiq	Just Friends	2000-10-14	89	83	65	55	54
Mya	Case Of The Ex (What...	2000-08-19	72	57	52	47	42
Mya	The Best Of Me	2000-04-15	85	70	65	62	55
Mystikal	Shake Ya Ass	2000-08-12	97	90	65	41	34
N'Sync	Bye Bye Bye	2000-01-29	42	20	19	14	13
N'Sync	It's Gonna Be Me	2000-05-06	82	70	51	39	26
N'Sync	This I Promise You	2000-09-30	68	31	19	15	11
Nas	You Owe Me	2000-03-25	74	72	68	59	59
Nelly	(Hot S**t) Country G...	2000-04-29	100	99	96	76	55
Next	Wifey	2000-05-27	85	61	46	40	36
Nine Days	Absolutely (Story Of...	2000-05-06	85	71	59	52	39
Nine Days	If I Am	2000-12-02	68	68	81	94	100
No Doubt	Simple Kind Of Life	2000-07-01	50	40	39	38	38
Nu Flavor	3 Little Words	2000-06-03	97	97	89	89	94
Offspring, The	Original Prankster	2000-11-25	74	71	70	70	77
Paisley, Brad	Me Neither	2000-05-13	87	85	90	92	NA
Paisley, Brad	We Danced	2000-10-14	71	68	52	52	45
Papa Roach	Last Resort	2000-07-29	75	71	69	69	66
Pearl Jam	Nothing As It Seems	2000-05-13	49	70	84	89	93
Pink	Most Girls	2000-08-12	85	70	52	36	27
Pink	There U Go	2000-03-04	25	15	12	11	11
Price, Kelly	As We Lay	2000-07-15	82	69	69	64	71
Price, Kelly	Love Sets You Free	2000-05-13	92	91	98	100	NA
Price, Kelly	You Should've Told M...	2000-09-23	91	91	91	87	86
Profyle	Liar	2000-09-16	52	32	25	17	16
Puff Daddy	Best Friend	2000-02-12	65	59	62	79	99
Q-Tip	Breathe And Stop	2000-01-22	71	71	81	82	96
R.E.M.	The Great Beyond	1999-12-25	79	79	70	62	60
Rascal Flatts	Prayin' For Daylight	2000-05-06	87	78	72	68	66
Raye, Collin	Couldn't Last A Mome...	2000-03-18	91	85	75	73	67
Red Hot Chili Peppers	Californication	2000-07-29	72	72	72	77	79
Red Hot Chili Peppers	Otherside	2000-02-12	80	72	65	52	51
Rimes, LeAnn	Big Deal	1999-10-16	71	52	51	51	51
Rimes, LeAnn	Can't Fight The Moon...	2000-09-09	82	71	79	83	96
Rimes, LeAnn	I Need You	2000-05-27	77	68	67	63	59
Rogers, Kenny	Buy Me A Rose	2000-03-11	79	72	65	65	54
Ruff Endz	No More	2000-07-01	76	38	19	17	12
Sammie	I Like It	2000-01-29	85	68	58	44	40
Santana	Maria, Maria	2000-02-12	15	8	6	5	2
Savage Garden	Crash And Burn	2000-04-08	75	58	51	36	33
Savage Garden	I Knew I Loved You	1999-10-23	71	48	43	31	20
SheDaisy	Deck The Halls	1999-12-25	97	61	NA	NA	NA
SheDaisy	I Will.. But	2000-07-15	78	74	70	61	59
SheDaisy	This Woman Needs	2000-02-05	82	70	70	67	57
Sheist, Shade	Where I Wanna Be	2000-11-11	96	95	99	99	100
Shyne	Bad Boyz	2000-09-09	94	87	90	90	82
Simpson, Jessica	I Think I'm In Love ...	2000-07-01	63	52	44	29	25
Simpson, Jessica	Where You Are	2000-04-01	73	66	62	62	76
Sisqo	Got To Get It	1999-11-20	92	76	73	58	48
Sisqo	Incomplete	2000-06-24	77	66	61	61	61
Sisqo	Thong Song	2000-01-29	74	63	35	26	26
Sister Hazel	Change Your Mind	2000-07-15	75	67	66	59	63
Smash Mouth	Then The Morning Com...	1999-10-30	83	59	56	46	27
Smith, Will	Freakin' It	2000-02-12	99	99	99	99	NA
Son By Four	A Puro Dolor (Purest...	2000-04-08	80	80	80	79	72
Sonique	It Feels So Good	2000-01-22	67	52	30	23	19
SoulDecision	Faded	2000-07-08	94	90	81	64	56
Spears, Britney	From The Bottom Of M...	2000-01-29	76	59	52	52	14
Spears, Britney	Lucky	2000-08-12	61	41	28	26	23
Spears, Britney	Oops!.. I Did It Aga...	2000-04-22	67	38	26	19	15
Spencer, Tracie	Still In My Heart	2000-03-04	95	88	98	NA	NA
Splender	I Think God Can Expl...	2000-06-10	71	66	62	62	62
Sting	Desert Rose	2000-05-13	98	88	72	59	55
Stone Temple Pilots	Sour Girl	2000-07-08	79	79	79	78	78
Stone, Angie	No More Rain (In Thi...	1999-12-25	86	86	74	66	56
Strait, George	Go On	2000-08-26	71	67	63	56	53
Strait, George	The Best Day	2000-01-29	73	64	54	45	44
Sugar Ray	Falls Apart	2000-01-15	70	64	54	40	34
TLC	Dear Lie	2000-02-12	63	55	52	51	56
Tamar	If You Don't Wanna L...	2000-03-25	98	98	92	89	92
Tamia	Can't Go For That	2000-09-16	90	86	84	88	97
Third Eye Blind	Deep Inside Of You	2000-09-02	80	73	70	70	70
Third Eye Blind	Never Let You Go	2000-01-22	65	32	25	24	23
Thomas, Carl	Emotional	2000-11-25	77	63	61	58	54
Thomas, Carl	I Wish	2000-03-25	75	64	48	39	32
Thomas, Carl	Summer Rain	2000-09-23	82	82	86	80	82
Tippin, Aaron	Kiss This	2000-08-26	74	72	66	53	52
Train	Meet Virginia	1999-10-09	76	67	59	54	48
Trick Daddy	Shut Up	2000-05-20	99	95	87	87	83
Trina	Pull Over	2000-09-09	97	93	96	100	NA
Tritt, Travis	Best Of Intentions	2000-08-19	97	86	79	70	63
Tuesday	I Know	2000-12-30	98	98	NA	NA	NA
Urban, Keith	Your Everything	2000-07-15	81	80	73	73	67
Usher	Pop Ya Collar	2000-11-04	68	64	60	60	62
Vassar, Phil	Carlene	2000-03-04	75	67	64	64	57
Vassar, Phil	Just Another Day In ...	2000-09-30	81	81	76	67	53
Vertical Horizon	Everything You Want	2000-01-22	70	61	53	46	40
Vertical Horizon	You're A God	2000-08-26	64	55	43	41	37
Vitamin C	Graduation (Friends ...	2000-04-15	81	64	54	54	46
Vitamin C	The Itch	2000-12-02	86	48	45	52	57
Walker, Clay	Live, Laugh, Love	1999-12-04	95	95	94	94	94
Walker, Clay	The Chain Of Love	2000-04-15	73	65	57	57	51
Wallflowers, The	Sleepwalker	2000-10-28	73	73	74	80	90
Westlife	Swear It Again	2000-04-01	96	82	66	55	55
Williams, Robbie	Angels	1999-11-20	85	77	69	69	62
Wills, Mark	Back At One	2000-01-15	89	55	51	43	37
Worley, Darryl	When You Need My Lov...	2000-06-17	98	88	93	92	85
Wright, Chely	It Was	2000-03-04	86	78	75	72	71
Yankee Grey	Another Nine Minutes	2000-04-29	86	83	77	74	83
Yearwood, Trisha	Real Live Woman	2000-04-01	85	83	83	82	81
Ying Yang Twins	Whistle While You Tw...	2000-03-18	95	94	91	85	84
Zombie Nation	Kernkraft 400	2000-09-02	99	99	NA	NA	NA
matchbox twenty	Bent	2000-04-29	60	37	29	24	22

Using Your Helpers

billboard |> 
  pivot_longer(
    cols = starts_with("wk"), 
    names_to = "week",
    names_prefix = "wk",
    values_to = "rank",
    values_drop_na = TRUE
  ) |>
  head(4)

# A tibble: 4 × 5
  artist track                   date.entered week   rank
  <chr>  <chr>                   <date>       <chr> <dbl>
1 2 Pac  Baby Don't Cry (Keep... 2000-02-26   1        87
2 2 Pac  Baby Don't Cry (Keep... 2000-02-26   2        82
3 2 Pac  Baby Don't Cry (Keep... 2000-02-26   3        72
4 2 Pac  Baby Don't Cry (Keep... 2000-02-26   4        77

Tell Us How We Did

https://gsu.qualtrics.com/jfe/form/SV_9nucJR3soZ9lkqO