1

I have a large table, called RobertsHemp2019_20, that looks something like this:

'FileName' 'CBDV-a' 'CBDV' 'CBD-a'
Roberts NA NA NA
NA 413847 NA NA
NA NA 208320 NA
NA NA NA 118807

and I want to collapse the rows and remove the NA's so that it will look like this:

'FileName' 'CBDV-a' 'CBDV' 'CBD-a'
Roberts 413347 208320 118807

Each row has just one character string or value. I think I want to apply a function to each of the columns to remove the NA's (as was tried here https://stackoverflow.com/a/47357833/15779611). But I am new to R and can't get passed the collapse_column <- function(RobertsHemp2019_20,col){RobertsHemp2019_20[!is.na(RobertsHemp2019_20[,col]),col]} command.

dan1st
  • 12,568
  • 8
  • 34
  • 67
kelly gude
  • 11
  • 1
  • It would be better if you could share a reproducible piece of your data by means of `dput(head(data))` , so other can use it to help you. – Anoushiravan R Apr 27 '21 at 21:57

1 Answers1

1

This code works for your sample data set but you need to provide a larger sample data so that solutions become more generalized. In this sample data you have 4 rows of data and 4 columns where there is only one single non-NA value. So It is possible to collapse all four rows into a single one, but in a larger data set as the lengths of NA and non-NA values in each column differ the output may not necessarily be a single row.

library(dplyr)

df %>%
  summarise(across(everything(), ~ na.omit(.x)))

# A tibble: 1 x 4
  FileName `CBDV-a`   CBDV `CBD-a`
  <chr>       <dbl>  <dbl>   <dbl>
1 Roberts    413847 208320  118807
Anoushiravan R
  • 21,622
  • 3
  • 18
  • 41
  • And as I've seen some other similar questions, I guess for a larger data set a grouping may be needed. – Anoushiravan R Apr 27 '21 at 22:09
  • 1
    Anoushiravan R - The example I gave, only includes one 'FileName' with the values for each compound denoted in the columns. I should have specified, but in the actual data set- there are many 'FileNames'. So I cannot collapse the whole data set into one row, and will possibly require grouping. However, there will only be one non-NA value per row. – kelly gude Apr 28 '21 at 21:13
  • Yes that was certainly going to be the case. If you needed more help perhaps you should share more of your data set that accounts for its diversity to some extent. – Anoushiravan R Apr 28 '21 at 21:16