[R] How to fix the format in write.csv

Jim Lemon jim at bitwrit.com.au
Fri May 2 23:21:21 CEST 2014


Hi ChangJiang,
Date conversion is one of the biggest headaches with Excel. Even if you 
import those data into Excel and then specify that the column should be 
"text" format, it won't convert the values back into what they 
originally were. Be aware that Excel may, when encountering dates from a 
different locale, _silently_ convert the ones that aren't valid in its 
current locale, usually by swapping the day and month values. That one 
has bitten me when I have had to use Excel. The solution I found was to 
always use international format (yyyy-mm-dd) as that didn't seem to be 
altered.

Jim

On 05/03/2014 06:56 AM, Bert Gunter wrote:
> ChangJiang:
>
> Open the .csv file with Notepad or other plain vanilla text processor,
> NOT EXCEL. You will find that the columns are text. Excel
> automatically converts them to dates. Read Excel's docs or get help
> from someone to learn how to convert the dates back to text.
>
> -- Bert
>
>
>
> Bert Gunter
> Genentech Nonclinical Biostatistics
> (650) 467-7374
>
> "Data is not information. Information is not knowledge. And knowledge
> is certainly not wisdom."
> H. Gilbert Welch
>
>
>
>
> On Fri, May 2, 2014 at 12:35 PM, ChangJiang Xu
> <changjiang.h.xu at gmail.com>  wrote:
>> Thanks. I still didn't get the solution. For example, I have a data frame,
>> called temp
>>> temp
>>    Chr Ref Var     AFF    UNAFF AFF.test     pvalue
>> 1  10   A   G  2/1/24    0/0/190        5/49  2.429e-09
>> 2  18   G   A  1/9/17  0/23/167       11/43  2.484e-04
>> 3   1   G   A  2/2/22    0/8/176         6/46  4.293e-04
>> 4  11   T   G  1/1/25    0/2/188          3/51 1.193e-03
>> 5   2   A   T 1/10/16   38/90/60       12/42  2.220e-03
>> 6   3   G   A 1/16/10  8/49/133        18/36 4.549e-03
>>
>> Then I want to write a csv file using R function write.csv, as follows:
>>> write.csv(temp, file="temp.csv", row.names = FALSE)
>>
>> and the csv file, temp.csv, looks like the below, not same as original data
>> Chr    Ref    Var    AFF           UNAFF        AFF.test    pvalue
>> 10        A    G    02/01/2024    0/0/190        May-49        2.43E-09
>> 18        G    A    01/09/2017    0/23/167      Nov-43        0.0002484
>> 1          G    A    02/02/2022    0/8/176        Jun-46        0.0004293
>> 11         T    G    01/01/2025    0/2/188        Mar-51        0.001193
>> 2          A    T    01/10/2016    38/90/60       Dec-42        0.00222
>> 3          G    A    1/16/10        8/49/133       18/36          0.004549
>>
>>
>> On Fri, May 2, 2014 at 3:06 PM, Bert Gunter<gunter.berton at gene.com>  wrote:
>>>
>>> Read your Excel documentation. AFAIK, R just writes text files -- you
>>> need to tell Excel how to read them in.
>>>
>>> Cheers,
>>> Bert
>>>
>>> Bert Gunter
>>> Genentech Nonclinical Biostatistics
>>> (650) 467-7374
>>>
>>> "Data is not information. Information is not knowledge. And knowledge
>>> is certainly not wisdom."
>>> H. Gilbert Welch
>>>
>>>
>>>
>>>
>>> On Fri, May 2, 2014 at 11:34 AM, ChangJiang Xu
>>> <changjiang.h.xu at gmail.com>  wrote:
>>>> By dafault, write.csv will change the characters such as "5/38" as a
>>>> date
>>>> "May-38". How can I not change the format?
>>>> Thanks.
>>>>
>>>> ChangJiang
>>>>
>>>>          [[alternative HTML version deleted]]
>>>>
>>>> ______________________________________________
>>>> R-help at r-project.org mailing list
>>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>>> PLEASE do read the posting guide
>>>> http://www.R-project.org/posting-guide.html
>>>> and provide commented, minimal, self-contained, reproducible code.
>>
>>
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.



More information about the R-help mailing list