R write dataframe column to csv having leading zeroes

Question

I have a table that stores prefixes of different lengths.. snippet of table(ClusterTable)

ClusterTable[ClusterTable$FeatureIndex == "Prefix2",'FeatureIndex', 'FeatureValue')]

   FeatureIndex FeatureValue
80      Prefix2           80
81      Prefix2           81
30      Prefix2           30
70      Prefix2           70
51      Prefix2           51
84      Prefix2           84
01      Prefix2           01
63      Prefix2           63
28      Prefix2           28
26      Prefix2           26
65      Prefix2           65
75      Prefix2           75

and I write to csv file using following:

write.csv(ClusterTable, file = "My_Clusters.csv")

The Feature Value 01 loses it leading zero.

I tried first converting the column to characters

ClusterTable$FeatureValue <- as.character(ClusterTable$FeatureValue)

and also tried to append it to an empty string to convert it to string before writing to file.

ClusterTable$FeatureValue <- paste("",ClusterTable$FeatureValue)

Also, I have in this table prefixes of various lengths, so I cant use simple format specifier of a fixed length. i.e the table also has Value 001(Prefix3),0001(Prefix4),etc. Thanks

Answer 1

EDIT: As of testing again on 8/5/2021, this doesn't work anymore. :(

I know this is an old question, but I happened upon a solution for keeping the lead zeroes when opening .csv output in excel. Before writing your .csv in R, add an apostrophe at the front of each value like so:

vector <- sapply(vector, function(x) paste0("'", x))

When you open the output in excel, the apostrophe will tell excel to keep all the characters and not drop lead zeroes. At this point you can format the column as "text" and then do a find and replace to remove the apostrophes (maybe make a macro for this).

Answer 2

If you just need it for the visual, just need to add one line before you write the csv file, as such:

ClusterTable <- read.table(text="   FeatureIndex FeatureValue
80      Prefix2           80
           81      Prefix2           81
           30      Prefix2           30
           70      Prefix2           70
           51      Prefix2           51
           84      Prefix2           84
           01      Prefix2           01
           63      Prefix2           63
           28      Prefix2           28
           26      Prefix2           26
           65      Prefix2           65
           75      Prefix2           75",
                           colClasses=c("character","character"))

ClusterTable$FeatureValue <- paste0(ClusterTable$FeatureValue,"\t")

write.csv(ClusterTable,file="My_Clusters.csv")

It adds a character to the end of the value, but it's hidden in Excel.

Answer 3

Save the file as a csv file, but with a txt extension. Then read it using read.table with sep=",":

write.csv(ClusterTable,file="My_Clusters.txt")
read.table(file=My_Clusters.txt, sep=",")