3

I am using gapminder data to subtract values of 1 country gdpPercapita from rest of the countries.

I have referred to the link on same topic but still getting issues. Subtract a column in a dataframe from many columns in R)

Issue When I subtract a column (eg. India) from all other countries then in return I am getting India as 0 values (which is correct) but it didn't subtract India from other columns for example Vietnam.

Pivoting data for comparing India, Vietnam before Subtraction

  gapminder %>% 
    select(country, year, gdpPercap) %>% 
    pivot_wider(names_from = country, values_from = gdpPercap) %>% 
    arrange(year) %>% 
    select(year,India,Vietnam)  

year     India      Vietnam
<int>    <dbl>      <dbl>

1952    546.5657    605.0665        
1957    590.0620    676.2854        
1962    658.3472    772.0492        
1967    700.7706    637.1233        
1972    724.0325    699.5016        
1977    813.3373    713.5371        
1982    855.7235    707.2358

Comparing India, Vietnam after Subtraction

  gapminder %>% 
    select(country, year, gdpPercap) %>% 
    pivot_wider(names_from = country, values_from = gdpPercap) %>% 
    arrange(year) %>% 
    mutate_at(vars(-matches("year")), ~ . - India) %>% 
    select(year,India,Vietnam)

year     India      Vietnam
<int>    <dbl>      <dbl>

1952    0         605.0665      
1957    0         676.2854      
1962    0         772.0492      
1967    0         637.1233      
1972    0         699.5016      
1977    0         713.5371      
1982    0         707.2358

I am not sure what is wrong with the code ?

Appreciate any help !!

ViSa
  • 1,563
  • 8
  • 30
  • 2
    across seems to work fine for me with `mutate(across(-1, ~. - India))` or `mutate(across(-matches('year'), ~. - India))`. Both return the expected output. – Ronak Shah Sep 27 '20 at 00:28
  • @RonakShah Yes, that works perfectly and seems to be the easiest to apply out of all options. – ViSa Sep 27 '20 at 07:58
  • I've tried it as well, but it only works now for me after re-installing `tidyverse`. I would de-accept my answer as clearly @RonakShah one should be the right solution in the light of new developments in `dplyr`. – arg0naut91 Sep 27 '20 at 08:17
  • 1
    @arg0naut91 you can include that in your answer. – Ronak Shah Sep 27 '20 at 08:40

1 Answers1

4

It is a behavior of mutate_at, you could switch to across (as suggested by @RonakShah) and do:

gapminder %>% 
  select(country, year, gdpPercap) %>% 
  pivot_wider(names_from = country, values_from = gdpPercap) %>% 
  arrange(year) %>% 
  mutate(across(-matches('year'), ~  . - India)) %>%
  select(year, India, Vietnam)

With mutate_at, you would need to make sure that the column used for calculation is the last one in your data - you could use relocate to move it, like below:

gapminder %>% 
  select(country, year, gdpPercap) %>% 
  pivot_wider(names_from = country, values_from = gdpPercap) %>% 
  arrange(year) %>% 
  relocate(India, .after = last_col()) %>%
  mutate_at(vars(-matches('year')), ~ . - India) %>%
  select(year, India, Vietnam)

Output:

# A tibble: 12 x 3
    year India Vietnam
   <int> <dbl>   <dbl>
 1  1952     0    58.5
 2  1957     0    86.2
 3  1962     0   114. 
 4  1967     0   -63.6
 5  1972     0   -24.5
 6  1977     0   -99.8
 7  1982     0  -148. 
 8  1987     0  -156. 
 9  1992     0  -175. 
10  1997     0   -72.9
11  2002     0    17.7
12  2007     0   -10.6
arg0naut91
  • 14,574
  • 2
  • 17
  • 38
  • do you mean that since India got 0 by subtracting from itself so countries after India had only 0 subtracting from it ? – ViSa Sep 26 '20 at 19:48
  • Exactly, it operated with 0 – arg0naut91 Sep 26 '20 at 19:49
  • No problem - I've also added another option, basically you can also `relocate` India column to the end and then others will not be affected. – arg0naut91 Sep 26 '20 at 19:52
  • yes thats a great option actually it suits to what i want but I tried another thing as well `-match("India")` ```{r} gapminder %>% select(country, year, gdpPercap) %>% pivot_wider(names_from = country, values_from = gdpPercap) %>% arrange(year) %>% # map_dbl( ~{.x - India }) mutate_at(vars(-matches("year"),-matches("India")), ~ . - India) %>% select(year,India,Vietnam) ``` – ViSa Sep 26 '20 at 19:55
  • Indeed, as mentioned you can also exclude it (in this case it will not be 0 though) – arg0naut91 Sep 26 '20 at 19:57
  • 1
    yes, that why I am gonna keep the options that you suggested. Thanks again !! – ViSa Sep 26 '20 at 19:59