19  Data Import and Cleaning

Data preparation and transformation are crucial steps in data analysis, ensuring that raw data is cleaned, structured, and formatted for further analysis (Hadley Wickham, 2014). In R, several functions and packages help perform these tasks efficiently. This topic covers the essentials: importing farm-level data into R, exploring it, and cleaning it up before analysis.

19.1 Data Handling in R

Data handling in R refers to the importing, managing, transforming, and cleaning of data before analysis. R provides powerful functions and packages such as readr, dplyr, tidyr, and data.table to efficiently handle large agricultural datasets — from a single district’s farm records to a multi-season national survey.

19.2 Importing Data into R

Importing data into R is the first step in any analysis. R offers functions for loading data from a range of sources.

  • R supports importing data from Excel, CSV, databases (SQL), JSON, and APIs.

19.2.1 Import a CSV File from a Website

19.2.2 Import a CSV File from a Local Folder

Once a dataset is saved to your computer, copy its file path (or, if your working directory is already set, just its file name) and pass it to read.csv("file name").

  • To copy a file’s path name: right-click the file and choose copy path name, or use the keyboard shortcut — Ctrl+Shift+C on Windows, Cmd+Option+C on Mac.
Code
# Read a CSV file saved locally
india_ag <- read.csv("Agriculture production india.csv")

# Display the first few rows
head(india_ag)

19.2.3 Import an Excel File from a Local Folder

readxl: To read Excel files in R, install the readxl package first.

  • The readxl package imports Excel spreadsheets (.xls and .xlsx) into R for further analysis.
  • Supports both .xls (Excel 97–2003) and .xlsx (Excel 2007+) formats.
  • Can read specific sheets, ranges, and named regions.

Install the readxl Package

Code

Sample Data File

Sample dataset: European agriculture (Excel)

Copy the file’s name (or full path) and pass it to read_excel("file_name").

Code
library(readxl)
europe_ag <- read_excel("Europeanagriculture.xlsx", sheet = 1)
head(europe_ag)

19.2.4 Import Data from a SQL Database

R can connect directly to a relational database and pull a table into a data frame — useful when farm records live in a departmental or cooperative database rather than a flat file.

  • Install the packages below.
  • Point dbConnect() at your own .sqlite file — the code below is illustrative and will only run once you supply a real database file with a farms table in it, so it is shown with eval: false rather than as a live example.
Code
Install the required packages for SQL access
install.packages("DBI")       # Database interface package
install.packages("RSQLite")   # SQLite database driver (for MySQL, use RMariaDB)
install.packages("dplyr")     # For data manipulation
Code
library(DBI)
library(RSQLite)
library(dplyr)

# Connect to a local SQLite database file
con <- dbConnect(RSQLite::SQLite(), "farm_records.sqlite")

# List tables in the database
dbListTables(con)

# Import an entire table into R
farms_db <- dbReadTable(con, "farms")

# Display the first few rows
head(farms_db)

# Close the database connection
dbDisconnect(con)

19.2.5 Creating a Data File in R

Use the data.frame() function to build a dataset directly in R — useful for small worksheets, quick examples, or data you’ve collected by hand. The farms dataset below is used throughout this topic; it deliberately includes a repeated row (F001/F002) and a few missing values (NA), so the cleaning steps that follow have something real to work with.

19.3 Viewing and Exploring Data

19.3.1 Checking Data Structure

  • View() — view the entire table in a window
  • head() — view the first few rows
  • str() — check the structure of a dataset
  • summary() — summary statistics

19.3.2 Checking Missing Values

19.3.3 Checking Duplicates

19.4 Handling Missing Values and Imputations

19.4.1 Replace Missing Values with the Mean

19.4.2 Remove Missing Values

19.4.3 Remove Duplicates

19.4.4 Changing Data Types

Summary

Concept Description
Importing Data
Data Handling in R The importing, managing, transforming, and cleaning of data before analysis, using packages such as readr, dplyr, tidyr, and data.table
Importing Data into R R can import data from CSV, Excel, SQL databases, JSON, and APIs
Import from SQL Database dbConnect(), dbListTables(), and dbReadTable() pull a table from a relational database into a data frame
Creating a Data File in R data.frame() builds a dataset directly in R from vectors of values
Viewing and Exploring Data
Checking Data Structure View(), head(), str(), and summary() reveal a dataset's shape and contents
Checking Missing Values colSums(is.na(data)) counts missing values in each column
Checking Duplicates duplicated() flags rows that repeat earlier rows
Handling Missing Values and Imputations
Replace Missing Values with the Mean mean(x, na.rm = TRUE) computes a mean while ignoring NAs, which can then fill missing values
Remove Missing Values na.omit() drops any row containing a missing value
Remove Duplicates Subsetting with !duplicated() removes repeated rows
Changing Data Types as.numeric() and as.character() convert a column's data type