How to get rid of NA rows in a combined variable?

Question

The code is supposed to give an overall total after combining various fields within the data set.

I'm using browser to debug and I noticed that one of my data sets has a row that is just NA. The error I'm getting is caused by 'across ()' and spits out 'Problem while computing column 'Property Count'. I'm assuming that's because of an NA row in between the df set, hence why I want to delete the N/A row.

The difficulty is that it is a combined set. I want to include something like na.omit in the code but I'm not sure how to structure it. I tried adding it before, but it doesn't make sense since stats haven't been combined yet at that point, which would mean I'd have to do it during or after stats get combined.

total_stats = stats_combined %>% 
    rename(`Net Absorption` = `Net Absorption QTD - Total`, 
           `Net Absorption YTD` = `Net Absorption YTD - Total`,
           `Construction Deliveries` = `Construction Deliveries QTD`) %>% 
    filter(Submarket %in% submarket_order) %>% 
    mutate(Submarket = factor(Submarket, levels = submarket_order, ordered = T)) %>% 
    arrange(Submarket) %>% 
    # glimpse()
    mutate(across(c(`Direct Vacancy Rate`, `Overall Vacancy Rate`, `Overall Availability Rate`), scales::percent, accuracy = .1),
           across(any_of(sum_vars), 
                  scales::dollar, accuracy = 1, style_negative="parens", prefix=""),
           across(any_of(c("Full Service Gross Asking Rate", "Lease Rate")),
                  scales::dollar)) %>%
    select(Submarket, all_of(stat_order))
  market_stats_table(total_stats, 
                     cell_width = if_else(property_type == "Office", 1.05, .9), 
                     cell_height = if_else(property_type == "Office", .33, .27),
                     submarket_order,
                     totals)

In this case would how would I include something similar to omit the na rows after it's been combined?

Edit: The df would look like this

df = data.frame(
  a = c(4,NA,3),
  b = c(1,NA,3),
  c = c(2,NA,4),
  d = c(2,NA,1)
)

I don't this it has anything to do with column b/c this is the error I'm getting:

Error in `$<-.data.frame`(`*tmp*`, "call_text", value = c("global key_market_stats(...)",  : 
  replacement has 17 rows, data has 16

userJQdZ · Answer

Here's a solution using base R and apply, to transform your dataframe prior to your dplyr change.

We use "all" and "is.na" and then apply that across each row of our dataframe, which we designate with the "MARGIN=1" argument in apply.

Because technically we're then going to use a logical vector to subset the data.frame, we have to be sure to use the inverse of the results, i.e to remove all rows where all are NA not the other way around, so we start with "!" in the front to negate the output.

Example below.

df = data.frame(
      a = c(NA,1,NA),
      b = c(1,NA,NA),
      c = c(1,2,NA),
      d = c(NA,NA,NA)
)
    
df = df[which(!apply(X=df,MARGIN=1,FUN=function(x){all(is.na(x))})),]

df

It's hard to say without an example data frame but theoretically you could add this to your first filter argument too with something like

filter(Submarket %in% submarket_order & 
!apply(X=df,MARGIN=1,FUN=function(x){all(is.na(x))})) 
) %>%

How to get rid of NA rows in a combined variable?

1 Answers