1

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))
jessirocha
  • 457
  • 4
  • 15
  • 2
    I didn't check, but I guess you'd be better off with using a dedicated package for Excel exporting. E.g. `writexl`or even `openxlsx`. openxlsx might look a bit overwhelming at first, but IMO it's currently the most powerful Excel package for R and shoudl hopefully preserve the properties of your R data. – deschen Jan 29 '21 at 08:50
  • 1
    just a quick thought, have you tryied to put `'` in front of each value of `Material`? Ex: `'0111`. Then open it with Excel. I do not have time to try it now sry... – Paul Jan 29 '21 at 08:52
  • Something like what @Paul suggests, another option is to add a character, export, set the format of the column in excel to text, and then find and remove the character... Not neat (but works)! – O.A Jan 29 '21 at 08:54
  • Another option is to save the file as text. I guess this is what you are looking for https://stackoverflow.com/questions/28675279/r-write-dataframe-column-to-csv-having-leading-zeroes – Ronak Shah Jan 29 '21 at 08:57

1 Answers1

2

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.

phiver
  • 23,048
  • 14
  • 44
  • 56