(This article was first published on

**Yet Another Blog in Statistical Computing » S+/R**, and kindly contributed to R-bloggers)Similar to R and Pandas in Python, Julia provides a simple yet efficient interface with SQLite database. In addition, it is extremely handy to use sqldf() function, which is almost identical to the sqldf package in R, in SQLite package for data munging.

julia> # LOADING SQLITE PACKAGE julia> using SQLite julia> # CONNECT TO THE SQLITE DB FILE julia> db = SQLite.connect("/home/liuwensui/Documents/db/sqlitedb/csdata.db") julia> # SHOW TABLES IN THE DB julia> query("select name from sqlite_master where type = 'table'") 1x1 DataFrame |-------|-----------| | Row # | name | | 1 | tblcsdata | julia> # PULL DATA FROM THE TABLE julia> # THE DATA WOULD BE AUTOMATICALLY SAVED AS A DATAFRAME julia> df1 = query("select * from tblcsdata"); julia> head(df1, 2) 6x12 DataFrame |-------|---------|----------|-----------|---------|-----------|----------|-----|-----------|-------|-------|-------|-------| | Row # | LEV_LT3 | TAX_NDEB | COLLAT1 | SIZE1 | PROF2 | GROWTH2 | AGE | LIQ | IND2A | IND3A | IND4A | IND5A | | 1 | 0.0 | 0.530298 | 0.0791719 | 13.132 | 0.0820164 | 1.16649 | 53 | 0.385779 | 0 | 0 | 1 | 0 | | 2 | 0.0 | 0.370025 | 0.0407454 | 12.1326 | 0.0826154 | 11.092 | 54 | 0.224123 | 1 | 0 | 0 | 0 | julia> # SELECT DATA FROM THE TABLE WITH SQLDF() FUNCTION julia> df2 = sqldf("select * from df1 where AGE between 25 and 30"); julia> # SUMMARIZE DATA WITH SQLDF() FUNCTION julia> df3 = sqldf("select age, avg(LEV_LT3) as avg_lev from df2 group by age") 6x2 DataFrame |-------|-----|-----------| | Row # | AGE | avg_lev | | 1 | 25 | 0.0923202 | | 2 | 26 | 0.0915009 | | 3 | 27 | 0.0579876 | | 4 | 28 | 0.104191 | | 5 | 29 | 0.0764582 | | 6 | 30 | 0.0806471 |

To

**leave a comment**for the author, please follow the link and comment on their blog:**Yet Another Blog in Statistical Computing » S+/R**.R-bloggers.com offers

**daily e-mail updates**about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...