## R Programming - Play on a Excel Sheets

This is one of a exciting exercise I am going to do i.e. Playing with data

Open R console & use the following command

>read.table("clipboard",header=T, sep = "\t")->data # it will bring all data on R Console and stored in vector data

To view the data use

>head(data)

Step 2: Find the percentage of each and add the new column of percentage

> percentage<- (data$M.O./data$M.M.)*100 #find the percentage of each column

> newdata<- data.frame(data,percentage) #add new column of percentage to data

> newdata # see new data

Step 3 : To arrange the complete table on the basis of Marks obtained

So, to achieve this first we need to order the marks obtained. Remember not to sort the marks but to order them.

>newdata$M.O. # find data according to M.O

>na.omit(newdata$M.O.) # Remove all null values

>na.omit(newdata$M.O.)->obtained # place it in a vector obtained

>order(obtained) # order it in increasing order by default

>newdata[order(obtained),] # now use the order no. as rows & all column. It will give the matrix in orderd

> na.omit(newdata)[order(obtained),]->orderednewdata #save new matrix in new vector

Note: remove all NA values from the data else the order of new row according to order will not work

Step 4: Now I need the students who score more than 80%

>percent<-(data$M.O./data$M.M.)*100 # first calculate the percentage of data & store it in percent

>which(percent>80) #find out the order no. of cases where percentage is greater than 80

>data[which(percent>80),] # use those rows no and find the matrix

Step 5: You want to visualize the data

> plot(data$M.O.)

> hist(data$M.O.)

Step 6: Finally to Write the data in xlsx format

>write.xlsx(x = orderednewdata,file = "theclassrank.xlsx",sheetName = "rank",col.names = T,row.names = T,append = T,showNA = T ) # This will write the data in excel format

> write.xlsx(x = data,file = "theclassrank.xlsx",sheetName = "olddata1",col.names=T,row.names = T,append = T) #I have also appended the same excel sheet with the old data as well

**Step 1****:**To place the data in R console one need to first arrange the data in good format in a new sheet in excel file itself. After the data to be analysed is arranged in a perfect manner than select & copy the data.Open R console & use the following command

>read.table("clipboard",header=T, sep = "\t")->data # it will bring all data on R Console and stored in vector data

To view the data use

>head(data)

Step 2: Find the percentage of each and add the new column of percentage

> percentage<- (data$M.O./data$M.M.)*100 #find the percentage of each column

> newdata<- data.frame(data,percentage) #add new column of percentage to data

> newdata # see new data

Step 3 : To arrange the complete table on the basis of Marks obtained

So, to achieve this first we need to order the marks obtained. Remember not to sort the marks but to order them.

>newdata$M.O. # find data according to M.O

>na.omit(newdata$M.O.) # Remove all null values

>na.omit(newdata$M.O.)->obtained # place it in a vector obtained

>order(obtained) # order it in increasing order by default

>newdata[order(obtained),] # now use the order no. as rows & all column. It will give the matrix in orderd

> na.omit(newdata)[order(obtained),]->orderednewdata #save new matrix in new vector

Note: remove all NA values from the data else the order of new row according to order will not work

Step 4: Now I need the students who score more than 80%

>percent<-(data$M.O./data$M.M.)*100 # first calculate the percentage of data & store it in percent

>which(percent>80) #find out the order no. of cases where percentage is greater than 80

>data[which(percent>80),] # use those rows no and find the matrix

Step 5: You want to visualize the data

> plot(data$M.O.)

> hist(data$M.O.)

Step 6: Finally to Write the data in xlsx format

>write.xlsx(x = orderednewdata,file = "theclassrank.xlsx",sheetName = "rank",col.names = T,row.names = T,append = T,showNA = T ) # This will write the data in excel format

> write.xlsx(x = data,file = "theclassrank.xlsx",sheetName = "olddata1",col.names=T,row.names = T,append = T) #I have also appended the same excel sheet with the old data as well