R generated files getting scrambled on import in ArcGIS

Question

I want to put this out there just in case someone somewhere has experienced similar issues. I have a .csv file that I have generated in R 3.0 using this code:

write.table(SubData,paste(RootOutputFileName,"TimeSubsetData_", Date,"_.csv", sep = ""), sep = ",", row.names = F)

When I bring in the resulting csv file into ArcGIS 10.1 it has scrambled the column order, renamed the columns, moved headings, removed data completely, replaced data with NA's, and all sorts of other odd things.

Here's what the csv file looks like outside of ArcGIS:

ID             Date_Time    moose   trial   x      y    label   label.1 dog  Reproduction   Position    EvalPt  DistX   DistY   Dist    Heading TimeDif Velocity    Angle   LocType LocNum
3311_1  2010-10-07 00:20    F07011  1   1476024 6620118 before  before  Ludde   No Calf End 1   10  -5  11.18034    2.034444    0.166667    67.082039   2.034444    Obs 1
3311_1  2010-10-07 00:20    F07011  1   1476019 6620136 before  before  Ludde   No Calf End 1   4.528243    12.549702   13.341664   0.346286    0.166667    80.049984   0.346286    rand    1
3311_1  2010-10-07 00:20    F07011  1   1476059 6620089 before  before  Ludde   No Calf End 1   45.067664   -34.189262  56.568542   2.219792    0.166667    339.411255  2.219792    rand    1
3311_1  2010-10-07 00:20    F07011  1   1476018 6620125 before  before  Ludde   No Calf End 1   4.341216    2.480695    5   1.05165 0.166667    30  1.05165 rand    1
3311_1  2010-10-07 00:20    F07011  1   1476014 6620127 before  before  Ludde   No Calf End 1   0   3.605551    3.605551    0   0.166667    21.633308   0   rand    1
3311_1  2010-10-07 00:20    F07011  1   1476020 6620126 before  before  Ludde   No Calf End 1   5.778044    3.407962    6.708204    1.037901    0.166667    40.249224   1.037901    rand    1

Here's what happens after all I do is AddData and then Open Attribute Table:

ID    ID_X   ID_Y              Date_Time    moose   moose_X          moose_Y    trial   x   y   label   label_1 dog Reproduction    Position    EvalPt  DistX   DistY   Dist    Heading TimeDif Velocity    Angle   LocType LocNum
3311_1  1   33.18333333 2010-10-08 09:20    F07011  -147.4600309    9.81849E+15 1   1476079 6620355 before  before  Ludde   No Calf End 1   -13 26  29.06888371 5.819537698 0.166666667 174.4133022 2.677945045 Obs 1
3311_1  1   33.18333333 2010-10-08 09:20    F07011  -147.4600309    9.81849E+15 1   0   0   before  before  Ludde   No Calf End 1   0   0   16.97056275 3.660738768 0.166666667 101.8233765 0.519146114 rand    1
3311_1  1   33.18333333 2010-10-08 09:20    F07011  -147.4600309    9.81849E+15 1   1476092 6620328 before  before  Ludde   No Calf End 1   0   -1  1   3.141592654 0.166666667 6   0   rand    1
3311_1  1   33.18333333 2010-10-08 09:20    F07011  -147.4600309    9.81849E+15 1   0   0   before  before  Ludde   No Calf End 1   0   0   437.7145188 5.561036001 0.166666667 2626.287113 2.419443348 rand    1
3311_1  1   33.18333333 2010-10-08 09:20    F07011  -147.4600309    9.81849E+15 1   0   0   before  before  Ludde   No Calf End 1   0   0   293.4365349 3.457878782 0.166666667 1760.619209 0.316286128 rand    1
3311_1  1   33.18333333 2010-10-08 09:20    F07011  -147.4600309    9.81849E+15 1   0   0   before  before  Ludde   No Calf End 1   0   0   75.92759709 5.98774447  0.166666667 455.5655825 2.846151816 rand    1

I do not know what R is doing because normally I have no issue, but "normally" for me Ive used write.csv and then didn't have to paste all the rest of that information together. I get that it is something with the formatting coding that goes on in the background and I am wondering if it has something to do with the write.table command and then forcing it into csv? But what is ArcGIS doing by creating ID_X ID_Y adding a moose_X and moose_Y and where are those numbers coming from? Why is it removing values (i.e., 2nd row under x and y values now are 0)? Why is it changing the date_time to 9:20?

I will also post this type of question in an R forum here - but if someone has experience or tricks to deal with this from the ArcGIS side of things - that would be great. Right now my work around is to take the R output (my csv file) bring it into some "neutral" program like TextPad and then save it as a new csv or txt file, then I can bring it into ArcGIS and seem to have no problems. Im also having better luck just opening the csv file in Excel and saving as xls and then opening that in ArcGIS.

Here is the structure of the R input before I write to csv

str(SubData) 'data.frame': 4740 obs. of 21 variables:
$ ID : Factor w/ 29 levels "3311_1","3313_1",..: 1 1 1 1 1 1 1 1 1 1 ...
$ Date_Time : POSIXct, format: "2010-10-08 09:20:00" "2010-10-08 09:20:00" "2010-10-08 09:20:00" "2010-10-08 09:20:00" ...
$ moose : Factor w/ 15 levels "F01001","F070014a",..: 7 7 7 7 7 7 7 7 7 7 ...
$ trial : int 1 1 1 1 1 1 1 1 1 1 ...
$ x : num 1476079 1476090 1476093 1476097 1476129 ...
$ y : num 6620355 6620319 6620332 6620323 6620317 ...
$ label : Factor w/ 11 levels "after","before",..: 2 2 2 2 2 2 2 2 2 2 ...
$ label.1 : Factor w/ 5 levels "after","before",..: 2 2 2 2 2 2 2 2 2 2 ...
$ dog : Factor w/ 3 levels "Ida","Ludde",..: 2 2 2 2 2 2 2 2 2 2 ...
$ Reproduction: Factor w/ 2 levels "Calf","No Calf": 2 2 2 2 2 2 2 2 2 2 ...
$ Position : chr "End" "End" "End" "End" ...
$ EvalPt : num 1 1 1 1 1 1 1 1 1 1 ...
$ DistX : num -13 -2.35 1.19 5.16 36.63 ...
$ DistY : num 26 -9.62 2.93 -6.43 -12.29 ...
$ Dist : num 29.07 9.9 3.16 8.25 38.64 ...
$ Heading : num 5.82 3.381 0.387 2.466 1.895 ...
$ TimeDif : num 0.167 0.167 0.167 0.167 0.167 ...
$ Velocity : num 174.4 59.4 19 49.5 231.8 ...
$ Angle : num 2.68 0.24 3.53 5.61 5.04 ...
$ LocType : chr "Obs" "rand" "rand" "rand" ...
$ LocNum : num 1 1 1 1 1 1 2 2 2 2 ..."

Answer 1

You may want to try renaming the following fields: ID, Date_Time, x, y, and label.1. Just a guess, but they could be reserved names in the case of the first 4, and ArcMap may not like the '.' in the final field name.