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

Lcm

Related question I found https://mcmap.net/q/1772976/-r-excel-leading-zeroes but this is for reading, and has fixed length – Lcm

When exactly does it lose the "0"? I tried to write AND open "01" (as character) with R and it worked fine. Opening it with any text editor should also work fine. – Pauperize

In my csv file. I use this csv file as a source for another program. If I open and check in csv, there are no leading zeros – Lcm

@karx, are you opening the csv in excel? I think excel may try to be smart and remove the leading zeros automatically. – Braswell

@Braswell You have no idea how dumb I feel. Thanks a ton. Viewed it in my text editor and wollah! I manually was going through the csv every time before feeding it to the next preogram. Just to be sure. Who knew being extra cautious could bite you in the back – Lcm

@karx, glad it's solved. I will post as the answer. – Braswell

Answer 1

When dealing with leading zeros you need to be cautious if exporting to excel. Excel has a tendency to outsmart itself and automatically trim leading zeros. You code is fine otherwise and opening the file in any other text editor should show the zeros.

Braswell

This isnt an answer and should not be marked as answering the question. JeffR's answer properly answers this question, not this one – Condemnatory

Answer 2

EDIT: As of testing again on 8/5/2021, the find-and-replace approach doesn't work anymore. :( Workaround with "flash fill"

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.

Newer Excel versions:
As find and replace does remove the apostrophes as well as 0s (in newer Excel versions), another way is to add a column and add the values without apostrophes using the "flash fill". If the values in the cells differ in format ( e.g. there are more than one number in some cells) the result of the "flash fill" should be double-checked and adapted if needed. Then the former column can be deleated.

Older Excel version:
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).

Maguire

This did not work for me. After the search-replace of the apostrophe with "", the leading zeroes disappeared. – Ventura

Just tried again and it also did not work for me latest version of Excel. Answer was from 6 years ago, so seems like Excel team found a way to be even more annoying. I'd recommend you simply just don't use Excel here to examine the data or keep the apostrophe. – Maguire

Answer 3

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.

Delly

I was stucked trying to solve a problem like this with a data.frame and paste0(variable, "\t") solved it perfectly. Thank you so much. – Anaphrodisiac

Answer 4

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=",")

Degeneracy

Answer 5

If you're trying to open the .csv with Excel, I recommend writing to excel instead. First you'll have to pad the data though.

    library(openxlsx)
    library(dplyr)

    ClusterTable <- ClusterTable %>% 
     mutate(FeatureValue = as.character(FeatureValue),
     FeatureValue = str_pad(FeatureValue, 2, 'left', '0'))

    write.xlsx(ClusterTable, "Filename.xlsx")

Nogging

Answer 6

This is pretty much the route you can take when exporting from R. It depends on the type of data and number of records (size of data) you are exporting:

• if you have many rows such as thousands, txt is the best route, you can export to csv if you know you don't have leading or trailing zeros in the data, either use txt or xlsx format. Exporting to csv will most likely remove the zeros.

• if you don't deal with many rows, then xlsx libraries are better

• xlsx libraries may depend on java so make sure you use a library that does not require it

• xlsx libraries are either problematic or slow when dealing with many rows, so still txt or csv can be a better route

for your specific problem, it seems you don't deal with a large number of rows, so you can use:

library(openxlsx)

# read data from an Excel file or Workbook object into a data.frame
df <- read.xlsx('name-of-your-excel-file.xlsx')

# for writing a data.frame or list of data.frames to an xlsx file
write.xlsx(df, 'name-of-your-excel-file.xlsx')

Khaddar

Answer 7

You have to modificate your column using format:

format(your_data$your_column, trim = F)

So when you export to .csv then leading zeros will keep on.

Dierolf