Write.table with new line after a pattern in each cell for Excel for friendly output

user_11456863 0
Viewed 50

I have my data like this: 

tt <- structure(list(A.T = structure(c(`4` = 2L, `5` = 3L, `6` = 1L
), .Label = c("2015(1583)", "2273(1876)", "4916(3954)"), class = "factor"), 
    A.G = structure(c(`4` = 2L, `5` = 1L, `6` = 3L), .Label = c("18645(2960)", 
    "6394(1409)", "8601(1275)"), class = "factor"), A.C = structure(c(`4` = 1L, 
    `5` = 3L, `6` = 2L), .Label = c("451(173)", "482(230)", "860(349)"
    ), class = "factor"), C.T = structure(c(`4` = 1L, `5` = 3L, 
    `6` = 2L), .Label = c("3885(3148)", "4042(3049)", "7772(5955)"
    ), class = "factor"), C.G = structure(c(`4` = 1L, `5` = 3L, 
    `6` = 2L), .Label = c("162(108)", "171(107)", "333(239)"), class = "factor"), 
    C.A = structure(c(`4` = 2L, `5` = 3L, `6` = 1L), .Label = c("1825(1481)", 
    "2118(1743)", "3449(2557)"), class = "factor"), G.T = structure(c(`4` = 2L, 
    `5` = 3L, `6` = 1L), .Label = c("2019(1794)", "2571(2336)", 
    "4538(4086)"), class = "factor")), class = "data.frame", row.names = c("4", 
"5", "6"))

Say if I write this table as write.table(tt, "mynewcsv.csv", sep = "\t", quote = FALSE) and then open mynewcsv.csv in Excel, it would look like this:

enter image description here

However, I want my excel table to look like this:

enter image description here

What do I need to do so that mynewcsv.csv in Excel looks the way I want?

r
1 Answers

You can insert a newline character using sub():

write.csv(sapply(tt, function(x) sub("(?<=\\d)(?=\\()", "\n", x, perl = TRUE)), "mynewcsv.csv",  quote = TRUE, row.names = FALSE)

After opening in Excel, you need to highlight the cells and enable "Wrap text" for it to display correctly.

The end result should be:

enter image description here

user_28352610
Related