Previously: Data manipulation

• Data input and output
• Mutate and summarise data
• Working with R projects (practical)

Today: Table operations

• Long and wide table formats
• Pivot operations (not as in Excel)
• Join operations

Each real-world entity is represented by multiple rows

• each one reporting only one of its attributes
• one column indicates which attribute each row represent
• another column is used to report the value

Common approach for temporal series

The tidyr (pronounced tidy-er) library is part of tidyverse

Provides a series of functions to "tidy-up" your data, including

• re-shape your data
  • tidyr::pivot_wider: pivot from long to wide
  • tidyr::pivot_longer: pivot from wide to long
• handle missing values
  • tidyr::drop_na: remove rows with missing data
  • tidyr::replace_na: replace missing data
  • tidyr::fill: fill missing data
  • tidyr::complete: add missing value combinations

Re-shape from long to wide format

city_info_wide <- 
  city_info_long %>%
  pivot_wider(
    # Column from which to extract 
    # new column names
    names_from = week_ending,
    # Column from which to extract 
    # values
    values_from = cases
  )

Re-shape from wide to long format

city_info_back_to_long <- 
  city_info_wide %>%
  pivot_longer(
    # Pivot all columns, excluding city
    cols = -city, 
    # Name column for column names
    names_to = "week_ending", 
    # Name column for values
    values_to = "cases" 
  )

city_info_back_to_long <- 
  city_info_wide %>%
  pivot_longer(
    # As before
    cols = -city, 
    names_to = "week_ending",  
    values_to = "cases",
    # Remove name prefix
    names_prefix = "cases_",
    # Transform the values that will  
    # become column names
    names_transform = list(
      # Provide a function name or define one
      week_ending = function (x) { 
        str_replace_all(x, "_", "-") 
        }
    )
  )

Some analysis or visualisation procedures might require a complete table

• where missing values are represented as NAs
• for instance, when creating a map
  • you might want to use a specific colour for missing values
  • rather than a missing polygon

Complete table by turning implicit missing values into explicit missing values

cities <- 
  data.frame(
    city_name = c(
      "Barcelona", "London", "Rome", "Los Angeles"
    ),
    country_name = c(
      "Spain", "UK", "Italy", "US"
    ),
    city_pop_M = c(
      1.62, 8.98, 4.34, 3.99
    )
  )
cities_area <-
  data.frame(
    city_name = c(
      "Barcelona", "London", "Rome", "Munich"
    ),
    city_area_km2 = c(
      101, 1572, 496, 310
    )
)

Tables can be joined (or 'merged')

• information from two tables can be combined
• specifying column(s) from two tables with common values
  • usually one with a unique identifier of an entity
• rows having the same value are joined
• depending on parameters
  • a row from one table can be merged with multiple rows from the other table
  • rows with no matching values in the other table can be retained
• merge base function or join functions in dplyr

dplyr provides a series of join verbs

• Mutating joins
  • inner_join: inner join
  • left_join: left join
  • right_join: right join
  • full_join: full join
• Nesting joins
  • nest_join: all rows columns from left table, plus a column of tibbles with matching from right
• Filtering joins (keep only columns from left)
  • semi_join: , rows from left where match with right
  • anti_join: rows from left where no match with right

Input tables

Input tables

Input tables

Input tables

Input tables

Input tables

Today: Table operations

• Long and wide table formats
• Pivot operations (not as in Excel)
• Join operations

Next week: Exploratory visualisation

• Grammar of graphics
• Visualising amounts and proportions
• Visualising variable distributions and relationships