0

I have a dataframe (relatives.ALL.with.status.age) that I want to print as a csv file. When I open the Related_IBD_Pairs_with_status_and_age.csv on Excel, I get this funny number (highlighted in yellow in excel snapshot of Result below). How do I avoid getting this?

I tried the following code:

write.csv(relatives.ALL.with.status.age, "Related_IBD_Pairs_with_status_and_age.csv", quote = FALSE, row.names = FALSE)

data:

relatives.ALL.with.status.age <- structure(list(FID1 = c("0", "0", "0", "0", "0", "0"), IID1 = c("5311", 
"10769", "10836", "26192", "26639", "33141"), FID2 = c("0", "0", 
"0", "0", "0", "0"), IID2 = c("NACC267941", "NACC106983", "NACC926235", 
"NACC902225", "NACC751952", "NACC185636"), RT = c("OT", "OT", 
"OT", "OT", "OT", "OT"), EZ = c(0L, 0L, 0L, 0L, 0L, 0L), Z0 = c(0, 
6e-04, 3e-04, 3e-04, 0.1957, 5e-04), Z1 = c(0.0022, 0.0082, 0.008, 
0.0033, 0.5578, 0.0084), Z2 = c(0.9977, 0.9912, 0.9917, 0.9964, 
0.2465, 0.9912), PI_HAT = c(0.9988, 0.9953, 0.9957, 0.9981, 0.5254, 
0.9954), PHE = c(-1L, -1L, -1L, -1L, -1L, -1L), DST = c(0.999539, 
0.99816, 0.998289, 0.999241, 0.825602, 0.99817), PPC = c(1L, 
1L, 1L, 1L, 1L, 1L), RATIO = c(NA, 1599.6667, 2394, 4762, 12.0258, 
1602.6667), Relationship = c("parent-offspring", "parent-offspring", 
"parent-offspring", "parent-offspring", "parent-offspring", "parent-offspring"
), key1 = c("0:5311", "0:10769", "0:10836", "0:26192", "0:26639", 
"0:33141"), key2 = c("0:NACC267941", "0:NACC106983", "0:NACC926235", 
"0:NACC902225", "0:NACC751952", "0:NACC185636"), key.1_STATUS = c("unknown", 
"unknown", "unknown", "unknown", "unknown", "unknown"), key.1_AGE_AT_ONSET = c(-9L, 
-9L, -9L, -9L, -9L, -9L), key.1_AGE_LAST_VISIT = c(-9L, -9L, 
-9L, -9L, -9L, -9L), key.1_AGE_AT_DEATH = c(-9L, -9L, -9L, -9L, 
-9L, -9L), key.2_STATUS = c("unknown", "CA", "CO", "unknown", 
"unknown", "unknown"), key.2_AGE_AT_ONSET = c(-9L, 81L, -9L, 
-9L, -9L, -9L), key.2_AGE_LAST_VISIT = c(-9L, -9L, 81L, -9L, 
-9L, -9L), key.2_AGE_AT_DEATH = c(-9L, 85L, -9L, -9L, -9L, -9L
)), row.names = c("6738", "6556", "6557", "6693", "6697", "6721"
), class = "data.frame")

Result: enter image description here

I am writing this file on a Linux server from a Windows machine. Perhaps that's the reason why I am getting these erratic values. less -S Related_IBD_Pairs_with_status_and_age.csv on Linux terminal reads OK though.

MAPK
  • 5,635
  • 4
  • 37
  • 88
  • 5
    That's an excel formatting issue. CSV files don't contain any metadata about the column types. Excel interprets it however it likes. This is really an Excel question rather than an R question so you should probably tag it that way instead. – MrFlick Oct 28 '20 at 01:10
  • 2
    Excel has "helpully" identified that as a time (0 hours 5311 minutes) and converted it to a Date/Time Serial Number. You could try [Importing](https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba#:~:text=On%20the%20Data%20tab%2C%20in,directly%20to%20a%20new%20worksheet.) rather than Opening the file – chris neilsen Oct 28 '20 at 01:44
  • 1
    Also [see this answer](https://stackoverflow.com/a/51806894) – chris neilsen Oct 28 '20 at 01:50
  • @chrisneilsen That still didn't solve the problem. I am writing these files on a Linux server from a Windows machine. Perhaps that's the reason why I am getting these erratic values – MAPK Oct 28 '20 at 01:56
  • `less -S Related_IBD_Pairs_with_status_and_age.csv` on Linux terminal is OK though. – MAPK Oct 28 '20 at 01:58
  • Perhaps try [this](https://stackoverflow.com/a/165052/10802499)? @MAPK – ekoam Oct 28 '20 at 03:35

0 Answers0