Multiplying Two Tables in R

Question

I have been having some issues trying to do some financial forecasting for my University Project. I am very new to R and still learning the basics.

As you can maybe able to see there are two tables Finance data and Stress data . Was trying to multiply each other , example for Direct In data in finance to be multiplied by AC23 stress % by year.

While i can just multiply each other using simple multiplication rules i do not know how to reference the year and the stress scenario ( ie finance data in 2023 multiplied by the stress % in 2023 )

Can some one please help!!

Finance Data

Stress Data

I tried multiplying just the tables with each other but that doesn't help i need to reference the year and stress scenario for each individual cell.

Answer 1

General overview of what you want to do:

1. Load the two tables into R with the appropriate method for the file type.
2. Then you want to transpose the dataframes (i.e., your rownames should become column names and your column names should become attributes in a row. This makes it easier to join the two dataframes together.
3. Join the two dataframes together by year
4. Transform your percentages to decimal factors.
5. Create new columns for your desired multiplications

How this would look in the tidyverse:

# import tidyverse
library(tidyverse)

# Read the files (You will need to provide the proper path)
finance_data <- read_csv("finance.csv")
stress_data <- read_csv("stress.csv")

# Transpose your tables
finance_trans <- pivot_longer(finance, cols = -c("Income"), names_to = "Year") %>% 
    pivot_wider(names_from = "Income", values_from = "value")

stress_trans <- pivot_longer(stress, cols = -c("Scenario Ref"), names_to = "Year") %>% 
    pivot_wider(names_from = "Scenario Ref", values_from = "value")

# Join the tables
finance_stress_joined <- finance_trans %>% 
    inner_join(stress_trans, by = "Year")

# Calculate your desired rows with the mutate statement
results <- finance_stress_joined %>% 
    mutate(direct_in_by_tt1 = `Direct In` * as.numeric(gsub("%", "", `TT1`)) / 100)

Note that there are a couple of gotchas and things to consider.

1. Depending on the filetype or structure of your tables you might need to use read_csv2, or another filereader altogether
2. Transposing your tables is a combination of pivot_wider and pivot_longer; as far as I know there is no specific function in tidyverse to do this. Use ?pivot_longer in the console to get a documentation of the function. You might need to use pivot_wider and pivot_longer a little bit to get a feel for what they do.
3. Since the resulting column names from pivoting your tables have whitespace " " you have to use `table name` to refer to them. You can use names_repair = "universal" in pivot_wider to ensure column names don't have whitespace. This can make it easier to work with the tables.
4. You need to transform your percentage columns to a decimal factor. To do so you first strip the '%? with gsub and then transform the string to numeric and divide it by 100. I decided to do this inline. Although it could make sense to use mutate to create a decimal_factor column.