[R] Imported tables from Access 2007

Fri Mar 19 00:18:33 CET 2010

On Mar 18, 2010, at 7:04 PM, David Winsemius wrote:

> (I am attaching your original question so it will be easier for me  
> and those who choose to comment further,)
>>
>>>> Hi all,
>>>>
>>>> I am very new to R and I'm trying to import data from Microsoft  
>>>> Access. So
>>>> far, I've managed to do so successfully using the following code:
>>>>
>>>> testdb <- file.path("c:\Databse.accdb")
>>>
>>> I am surprised that worked. I was under the impression won needed  
>>> to double the back-slashes or use "/"'s. But then I don't use  
>>> Access or odbc, so I may be completely off base.
>>>
>>>> channel2 <- odbcConnectAccess2007(testdb)
>>>> data.table <- sqlFetch(channel2,"data")
>>>>
>>>> This successfully imports a table(?) called "data.table".
>>>>
>>>> But when I try to run basic stats or manipulate the data I've  
>>>> imported, I
>>>> can only do so when specifying "data.table", rather than the  
>>>> variables
>>>> names (like one can do when importing Excel data. For instance, I  
>>>> need to
>>>> specify:
>>>
>>> Please post the results of str(data.table)
>>>
>>> There will almost certainly be variable names. They may need to be  
>>> quoted if you have spaces or other special characters in them.
>>>
>>> -- 
>>> David.
>>>
>>>>
>>>> var_3<-data.table[[1]]-data.table[[2]]
>
> Generally one would want to create a new column within the dataframe:
>
> data.table$var_3 <- data.table$'Fish (PCOrd)' - data.table$Location
>
>>>>
>>>> or
>>>>
>>>> lm.1<-lm(data.table[[1]]~data.table[[4]])
>
> Your use of parentheses in the variable names may cause problems I  
> am not skilled enough to work around. Such a practice is not typical  
> R programming. I would have tried:
>
> lm.1 <- lm( 'Fish (POrf)' ~ 'Treatment Location', data = data.table)
>
> But when I hacked the column names on the lm help page I could not  
> get names with parens and or space to work properly.  My advice  
> would be that you make your variable names more in conformance to  
> standard R practices, i.e no parens , dashes, or spaces in variable  
> names.

It is "possible":
 > names(dfrm) <- c('Trt Group', 'Wt (grp)')
 > dfrm
    Trt Group Wt (grp)
1        Ctl     4.17
2        Ctl     5.58
3        Ctl     5.18
4        Ctl     6.11
5        Ctl     4.50
6        Ctl     4.61
7        Ctl     5.17
8        Ctl     4.53
9        Ctl     5.33
10       Ctl     5.14
11       Trt     4.81
12       Trt     4.17
13       Trt     4.41
14       Trt     3.59
15       Trt     5.87
16       Trt     3.83
17       Trt     6.03
18       Trt     4.89
19       Trt     4.32
20       Trt     4.69
 > anova(lm.D9 <- lm(`Wt (grp)` ~ `Trt Group`, data= dfrm))
Analysis of Variance Table

Response: Wt (grp)
             Df Sum Sq Mean Sq F value Pr(>F)
`Trt Group`  1 0.6882 0.68820  1.4191  0.249
Residuals   18 8.7292 0.48496

Notice the use of back-ticks in the formula. Mind you my advice still  
stands. Your R-life will be easier if you don't need to decide whether  
ot not to us back=ticks.

(I would also find a different name for your dataframe. "data.table"  
is a function in a package of that name, and you will make some of us  
think you are using that package.

-- 
David.
>
>
>>>>
>>>> Any way I can change the import process so that R recognizes  
>>>> variable
>>>> names? Or at least change what I'm importing so that I can simply  
>>>> use the
>>>> variables names stored in Access? I've tried various combinations  
>>>> of
>>>> "columnnames=TRUE" and "rownames=FALSE" with no success (or at  
>>>> least
>>>> anything that makes sense).
>>>>
>>>> Thanks ahead of time for any input or advice...
>>>>
>>>> Ryan
>>
> On Mar 18, 2010, at 6:38 PM, Ryan Utz wrote:
>
>> David,
>>
>> Thanks for responding. You were right; I misplaced a backspace (\)  
>> with a
>> forward space (/) in my posting. The c:\ doesn't actually work; in  
>> my true
>> code there is a forward space.
>>
>> Anyway, the results of str(data) appears as follows:
>>
>> 'data.frame':   90 obs. of  9 variables:
>> $ Fish (PCOrd)          : Factor w/ 90 levels "Fish1","Fish10",..:  
>> 1 2 3
>> 4 5 6 7 8 9 10 ...
>> $ Location              : Factor w/ 10 levels "16","18","24",..: 4  
>> 4 4 10
>> 10 4 4 4 4 4 ...
>> $ Treatment             : Factor w/ 2 levels "RES","UN": 1 1 1 2 2  
>> 1 1 1
>> 1 1 ...
>> $ Treatment Location    : Factor w/ 3 levels "IN","TOP","UN": 2 2 2  
>> 3 3 2
>> 2 2 2 2 ...
>> $ Fish Length (cm)      : num  7.8 6 7.1 6.6 7.2 6.6 6.4 6 6.15  
>> 5.7 ...
>> $ Fish Weight (g)       : num  5.72 3.27 4.46 3.57 4.78 3.41 3.3  
>> 2.3 2.42
>> 2.4 ...
>> $ Dried contents        : num  0.024 0.0349 0.017 0.0254 0.0456 ...
>> $ Combusted contents    : num  0.00203 0.00282 0.00155 0.00226  
>> 0.00353 ...
>> $ Estimated content mass: num  6.66e-05 2.91e-03 7.45e-03 1.28e-02
>> 5.46e-03 ...
>>>
>>
>> Any thoughts now?
>>
>>
>
> David Winsemius, MD
> West Hartford, CT
>
> ______________________________________________
> 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.

David Winsemius, MD
West Hartford, CT