Data Analysing is a skill
It is not like, all the data will be available already in the form we require them. It is the duty of the data analyst to take the data, and sometimes he needs to prepare the data by himself, identifying the missing values and replacing them, transpose the data, transform the variables and many other operations in the data. So data handling is a very important part in R. Data Handling involves all kind of processes like import, curate, validation and exploration of data. Hence before moving on to Analysis of data, it is very important for one to be good with data handling.
The data can be stored in different formats, like CSV (Comma Separated Variable), Excel, SQL (database), and many other ways. Various statistical tools are available which are used for the critical analysis of the data. R is highly flexible and open source software which is integrated with all the formats of data storage due to which data handling becomes easy in R.
Here we are going to discuss various data handling which includes
- Data importing from files
- Database server connections
- Working with datasets
- Manipulating the datasets in R
- Creating new variables in R
- Sorting in R & Removing Duplicates
- Exporting the R datasets into external files
- Data Merging, etc.
Importing Data from CSV file
CSV (Comma Separated Variable) extension is one of the most commonly used data type. R is compatible with CSV format. By calling a simple function, csv (), we can easily import the data into R.
While defining the path of the CSV file in the csv() function, we must either use “/” or “\\” in the path. The windows style of giving path in which we use a single “\”, doesn’t work in R.
To understand how read.csv() functions works, let’s consider a sample program to read the Sales related data from Sales_data.csv as shown below. The function read.csv() imports the data from the CSV file (the path for which is mentioned in the syntax) to R.
In the syntax below, we need to give the local path of the data set as an input to read.csv() function and replace all ‘\’ with ‘\\’.
> Sales <- read.csv("C:\\Users\\venk\\Google Drive\\Training\\Datasets\\Superstore Sales Data\\Sales_sample.csv") > Sales > Sales <- read.csv("C:\Users\venk\Google Drive\Training\Datasets\Superstore Sales Data\Sales_sample.csv") > Sales1 <- read.csv("C:/Users/venk/Google Drive/Training/Datasets/Superstore Sales Data/Sales_sample.csv") > Sales1## custId custName custCountry productSold ## 1 23262 Candice Levy Congo SUPA101 ## 2 23263 Xerxes Smith Panama DETA200 ## 3 23264 Levi Douglas Tanzania, United Republic of DETA800 ## 4 23265 Uriel Benton South Africa SUPA104 ## 5 23266 Celeste Pugh Gabon PURA200 ## 6 23267 Vance Campos Syrian Arab Republic PURA100 ## 7 23268 Latifah Wall Guadeloupe DETA100 ## 8 23269 Jane Hernandez Macedonia PURA100 ## 9 23270 Wanda Garza Kyrgyzstan SUPA103 ## 10 23271 Athena Fitzpatrick Reunion SUPA103 ## 11 23272 Anjolie Hicks Turks and Caicos Islands DETA200 ## salesChannel unitsSold dateSold ## 1 Retail 117 8/9/2012 ## 2 Online 73 7/6/2012 ## 3 Online 205 8/18/2012 ## 4 Online 14 8/5/2012 ## 5 Retail 170 8/11/2012 ## 6 Retail 129 7/11/2012 ## 7 Retail 82 7/12/2012 ## 8 Retail 116 6/3/2012 ## 9 Online 67 6/7/2012 ## 10 Retail 125 7/27/2012 ## 11 Retail 71 7/31/2012
Importing from SAS files
We have earlier discussed about installing packages. In order to import data from SAS files, we need to install the package “sas7bdat”.
SAS, also known as the Statistical Analysis Software, is used to perform the functions of Data Analytics. SAS is a highly specialized tool in analytics. R is compatible with the SAS and the JMV format. There is an external library available which needs to be installed before importing the data of SAS format.
To install the package, use the command packages(sas7bdat) and to use that package, we need to call library(sas7bdat). After installing, it will automatically load the library into the R directory. Once the library is installed, user can import the data by calling the function sas7bdat(). Giving path for this function is same as we did for the csv file format.
A sample program to import the SAS data into R is shown below:
> library(sas7bdat) > gnpdata <-read.sas7bdat("C:\\Users\\venk\\Google Drive\\Training\\Datasets\\SAS datasets\\gnp.sas7bdat") > View(gnpdata)
## Warning: package 'sas7bdat' was built under R version 3.2.3 ## DATE GNP CONSUMP INVEST EXPORTS GOVT ## 1 0 516.1 325.5 88.7 4.3 97.6 ## 2 91 514.5 331.6 78.1 5.1 99.6 ## 3 182 517.7 331.7 77.4 6.5 102.1 ## 4 274 513.0 333.8 68.5 7.7 103.0 ## 5 366 517.4 334.4 69.5 8.3 105.3 ## 6 456 527.9 339.1 74.7 7.0 107.1
Data import from Excel files
Excel is one of the most widely used tool for reporting and ad-hoc data analysis. The excel files contain mostly aggregated and even raw data sometimes. The function, xlsx() is used to import the desired excel file into R. The spreadsheet is read through this function and is stored into a data frame. Here’s an example on how to read the data from excel into R.
> library(xlsx) > wb_data <- read.xlsx("C:\\Users\\venk\\Google Drive\\Training\\Datasets\\World Bank Data\\World Bank Indicators.xlsx" , sheetName="Data by country")
There’s another way through which data can be imported into R. There is a function called loadWorkbook() from the XLConnect package, which can be used to read the complete workbook. To import any such worksheet, the function readWorksheet() is called. Java package needs to be installed before using XLConnect.
> library(XLConnect) > wb_data <- readWorksheet(loadWorkbook("C:\\Users\\venk\\Google Drive\\Training\\Datasets\\World Bank Data\\World Bank Indicators.xlsx" ),sheet=1)
We may have some Java related issues while importing Excel files. Excel is not just a flat file like CSV. The excel file contains lots of other information such as indexes, apart from just data. If we find errors even after installing the necessary packages, then we must first store the excel data in CSV format and then import it.