Unable to convert "NULL" to either zero or NA

Question

[Cancer]

https://1drv.ms/u/s!AmF4i5owawoHg1PzaewyF2npZ69Q

The link for the excel file I upload in R ^

I am trying to add values in columns by using the "sum" function but get the error

full code including libraries

library(tidyverse)
library(magrittr)

my_data <- read.table(file = "clipboard", 
                      sep = "\t", header=TRUE) 



mydata2 <- my_data %>%
  #filter( `Tissue.Category`!= "Necrosis") %>%
  filter(Phenotype != "Negative") %>%
  filter(Phenotype != "NA") %>%

group_by(`Sample.Name`,
         `Tissue.Category`, 
         Phenotype) %>%  

  unite('Key','Tissue.Category','Phenotype')%>%
  pivot_wider(names_from = Key, values_from = `cell.density.per.mm2`) %>% 
    group_by(`Sample.Name`)  %>% 




  summarise(sum_Tumour_CD8 = sum(Tumour_CD8, na.rm = T),
          sum_Tumour_Treg = sum(Tumour_Treg, na.rm=T),
           sum_Tumour_CD4 = sum(Tumour_CD4, na.rm=T),
          sum_Tumour_CK = sum(Tumour_CK, na.rm=T),
           sum_Tumour_FOXP3 = sum(Tumour_FOXP3, na.rm=T),
           sum_Stroma_CD8 = sum(Stroma_CD8, na.rm = T),
         sum_Stroma_CD4 = sum(Stroma_CD4, na.rm = T),
          sum_Stroma_FOXP3 = sum(Stroma_FOXP3, na.rm = T),
          sum_Stroma_Treg = sum(Stroma_Treg, na.rm = T),`


Error in summarise(): ! Problem while computing sum_Tumour_CD8 = sum(Tumour_CD8, na.rm = T). ℹ The error occurred in group 1: Sample.Name = "ML28io_1_Scan1.qptiff". Caused by error in sum(): invalid 'type' (list) of argument

This is because some values in these columns are "NULL". All the other times with other data frames the empty values are always "NA" and I can use na.rm=T function but I am not sure why this particular data frame has NULL.

I have tried

mydata2 %>% discard(~ length(.x) == 0) summarise(sum_Tumour_CD8 = sum(Tumour_CD8, rm.null(l)), mydata2[mydata2 == 'NULL'] <- NA mydata2 %>% discard(is.null)

Amongst many other things. The columns are "list" rather than integer which is why this problem is occuring

listvectornullintegerna

Answer 1

[Matthias Zepper]

In your script, provide the parameter na.strings to the read.table() function and you can use your tried and tested methods downstream:

my_data <- read.table(file = "clipboard", sep = "\t", na.strings = "NULL", header=TRUE) 

[Cancer]

Thank you for responding. NULLs are not present in my copy pasted data if you see the excel file. They only appear before the summarise stage so add it to the beginning doesn't make a difference

[Matthias Zepper]

Fair enough, I just glanced over your screenshot, because that magrittr pipe is intimidatingly long, and you had already ascertained that this is because some values in these columns are "NULL". All the other times with other data frames the empty values are always "NA" and I can use na.rm=T function but I am not sure why this particular data frame has NULL.

Without going into too much detail, there is only one function in your pipe, that can possibly produce those values: pivot_wider().

Try instead:

pivot_wider(names_from = Key, values_from = `cell.density.per.mm2`, values_fill = NA)

Answer 2

[Jeremy]

One option would be to replace 'NULL' with 'NA' using sed on the command line.

sed 's/NULL/NA/g' mydata2.csv > mydata3.csv

Otherwise, since you're using Excel, could you use Find and Replace in Excel?

[Cancer]

this is the error that I am getting.

sed 's/NULL/NA/g'mydata2.csv > mydata3.csv Error: unexpected string constant in "sed 's/NULL/NA/g'"

Also, The excel file at the start doesn't have NULLs. Its only before the summarise function they appear. I have tried importing the data at NULL stage to excel to replace but that isn't working either

[Jeremy]

It looks like you're getting an R error, but sed should be run at the command line (e.g. Linux Terminal).