Home > Software engineering >  Does the R sqldf() function support a way to show the columns of a table (i.e. describe)
Does the R sqldf() function support a way to show the columns of a table (i.e. describe)

Time:11-09

Does the R function sqldf() support a way to show table columns similar to what the sql command "desc my_table" would do. It does not seem to support "desc my_table" or "describe_my_table" and I'm not seeing a way to do this in the documentation at the links below:

https://www.rdocumentation.org/packages/sqldf/versions/0.4-11

https://www.rdocumentation.org/packages/sqldf/versions/0.4-11/topics/sqldf

install.packages("sqldf")

> data_file <- "path/to/file/dash-activity.csv"
> data <- read.csv(data_file)
> sqldf("
    select 
      state, 
      count(*) as count, 
      round(cast(count(*) as real)/154 * 100, 2) as pct
    from data
    group by 1
    order by 1
  ")
   state count   pct
1     DE    12  7.79
2     FL    18 11.69
3     HI    23 14.94
4     KY     5  3.25
5     MD    31 20.13
6     ME     4  2.60
7     NC     7  4.55
8     ND     4  2.60
9     NM    13  8.44
10    NV     4  2.60
11    RI     5  3.25
12    VA     9  5.84
13    VT    13  8.44
14    WV     6  3.90
> sqldf("
    desc data
  ")
Error: near "desc": syntax error
> sqldf("
    describe data
  ")
Error: near "describe": syntax error
> 

--- EDIT -----------------------

The accepted answer gives the following results (my table is named "data")

> sqldf("pragma table_info('data')")
   cid                  name    type notnull dflt_value pk
1    0                   sex    TEXT       0         NA  0
2    1                  race    TEXT       0         NA  0
3    2                 grade    TEXT       0         NA  0
4    3           location_id INTEGER       0         NA  0
5    4                 state    TEXT       0         NA  0
6    5         location_desc    TEXT       0         NA  0
7    6        short_question    TEXT       0         NA  0
8    7 greater_risk_question    TEXT       0         NA  0
9    8   pct_were_not_active    REAL       0         NA  0
10   9                 units    TEXT       0         NA  0

CodePudding user response:

Using pragma:

library(sqldf)

d <- head(iris[ c(1,5) ])
sqldf("select * from d")

sqldf("pragma table_info('d')")
#   cid         name type notnull dflt_value pk
# 1   0 Sepal.Length REAL       0         NA  0
# 2   1      Species TEXT       0         NA  0

See SQLite Describe Table.

  • Related