Working with excel files in R

Filed Under: R Programming
OPERATING ON EXCEL FILES IN R

Hello, readers! In this article, we will be focusing on an important and most extensively used concept of working with Excel files in R programming, in detail.

So, let us begin!!


1. Loading the excel file into R

Before getting started with this portion, I would like to highlight that in the domain of data science and Machine learning, the initial format of data is either a CSV file or an excel sheet.

Thus, the very first step is to load the dataset into the R environment. There are various ways to load an excel sheet in the current environment. You can find them here!

Here, we have made use of readxl library to read the excel files.

Example:

#Removed all the existing objects
rm(list = ls())
#Setting the working directory
setwd("D:/Ediwsor_Project - Bike_Rental_Count/")
getwd()

install.packages("readxl") 

# Loading the package 
library(readxl) 

# Importing excel file 
data <- read_excel("Bike.xlsx")
head(data)
tail(data)

The head() function enables us to represent the first 5 values of the file whereas, the tail() function helps us inspect the last 5 values of the excel sheet.

Output:

> head(data)
# A tibble: 6 x 9
  instant dteday              season    yr  mnth holiday weekday workingday weathersit
    <dbl> <dttm>               <dbl> <dbl> <dbl>   <dbl>   <dbl>      <dbl>      <dbl>
1       1 2011-01-01 00:00:00      1     0     1       0       6          0          2
2       2 2011-01-02 00:00:00      1     0     1       0       0          0          2
3       3 2011-01-03 00:00:00      1     0     1       0       1          1          1
4       4 2011-01-04 00:00:00      1     0     1       0       2          1          1
5       5 2011-01-05 00:00:00      1     0     1       0       3          1          1
6       6 2011-01-06 00:00:00      1     0     1       0       4          1          1

> tail(data)
# A tibble: 6 x 9
  instant dteday              season    yr  mnth holiday weekday workingday weathersit
    <dbl> <dttm>               <dbl> <dbl> <dbl>   <dbl>   <dbl>      <dbl>      <dbl>
1      15 2011-01-15 00:00:00      1     0     1       0       6          0          2
2      16 2011-01-16 00:00:00      1     0     1      NA       0          0          1
3      17 2011-01-17 00:00:00      1     0     1      NA       1          0          2
4      18 2011-01-18 00:00:00      1     0     1      NA       2          1          2
5      19 2011-01-19 00:00:00      1     0     1       0       3          1          2
6      20 2011-01-20 00:00:00      1     0     1       0       4          1          2

2. Manipulating an excel file in R

We can make changes to the excel file in R. In the below example, we have changed the value of the column ‘weekday’ to 7. To highlight, the changes made get reflected on the entire column.

Example:

data$weekday = 7
head(data)

Output:

As seen below, the value of the entire column gets changed to 7.

> head(data)
# A tibble: 6 x 9
  instant dteday              season    yr  mnth holiday weekday workingday weathersit
    <dbl> <dttm>               <dbl> <dbl> <dbl>   <dbl>   <dbl>      <dbl>      <dbl>
1       1 2011-01-01 00:00:00      1     0     1       0       7          0          2
2       2 2011-01-02 00:00:00      1     0     1       0       7          0          2
3       3 2011-01-03 00:00:00      1     0     1       0       7          1          1
4       4 2011-01-04 00:00:00      1     0     1       0       7          1          1
5       5 2011-01-05 00:00:00      1     0     1       0       7          1          1
6       6 2011-01-06 00:00:00      1     0     1       0       7          1          1

3. Addition of new columns to an excel file in R

Apart from manipulating the column values, R provides us the facility to add new columns to the excel file.

Syntax:

object$column = "value"

Example:

data$new = "101"
head(data)

We have added a new column ‘new’ with the value ‘101’ to our excel sheet. To add, all the rows of the column ‘new’ get the value as ‘101’.

Output:

> head(data)
# A tibble: 6 x 10
  instant dteday season    yr  mnth holiday weekday workingday weathersit   new  
    <dbl> <dttm>               <dbl> <dbl> <dbl>   <dbl>   <dbl>      <dbl>      <dbl> <chr>
1       1 2011-01-01 00:00:00      1     0     1       0       7          0          2 101  
2       2 2011-01-02 00:00:00      1     0     1       0       7          0          2 101  
3       3 2011-01-03 00:00:00      1     0     1       0       7          1          1 101  
4       4 2011-01-04 00:00:00      1     0     1       0       7          1          1 101  
5       5 2011-01-05 00:00:00      1     0     1       0       7          1          1 101  
6       6 2011-01-06 00:00:00      1     0     1       0       7          1          1 101  

4. Deletion of a column through R

We can even delete a column of the loaded excel file through R scripts.

Have a look at the below syntax!

data_obj = data_obj [-index]

We need to pass the index position of the column to the object.

Example:

data = data[-2]
head(data)

In this example, we hade deleted the 2nd column of the excel file i.e. ‘dteday’.

Output:

# A tibble: 6 x 9
  instant season    yr  mnth holiday weekday workingday weathersit new  
    <dbl>  <dbl> <dbl> <dbl>   <dbl>   <dbl>      <dbl>      <dbl> <chr>
1       1      1     0     1       0       7          0          2 101  
2       2      1     0     1       0       7          0          2 101  
3       3      1     0     1       0       7          1          1 101  
4       4      1     0     1       0       7          1          1 101  
5       5      1     0     1       0       7          1          1 101  
6       6      1     0     1       0       7          1          1 101

5. Merging excel files in R

The merge() function enables us to merge two excel files into a single file.

In this example, we have loaded two excel sheets. You can find the excel files below. Further, we have merged them together using merge() function.

Bike.xlsx

Bike Excel File
Bike Excel File

Dev.xlsx

Dev Excel File
Dev Excel File

Example:

#Removed all the existing objects
rm(list = ls())
#Setting the working directory
setwd("D:/Ediwsor_Project - Bike_Rental_Count/")
getwd()
 
library(readxl) 


data <- read_excel("Bike.xlsx")

data1 <- read_excel("Dev.xlsx")

# Merging Files 
merge_data <- merge(data, data1, all.x = TRUE, all.y = TRUE) 


head(merge_data) 


Output:

Excel Merge In R
Excel Merge In R

Conclusion

By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question.

For more such posts related to R programming, Stay tuned.

Till then, Happy Learning.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages