Unable to convert "NULL" to either zero or NA
2
1
Entering edit mode
2 days ago
Cancer ▴ 10

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

List Integer NULL NA • 281 views
ADD COMMENT
1
Entering edit mode
2 days ago

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)

ADD COMMENT
0
Entering edit mode

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

ADD REPLY
0
Entering edit mode

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)

ADD REPLY
0
Entering edit mode
2 days ago
Jeremy ▴ 740

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?

ADD COMMENT
0
Entering edit mode

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

ADD REPLY
0
Entering edit mode

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

ADD REPLY

Login before adding your answer.

Traffic: 1127 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6