How can I remove rows and columns with unwanted 0's r?

user_18121725 0
Viewed 63

I have the following test df:

df <- data.frame(Block = c('1' , '1' , '1' , '2' , '2' , '2' , '3' , '3' , '3') ,
                 A  = c('1' , '0' , '0', '4' , '0', '0' , '1' , '0' , '0') ,
                B  = c('0' , '1' , '0' , '0' , '4' , '0', '0', '6' , '0') ,
                  C = c('0' , '0' , '1' , '0' , '0' , '4' , '0' , '0' , '5'))

I'd like to remove the unnecessary 0's so that the A, B, and C only have values >0.

The output would be:

df1 <- data.frame(Block = c('1' , '2' , '3') ,
                  A = c('1' , '4' , '1') ,
                  B = c('1' , '4' , '6') ,
                  C = c('1' , '4' , '5'))
dplyr r
4 Answers

We could do a group by order so that all 0 values will be at the tail end for each column and then use filter to remove rows having only 0's

library(dplyr)
df %>% 
   group_by(Block) %>% 
  mutate(across(everything(),  ~.x[order(.x ==0)])) %>% 
  filter(if_any(everything(), ~ .x != 0)) %>% 
  ungroup

-output

# A tibble: 3 × 4
  Block A     B     C    
  <chr> <chr> <chr> <chr>
1 1     1     1     1    
2 2     4     4     4    
3 3     1     6     5
user_37322710

You could extract non-zero values in each column by groups.

df %>%
  group_by(Block) %>%
  summarise(across(everything(), ~ .x[.x != 0]))

# # A tibble: 3 × 4
#   Block A     B     C    
#   <chr> <chr> <chr> <chr>
# 1 1     1     1     1    
# 2 2     4     4     4    
# 3 3     1     6     5
user_100689850

We could use sum after transforming character to integer with type.convert(as.is = TRUE):

library(dplyr)

df %>% 
  group_by(Block) %>% 
  type.convert(as.is = TRUE) %>% 
  summarise(across(everything(), sum))

  Block     A     B     C
  <int> <int> <int> <int>
1     1     1     1     1
2     2     4     4     4
3     3     1     6     5
user_133216470

Base R option where first convert all zeros to NA and use aggregate to merge the rows per group like this:

df[df == 0] <- NA
aggregate(. ~ Block, data=df, FUN=na.omit, na.action="na.pass")
#>   Block A B C
#> 1     1 1 1 1
#> 2     2 4 4 4
#> 3     3 1 6 5

Created on 2022-08-25 with reprex v2.0.2

user_142827140
Related