Search code examples
rdataframeexport-to-excelwrite.table

Function write.table() is converting my character column to numeric when exporting to excel. How can I prevent this?

I have a dataframe in a similar way:

  Material  c1  c2  c3  c4  c5  c6
1   0111    30  44  24  25  52  27
2   0112    19  70  93  23  68  100
3   1124    22  NA  79  18  133 143
4   2389    79  NA  NA  81  60  NA
5   3480    57  8   95  62  NA  90
6   0134    350 60  50  302 44  4

Here I am forcing as character type, but in my real dataframe it alredy is.

df['Material']=as.character(df['Material'])

sapply(df,mode)

write.table(df, paste(".\\exports\\", "dummy.csv", sep = ""), sep = ";", dec = ",",row.names=F)

The problem is that when I export to excel, my Material column becomes numeric and I loose the 0 in the beginning that is a part of the material code description. My rows get sorted and I would like to have them in the original position. Is there a way of preventing this to happen? To keep my Material column as character when exporting?

In case you want to replicate it, I tried to create a dummy version:

df <- data.frame(Material = c('0111','0112','1124','2389','3480'),
                    actual_202009 = c(30,19,22,79,57),
                    actual_202010 = c(44,70,NA,NA,8),
                    actual_202011 = c(24,93,79,NA,95), 
                    pred_202009 = c(25,23,18,81,62),
                    pred_202010 = c(52,68,133,60,NA),
                    pred_202011 = c(27,100,143,NA,90))
Solution

  • As mentioned in the comments:

    This works.

    library(openxlsx)
write.xlsx(df, file = "dummy.xlsx")

    Even though the data in your csv has quotes around the Material column, excel likes to think for you and not honour the character quotes. Now you could use libreoffice with calc. This will open the text import option where you can tell it that the first column is text and it will retain the correct format for the Material column.

    But the easiest is just to use the above code.