Data wrangling

Section C.1 Data wrangling

In this section, we address some of the most common data wrangling questions we’ve encountered in student projects:

Subsection C.1.1: Dealing with missing values
Subsection C.1.2: Reordering bars in a barplot
Subsection C.1.3: Showing money on an axis
Subsection C.1.4: Changing values inside cells
Subsection C.1.5: Converting a numerical variable to a categorical one
Subsection C.1.6: Computing proportions
Subsection C.1.7: Dealing with %, commas, and $

Let’s load an example movies dataset, pare down the rows and columns a bit, and then show the first 10 rows using slice().

movies_ex <- read_csv("https://moderndive.com/data/movies.csv") |>
  filter(type %in% c("action", "comedy", "drama", "animated", "fantasy", "rom comedy")) |>
  select(-over200)

movies_ex |>
  slice(1:10)

Subsection C.1.1 Dealing with missing values

You may see the revenue in millions value for some movies is NA (missing). So the following occurs when computing the median revenue:

movies_ex |>

  summarize(mean_profit = median(millions))

# A tibble: 1 × 1
  mean_profit
        <dbl>
1          NA

You should always think about why a data value might be missing and what that missingness may mean. For example, imagine you are conducting a study on the effects of smoking on lung cancer and a lot of your patients’ data is missing because they died of lung cancer. If you just “sweep these patients under the rug” and ignore them, you are clearly biasing the results.

While there are statistical methods to deal with missing data they are beyond the reach of this class. The easiest thing to do is to remove all missing cases, but you should always at the very least report to the reader if you do so, as by removing the missing values you may be biasing your results.

You can do this with a na.rm = TRUE argument like so:

movies_ex |>

  summarize(mean_profit = median(millions, na.rm = TRUE))

# A tibble: 1 × 1
  mean_profit
        <dbl>
1        43.4

If you decide you want to remove the row with the missing data, you can use the filter function like so:

movies_no_missing <- movies_ex |>

  filter(!is.na(millions))

movies_no_missing |>

  slice(1:10)

# A tibble: 10 × 6
   name             score rating type       millions over200
   <chr>            <dbl> <chr>  <chr>         <dbl>   <dbl>
 1 A Guy Thing       39.5 PG-13  rom comedy    15.5        0
 2 A Man Apart       42.9 R      action        26.2        0
 3 A Mighty Wind     79.9 PG-13  comedy        17.8        0
 4 Agent Cody Banks  57.9 PG     action        47.8        0
 5 Alex & Emma       35.1 PG-13  rom comedy    14.2        0
 6 American Wedding  50.7 R      comedy       104.         0
 7 Anger Management  62.6 PG-13  comedy       134.         0
 8 Anything Else     63.3 R      rom comedy     3.21       0
 9 Bad Boys II       38.1 R      action       138.         0
10 Bad Santa         75.8 R      comedy        59.5        0

Subsection C.1.2 Reordering bars in a barplot

Let’s compute the total revenue for each movie type and plot a barplot.

revenue_by_type <- movies_ex |>
  group_by(type) |>
  summarize(total_revenue = sum(millions))

ggplot(revenue_by_type, aes(x = type, y = total_revenue)) +
  geom_col() +
  labs(x = "Movie genre", y = "Total box office revenue (in millions of $)")

Say we want to reorder the categorical variable type so that the bars show in a different order. We can reorder the bars by manually defining the order of the levels in the factor() command:

type_levels <- c("rom comedy", "action", "drama", "animated", "comedy", "fantasy")

revenue_by_type <- revenue_by_type |>
  mutate(type = factor(type, levels = type_levels))

ggplot(revenue_by_type, aes(x = type, y = total_revenue)) +
  geom_col() +
  labs(x = "Movie genre", y = "Total boxoffice revenue (in millions of $)")

Or if you want to reorder type in ascending order of total_revenue, we use reorder():

revenue_by_type <- revenue_by_type |>
  mutate(type = reorder(type, total_revenue))

ggplot(revenue_by_type, aes(x = type, y = total_revenue)) +
  geom_col() +
  labs(x = "Movie genre", y = "Total boxoffice revenue (in millions of $)")

Or if you want to reorder type in descending order of total_revenue, just put a - sign in front of -total_revenue in reorder():

revenue_by_type <- revenue_by_type |>
  mutate(type = reorder(type, -total_revenue))

ggplot(revenue_by_type, aes(x = type, y = total_revenue)) +
  geom_col() +
  labs(x = "Movie genre", y = "Total boxoffice revenue (in millions of $)")

For more advanced categorical variable (i.e. factor) manipulations, check out the forcats package. Note: forcats is an anagram of factors.

Subsection C.1.3 Showing money on an axis

movies_ex <- movies_ex |>
  mutate(revenue = millions * 10^6)

ggplot(data = movies_ex, aes(x = rating, y = revenue)) +
  geom_boxplot() +
  labs(x = "rating", y = "Revenue in $", title = "Profits for different movie ratings")

To format the y-axis with dollar signs, load the scales package and use scale_y_continuous(labels = dollar):

library(scales)

ggplot(data = movies_ex, aes(x = rating, y = revenue)) +
  geom_boxplot() +
  labs(x = "rating", y = "Revenue in $", title = "Profits for different movie ratings") +
  scale_y_continuous(labels = dollar)

Subsection C.1.4 Changing values inside cells

The rename() function in the dplyr package renames column/variable names. To “rename” values inside cells of a particular column, you need to mutate() the column using one of the three functions below. In these examples, we’ll change values in the variable type.

if_else()
recode()
case_when()

Subsubsection C.1.4.1 `if_else()`

Switch all instances of rom comedy with romantic comedy using if_else() from the dplyr package. If a particular row has type == "rom comedy", then return "romantic comedy", else return whatever was originally in type. Save everything in a new variable type_new:

movies_ex |>

  mutate(type_new = if_else(type == "rom comedy", "romantic comedy", type)) |>

  slice(1:10)

# A tibble: 5 × 7
  name             score rating type       millions over200 type_new       
  <chr>            <dbl> <chr>  <chr>         <dbl>   <dbl> <chr>          
1 2 Fast 2 Furious  48.9 PG-13  action         NA         0 action         
2 A Guy Thing       39.5 PG-13  rom comedy     15.5       0 romantic comedy
3 A Man Apart       42.9 R      action         26.2       0 action         
4 A Mighty Wind     79.9 PG-13  comedy         17.8       0 comedy         
5 Agent Cody Banks  57.9 PG     action         47.8       0 action

Do the same here, but return "not romantic comedy" if type is not "rom comedy" and this time overwrite the original type variable:

movies_ex |>

  mutate(type = if_else(type == "rom comedy", "romantic comedy", "not romantic comedy")) |>

  slice(1:10)

# A tibble: 5 × 7
  name             score rating type       millions over200 type       
  <chr>            <dbl> <chr>  <chr>         <dbl>   <dbl> <chr>      
1 2 Fast 2 Furious  48.9 PG-13  action         NA         0 not romantic comedy
2 A Guy Thing       39.5 PG-13  rom comedy     15.5       0 romantic comedy
3 A Man Apart       42.9 R      action         26.2       0 not romantic comedy
4 A Mighty Wind     79.9 PG-13  comedy         17.8       0 not romantic comedy
5 Agent Cody Banks  57.9 PG     action         47.8       0 not romantic comedy

Subsubsection C.1.4.2 `recode()`

if_else() is rather limited. What if we want to “rename” all type so that they start with uppercase? Use recode():

movies_ex |>

  mutate(type_new = recode(type,

    "action" = "Action",

    "animated" = "Animated",

    "comedy" = "Comedy",

    "drama" = "Drama",

    "fantasy" = "Fantasy",

    "rom comedy" = "Romantic Comedy"

  )) |>

  slice(1:10)

# A tibble: 5 × 7
  name             score rating type       millions over200 type_new       
  <chr>            <dbl> <chr>  <chr>         <dbl>   <dbl> <chr>          
1 2 Fast 2 Furious  48.9 PG-13  action         NA         0 Action         
2 A Guy Thing       39.5 PG-13  rom comedy     15.5       0 Romantic Comedy
3 A Man Apart       42.9 R      action         26.2       0 Action         
4 A Mighty Wind     79.9 PG-13  comedy         17.8       0 Comedy         
5 Agent Cody Banks  57.9 PG     action         47.8       0 Action

Subsubsection C.1.4.3 `case_when()`

case_when() is a little trickier, but allows you to evaluate boolean operations using ==, >, >=, &, |, etc:

movies_ex |>

  mutate(

    type_new = case_when(

      type == "action" & millions > 40 ~ "Big budget action",

      type == "rom comedy" & millions < 40 ~ "Small budget romcom",

      TRUE ~ "Rest"

    )

  )

# A tibble: 5 × 4
  name             type       millions type_new         
  <chr>            <chr>         <dbl> <chr>            
1 2 Fast 2 Furious action         NA   Action           
2 A Guy Thing      rom comedy     15.5 Not action       
3 A Man Apart      action         26.2 Action           
4 A Mighty Wind    comedy         17.8 Not action       
5 Agent Cody Banks action         47.8 Big budget action

Subsection C.1.5 Converting a numerical variable to a categorical one

Sometimes we want to turn a numerical, continuous variable into a categorical variable. For instance, what if we wanted to have a variable that tells us if a movie made one hundred million dollars or more. We can create a binary variable using the mutate() function:

movies_ex |>

  mutate(big_budget = millions > 100) |>

  slice(1:10)

# A tibble: 10 × 7
   name             score rating type       millions over200 big_budget
   <chr>            <dbl> <chr>  <chr>         <dbl>   <dbl> <lgl>     
 1 2 Fast 2 Furious  48.9 PG-13  action        NA          0 NA        
 2 A Guy Thing       39.5 PG-13  rom comedy    15.5        0 FALSE     
 3 A Man Apart       42.9 R      action        26.2        0 FALSE     
 4 A Mighty Wind     79.9 PG-13  comedy        17.8        0 FALSE     
 5 Agent Cody Banks  57.9 PG     action        47.8        0 FALSE     
 6 Alex & Emma       35.1 PG-13  rom comedy    14.2        0 FALSE     
 7 American Wedding  50.7 R      comedy       104.         0 TRUE      
 8 Anger Management  62.6 PG-13  comedy       134.         0 TRUE      
 9 Anything Else     63.3 R      rom comedy     3.21       0 FALSE     
10 Bad Boys II       38.1 R      action       138.         0 TRUE

What if you want to convert a numerical variable into a categorical variable with more than 2 levels? One way is to use the cut() command. For instance, below, we cut() the score variable to recode it into 4 categories:

0 – 40 = bad
40.1 – 60 = so-so
60.1 – 80 = good
80.1+ = great

movies_ex |>

  mutate(score_categ = cut(score,

    breaks = c(0, 40, 60, 80, 100),

    labels = c("bad", "so-so", "good", "great")

  )) |>

  slice(1:10)

# A tibble: 5 × 7
  name             score rating type       millions over200 score_categ
  <chr>            <dbl> <chr>  <chr>         <dbl>   <dbl> <fct>      
1 2 Fast 2 Furious  48.9 PG-13  action         NA         0 mediocre   
2 A Guy Thing       39.5 PG-13  rom comedy     15.5       0 bad        
3 A Man Apart       42.9 R      action         26.2       0 mediocre   
4 A Mighty Wind     79.9 PG-13  comedy         17.8       0 good       
5 Agent Cody Banks  57.9 PG     action         47.8       0 mediocre

Other options with the cut function:

By default, if the value is exactly the upper bound of an interval, it’s included in the lesser category (e.g. 60.0 is “so-so” not “good”). To flip this, include the argument right = FALSE.
You could also have R equally divide the variable into a balanced number of groups. For example, specifying breaks = 3 would create 3 groups with approximately the same number of values in each group.

Subsection C.1.6 Computing proportions

By using a group_by() followed not by a summarize() as is often the case, but rather a mutate(). So say we compute the total revenue millions for each movie rating and type:

rating_by_type_millions <- movies_ex |>
  group_by(rating, type) |>
  summarize(millions = sum(millions)) |>
  arrange(rating, type)

rating_by_type_millions

Say within each movie rating (G, PG, PG-13, R), we want to know the proportion of total_millions made by each movie type (animated, action, comedy, etc). We can:

rating_by_type_millions |>
  group_by(rating) |>
  mutate(
    total_millions = sum(millions),
    prop = millions / total_millions
  )

So for example, the 4 proportions corresponding to R rated movies sum to 1.

Subsection C.1.7 Dealing with %, commas, and $

Say you have numerical data that are recorded as percentages, have commas, or are in dollar form and hence are character strings. How do you convert these to numerical values? Using the parse_number() function from the readr package inside a mutate()!

library(readr)
parse_number("10.5%")
parse_number("145,897")
parse_number("$1,234.5")

[1] 1234.5

What about the other way around? Use the scales package!

library(scales)
percent(0.105)
comma(145897)
dollar(1234.5)

[1] "$1,234.50"

Prev Top Next