Conditional calculation of new variable based on specific groups of rows, row values and columns in R dataframe

Question

Example Data

I have got the following survey dataset in R and need help with the conditional calculation of a specific new variable.

# Load package
library(tidyverse)

# Important: set seed for replicability
set.seed(123)

# Create data: step 1
df <- tibble(
  country = c(rep("A", 10), rep("B", 10)),
  respondent_id = 1:20,
  vote_choice = c(sample(c("PartyA", "PartyB", "PartyC"), 10, replace = TRUE),
                  sample(c("PartyD", "PartyE", "PartyF"), 10, replace = TRUE)),
  ptv_1 = runif(20, min = 0, max = 1) %>% round(., 3),
  ptv_2 = runif(20, min = 0, max = 1) %>% round(., 3),
  ptv_3 = runif(20, min = 0, max = 1) %>% round(., 3)
)

# Create data: step 2
df <- df %>% 
  group_by(vote_choice, country) %>%
  summarize(across(starts_with("ptv"), \(x) mean(x, na.rm = TRUE))) %>%
  pivot_longer(cols = starts_with("ptv"), names_to = "party_to_ptv", values_to = "average_value") %>%
  group_by(vote_choice, country) %>%
  slice_max(order_by = average_value) %>%
  ungroup() %>%
  mutate(average_value = NULL) %>%
  right_join(., df, by = c("vote_choice", "country"))

# Inspect data
df

# A tibble: 20 × 7
   vote_choice country party_to_ptv respondent_id ptv_1 ptv_2 ptv_3
                                
 1 PartyA      A       ptv_2                   10 0.691 0.799 0.710 
 2 PartyB      A       ptv_3                    4 0.544 0.233 0.810 
 3 PartyB      A       ptv_3                    6 0.289 0.266 0.794
 4 PartyB      A       ptv_3                    7 0.147 0.858 0.440 
 5 PartyB      A       ptv_3                    8 0.963 0.046 0.754
 6 PartyC      A       ptv_1                    1 0.994 0.369 0.274
 7 PartyC      A       ptv_1                    2 0.656 0.152 0.815
 8 PartyC      A       ptv_1                    3 0.709 0.139 0.449
 9 PartyC      A       ptv_1                    5 0.594 0.466 0.812
10 PartyC      A       ptv_1                    9 0.902 0.442 0.629
11 PartyD      B       ptv_3                   13 0.478 0.207 0.220 
12 PartyD      B       ptv_3                   16 0.318 0.895 0.352
13 PartyD      B       ptv_3                   19 0.415 0.095 0.668
14 PartyD      B       ptv_3                   20 0.414 0.384 0.418
15 PartyE      B       ptv_1                   11 0.795 0.122 0.001
16 PartyE      B       ptv_1                   12 0.025 0.561 0.475
17 PartyE      B       ptv_1                   14 0.758 0.128 0.380 
18 PartyF      B       ptv_2                   15 0.216 0.753 0.613
19 PartyF      B       ptv_2                   17 0.232 0.374 0.111
20 PartyF      B       ptv_2                   18 0.143 0.665 0.244

Information on the variables:

country encompasses 2 countries in my example df which each contain 10 respondents and a set of 3 distinct political parties respondents got to choose between at the last election (the real data also contain a variable year which I did not include for the sake of simplicity)
respondent_id refers to the respondent in the survey dataset and demonstrates that the dataset is at respondent-level but can otherwise be ignored
vote_choice denotes by name the party the respondent voted for at the last election
ptv_1, ptv_2, and ptv_3 indicate for each party that is available the leaning of each respondent to this party (in the real data, respondents of course lean more strongly to the party they voted for); scale: 0-1
party_to_ptv is a conversion list that indicates which party in vote_choice corresponds to which ptv_* column

Problem Description

I now need to calculate a set of (3) new variables called electoral_opportunites_* where the asterisk is a placeholder for 1-3 refering to the three PTVs. The idea is to calculate the changes parties have of gaining new voters based on the favorable leaning of other parties' voters.

To do so, I need to calculate: 1 - (sqrt(PTV of party voted for) - sqrt(PTV of other party)), the idea of which is to set the strength of support of one's own party in relation to a new party. For example, if a respondent strongly supports their own party, A, by PTV = 1.0, it doesn't really matter that much that they also lean to B by PTV = 0.4.

My problem with the calculation is the conditionality: I need to find rowwise for each respondent the PTV column value that corresponds to their party of choice (which may not be the highest PTV value in the row), and then subtract from it the square-rooted value of another column.

Manually, I would do it as follows for the example df.

Expected Outcome (for electoral_opportunities_1)

df %>% 
  mutate(electoral_potential_1 = 
           # Subtract: PTV (party voted for) - PTV (PTV column 1)...
           c(1 - ( sqrt(0.799) - sqrt(0.691) ),
             1 - ( sqrt(0.810) - sqrt(0.544) ),
             1 - ( sqrt(0.794) - sqrt(0.289) ),
             1 - ( sqrt(0.440) - sqrt(0.147) ),
             1 - ( sqrt(0.754) - sqrt(0.963) ),
             NA, # ...unless they are both the same.
             NA,
             NA,
             NA,
             NA,
             1 - ( sqrt(0.220) - sqrt(0.478) ),
             1 - ( sqrt(0.352) - sqrt(0.318) ),
             1 - ( sqrt(0.668) - sqrt(0.415) ),
             1 - ( sqrt(0.418) - sqrt(0.414) ),
             NA,
             NA,
             NA,
             1 - ( sqrt(0.753) - sqrt(0.216) ),
             1 - ( sqrt(0.374) - sqrt(0.232) ),
             1 - ( sqrt(0.665) - sqrt(0.143) )) ) -> df

df

As a minor detail, I would afterwards check if there are any values > 1 and cap them at 1, which means that if respondents are leaning more strongly to a party they did not actually vote for, said party will receive the highest score (1) in terms of its eletoral changes to pursuade that voter.

df %>% 
  mutate(electoral_opportunities_1 = ifelse(electoral_opportunities_1 > 1, 1, electoral_opportunities_1)) -> df

I cannot do all of this by hand. Hence I would be grateful for an efficient and tidy solution to calculate the electoral opportunities for individual PTV columns. I have tried many different approaches, including pivoting the df, none of which have worked so far. Taken together, the process is:

Take the value of the PTV column that corresponds to vote_choice.
Subtract from this value the value of a given PTV column to calculate that party's electoral opportunities.
Unless both parties are the same, in which case set the value to NA.
Then, check if there are any values > 1 and cap them at 1.

EDIT

I just noticed that in the final df I would of course need the average electoral opportunity for each party in vote_choice, instead of three separate columns!

Dr. Fabian Habersack · Accepted Answer

Okay, it is apparently more straight forward than I initially thought. Here is what I have done to solve the biggest part of the problem.

library(tidyverse)

df %>% 
  mutate(ptv_v = case_when(party_to_ptv == "ptv_1" ~ ptv_1,
                                    party_to_ptv == "ptv_2" ~ ptv_2,
                                    party_to_ptv == "ptv_3" ~ ptv_3,
                                    T ~ NA_real_)) %>% 
  mutate(electoral_opportunity_1 = ifelse(ptv_1 > ptv_v, 1, 1 - (sqrt(ptv_v) - sqrt(ptv_1)) ) %>% ifelse(party_to_ptv == "ptv_1", NA, .),
         electoral_opportunity_2 = ifelse(ptv_2 > ptv_v, 1, 1 - (sqrt(ptv_v) - sqrt(ptv_2)) ) %>% ifelse(party_to_ptv == "ptv_2", NA, .),
         electoral_opportunity_3 = ifelse(ptv_3 > ptv_v, 1, 1 - (sqrt(ptv_v) - sqrt(ptv_3)) ) %>% ifelse(party_to_ptv == "ptv_3", NA, .) ) -> df

Now I just need to get the average electoral opportunity for each party in vote_choice. It's tricky and I'm still triyng to figure out what I actually want. This is all a bit clumsy but I think it does what I want:

df %>%
  mutate(opportunity = case_when(is.na(electoral_opportunity_1) ~ mean(electoral_opportunity_1, na.rm = T),
                                 is.na(electoral_opportunity_2) ~ mean(electoral_opportunity_2, na.rm = T),
                                 is.na(electoral_opportunity_3) ~ mean(electoral_opportunity_3, na.rm = T),
                                 T ~ NA_real_)) -> df

df %>% 
  group_by(vote_choice, country) %>%
  summarize(opportunity = mean(opportunity, na.rm = T))

  vote_choice country opportunity
                  
1 PartyA      A             0.706
2 PartyB      A             0.779
3 PartyC      A             0.830
4 PartyD      B             0.779
5 PartyE      B             0.830
6 PartyF      B             0.706

Conditional calculation of new variable based on specific groups of rows, row values and columns in R dataframe

Answers (2)

Related Questions