Data can come from many different sources. Whether it’s entered manually, stored in files, or pulled from a database, importing data into R is the first step in any analysis. In this chapter, we’ll explore several methods to get data into R, including typing it in manually, importing CSV and Excel files, and connecting to more complex sources like Google Sheets and databases.
We’ll focus on the most common and practical methods first—methods you’ll encounter frequently in real-world scenarios. These methods vary based on the type of data you need to import.
If you’re working with small datasets or need to quickly test something, you might create a data frame manually in R. This is also a good way to familiarize yourself with the structure of data in R.
Here’s how to create a small data frame directly in R using the tibble() function from the tidyverse:
## Manually creating a small dataset
data <- tibble(
name = c("John", "Jane", "Alice", "Bob"),
age = c(25, 30, 28, 22),
occupation = c("Engineer", "Doctor", "Artist", "Student")
)
data# A tibble: 4 × 3
name age occupation
<chr> <dbl> <chr>
1 John 25 Engineer
2 Jane 30 Doctor
3 Alice 28 Artist
4 Bob 22 Student When the value is a string like a name, it is entered in quotes (“John”); when the value is numeric like an age, it is entered without quotes (25).
Also note that there is a comma (,) at the end of every line to signify that another argument is coming. There is no comma (,) at the end of the last argument occupation = ... because there are no more arguments after the occupation variable.
Starting with the code that manually creates a dataset named data, change the code to add an additional variable after age named gender where the gender of John is “Male”, Jane is “Female”, Alice is “Female”, and Bob is “Male”.
The variables name, age, and occupation are created by a new line with variable_name = c(value1, value2, value3, value4). What can you do to add a variable for gender as described above?
Add a line after age and define the gender variable with values of “Male”, “Female”, “Female”, “Male.”
gender = c(""Male", "Female", "Female", "Male")Add a line after age and define the gender variable with values of “Male”, “Female”, “Female”, “Male”.
1data <- tibble(
2 name = c("John", "Jane", "Alice", "Bob"),
3 age = c(25, 30, 28, 22),
4 gender = c("Male", "Female", "Female", "Male"),
5 occupation = c("Engineer", "Doctor", "Artist", "Student")
6)data by calling the tibble function
name variable with values of “John”, “Jane”, “Alice”, “Bob”
age variable with values of 25, 30, 28, 22
gender variable with values of “Male”, “Female”, “Female”, “Male”
occupation variable with values of “Engineer”, “Doctor”, “Artist”, “Student”
) to enclose the arguments of the function
This method is useful for small datasets or testing, but it is increasingly tedious and inaccurate as your data grows. You’ll usually import data from external sources like CSV or Excel files.
CSV (Comma-Separated Values) files are one of the most common formats for storing and sharing data. They are simple text files where each line corresponds to a row of data, and columns are separated by commas.
RStudio provides a user-friendly, graphical interface to import data files. This is especially helpful for beginners because it eliminates the need to write code manually and helps you understand the process as the IDE generates the import code for you.
Open the Files Pane: In the bottom-right of the RStudio interface, find the Files Pane, where you can browse the files on your computer.
Select the CSV File: Click on the .csv file that you want to import.
Open the Import Wizard: When you select the file, RStudio automatically opens an import wizard to guide you through the process.
Configure Import Settings: In the import wizard, you can adjust settings based on your dataset. Here are the key options:
,), but if your file uses something else (e.g., a semicolon), you can change it here."New York, NY").#).NA, NULL, ?).Adjust Data Types: At the top of each column in the data preview, you can see the data type (e.g., character, numeric). Change the type if R has incorrectly guessed the data type:
Click “Import”: Once you’ve adjusted the settings, click the “Import” button to bring the data into R.
View the Data: The data opens in RStudio’s Data Viewer, where you can inspect it visually.
Save the Import Code: The IDE automatically generates R code for the import and runs it in the console. Copy this code into your script to reuse it later, so you don’t have to reconfigure the settings manually each time.
While importing .csv data through the RStudio IDE is intuitive and visual, it has its limitations. The process can be manual, and any changes you make must be repeated each time you want to import the data. This is because the IDE doesn’t automatically store the import code in your R script, making the process not automatically reproducible. This is where AI-generated code can be a powerful solution.
Using AI to generate code opens up new possibilities for data analysis. Here are some of the benefits:
To make your workflow more efficient and reliable, consider the following approaches:
.csv Data with AI-Generated CodeTo import a simple .csv file using AI, provide the name of your .csv file and the desired name for the dataset in R.
Importing .csv Data:
“Please provide the tidyverse code to import a .csv file named tb.csv and name the resulting tibble tb_data.”
.csv Data with AI-Generated CodeQualtrics exports often contain metadata in both rows and columns. For example, the first and third rows, as well as certain columns, might include metadata you need to exclude. To handle this, you can use a more complex AI prompt like the one below.
Importing .csv Data with Metadata:
Please provide the tidyverse code to import a CSV file named ‘“long Qualtrics file name.csv”’ with the following requirements:
descriptive_name_data.By incorporating AI into your workflow, you can streamline the data import process, making it faster, more reliable, and easier to maintain. You maintain reproducibility and can easily handle more complex import tasks, such as skipping metadata rows and columns. This approach is especially useful when working with complex datasets or repetitive tasks where consistency is key.
While the RStudio IDE and AI tools can generate useful code for importing data, the quality and accuracy of the code they produce depend entirely on the quality of the prompts you provide through their respective interfaces. In both cases, you are responsible for the code because you generated the prompts that led to its creation.
This means that you, the user, must fully understand the code being generated to determine whether it accomplishes what you expected and to identify any errors. Owning the code means taking responsibility for both the prompt and the outcome, ensuring that the data is imported and handled as intended.
Understanding the code empowers you to:
Here, we’ll guide you through common scenarios for importing data, particularly dealing with .csv files from sources like Qualtrics. Understanding these reference templates will help you better recognize and verify the code you generate from the IDE or AI.
By learning how to write or adapt your own code, you’ll be able to confirm the accuracy of what your tools produce, troubleshoot errors, and modify scripts for future use.
To import a CSV file into R, we use the read_csv() function from the readr package, part of the tidyverse:
## Importing data from a CSV file
data <- read_csv("path/to/your/file.csv")Replace "path/to/your/file.csv" with the actual file path of your CSV file. This method is ideal for structured data that’s exported from spreadsheets, databases, or other software.
.csv DataLet’s go through a more complex example of importing Qualtrics data that includes metadata. We will build a single integrated code block, along with explanations for each step.
Qualtrics survey data often contains metadata that can interfere with your analysis. This metadata can be in the form of extra rows or columns that should be excluded. Below, we’ll demonstrate how to:
Metadata is “data about data.” In the context of a Qualtrics survey, it refers to the additional information generated by the survey platform about your actual survey data (the responses). This can include details about when the survey was completed, how long it took, the version of the survey, and more.
Here’s a consolidated example for handling Qualtrics metadata during and after import:
## Import the CSV data and skip the first (metadata) row
descriptive_name_data <- read_csv("long Qualtrics CSV file name.csv",
skip = 1) |>
## Remove unwanted columns
select(!(column1:columnN)) |> ## where column1 is the first column of metadata and columnN is the last column of metadata.
slice(-1) |> ## Remove the remaining row of metadata
type_convert() ## Reevaluate the data typesskip = 1: Skips the first row (metadata).select(!(column1:columnN)): Removes metadata columns 1 through N.slice(-1): Deletes the (new) first row (old third row) that still contains metadata.type_convert(): Automatically converts the data to appropriate types after cleaning.While the RStudio IDE and AI can generate similar code, slight differences can significantly impact functionality. Let’s look at how to spot these:
type_convert(), use glimpse(data) to verify that columns have been correctly identified as numeric, character, or date.head(data) and summary(data) to verify that your data was imported correctly.Let’s apply these steps to a real example. An entrepreneur had the idea to start a popup cupcake stand that would show up outside of university classrooms between classes. Students would be able to get a snack without leaving the classroom area or carrying it from home. The dataset is in a file named cupcakes.csv and the file is located in the data subdirectory of this document. Let’s import this CSV data file and deal with the metadata inserted by the Qualtrics survey:
cupcakes_data.Change the name of the data object as well as the argument to the read_csv() function to import the cupcakes data. Skip the first and third rows (metadata) and delete columns 1 through 17. Re-evaluate the data types.
data to cupcakes_datadatacupcakes.csvread_csv functionChange the code to match the specifics of importing the csv file cupcakes.csv.
cupcakes_data <- read_csv("data/cupcakes.csv",
skip = 1) |>
select(!(1:17)) |>
slice(-1) |>
type.convert()cupcakes_data"data"cupcakes.csvread_csv functionMastering how to write, understand, and verify code will make you more confident in using R for data analysis, even when relying on tools like the IDE or AI to generate code for you. Always make sure you know what your code is doing and be prepared to adjust and debug as needed.
Universal Format: CSV (Comma Separated Values) is a widely supported format across different platforms and tools, making it easy to share and transfer data.
Lightweight and Efficient: CSV files are simple text files, so they are lightweight and can be processed quickly by R without needing extra software or dependencies.
Standard Structure: CSV files have a straightforward row-and-column structure, making them easy to work with in R using functions like read.csv() or readr::read_csv().
Human-Readable: Since CSV files are plain text, you can easily open them in any text editor or spreadsheet software to view the raw data.
Minimal Formatting: CSV files don’t include complex formatting like fonts, formulas, or colors. This reduces the risk of issues during import, making it easier to focus on the actual data.
Preferred Format in Data Science: CSV is widely used by data science professionals due to its simplicity and compatibility. It avoids the problems associated with proprietary formats and is ideal for storing raw data.
Mastering CSV imports allows you to work efficiently with data in its simplest form, integrating seamlessly into your R workflow for analysis and visualization.
Excel is a widely used tool in business for data collection and analysis. R makes it easy to import Excel data, thanks to the readxl package. Whether you’re working with .xls or .xlsx formats, readxl provides a straightforward way to read Excel files into R.
readxl PackageTo work with Excel files in R, you need the readxl package. If it’s not already installed, you can install and load it with the following commands:
install.packages("readxl") ## Install the package (run once)
library(readxl) ## Load the packageOnce the readxl package is installed, you don’t need to install it again unless you are updating to a new version. However, you do need to load the package with library(readxl) every time you run your R script.
To avoid unnecessary installs, comment out the install.packages() line after the first installation. You should only keep the library(readxl) line active when running your code.
#install.packages("readxl") ## Install the package (run once)
library(readxl) ## Load the packageRStudio provides a graphical interface for importing Excel files, which can be easier than writing code manually. This approach allows you to visually select your settings and have RStudio generate the corresponding R code for you.
Navigate to the Files Pane: In the bottom-right corner of the RStudio interface, find the Files Pane where you can browse your local files.
Select the Excel File: Click on the .xlsx file you want to import.
Open the Import Wizard: RStudio will automatically launch the import wizard for Excel files.
Configure the Import Settings:
Save the Import Code: After completing the import, copy the generated R code from the console and paste it into your R script. This way, you can reuse the code for future imports without needing to reconfigure the settings.
Importing Excel spreadsheets (.xlsx) through the RStudio IDE is intuitive and visual but it is also manual and not automatically reproducible. An AI can provide code for you R script that will work every time you need to import the data.
Importing .xlsx Data:
“Please provide the tidyverse code to import a .xlsx file named data_file.xlsx and name the resulting tibble descriptive_name_data.”
.xlsx Data with AI-Generated CodeQualtrics exports often contain metadata in both rows and columns. For example, the first and third rows, as well as certain columns, might include metadata you need to exclude. To handle this, you can use a more complex AI prompt like the one below.
Importing .xlsx Data with Metadata:
Please provide the tidyverse code to import an Excel file named "long Qualtrics file name.xlsx" with the following requirements:
Start Date through User Language) contain metadata and should be excluded from the dataset.descriptive_name_data.By using AI to generate the import code, you maintain reproducibility and can easily handle more complex import tasks, such as skipping metadata rows and columns.
When using RStudio’s IDE or AI-generated tools, the quality and reliability of the code depend on the clarity of your prompts or configuration choices. Owning the code means taking responsibility for what the code does, verifying its output, and being able to debug or modify it if needed. This becomes especially important when working with more complex data formats like Excel files.
Understanding and being able to read the code allows you to:
The read_excel() function from the readxl package is typically used to import Excel files into R. Below is a reference template that you can compare with your generated code:
## Template for importing Excel data
data <- read_excel("path/to/your/file.xlsx",
skip = 1) |> ## Skip the first row of metadata
slice(-1) |> ## Remove the new first row (originally the third row) of metadata
select(-(1:N)) |> ## Exclude columns from column 1 through column N - can also use column headers
type.convert(). ## Re-evaluate the data types for all columnsskip = 1: Skips the first row of metadata, making the second row the column headersslice(-1): Remove the new first row (originally the third row) of metadataselect(-(1:N)): Exclude columns of metadata (1 through N) by their column numbers - can also exclude by their column namestype_convert(): Automatically converts the data to appropriate types after cleaning.Replace “path/to/your/file.xlsx” with the actual file path, and modify the arguments based on your specific data needs. This code provides more control over the import process, allowing you to skip metadata rows, select a specific sheet, or limit the import to a defined range of cells.
The cupcake entrepreneur is also considering a popup cinnamon roll stand. The Qualtrics dataset is an Excel file named cinnamon_rolls.xlsx and the file is located in the data subdirectory of this document. It has two sheets. The first sheet contains all of the original data where the first and third rows are meta data and columns A through N are also meta data. The second sheet has been edited in Excel to remove all meta data to include only customer data.
It is more reproducible to use code during data import than to manually edit the spreadsheet. Change the code to successfully import only the customer data (excluding the meta data) from the first sheet of the cinnamon rolls Excel file. Note that the customer data is found in columns O through X and there were 23 respondents.
Let’s import this Excel data file:
cinnamon_rolls_dataChange the arguments of the read_excel() function to specify the name of the data, and the skip argument to skip the first row of metadata. Then delete the remaining row of metadata (if there is one), exclude the columns of metadata, and re-evaluate the data types.
cinnamon_rolls_dataskip argument to exclude the first row of metadataslice()select()type.convert()Change the code to skip the first row of metadata making the second row column headers. Then remove the new first row of metadata because there is one, then exclude columns 1 through 14 containing metadata, and re-evaluate the data types.
cinnamon_rolls_data <- read_excel("data/cinnamon_rolls.xlsx",
skip = 1) |>
slice(-1) |>
select(-(1:14)) |>
type.convert()
cinnamon_rolls_datacinnamon_rolls_data"data"cinnamon_rolls.xlsxskip = 1slice(-1)select(-(1:14))type.convert()readxl makes it easy to access specific sheets.readxl does not require external Java or Perl dependencies, making it lightweight and straightforward to use.By mastering Excel imports, you’ll be able to integrate datasets stored in spreadsheets into your R workflow seamlessly, allowing for further manipulation, analysis, and visualization.
In addition to CSV and Excel files, you may often need to import data directly from Google Sheets. Google Sheets is a popular cloud-based spreadsheet application that allows you to share and collaborate on data. Using the googlesheets4 package, you can import data directly from a Google Sheet into R.
googlesheets4 PackageIf you haven’t already installed the googlesheets4 package, you can do so by running the following command:
install.packages("googlesheets4")Then, load the package:
library(googlesheets4)As we saw earlier, using the RStudio IDE to import data can be a helpful way to quickly set up an import. Unfortunately, RStudio has no option to import data from a Google Sheet using the graphical interface of the IDE. We must use AI-generated code or user-generated code to import data from a Google sheet.
To import a Google Sheet through AI, you need to provide the AI with the URL of your Google Sheet and specify the name you want to assign the dataset in R. Additionally, you’ll need to ensure the googlesheets4 package is installed and loaded.
Import Google Sheets data:
Please provide the tidyverse code to import a Google Sheet from the following URL: https://docs.google.com/spreadsheets/d/your_sheet_id/edit with the following requirements:
googlesheets4 package to read the data.sheet_data.To read data from a Google Sheet, you will need the URL of the Google Sheet. Here’s an example of how to import data:
## Import data from a Google Sheet
sheet_url <- "https://docs.google.com/spreadsheets/d/your_sheet_id/edit"
data <- read_sheet(sheet_url)
dataReplace "https://docs.google.com/spreadsheets/d/your_sheet_id/edit" with the actual URL of your Google Sheet.
If the Google Sheet contains multiple tabs, you can specify which sheet to read using the sheet argument:
data <- read_sheet(sheet_url, sheet = "Sheet2")The default name of the first Google worksheet is Sheet1 in contrast to the default name of the first Excel worksheet Sheet0.
You can also specify a particular range of cells to import:
data <- read_sheet(sheet_url, range = "A1:D10")This is particularly useful if you only need a subset of the data from the sheet.
googlesheets4, importing data from Google Sheets is as straightforward as reading a CSV file.By using Google Sheets in combination with R, you can work with dynamic datasets that are updated live, streamlining collaboration and analysis.
In many cases, data is stored in databases rather than in spreadsheets or files. R provides excellent tools for connecting to databases, querying data, and importing it for analysis. In this section, we will use the DBI package along with RSQLite to connect to SQLite databases. These tools are versatile and can also be adapted to connect to other database systems such as PostgreSQL, MySQL, or SQL Server with minimal changes.
If you haven’t already installed the DBI and RSQLite packages, you can do so by running the following commands. Then load the libraries:
### Install the `DBI` and `RSQLite` Packages
install.packages("DBI")
install.packages("RSQLite")
### Load the packages
library(DBI)
library(RSQLite)SQLite is a lightweight, file-based database that is easy to work with. To connect to an SQLite database in R, use the dbConnect() function from the DBI package. Here’s an example of how to connect to an SQLite database:
## Create a connection to the SQLite database
con <- dbConnect(RSQLite::SQLite(), "path/to/your/database.sqlite")Replace "path/to/your/database.sqlite" with the path to your SQLite database file.
Once you’ve established a connection, you can run SQL queries directly from R to extract the data you need. For example, you can use the dbGetQuery() function to run a SELECT query and store the result in a data frame:
## Run a SQL query and retrieve data
data <- dbGetQuery(con, "SELECT * FROM your_table")
## View the data
print(data)This command runs a SQL query that selects all the data from the specified table (your_table) in the database.
You can explore the structure of your database and list all available tables with the following command:
## List all tables in the database
tables <- dbListTables(con)
print(tables)To examine the structure of a particular table, you can run:
## List the fields in a specific table
fields <- dbListFields(con, "your_table")
print(fields)This can be helpful when you need to understand the schema of the database.
After you have finished working with the database, it is good practice to disconnect from it. Use the dbDisconnect() function to close the connection:
## Disconnect from the database
dbDisconnect(con)The process for connecting to other databases (such as PostgreSQL, MySQL, or SQL Server) is similar. The main difference is that you will use a different driver package specific to that database (e.g., RPostgres for PostgreSQL, RMySQL for MySQL).
Here’s an example of connecting to a PostgreSQL database:
## Install the PostgreSQL driver
install.packages("RPostgres")
## Load the package
library(RPostgres)
## Create a connection to a PostgreSQL database
con <- dbConnect(RPostgres::Postgres(), dbname = "your_db_name", host = "localhost", port = 5432, user = "your_username", password = "your_password")The same DBI functions like dbGetQuery(), dbListTables(), and dbDisconnect() will work with any database supported by DBI.
By using the DBI and RSQLite packages, you can seamlessly integrate databases into your R workflow, query data directly from the source, and manage large datasets with ease.
In our connected world, much valuable data resides online and is accessible through web services and APIs (Application Programming Interfaces). Using R, you can connect to these web services to pull in data directly. In this section, we will introduce how to use the httr package to access APIs and import data from web services into R.
APIs are commonly used to retrieve data from various platforms such as social media sites, financial databases, weather services, and more. The httr package makes it simple to send HTTP requests and retrieve data from these APIs.
httr PackageIf you haven’t already installed the httr package, you can do so and then load the package with the following commands:
install.packages("httr")
library(httr)APIs usually require you to make HTTP requests, and they respond with data in formats such as JSON or XML. The httr package simplifies this process by providing functions to make requests and handle responses.
Here’s an example of how to make a GET request to an API:
## Example: Making a GET request to a public API
response <- GET("https://api.example.com/data")
## Check the status of the response
status_code(response)
## View the content of the response
content(response, "text")GET(): Sends a GET request to the API endpoint. Replace "https://api.example.com/data" with the actual API endpoint you want to query.APIs often return data in JSON (JavaScript Object Notation) format. After retrieving the response, you need to parse the JSON data and convert it into a format that R can work with, such as a data frame.
To parse JSON data, you can use the jsonlite package:
install.packages("jsonlite")
library(jsonlite)
## Parse the JSON content and convert it to a data frame
data <- fromJSON(content(response, "text"))
## View the data
print(data)This code converts the JSON response into a data frame that you can use for further analysis in R.
Many APIs require an API key or some form of authentication to access the data. You will need to include your API key in the request headers. Here’s an example of how to make an authenticated request:
## Example: Making an authenticated GET request
api_key <- "your_api_key"
response <- GET("https://api.example.com/data", add_headers(Authorization = paste("Bearer", api_key)))
## Parse the JSON content and convert it to a data frame
data <- fromJSON(content(response, "text"))
## View the data
print(data)Replace "your_api_key" with the actual API key provided by the web service. The add_headers() function adds the necessary authorization to the request.
Here’s an example of how you could use the httr package to fetch data on the location of the International Space Station (ISS) at this moment:
library(httr)
library(jsonlite)
## Make a GET request to Open Notify API (current ISS location)
response <- GET("http://api.open-notify.org/iss-now.json")
data <- fromJSON(content(response, "text"))
## View the data
print(data)This code sends a request to the open-notify.org API for the ISS location data. The JSON response is parsed and converted into a format you can analyze.
Here’s an example of how you could use the httr package to fetch data on random dog breeds:
library(httr)
library(jsonlite)
## Make a GET request to The Dog API (random dog image)
response <- GET("https://dog.ceo/api/breeds/image/random")
data <- fromJSON(content(response, "text"))
## View the data
print(data)This code sends a request to the dog.ceo API for the url of a random dog breed. The JSON response is parsed and converted into a format you can analyze.
Note that these examples are chosen to avoid providing a personal api. Usually, you would need to sign up for an account and log in. Once logged in, navigate to the API keys section and generate an API key. Copy the generated API key and paste it in the approprate place for an api key in your code.
By using APIs and the httr package, you can integrate real-time web data into your R workflows, opening up a wide range of possibilities for data analysis.
Leave the default Locale setting unless you are working with European CSVs where the file uses commas for decimals and periods for thousands separators.; dealing with data from multiple countries with different encoding, date formats, or language-based special characters; or when text includes special non-ASCII characters (e.g., accented letters).↩︎
In CSV files, escaping is necessary when you want to include characters that otherwise have a special meaning in the file, such as the delimiter (comma) or quotation marks within a field, without breaking the file’s structure.↩︎