Home R: Change value in one column to a value based on another column if NA in one column

Questions

R: Change value in one column to a value based on another column if NA in one column

May 29, 2022

I have the following data:

structure(list(Date = c("01.08.2018", "02.08.2018", "03.08.2018", 
"04.08.2018", "31.08.2018", "06.04.2019", "07.04.2019", "08.04.2019", 
"01.08.2018", "02.08.2018", "03.08.2018", "04.08.2018", "06.04.2019", 
"07.04.2019", "08.04.2019", "01.08.2018", "02.08.2018", "03.08.2018", 
"04.08.2018", "05.08.2018", "07.04.2019", "30.04.2019"), Name = c("A", 
"A", "A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", "B", 
"B", "C", "C", "C", "C", "C", "C", "C"), Rating = c(1L, 1L, 1L, 
3L, 3L, 4L, 4L, 4L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 3L, 3L, 3L, 
5L, 5L, 5L), Size = c(1234L, 24123L, 23L, 1L, 23L, 3L, 23L, 4L, 
323L, 3424L, 523L, 234L, 35L, 354L, 45L, 23L, 46L, 456L, 546L, 
24L, 134L, 1L), Company = c("hello", "hello", "hello", "", "", 
"bonjour", "bonjour", "bonjour", "", "", "hallo", "hallo", "hallo", 
"hallo", "", "", "hallo", "hallo", "hallo", "", "", "hallo")), class = "data.frame", row.names = c(NA, 
-22L))

First, I would like to add the sum of the column Size for each Company. For that I have the following code:

Data <- Data %>%
  group_by(Company, Date) %>%
  dplyr:: mutate(Sum_Size = sum(Size))

Now with this code, R treats the NA values in the column Company as one group. However, I don’t want this to be one group. If the column Company is NA, then I would like to have the value of the column Size to be in the Sum_Size Column.

For that I have the following code:

Test <- Data %>%
  dplyr:: mutate(Sum_Size=replace(Sum_Size, is.na(Company), Size))

However, the problem now is that with the code above, e.g. in row 9 Sum_Size is still the same as before and not equal to the value in column Size. What do I need to adjust in the code to achieve my desired outcome?

>Solution :

A possible solution:

library(tidyverse)

df %>%
  filter(Company != "") %>% 
  group_by(Company) %>% 
  mutate(Sum_Size = sum(Size)) %>% 
  bind_rows(df %>% filter(Company == "") %>% mutate(Sum_Size = Size)) 

#> # A tibble: 22 × 6
#> # Groups:   Company [4]
#>    Date       Name  Rating  Size Company   Sum_Size
#>    <chr>      <chr>  <int> <int> <chr>        <int>
#>  1 01.08.2018 A          1  1234 "hello"      25380
#>  2 02.08.2018 A          1 24123 "hello"      25380
#>  3 03.08.2018 A          1    23 "hello"      25380
#>  4 06.04.2019 A          4     3 "bonjour"       30
#>  5 07.04.2019 A          4    23 "bonjour"       30
#>  6 08.04.2019 A          4     4 "bonjour"       30
#>  7 03.08.2018 B          2   523 "hallo"       2195
#>  8 04.08.2018 B          2   234 "hallo"       2195
#>  9 06.04.2019 B          2    35 "hallo"       2195
#> 10 07.04.2019 B          1   354 "hallo"       2195
#> 11 02.08.2018 C          3    46 "hallo"       2195
#> 12 03.08.2018 C          3   456 "hallo"       2195
#> 13 04.08.2018 C          3   546 "hallo"       2195
#> 14 30.04.2019 C          5     1 "hallo"       2195
#> 15 04.08.2018 A          3     1 ""               1
#> 16 31.08.2018 A          3    23 ""              23
#> 17 01.08.2018 A          3   323 ""             323
#> 18 02.08.2018 B          3  3424 ""            3424
#> 19 08.04.2019 B          1    45 ""              45
#> 20 01.08.2018 C          1    23 ""              23
#> 21 05.08.2018 C          5    24 ""              24
#> 22 07.04.2019 C          5   134 ""             134