Tutorial 5: Shaping and Joining Data

Learning Goals

By the end of this tutorial, you should be able to:

Explain the difference between tidy and messy data, and use pivot_longer() to tidy up wide data.
Summarize and visualise financial data, and explain why tidy formats make analysis easier.
Plot and interpret relationships between share price and earnings per share (EPS).
Combine data from multiple tables using joins (left_join(), inner_join(), anti_join()), and understand what each join includes or excludes.

The Business Challenge

The Topic: What shapes how the market values Australian firms?

Investors do not just care about whether a company is profitable. They also care about how much they are paying for those earnings.

In this tutorial, we will work with data on large Australian firms to explore a simple question:

How strongly are share prices and valuation ratios related to company earnings, and how do those patterns vary across firms and industries?

To answer that question, we first need to prepare the data. Some of the financial variables are stored in a messy wide format, and other information is spread across separate files. So before we can analyse anything, we need to:

reshape the data into tidy form
join together firm, industry, and valuation data
check what information is missing after those joins

Two key variables in today’s tutorial are:

\[ \text{EPS} = \frac{\text{Profit}}{\text{Shares Outstanding}} \]

EPS tells us how much profit a company earns for each share.

\[ \text{P/E} = \frac{\text{Share Price}}{\text{EPS}} \]

The P/E ratio tells us how much investors are willing to pay for each dollar of earnings.

By the end of the tutorial, you will have built and explored a dataset that lets you compare firms and industries using share prices, earnings, and valuation ratios.

R packages for today

library(tidyverse)     # collection of packages for data manipulation and visualization

Prepare these Exercises before Class

Prepare these exercises before coming to class. Plan to spend 45 minutes on these exercises.

Exercise 1: Identifying Issues with Untidy Data

We will start with a deliberately messy version of the ASX stock price data. Run the following code to load all of our data.

asx_prices_messy <- read_csv("data/asx_prices_messy.csv")  # stock price data
firm_codes <- read_csv("data/asx_200_2024.csv") |>
    select(gvkey, conm, gsubind)  # firm codes and names
subindustry_names <- read_csv("data/GICS_subindustry.csv")  # subindustry names

Then inspect a subset of the data:

asx_prices_messy |>
    select(gvkey, conm, price_2023, price_2024,
           eps_2023, eps_2024) |>
    head(10)

(a) In what ways is this data frame not tidy? What principles of tidy data are violated?

(b) Use the starter code below to compute the average share price across all firms for 2023 and 2024.

asx_prices_messy |>
    summarise(
        # Add up all 2023 share prices
        sum_prices_2023 = sum(YOUR_CODE_HERE, na.rm = TRUE),
        # Add up all 2024 share prices
        sum_prices_2024 = sum(YOUR_CODE_HERE, na.rm = TRUE),
        # Count how many firms have a non-missing share price in 2023
        count_firms_2023 = sum(!is.na(price_2023)),
        # Count how many firms have a non-missing share price in 2024
        count_firms_2024 = sum(!is.na(price_2024))
    ) |>
    mutate(
        # Combine the 2023 and 2024 price totals
        total_sum = YOUR_CODE_HERE + YOUR_CODE_HERE,
        # Combine the number of non-missing firm prices across both years
        total_count = count_firms_2023 + count_firms_2024
    ) |>
    mutate(
        # Divide the total sum of prices by the total number of firms
        avg_price_23_24 = YOUR_CODE_HERE / YOUR_CODE_HERE
    ) |> 
    select(avg_price_23_24)

(c) Why does the approach in part (b) not scale well as more years are added?

(d) The code below plots share price against earnings per share in 2024.

asx_prices_messy |>
    filter(eps_2024 > -5) |>
    ggplot(aes(x = eps_2024, y = price_2024)) +
    YOUR_CODE_HERE +
    YOUR_CODE_HERE +
    labs(
        title = "YOUR_LABEL_HERE",
        x = "YOUR_LABEL_HERE",
        y = "YOUR_LABEL_HERE"
    ) +
    theme_minimal()

What relationship do you see? Why does this make sense economically?

(e) Suppose you also wanted to include 2023 in this analysis. Briefly explain:

which extra columns you would need
what you would need to change in the plotting process
why this approach would become awkward if extended to 2019 to 2024

(f) In two or three sentences, explain to a manager why this data structure makes analysis harder than it needs to be.

Write your answer here

Exercise 2: Combining Datasets

In this exercise, you will combine information from two datasets using a join. Start with a quick look at the firm_codes and subindustry_names datasets:

firm_codes |>
    head(10)

subindustry_names |>
    head(10)

(a) Look at the variables in both datasets. Which variable or variables look like they could be used to join these two datasets?

(b) Which join variable would you choose? Explain why it is the best choice here.

(c) Use a left join to add the subindustry name to each firm in firm_codes.

firm_info <-
    YOUR_DATASET_NAME |>
    left_join(YOUR_DATASET_NAME, by = join_by(YOUR_VARIABLE_NAME))

firm_info |>
    head(10)

(d) What changed after the join? Briefly explain:

what information was added
whether the join mainly added rows or columns
why this makes the dataset more useful for analysis

(e) Look at how gsubind behaves in the two datasets.

In which dataset would you expect each gsubind to appear only once?
In which dataset can the same gsubind appear many times?
Why does that make sense?

(f) Fill in the variable name in each sentence below.

In subindustry_names, _____ behaves like a primary key because each code identifies one subindustry label.

In firm_codes, _____ behaves like a foreign key because many firms can share the same subindustry code.

(g) Suppose we reversed the join and started with subindustry_names on the left-hand side. What would be different about the resulting dataset, and why?

In-Class Exercises

You will discuss these exercises in class with your peers in small groups and with your tutor. These exercises build from the exercises you have prepared above, you will get the most value from the class if you have completed those above before coming to class.

Exercise 3: Tidying Data to Make Analysis Easier

(a) Look at the column names in asx_prices_messy, and answer the following:

What two pieces of information are stored in these column names?
What character separates those two pieces of information?

(b) We now want to reshape the stock price data so that each row corresponds to one firm in one year. Complete the starter code below to turn the data into a tidy format.

asx_prices_tidy <-
    asx_prices_messy |>
    pivot_YOUR_CODE(
        cols = YOUR_CODE_HERE,
        names_to = c(".value", "fyear"),
        names_sep = YOUR_CODE_HERE,
        values_drop_na = TRUE
    ) |>
    mutate(fyear = as.numeric(fyear))

asx_prices_tidy |>
    head(10)

(c) In Exercise 1, calculating the average share price across 2023 and 2024 took several steps. Use the tidy dataset to calculate the average share price across those two years.

asx_prices_tidy |>
    YOUR_CODE(fyear %in% c(YOUR_YEARS)) |>
    YOUR_CODE(avg_price = mean(YOUR_VARIABLE, na.rm = TRUE))

(d) Why is the calculation in part (c) easier to write using the tidy dataset than it was in Exercise 1?

What would we need to do if we added many more years of data?

Exercise 4: Joins in Miniature

In Exercise 2 you used a left_join() to attach subindustry names to a list of firms. A left_join() is only one of several ways to combine two tables, and the choice between them matters.

Before we work with joins on the full ASX dataset, we will practise on two tiny tables where you can count every row by hand.

Run the code below to create them.

firms_toy <- tribble(
    ~gvkey,  ~conm,           ~gsubind,
    "001",   "Alpha Mining",  15104020,
    "002",   "Bright Retail", 30101030,
    "003",   "Coral Energy",  10102020,
    "004",   "Delta Tech",    99999999
)

industry_toy <- tribble(
    ~gsubind,  ~subind,
    15104020,  "Diversified Metals & Mining",
    30101030,  "Food Retail",
    10102020,  "Oil & Gas Exploration & Production",
    20305020,  "Highways & Railtracks"
)

firms_toy

# A tibble: 4 × 3
  gvkey conm           gsubind
  <chr> <chr>            <dbl>
1 001   Alpha Mining  15104020
2 002   Bright Retail 30101030
3 003   Coral Energy  10102020
4 004   Delta Tech    99999999

industry_toy

# A tibble: 4 × 2
   gsubind subind                            
     <dbl> <chr>                             
1 15104020 Diversified Metals & Mining       
2 30101030 Food Retail                       
3 10102020 Oil & Gas Exploration & Production
4 20305020 Highways & Railtracks

(a) Look carefully at the two tables before running any joins.

Which firm has a gsubind that does not appear in industry_toy?
Which subindustry has no firm in firms_toy?

Now predict, without running anything, how many rows each of these will return:

left_join(firms_toy, industry_toy, by = join_by(gsubind))
inner_join(firms_toy, industry_toy, by = join_by(gsubind))
anti_join(firms_toy, industry_toy, by = join_by(gsubind))

Write your three predictions down before you continue.

(b) Now run all three joins and compare the results with your predictions.

firms_toy |> join_one(industry_toy, by = join_by(gsubind))

firms_toy |> join_two(industry_toy, by = join_by(gsubind))

firms_toy |> join_three(industry_toy, by = join_by(gsubind))

For each join, say what happened to Delta Tech and why.

(c) What happens if we swap the table on the left with the table on the right for each join?

industry_toy |> join_one(firms_toy, by = join_by(gsubind))

industry_toy |> join_two(firms_toy, by = join_by(gsubind))

industry_toy |> join_three(firms_toy, by = join_by(gsubind))

What does this result tell you? Which joins are directional?

(d) Complete the sentences below in your own words.

I would use left_join() when I want to _____
I would use inner_join() when I want to _____
I would use anti_join() when I want to _____

(e) So far each firm has appeared only once. Real firm-year data is different, because the same firm appears in several years.

Run the code below to create a second pair of tiny tables.

prices_toy <- tribble(
    ~gvkey, ~fyear, ~price,
    "001",  2022,   10.0,
    "001",  2023,   12.0,
    "001",  2024,   15.0
)

pe_toy <- tribble(
    ~gvkey, ~fyear, ~pe,
    "001",  2023,   18.0,
    "001",  2024,   20.0
)

(f) Now left join prices_toy and pe_toy using both gvkey and fyear.

prices_toy |> YOUR_CODE_HERE

Did the join work as intended? How can you tell?

Exercise 5: Joins with real data

You now know what left_join(), inner_join() and anti_join() do, and why a join key has to match the observational unit. In this exercise you will apply all of that to the full ASX dataset.

Run the code below to load and prepare the P/E ratio data.

pe_data <-
    read_csv("data/pe.csv") |>
    select(gvkey, fyear, pe) |>
    filter(!is.na(pe)) |>
    arrange(gvkey, fyear)

(a) Before joining anything, work out what you are joining.

What does one row of asx_prices_tidy represent? How many variables together identify a row/observation?
What about for pe_data?
Which variables should you therefore join on?

(b) Complete the code below to add the pe variable from pe_data to asx_prices_tidy.

asx_with_pe <-
    asx_prices_tidy |>
    left_join(
        pe_data,
        by = YOUR_CODE_HERE
    )

Did the join add rows, columns, or both?

(c) Run the two other joins on the same pair of tables.

asx_with_pe_inner <-
    asx_prices_tidy |>
    inner_join(
        pe_data,
        by = join_by(gvkey, fyear)
    )

missing_pe <-
    asx_prices_tidy |>
    anti_join(
        pe_data,
        by = join_by(gvkey, fyear)
    )

A join is not finished until you have checked what it did. Now compare the row counts.

nrow(asx_prices_tidy)
nrow(asx_with_pe)
nrow(asx_with_pe_inner)
nrow(missing_pe)

Which two of these four numbers are equal, and why must they be?
Which two add up to the total, and why must they?

(d) Using those row counts, what share of firm-year observations actually have a P/E ratio?

Is that higher or lower than you expected before you looked?

(e) A quarter of the data is a lot to lose without knowing why. Investigate missing_pe to work out where the gaps are.

Two things worth checking: whether the missing observations are concentrated in particular years…

missing_pe |>
    YOUR_CODE(fyear)

and whether they differ from the matched observations in some other way, for example, whether they differ in their earnings per share.

missing_pe |>
    summarise(
        n = n(),
        min_eps = YOUR_CODE_HERE(eps, na.rm = TRUE),
        max_eps = YOUR_CODE_HERE(eps, na.rm = TRUE),
        avg_eps = YOUR_CODE_HERE(eps, na.rm = TRUE)
    )

asx_with_pe_inner |>
    summarise(
        n = n(),
        min_eps = YOUR_CODE_HERE(eps, na.rm = TRUE),
        max_eps = YOUR_CODE_HERE(eps, na.rm = TRUE),
        avg_eps = YOUR_CODE_HERE(eps, na.rm = TRUE)
    )

Write one or two short findings, then suggest an explanation for what you found.

Exercise 6: Comparing Industries with Grouped Summaries

(a) We now want to compare valuation patterns across industries.

To do that, we first need to add industry codes to asx_with_pe. Run the following code, which joins each of our datasets together.

# join firm codes to subindustry names (a repeat from exercise 2)
firm_info <-
    firm_codes |>
    left_join(subindustry_names, by = join_by(gsubind))

# join subindustry names to the main dataset
asx_analysis <-
    asx_with_pe |>
    left_join(
        firm_info |> select(-conm),
        by = join_by(gvkey)
    )

What new information does the last join add to our previous dataset?

(b) Using asx_analysis, create a summary dataset called industry_year_summary that does all of the following:

groups the data by subindustry and year
calculates the average P/E ratio
calculates the average share price
calculates the average EPS
calculates the number of firms in each subindustry-year

industry_year_summary <-
    asx_analysis |>
    YOUR_CODE(gsubind, subind, YOUR_VARIABLE) |>
    summarise(
        avg_pe    = mean(pe, na.rm = TRUE),
        avg_price = YOUR_CODE_HERE,
        avg_eps   = YOUR_CODE_HERE,
        n_firms   = YOUR_CODE_HERE,
        .groups   = "drop"
    )

Note

Group by gsubind and subind together. The code identifies the subindustry, and carrying the name along keeps the output readable.

(c) Using industry_year_summary, create a scatterplot that does all of the following:

only includes subindustries that have at least 4 firms
only includes subindustries with average P/E ratios less than 100
puts average P/E ratio on the x-axis
puts average share price on the y-axis
shows one point for each subindustry-year observation, with colour varying with subindustry
includes a line-of-best-fit describing the pattern in the data
includes a clear title and axis labels
uses a theme

industry_year_summary |>
    # keep only the subindustry-years you want to plot
    filter(n_firms >= YOUR_CODE, avg_pe < YOUR_CODE, !is.na(subind)) |>
    # put average P/E on the x-axis and average price on the y-axis
    ggplot(aes(x = YOUR_VARIABLE, y = YOUR_VARIABLE)) +
    # one point per subindustry-year, coloured by subindustry
    YOUR_CODE +
    # add a straight summary line
    YOUR_CODE +
    # add a title and axis labels
    YOUR_CODE +
    # use a theme
    YOUR_CODE

(d) What do you notice in the plot?

In a 2-3 sentences, comment on:

whether industry-year observations with higher average P/E ratios also tend to have higher average share prices
whether there are any clear clusters or outliers
what kinds of industry differences might help explain the pattern

Write your answer here