Excel files with readxl in R Programming - Mini Project: Build & Apply

Excel files with readxl

📖 Scenario: You work in a small business that keeps sales data in Excel files. You want to read this data into R to analyze it easily.

🎯 Goal: Learn how to use the readxl package to read Excel files into R and display the data.

📋 What You'll Learn

Use the readxl package

Read an Excel file named sales_data.xlsx

Store the data in a variable called sales

Print the sales data frame

💡 Why This Matters

🌍 Real World

Reading Excel files is common in business to analyze sales, inventory, or customer data.

💼 Career

Data analysts and scientists often import Excel data into R for cleaning and analysis.

1

Load the readxl package

Write library(readxl) to load the readxl package.

R Programming

# Load the readxl package
# Your code here

Hint

Use library(readxl) to load the package before reading Excel files.

2

Read the Excel file

Write sales <- read_excel("sales_data.xlsx") to read the Excel file into a variable called sales.

R Programming

library(readxl)
# Read the Excel file into sales
# Your code here

Hint

Use read_excel() with the file name to read the Excel file.

3

Check the data structure

Write str(sales) to display the structure of the sales data frame.

R Programming

library(readxl)
sales <- read_excel("sales_data.xlsx")
# Show the structure of sales
# Your code here

Hint

Use str() to see the data frame structure.

4

Print the sales data

Write print(sales) to display the contents of the sales data frame.

R Programming

library(readxl)
sales <- read_excel("sales_data.xlsx")
str(sales)
# Print the sales data
# Your code here

Hint

Use print() to show the data frame contents.

Practice

1. What is the primary purpose of the readxl package in R?

easy

A. To convert Excel files to CSV format

B. To write Excel files from R data frames

C. To read Excel files (.xls, .xlsx) into R data frames

D. To create Excel charts from R data

Solution

1. Step 1: Understand the purpose of readxl

   The readxl package is designed to import Excel files into R as data frames.

2. Step 2: Differentiate from other packages

   It does not write files or create charts; it only reads Excel files.

3. Final Answer:

   To read Excel files (.xls, .xlsx) into R data frames -> Option C

4. Quick Check:

   readxl = read Excel files [OK]

Hint: Remember: readxl reads Excel files into R [OK]

Common Mistakes:

• Confusing readxl with packages that write Excel files
• Thinking readxl creates charts
• Assuming readxl converts files to CSV

2. Which of the following is the correct syntax to read the first sheet of an Excel file named data.xlsx using readxl?

easy

A. read_excel(1, "data.xlsx")

B. read_excel("data.xlsx", sheet = 1)

C. read_excel("data.xlsx", sheet = "first")

D. read_excel("data.xlsx", sheet = TRUE)

Solution

1. Step 1: Check the function syntax

   The function read_excel() takes the file path as the first argument, then optional parameters like sheet.

2. Step 2: Validate sheet argument

   Sheet can be specified by number (1) or name (string). read_excel("data.xlsx", sheet = 1) uses sheet = 1 correctly.

3. Final Answer:

   read_excel("data.xlsx", sheet = 1) -> Option B

4. Quick Check:

   File path first, then sheet number [OK]

Hint: File path first, sheet number second in read_excel() [OK]

Common Mistakes:

• Swapping argument order
• Using invalid sheet names like "first"
• Passing TRUE instead of sheet number or name

3. What will be the output of this R code snippet using readxl?

library(readxl)
data <- read_excel("file.xlsx", range = "B2:D4")
print(dim(data))

medium

A. [1] 3 3

B. [1] 4 3

C. [1] 3 4

D. [1] 4 4

Solution

1. Step 1: Understand the range argument

   The range "B2:D4" selects columns B to D (3 columns) and rows 2 to 4 (3 rows).

2. Step 2: Determine dimensions of the data frame

   So the data frame will have 3 rows and 3 columns.

3. Final Answer:

   [1] 3 3 -> Option A

4. Quick Check:

   Range B2:D4 = 3 rows x 3 columns [OK]

Hint: Count rows and columns in range like Excel cells [OK]

Common Mistakes:

• Counting rows or columns incorrectly
• Confusing row and column order
• Assuming range includes headers automatically

4. Identify the error in this code snippet that tries to read the sheet named "Sales" from report.xlsx:

library(readxl)
data <- read_excel("report.xlsx", sheet = Sales)

medium

A. Sheet name must be a string in quotes

B. File path should not be in quotes

C. read_excel does not support sheet argument

D. Missing library call for readxl

Solution

1. Step 1: Check sheet argument type

   The sheet argument expects a string for sheet name, so it must be in quotes like "Sales".

2. Step 2: Verify other parts

   File path is correctly quoted, readxl supports sheet argument, and library is loaded.

3. Final Answer:

   Sheet name must be a string in quotes -> Option A

4. Quick Check:

   Sheet names need quotes [OK]

Hint: Put sheet names inside quotes in read_excel() [OK]

Common Mistakes:

• Forgetting quotes around sheet names
• Removing quotes from file path
• Not loading readxl library

5. You want to read only the cells from columns C to E and rows 5 to 10 in the sheet named "Data" from records.xlsx. Which code correctly does this using readxl?

hard

A. read_excel("records.xlsx", sheet = "Data", range = "5:10")

B. read_excel("records.xlsx", sheet = Data, range = "C5:E10")

C. read_excel("records.xlsx", range = "C5:E10")

D. read_excel("records.xlsx", sheet = "Data", range = "C5:E10")

Solution

1. Step 1: Specify sheet name correctly

   The sheet name "Data" must be a string in quotes.

2. Step 2: Use correct range format

   The range "C5:E10" selects columns C to E and rows 5 to 10, exactly what is needed.

3. Step 3: Confirm all arguments present

   read_excel("records.xlsx", sheet = "Data", range = "C5:E10") includes file path, sheet name, and range correctly.

4. Final Answer:

   read_excel("records.xlsx", sheet = "Data", range = "C5:E10") -> Option D

5. Quick Check:

   Sheet name + range in quotes = correct [OK]

Hint: Use sheet and range as strings in read_excel() [OK]

Common Mistakes:

• Omitting quotes around sheet name
• Using incomplete range like "5:10"
• Not specifying sheet when multiple sheets exist