Error in x[is.na(x)] <- na.string : replacement has length zero WHEN I export with write.xlsx

[Zcythor]

I am trying to export the following dataframe as an excel file but I keep getting this error:

Error in x[is.na(x)] <- na.string : replacement has length zero

I believe it is due to NA values causing this error so I tried to replace them with "NULL" but only when I convert my dataframe into a list.

When I try replacing NA with NULL I get:

Assigned data `"NULL"` must be compatible with existing data.
i Error occurred for column `If you feel comfortable, please explain the answer to the last question..y`.
x Can't convert <character> to <list>.

so there is a column that is causing all this.
it contains cells like "c(NA NA NA)

how can I correct this columns format so I can finally print out my dataframe.

I have tried reprexing my dataframe but unfortunately I get R code Execution error

R CODE EXECUTION ERROR

My code and part of the dataframe :

my code:

dfTranformedWithEmployeeProfile2 %>% replace_na(list(dfTranformedWithEmployeeProfile2 = "NULL"))

dfTransformedWithEmployeeProfile3 = dfTranformedWithEmployeeProfile2[is.na(dfTranformedWithEmployeeProfile2)]<-"NULL"

write.xlsx(dfTranformedWithEmployeeProfile2, outputFilepath)
rm(list = ls())

sort(names(df))

print(dfTranformedWithEmployeeProfile2)


dp_set_max_rows(6)
exampleDF = head(dfTranformedWithEmployeeProfile2,5)

head(exampleDF)
dpasta(exampleDF)
exampleDF <- tibble::tribble(
  ~Employee.ID,                                                           ~Workflow.Step, ~I.feel.my.objectives.are.challenging.but.achievable., ~It.was.easy.to.update.my.objectives., ~I.feel.motivated.by.my.objectives., ~I.understand.what.s.expected.of.me.in.terms.of.my.performance.,                                                                      ~If.you.feel.comfortable..please.explain.the.answer.to.the.last.question..y,                                                                             ~Is.there.anything.else.you.would.like.to.share..y,
         28999, "Manage Objectives (Default Definition) step c - Complete Questionnaire",                                      "Strongly agree",                      "Strongly agree",                    "Strongly agree",                                                "Strongly agree",                                                                                                                                             NULL,                                                                                                                           NULL,
         26050, "Manage Objectives (Default Definition) step c - Complete Questionnaire",                                      "Strongly agree",                            "Disagree",                    "Strongly agree",                                    "Neither agree nor disagree", The objectives "above" me (at Centrica and DTS level) could be better quantified, so that I knew exactly which KPIs I was expected to influence., The Workday experience is awful.  The UI is ugly and the process seems to have been designed by someone who has never used it.,
        125307, "Manage Objectives (Default Definition) step c - Complete Questionnaire",                                               "Agree",          "Neither agree nor disagree",                             "Agree",                                                "Strongly agree",                                                                                                                                    c(NA, NA, NA),                                                                                                                           NULL,
        107986, "Manage Objectives (Default Definition) step c - Complete Questionnaire",                                               "Agree",                            "Disagree",        "Neither agree nor disagree",                                                         "Agree",                                                                                                                                    c(NA, NA, NA),                                                                                                                             NA,
        127131, "Manage Objectives (Default Definition) step c - Complete Questionnaire",                                      "Strongly agree",                      "Strongly agree",                    "Strongly agree",                                                "Strongly agree",                                                                                                                                    c(NA, NA, NA),                                                                                                                             NA
  )
head(exampleDF)

[nirgrahamuk]

I cant run exampleDF <- tibble::tribble( etc.
it seems dpasta let you down on this occasion. recommend you switch to dput()

[Zcythor]

structure(list(`Employee ID` = c(28999, 26050, 125307, 107986, 
127131), `Workflow Step` = c("Manage Objectives (Default Definition) step c - Complete Questionnaire", 
"Manage Objectives (Default Definition) step c - Complete Questionnaire", 
"Manage Objectives (Default Definition) step c - Complete Questionnaire", 
"Manage Objectives (Default Definition) step c - Complete Questionnaire", 
"Manage Objectives (Default Definition) step c - Complete Questionnaire"
), `I feel my objectives are challenging but achievable.` = c("Strongly agree", 
"Strongly agree", "Agree", "Agree", "Strongly agree"), `It was easy to update my objectives.` = c("Strongly agree", 
"Disagree", "Neither agree nor disagree", "Disagree", "Strongly agree"
), `I feel motivated by my objectives.` = c("Strongly agree", 
"Strongly agree", "Agree", "Neither agree nor disagree", "Strongly agree"
), `I understand what's expected of me in terms of my performance.` = c("Strongly agree", 
"Neither agree nor disagree", "Strongly agree", "Agree", "Strongly agree"
), `If you feel comfortable, please explain the answer to the last question..y` = list(
    NULL, "The objectives \"above\" me (at Centrica and DTS level) could be better quantified, so that I knew exactly which KPIs I was expected to influence.", 
    c(NA_character_, NA_character_, NA_character_), c(NA_character_, 
    NA_character_, NA_character_), c(NA_character_, NA_character_, 
    NA_character_)), `Is there anything else you would like to share?.y` = list(
    NULL, "The Workday experience is awful.  The UI is ugly and the process seems to have been designed by someone who has never used it.", 
    NULL, "NA", "NA")), row.names = c(NA, 5L), class = "data.frame")

will this suffice?

[nirgrahamuk]

yeah, those columns which are lists are problematic.
to simply print a character representation of them you could force all such columns to be character

ready_to_write_out_df  <- exampleDF |> mutate(across(where(is.list),as.character))

[Zcythor]

Thanks. I ended up solving the problem by changing the excel file manually. What was happening was that I had to make sure wherever Multiple Choice Question and their corresponding Question Answer were, that the Free Text Question and Free Text Answer were not present and vice versa. Otherwise it seems to join missing values with non missing values instead of what I assumed was going to be merging blank cells with answer cells which R script takes quite literally as NA + ANSWER = c(NA NA NA) or C(NA ANSWER), etc...
So I spent about 20 minutes clearing content on the spreadsheet to clean the file.
This is because whoever made the file was requested to include a Free Text Question Column to separate Free Text Questions and Multiple Choice Questions but probably just duplicated the Multiple Choice Question Column and only removed Multiple Choice Questions in that now duplicated column. The other three columns needed cleaning too.

Just in case anyone was curious about the whole issue since this Zero Value error is pretty much impossible to deal with through coding. (at least at my awful level of coding and the lack of resources pertaining this question lol.)