Reading an Excel File, Performing Data Analysis and Visualization, and Writing Results to an Excel File in R

 

Experiment

Reading an Excel File, Performing Data Analysis and Visualization, and Writing Results to an Excel File in R

Aim

To read student data from an Excel file, perform statistical analysis and visualization, and write the student list sorted by total marks to a new Excel file.

---

Objectives

1. To read data from an Excel file using R.
2. To explore the structure and summary statistics of the dataset.
3. To calculate total marks and average marks.
4. To identify toppers and passed students.
5. To visualize the data using various plots.
6. To sort the students according to total marks.
7. To write the sorted list to a new Excel file.

---

Theory

Excel files are widely used for storing tabular data. R provides packages such as readxl and openxlsx for reading and writing Excel files.

Functions used:

Function	Purpose
read_excel()	Read Excel file
write.xlsx()	Write Excel file
summary()	Summary statistics
str()	Structure of dataset
order()	Sorting
barplot()	Bar graph
hist()	Histogram
boxplot()	Box plot
plot()	Scatter plot
pie()	Pie chart

---

Software Required

• R
• RStudio
• Packages:
  • readxl
  • openxlsx

Install the packages:

install.packages("readxl")
install.packages("openxlsx")

Load the packages:

library(readxl)
library(openxlsx)

---

Sample Excel File

Suppose the file stud.xlsx contains:

Rno	Name	M1	M2
101	John	85	78
102	Mary	95	88
103	Alex	45	65
104	David	76	82
105	Sara	90	95
106	Tom	55	60
107	Riya	88	91
108	Sam	62	70

---

Algorithm

1. Load the required packages.
2. Read the Excel file.
3. Display the structure and summary.
4. Compute total and average marks.
5. Find the topper.
6. Find passed students.
7. Sort students according to total marks.
8. Create various plots.
9. Write the sorted data to an Excel file.

---

Program

# Load libraries
library(readxl)
library(openxlsx)

# Read Excel file
stud <- read_excel("stud.xlsx")

# Display the data
print(stud)

# Structure of data
str(stud)

# Summary statistics
summary(stud)

# Dimensions
dim(stud)

# Add Total and Average columns
stud$Total <- stud$M1 + stud$M2
stud$Average <- stud$Total/2

# Display updated data
print(stud)

# Find topper
topper <- stud[stud$Total == max(stud$Total), ]

cat("Topper Details:\n")
print(topper)

# Passed students (marks >=50 in both subjects)
passed <- stud[stud$M1 >= 50 &
               stud$M2 >= 50, ]

cat("Passed Students:\n")
print(passed)

# Sort according to total marks
sorted <- stud[order(stud$Total,
                     decreasing = TRUE), ]

cat("Students Sorted by Total Marks:\n")
print(sorted)

# Write sorted list to Excel file
write.xlsx(sorted,
           "sorted_students.xlsx",
           rowNames = FALSE)

cat("Sorted data written to sorted_students.xlsx")

---

Output

> # Load libraries
> library(readxl)

> library(openxlsx)

> # Read Excel file
> stud <- read_excel("C:\\Users\\HP\\Downloads\\stud.xlsx")

> # Display the data
> print(stud)
# A tibble: 8 × 4
    Rno Name     M1    M2
  <dbl> <chr> <dbl> <dbl>
1   101 John     85    78
2   102 Mary     95    88
3   103 Alex     45    65
4   104 David    76    82
5   105 Sara     90    95
6   106 Tom      55    60
7   107 Riya     88    91
8   108 Sam      62    70

> # Structure of data
> str(stud)
tibble [8 × 4] (S3: tbl_df/tbl/data.frame)
 $ Rno : num [1:8] 101 102 103 104 105 106 107 108
 $ Name: chr [1:8] "John" "Mary" "Alex" "David" ...
 $ M1  : num [1:8] 85 95 45 76 90 55 88 62
 $ M2  : num [1:8] 78 88 65 82 95 60 91 70

> # Summary statistics
> summary(stud)
      Rno               Name         M1              M2       
 Min.   :101.0   Length   :8   Min.   :45.00   Min.   :60.00  
 1st Qu.:102.8   N.unique :8   1st Qu.:60.25   1st Qu.:68.75  
 Median :104.5   N.blank  :0   Median :80.50   Median :80.00  
 Mean   :104.5   Min.nchar:3   Mean   :74.50   Mean   :78.62  
 3rd Qu.:106.2   Max.nchar:5   3rd Qu.:88.50   3rd Qu.:88.75  
 Max.   :108.0                 Max.   :95.00   Max.   :95.00  

> # Dimensions
> dim(stud)
[1] 8 4

> # Add Total and Average columns
> stud$Total <- stud$M1 + stud$M2

> stud$Average <- stud$Total/2

> # Display updated data
> print(stud)
# A tibble: 8 × 6
    Rno Name     M1    M2 Total Average
  <dbl> <chr> <dbl> <dbl> <dbl>   <dbl>
1   101 John     85    78   163    81.5
2   102 Mary     95    88   183    91.5
3   103 Alex     45    65   110    55  
4   104 David    76    82   158    79  
5   105 Sara     90    95   185    92.5
6   106 Tom      55    60   115    57.5
7   107 Riya     88    91   179    89.5
8   108 Sam      62    70   132    66  

> # Find topper
> topper <- stud[stud$Total == max(stud$Total), ]

> cat("Topper Details:\n")
Topper Details:

> print(topper)
# A tibble: 1 × 6
    Rno Name     M1    M2 Total Average
  <dbl> <chr> <dbl> <dbl> <dbl>   <dbl>
1   105 Sara     90    95   185    92.5

> # Passed students (marks >=50 in both subjects)
> passed <- stud[stud$M1 >= 50 &
+                  stud$M2 >= 50, ]

> cat("Passed Students:\n")
Passed Students:

> print(passed)
# A tibble: 7 × 6
    Rno Name     M1    M2 Total Average
  <dbl> <chr> <dbl> <dbl> <dbl>   <dbl>
1   101 John     85    78   163    81.5
2   102 Mary     95    88   183    91.5
3   104 David    76    82   158    79  
4   105 Sara     90    95   185    92.5
5   106 Tom      55    60   115    57.5
6   107 Riya     88    91   179    89.5
7   108 Sam      62    70   132    66  

> # Sort according to total marks
> sorted <- stud[order(stud$Total,
+                      decreasing = TRUE), ]

> cat("Students Sorted by Total Marks:\n")
Students Sorted by Total Marks:

> print(sorted)
# A tibble: 8 × 6
    Rno Name     M1    M2 Total Average
  <dbl> <chr> <dbl> <dbl> <dbl>   <dbl>
1   105 Sara     90    95   185    92.5
2   102 Mary     95    88   183    91.5
3   107 Riya     88    91   179    89.5
4   101 John     85    78   163    81.5
5   104 David    76    82   158    79  
6   108 Sam      62    70   132    66  
7   106 Tom      55    60   115    57.5
8   103 Alex     45    65   110    55  

> # Write sorted list to Excel file
> write.xlsx(sorted,
+            "sorted_students.xlsx",
+            rowNames = FALSE)

> cat("Sorted data written to sorted_students.xlsx")
Sorted data written to sorted_students.xlsx

Data Visualization

1. Bar Plot of Total Marks

barplot(stud$Total,
        names.arg=stud$Name,
        col="skyblue",
        main="Total Marks of Students",
        xlab="Students",
        ylab="Total Marks")

---

2. Histogram of Average Marks

hist(stud$Average,
     col="lightgreen",
     main="Distribution of Average Marks",
     xlab="Average Marks")

---

3. Box Plot of Subject Marks

boxplot(stud$M1,
        stud$M2,
        names=c("M1","M2"),
        col=c("red","blue"),
        main="Subject-wise Marks")

---

4. Scatter Plot of M1 and M2

plot(stud$M1,
     stud$M2,
     pch=19,
     col="blue",
     main="M1 vs M2",
     xlab="M1 Marks",
     ylab="M2 Marks")

---

5. Pie Chart Showing Students with Average ≥ 75

good <- sum(stud$Average >= 75)
others <- sum(stud$Average < 75)

pie(c(good,others),
    labels=c("Average >=75",
             "Average <75"),
    col=c("green","red"),
    main="Student Performance")

---

Display Four Graphs Together

par(mfrow=c(2,2))

barplot(stud$Total,
        names.arg=stud$Name,
        col="skyblue",
        main="Total Marks")

hist(stud$Average,
     col="yellow",
     main="Average Marks")

boxplot(stud$M1,
        stud$M2,
        names=c("M1","M2"),
        col=c("red","blue"),
        main="Subject Marks")

plot(stud$M1,
     stud$M2,
     pch=19,
     col="blue",
     main="M1 vs M2")

par(mfrow=c(1,1))

---

Result

The student data were successfully read from the Excel file using the read_excel() function. Statistical analysis such as total marks, average marks, topper identification, and filtering of passed students was performed. Various visualizations including bar plots, histograms, box plots, scatter plots, and pie charts were generated. Finally, the student list sorted in descending order of total marks was successfully written to a new Excel file named sorted_students.xlsx using the write.xlsx() function.