Condense Dataframe

user_10065413 0
Viewed 81

How can I condense this dataframe:

Date    AAPL   MSFT NASDAQ
1.1.19  NA     NA   NA
2.1.19  2%     NA   5%
3.1.19  3%     NA   6%
...
1.1.19  NA     NA   NA
2.1.19  NA     4%   5%
3.1.19  NA     5%   6%
...

to look like:

Date    AAPL   MSFT NASDAQ
1.1.19  NA     NA   NA
2.1.19  2%     4%   5%
3.1.19  3%     5%   6%
...

Is there a function to not have duplicate dates and NASDAQ values, removes the NAs?

Thanks!

data-cleaning na dataframe r
2 Answers

We can use summarise_all (assuming that there are only one non-NA element per 'Date' for each column)

library(dplyr)
df %>%
   group_by(Date) %>%
   summarise_all(na.omit)

If we have more than one non-NA element and also have some cases with only NAs, either create a list column or paste

df %>%
    group_by(Date) %>%
    summarise_at(vars(-group_cols()), ~ list(if(all(is.na(.))) .[n() + 1] else .[!is.na(.)]))
# A tibble: 3 x 4
#  Date   AAPL      MSFT      NASDAQ   
#  <chr>  <list>    <list>    <list>   
#1 1.1.19 <chr [1]> <chr [1]> <chr [1]>
#2 2.1.19 <chr [1]> <chr [1]> <chr [2]>
#3 3.1.19 <chr [1]> <chr [1]> <chr [2]>

Also, if some elements are repeated,then we take the unique and assuming that there are no completely distinct elements per group

df %>%
    group_by(Date) %>%
    summarise_at(vars(-group_cols()), ~ if(all(is.na(.))) .[n() + 1] else unique(.[!is.na(.)]))
# A tibble: 3 x 4
#  Date   AAPL  MSFT  NASDAQ
#  <chr>  <chr> <chr> <chr> 
#1 1.1.19 <NA>  <NA>  <NA>  
#2 2.1.19 2%    4%    5%    
#3 3.1.19 3%    5%    6%

Or do the distinct first and then do a group by operation

distinct(df) %>% 
      group_by(Date) %>% 
      summarise_at(vars(-group_cols()), ~ .[!is.na(.)][1])
# A tibble: 3 x 4
#  Date   AAPL  MSFT  NASDAQ
#  <chr>  <chr> <chr> <chr> 
#1 1.1.19 <NA>  <NA>  <NA>  
#2 2.1.19 2%    4%    5%    
#3 3.1.19 3%    5%    6%

Or in the devel version of dplyr, we can use condense

df %>% 
     group_by(Date) %>% 
     condense(data = across(everything(),  ~ .[!is.na(.)]))
# A tibble: 3 x 2
# Rowwise:  Date
#  Date   data            
#  <chr>  <list>          
#1 1.1.19 <tibble [0 × 3]>
#2 2.1.19 <tibble [2 × 3]>
#3 3.1.19 <tibble [2 × 3]>

data

df <- structure(list(Date = c("1.1.19", "2.1.19", "3.1.19", "1.1.19", 
"2.1.19", "3.1.19"), AAPL = c(NA, "2%", "3%", NA, NA, NA), MSFT = c(NA, 
NA, NA, NA, "4%", "5%"), NASDAQ = c(NA, "5%", "6%", NA, "5%", 
"6%")), class = "data.frame", row.names = c(NA, -6L))
user_37322710

Here are some base R solutions:

  • Using split + rbind
dfout <- do.call(rbind,
                 c(make.row.names = F,
                   Map(function(z) data.frame(Map(function(x) ifelse(all(is.na(x)),NA,na.omit(x)),z)),
                       split(df,df$Date))))
  • Using ave + unique
dfout <- unique(cbind(df[1],
                      sapply(df[-1],
                             function(q) ave(q,df$Date,FUN = function(x) ifelse(all(is.na(x)),NA,na.omit(x))))))

such that

> dfout
    Date AAPL MSFT NASDAQ
1 1.1.19 <NA> <NA>   <NA>
2 2.1.19   2%   4%     5%
3 3.1.19   3%   5%     6%

DATA

df <- structure(list(Date = c("1.1.19", "2.1.19", "3.1.19", "1.1.19", 
"2.1.19", "3.1.19"), AAPL = c(NA, "2%", "3%", NA, NA, NA), MSFT = c(NA, 
NA, NA, NA, "4%", "5%"), NASDAQ = c(NA, "5%", "6%", NA, "5%", 
"6%")), class = "data.frame", row.names = c(NA, -6L))
user_121587570
Related