CODEWITHSUNDEEP
rdplyrdata.tablenazero
Julia
Julia

Reputation: 31

How to replace columns that are ALL 0 with NA for a given year?

The following dataset has some years for which columns are populated either entirely or partially by zeroes. I am attempting to replace the columns that are all zero for a given year with NAs. The code I have tried below is included.

  year            tract Black MedianRent Moved in 2015 or later Moved in 2017 or later Built 2010 or later SNAP benefits Bachelor's degree
1 2018 Census Tract 401    21        919                      0                      0                   0         91831              2071
2 2018 Census Tract 402     0        882                      0                      0                   0         90117              1217
3 2018 Census Tract 403    37        749                      0                      0                   0         94611              1479
4 2018 Census Tract 404    85        908                      0                      0                   0         97322              2272
5 2018 Census Tract 405     5        810                      0                      0                   0         76444              1731
6 2018 Census Tract 406     0        946                      0                      0                   0        122761               581

years <- unique(final$year)
 
 final %>%
   group_by(year) %>%
   mutate_at(final[,c(2)], ~ if (all(. == 0)) . else NA_real_) %>%
   ungroup()

 years <- unique(final$year)
 bd1 <- data.frame()
 bd2 <- data.frame()
 
 for (i in years){
   bd1 <- final %>% 
   filter(year == i)
   bd1 <- bd1 %>% 
     na_if(all(0))
 
   bd2 <- smartbind(bd1,bd2)
 }
 
 
 final2 <- final %>%
   group_by(year) %>%
   mutate_at(vars(), ~ if (any(. != 0)) . else NA_real_) %>%
   ungroup()

Upvotes: 0

Views: 146

Answers (2)

Karthik S
Karthik S

Reputation: 11596

Does this work:

> library(dplyr)
> rent %>% group_by(year) %>% mutate(across(where(is.numeric), ~ replace(., sum(.) == 0, NA)))
# A tibble: 6 x 9
# Groups:   year [1]
   year tract       Black MedianRent `Moved in 2015 or l~ `Moved in 2017 or l~ `Built 2010 or la~ `SNAP benefits` `Bachelor's degr~
  <dbl> <chr>       <dbl>      <dbl>                <dbl>                <dbl>              <dbl>           <dbl>             <dbl>
1  2018 Census Tra~    21        919                   NA                   NA                 NA           91831              2071
2  2018 Census Tra~     0        882                   NA                   NA                 NA           90117              1217
3  2018 Census Tra~    37        749                   NA                   NA                 NA           94611              1479
4  2018 Census Tra~    85        908                   NA                   NA                 NA           97322              2272
5  2018 Census Tra~     5        810                   NA                   NA                 NA           76444              1731
6  2018 Census Tra~     0        946                   NA                   NA                 NA          122761               581
>

Data used:

structure(list(year = c(2018, 2018, 2018, 2018, 2018, 2018), 
    tract = c("Census Tract 401", "Census Tract 402", "Census Tract 403", 
    "Census Tract 404", "Census Tract 405", "Census Tract 406"
    ), Black = c(21, 0, 37, 85, 5, 0), MedianRent = c(919, 882, 
    749, 908, 810, 946), `Moved in 2015 or later` = c(0, 0, 0, 
    0, 0, 0), `Moved in 2017 or later` = c(0, 0, 0, 0, 0, 0), 
    `Built 2010 or later` = c(0, 0, 0, 0, 0, 0), `SNAP benefits` = c(91831, 
    90117, 94611, 97322, 76444, 122761), `Bachelor's degree` = c(2071, 
    1217, 1479, 2272, 1731, 581)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389155

Try the following :

library(dplyr)

final %>%
  group_by(year) %>%
  mutate(across(-2, ~if (all(. == 0)) NA_real_ else .)) %>%
  #With mutate_at
  #mutate_at(-2, ~if (all(. == 0)) NA_real_ else .) %>%
  ungroup()

This replace column values for given year with NA if all the values are 0.

Upvotes: 1

Related Questions