Workshop Overview: Setup for R and SQLite
Overview
This workshop is designed to be run on your laptop. First, you will need to download the data we use in the workshop. Then, you need to install some software. After following the instructions on this page, you should have everything you need to participate fully in the workshop!
Software
| Software | Install | Manual | Available for | Description |
|---|---|---|---|---|
| SQLite Browser | Link | Link | Linux, MacOS, Windows | Tool for creating, designing, and editing database files. |
| R | See install instructions below. | Linux, MacOS, Windows | Programming language for data analysis and visualisation. |
SQL
Download datasets:
- pre-populated sqlite database: SQL_SAFI.sqlite
- Farms table as csv : SAFI_farms.csv
Installing DB Browser for SQLite
The software can be downloaded from the DB Browser site From the front page you can select the version you require. There are specific downloads for Windows and Mac users. For various Linux distributions there are detailed instructions at the bottom of the page.
Installing for Windows.
For a current Windows environment the 64-bit windows download will be most appropriate.
The download is a windows executable file which you can run by double clicking it. It opens an installation wizard. You can default all of the options in the wizard. You will require admin permissions on the PC/Laptop you install on. By default the application is launched automatically when the installation is complete. It does not create an icon on the desktop. To explicitly launch the application after installing it, use the windows button (bottom left of screen) and type in ‘DB Browser’ in the search bar and selecting the application when it appears.
Install the SQLite Shell program
The SQLite shell can be downloaded from here. There are versions available for Linux, Mac and Windows. As I have a Windows machine I will download the Windows version. You should download the version appropriate to your machine. Note that MacOS already have sqlite installed so you can skip this section.
The number after the x86- may be different when you download if a later version has been released. The download is a .zip file. You need to unzip the file and store the contents (3 files) in a folder of your choosing. There is no actual install process, the program (file) sqlite3.exe can be run directly from the folder. You may however like to add the folder location to your PATH environment variable so that you can call sqlite3 from any command prompt.
Invoke the SQLite Shell program
You invoke the SQLite Shell from the commandline. Remember that the program is sqlite3 and you must have added the folder name to your envirnment PATH or explicitly navigated to the folder before trying to run the program.
You do not need to specify any parameters, connection to a databse can be done from within the shell.
Installing the SQLite ODBC connector
The SQLIte main site at https://sqlite.org/ does not provide a download for an ODBC connector. A Google search will provide other sites that do. One freely available SQLite ODBC connector is available at http://www.ch-werner.de/sqliteodbc/. You should download the sqliteodbc.exe file. The file is a self contained Windows installer which you can run by double clicking it. You will however need Admin rights on the machine to perform the install.
This is a 32bit ODBC connector so it is assumed that you are using a 32bit version of Excel. A 64bit version of the driver is available from the Werner site should you need it.
You can check that the driver has been successfully installed by typing ODBC into the Windows start search panel and then selecting ‘ODBC DataSources (32 bit)’
At the bottom of the list in the ‘system DSN’ tab youshould see the entry for the ‘SQlite3 datasource’.
R
R and RStudio are separate downloads and installations. R is the underlying statistical computing environment, but using R alone is no fun. RStudio is a graphical integrated development environment (IDE) that makes using R much easier and more interactive. You need to install R before you install RStudio. Once installed, because RStudio is an IDE, RStudio will run R in the background. You do not need to run it separately.
After installing both programs,
you will need to install the tidyverse package from within RStudio. The
tidyverse package is a powerful collection of data science tools within R
see the tidyverse website for more details.
Follow the instructions below for your operating system, and then follow the
instructions to install tidyverse.
Windows
If you already have R and RStudio installed
- Open RStudio, and click on “Help” > “Check for updates”. If a new version is available, quit RStudio, and download the latest version for RStudio.
- To check which version of R you are using, start RStudio and the first thing
that appears in the console indicates the version of R you are
running. Alternatively, you can type
sessionInfo(), which will also display which version of R you are running. Go on the CRAN website and check whether a more recent version is available. If so, please download and install it. You can check here for more information on how to remove old versions from your system if you wish to do so.
If you don’t have R and RStudio installed
- Download R from the CRAN website.
- Run the
.exefile that was just downloaded. - Go to the RStudio download page.
- Under Installers select RStudio x.yy.zzz - Windows. Vista/7/8/10 (where x, y, and z represent version numbers).
- Double click the file to install it.
- Once it’s installed, open RStudio to make sure it works and you don’t get any error messages.
macOS
If you already have R and RStudio installed
- Open RStudio, and click on “Help” > “Check for updates”. If a new version is available, quit RStudio, and download the latest version for RStudio.
- To check the version of R you are using, start RStudio and the first thing
that appears on the terminal indicates the version of R you are running. Alternatively, you can type
sessionInfo(), which will also display which version of R you are running. Go on the CRAN website and check whether a more recent version is available. If so, please download and install it. In any case, make sure you have at least R 3.2.
If you don’t have R and RStudio installed
- Download R from the CRAN website.
- Select the
.pkgfile for the latest R version. - Double click on the downloaded file to install R.
- It is also a good idea to install XQuartz (needed by some packages).
- Go to the RStudio download page.
- Under Installers select RStudio x.yy.zzz - Mac OS X 10.6+ (64-bit) (where x, y, and z represent version numbers).
- Double click the file to install RStudio.
- Once it’s installed, open RStudio to make sure it works and you don’t get any error messages.
Linux
- Follow the instructions for your distribution
from CRAN, they provide information
to get the most recent version of R for common distributions. For most
distributions, you could use your package manager (e.g., for Debian/Ubuntu run
sudo apt-get install r-base, and for Fedorasudo yum install R), but we don’t recommend this approach as the versions provided by this approach are usually out of date. In any case, make sure you have at least R 3.2. - Go to the RStudio download page.
- Under Installers select the version that matches your distribution, and
install it with your preferred method (e.g., with Debian/Ubuntu
sudo dpkg -i rstudio-x.yy.zzz-amd64.debat the terminal). - Once it’s installed, open RStudio to make sure it works and you don’t get any error messages.
For everyone
After installing R and RStudio, you need to install the tidyverse package.
-
After starting RStudio, at the console type:
install.packages("tidyverse") -
For reference, the lesson uses
SAFI_clean.csv. The direct download link for this file is: https://ndownloader.figshare.com/files/11492171. To download fromR:
{r, eval=TRUE, message=FALSE, purl=FALSE}
download.file("https://ndownloader.figshare.com/files/11492171",
"data/SAFI_clean.csv", mode = "wb")
- The json episode uses
SAFI.json. The downlink link is https://datacarpentry.org/r-socialsci/data/SAFI.json.
Congratulations! You are now ready for the workshop!