Moving from Excel to R: How to manipulate data based on specific cell-values?

Question

I am slowly moving from Excel to R but keep running into problems with tasks that would take me two seconds to do in Excel... For instance, see the following example of data which is GDP in France and UK:

Let imagine I would like to calculated percent change from the 1929, which is the Great Depression. In Excel I would do something like this in a new column for France: =(B2/$B$11)*100 and then fill the formula down to the adjacent cells. And then, repeat for UK.

How would you do this in R (note, this is just an example. I am interested in the thought process behind)? Obviously, the data would be structured differently with three variables: year, country, gdp.

I am thinking about using mutate() and then case_when() to pin point the right country. But this is where I get stuck. Have a look at my code. Data is Maddison:

library(tidyverse)
library(ggplot2)
library(haven)
library(readxl)

# Loading df
df <- read_excel("/PATH TO DATA/mpd2018.xlsx", sheet = 2)

# Tidy dataset
df <- df %>%
  transmute(
    cntry = as_factor(countrycode), # Rename and define as factor
    year = zap_labels(year), # Zap labels
    gdp = zap_labels(rgdpnapc) # Rename and zap labels
  ) %>%
  dplyr::filter(
    cntry %in% c("FRA","GBR"), # Keep only FRA and GRB
    year >= 1920 & year <= 1950 # Only the interval between 1920 and 1950
  )

# Calculations 
 df <- df %>% mutate(
              gdp_rel = case_when(
                cntry == "FRA" ~ (df$gdp/df[10,3])*100,
                cntry == "GBR" ~ (df$gdp/df[41,3])*100
              ))

First of all, the code produces an error. But more importantly, I believe it can be done smarter than pin-pointing by df[x, y]. What is the data frame was much bigger?

user_3962914 · Answer

Once you get hold off the syntax R is usually more scalable and easier to do such tasks.

You can use across to apply a function to multiple columns. Here in across we mention which columns we want to apply the function (France, UK) and then mention what function we want to apply.

library(dplyr)
df %>% mutate(across(c(France, UK), ~./.[Year == 1929] * 100))

user_10782538 · Answer

There are myriad ways to achieve your desired result. Here's 2 different options.

library(tidyverse)
# Seed for reproducibility
set.seed(1234) 
# Example data
data <- data.frame(Year = 1920:1939, 
                   France = 1920:1939 * 3 + rnorm(1939 - 1920 + 1, 5, 10), 
                   Germany = 1920:1939 * 3.5 + rnorm(1939 - 1920 + 1, 2, 18))
row_id <- which(data$Year == 1929)
# dplyr. Note that "across" performs caclulation across all columns 
# selected in the first argument
data %>% 
  mutate(across(-Year, # All columns except for year 
                #Row 10 (row_id) has year = 1929
                ~ . / .[[row_id]] * 100, 
                # Add column name to new transformed result.
                .names = '{.col}_return')) 


# Manual way
res <- list()
for(i in names(data)[-1]){
  # Manual mutate
  res[[paste0(i, '_return')]] <- data[[i]] / data[10, i] * 100
}
# Combine result
cbind(data, res)

both of which yield the following result (on the simulated data):

   Year   France  Germany France_return Germany_return
1  1920 5752.929 6724.414      99.47830       99.81832
2  1921 5770.774 6716.668      99.78687       99.70334
3  1922 5781.844 6721.070      99.97830       99.76869
4  1923 5750.543 6740.773      99.43704      100.06115
5  1924 5781.291 6723.513      99.96873       99.80495
6  1925 5785.061 6713.432     100.03391       99.65531
7  1926 5777.253 6753.346      99.89889      100.24779
8  1927 5780.534 6728.074      99.95563       99.87266
9  1928 5783.355 6749.728     100.00442      100.19408
10 1929 5783.100 6736.653     100.00000      100.00000
11 1930 5790.228 6776.841     100.12326      100.59656
12 1931 5788.016 6751.939     100.08502      100.22691
13 1932 5793.237 6751.230     100.17530      100.21639
14 1933 5804.645 6758.477     100.37255      100.32397
15 1934 5816.595 6741.676     100.57919      100.07457
16 1935 5808.897 6753.483     100.44608      100.24983
17 1936 5807.890 6738.759     100.42867      100.03127
18 1937 5806.888 6757.362     100.41134      100.30741
19 1938 5810.628 6779.703     100.47602      100.63904
20 1939 5846.158 6780.114     101.09040      100.64514

long vs wide

As per comments by SnupSnurre, I am here providing an example of how this could be done assuming the data was stored in a "long" format (vertically).


# Use pivot_longer to make wide data long
data_long <- pivot_longer(data, 
                          -Year, 
                          names_to = 'Country')

# Calculate on long format:
(return_1929 <- data_long %>% 
    # Group by country, calculations will be done for each country
  group_by(Country) %>% 
    # Perform the actual calculations
  mutate(value_return = value / value[Year == 1929] * 100) %>%
    # Remove the country grouping
  ungroup()
)
# Return to wide format
return_1929 %>% 
  pivot_wider(id_cols = Year, 
              # Column to "expand" to a wide format.
              names_from = Country,
              # Coluns to get values from
              values_from = c(value, value_return),
              )

user_10276092 · Answer

In Excel I would do something like this in a new column for France: =(B2/$B$11)*100 and then fill the formula down to the adjacent cells. And then, repeat

I went from Excel to R also. Using @Oliver 's data I would (bruteforce) implement your formula like this:

set.seed(1234) 
data <- data.frame(Year = 1920:1939, 
                   France = 1920:1939 * 3 + rnorm(1939 - 1920 + 1, 5, 10), 
                   Germany = 1920:1939 * 3.5 + rnorm(1939 - 1920 + 1, 2, 18))

data$FranceP <- data$France/data[data$Year == 1929, "France"]*100
data$GermanyP <- data$Germany/data[data$Year == 1929, "Germany"]*100

data

 Year   France  Germany   FranceP  GermanyP
1  1920 5752.929 6724.414  99.47830  99.81832
2  1921 5770.774 6716.668  99.78687  99.70334
3  1922 5781.844 6721.070  99.97830  99.76869
4  1923 5750.543 6740.773  99.43704 100.06115
5  1924 5781.291 6723.513  99.96873  99.80495
6  1925 5785.061 6713.432 100.03391  99.65531
7  1926 5777.253 6753.346  99.89889 100.24779
8  1927 5780.534 6728.074  99.95563  99.87266
9  1928 5783.355 6749.728 100.00442 100.19408
10 1929 5783.100 6736.653 100.00000 100.00000
11 1930 5790.228 6776.841 100.12326 100.59656
...

Moving from Excel to R: How to manipulate data based on specific cell-values?

3 Answers

long vs wide