Solution found! Scroll to the end to see what I did. Hopefully, this function can help others.
TLDR: I have a list: https://i.stack.imgur.com/7t6Ej.png
and I need to do something like this to it
lapply(irabdf, function(x) c(x[!is.na(x)], x[is.na(x)]))
But I need this function to do this to each element of the list individually, and not delete the column names. Currently, I can get it to sort lowest to highest but it moves everything into a single column and deletes the column names.
I have a list in R that I am exporting as a XLS file using the Openxlsx package. I have everything that I need functionally, but my P.I has requested that I sort each column lowest to highest for reviewers, as there are a lot of empty cells that make the document look funny. I am trying to add this feature in R so that I don't need to do it manually. All columns were created from separate .csv files, and rows are unimportant.
The List: https://i.stack.imgur.com/7t6Ej.png
The generated XLSX file looks like this: https://i.stack.imgur.com/ftg00.png.
The columns are not blank, the data is just much further down.
My code for writing the file:
wb <- createWorkbook()
lapply(names(master), function(i){
addWorksheet(wb=wb, sheetName = names(master[i]))
writeData(wb, sheet = i, master[[i]])
addFilter(wb, sheet = i, rows = 1, cols = 1:(a))
})
#Save Workbook
saveWorkbook(wb, saveFile, overwrite = TRUE)
a = this value obtained through (length(unique(x)). X is the levels of a variable.
What I have:
Column1, Column2, Column3, Column4
1. 1 NA NA NA
2. 2 NA NA NA
3. NA 3 NA NA
4. NA 4 NA NA
5. NA NA 5 NA
6. NA NA 6 NA
7. NA NA NA 7
8. NA NA NA 8
What I want:
Column1, Column2, Column3, Column4
1. 1 3 5 7
2. 2 4 6 8
3. NA NA NA NA
4. NA NA NA NA
5. NA NA NA NA
6. NA NA NA NA
7. NA NA NA NA
8. NA NA NA NA
The actual file has 1,000's of rows and 100's of blank cells for each column. The solution would replicate this across all tabs of the XLSX file.
What I have tried: In a previous version of this script I was able to do this. I had separate df's which were assigned names through user-dialogue options. This is an example of the code I used to do that.
irabdf <- masterdf %>%
filter(Fluorescence == "Infrared") %>%
select(mean, Conditions) %>%
mutate(row = row_number()) %>%
spread(Conditions, mean) %>%
select(!row)
irabdf <- lapply(irabdf, function(x) c(x[!is.na(x)], x[is.na(x)])) %>% ## Move NAs to the bottom of the df
data.frame()
# Create a blank workbook
WB <- createWorkbook()
# Add some sheets to the workbook
addWorksheet(WB, gab)
addWorksheet(WB, rab)
addWorksheet(WB, irab)
# Write the data to the sheets
writeData(WB, sheet = gab, x = gabdf)
writeData(WB, sheet = rab, x = rabdf)
writeData(WB, sheet = irab, x = irabdf)
# Reorder worksheets
worksheetOrder(WB) <- c(1:3)
# Export the file
saveWorkbook(WB, saveFile)
Now that I have removed the user interface and am now using a list I can no longer do this. I have also tried a myriad of other things with most utilizing lapply.
If you need any more information just ask.
Thanks in advance for the assistance!
09/21
I think I am getting closer but I still haven't resolved the issue.
When I use this code
list <- lapply(master[[1]],
function(x) c(x[!is.na(x)], x[is.na(x)]))
I get the results I want but end up losing the first element. If I could keep the first element and apply this over my entire list that should do the trick.
09/22
I have found something that works! However, it isn't dynamic. If someone could help me loop this function across all of the elements of this list (or knows a better solution) just let me know.
list1 <- lapply(master[[1]],
function(x) c(x[!is.na(x)], x[is.na(x)]))
list1 <- data.frame(list1)
master[[1]] <- list1
I need to specify list1 as a df for me to maintain my column names in my XLSX output.
09/22 - 2
Okay, I have the script doing exactly what I want it to do. However, it isn't pretty and it isn't "very" dynamic.
+rep to anyone who can help me convert this into a pretty lapply loop!
if (b >= 1) {
list1 <- lapply(master[[1]],
function(x) c(x[!is.na(x)], x[is.na(x)]))
list1 <- data.frame(list1)
master[[1]] <- list1
}
if (b >= 2) {
list2 <- lapply(master[[2]],
function(x) c(x[!is.na(x)], x[is.na(x)]))
list2 <- data.frame(list2)
master[[2]] <- list2
}
ect... x12
b has a value of 12 here. However, it could be any number practically.
09/22 - 3
Alright, I figured it out. I created the following loop to do what I needed to do and everything appears to be working perfectly. Part of me wants to scream from happiness.
for (i in 1:length(unique(masterdf$ABwant))) {
if (i >= 1)
list.i <- lapply(master[[i]],
function(x) c(x[!is.na(x)], x[is.na(x)]))
list.i<- data.frame(list.i)
master[[i]] <- list.i
}
I'll keep the thread open the rest of the week and if someone has a better solution I will accept it and give you some rep. Else, GG.
