Importing excel and graphing

noetsi

Fortran must die
#1
Two things I really struggle with in R is importing excel files (I use CSV ones which are something of a problem in our system because I can not yet import excel into R) and graphing.

Does excel have an easy way to import excel files and do ODS graphing? I am trying to find the most automated way to do graphing I can find.
 

trinker

ggplot2orBust
#2
Importing files trips a lot of people up but R has nice tools to do this with little lift. Once you get it down it really makes it easy to automate tasks to make plots/reports. If you can post the code you tried and the error it gave that could be a start to getting help.
 

noetsi

Fortran must die
#3
I have not gotten any errors importing excel. I have no idea how to do it at all. :)

Here is a typical example of the code I run now [I don't have any way to give you access to the data so I tried to attached it but it won't let me attach csv files].

Code:
> mydata<-read.csv("S:\\CIU\\Testfolder\\DataforTS2.csv")
> tsdata=ts(mydata$Spend,start=c(2014,12),frequency=12)
> acf(tsdata)
> pacf(tsdata)
 

trinker

ggplot2orBust
#4
I'd recommend the readxl package to read in most xlsx files. So this will install the package:

Code:
install.packages('readxl')
Then when you want to use it:

Code:
library(readxl)
mydata <- read_excel("S:\\CIU\\Testfolder\\DataforTS2.xlsx")
 

noetsi

Fortran must die
#5
I did this trinker with a real excel file I have there (I already installed the package you mentioned but failed to use it correctly years ago)

thanks a lot.
Code:
library(readxl)
mydataex <- read_excel("S:\\CIU\\Testfolder\\DataforTS.xlsx")
tsdatax=ts(mydataex$Spend,start=c(2014,12),frequency=12)
head(tsdatax)
It did work I decomposed the time series and ran the pieces.

It looks like it worked :)
Can you actually access that location to run anything (I ask because I doubt you can and I am trying to figure out how to post data here for R questions). Being a state employee we have a wide range of blocks on our data. :(
 
Last edited:

gianmarco

TS Contributor
#6
Just a small note:
for lazy people who do not want to type in the path to the excel file, I would go for something like:
C-like:
mydata <- read_excel(file.choose())
 
#9
For what it's worth, there is a point and click method in the corner of your R session. You can browse your computer for the file and it will write the code for you. At least that's what I've used. Especially when I have long file paths.
1598491864323.png
 

noetsi

Fortran must die
#15
I got selected to teach the other, master programmers, in my unit R which dason is going to find incredibly amusing :) Given my known love of that language. :p

But realistically for the limited uses I will make of it I will be able to use it. I am not doing rocket science.
 

noetsi

Fortran must die
#17
Code:
library(graphics)
boxplot(airquality[,0:4], main='Multiple Box plots',col=blues9)
This generates multiple boxplots. How can you make each boxplot a different color? Or can you.

I have not found the list of colors yet :p
 
#18
Have a look at ggplot: "the grammar of graphics". Not saying this is exactly what you want or need. But, it was my workaround. check this link: https://r-graphics.org/
Code:
library(graphics)
library(ggplot2)

ozone<-data.frame(response=airquality$Ozone,airquality$Month,airquality$Day)
ozone$cat<-"Ozone"
solar<-data.frame(response=airquality$Solar.R,airquality$Month,airquality$Day)
solar$cat<-"Solar.R"
wind<-data.frame(response=airquality$Wind,airquality$Month,airquality$Day)
wind$cat<-"Wind"
temp<-data.frame(response=airquality$Temp,airquality$Month,airquality$Day)
temp$cat<-"Temp"

new_data<-rbind(ozone,solar,wind,temp)

ggplot(data = new_data)+geom_boxplot(aes(y=response,fill=cat))+
  labs(title = "Air Quality")+scale_fill_brewer(palette="Blues")
1598924444980.png
 
Last edited:

Dason

Ambassador to the humans
#19
Code:
library(graphics)
boxplot(airquality[,0:4], main='Multiple Box plots',col=blues9)
This generates multiple boxplots. How can you make each boxplot a different color? Or can you.

I have not found the list of colors yet :p
First thing to note: you want to use airquality[, 1:4] and not airquality[, 0:4]. R is 1-indexed so you don't want to select the '0' column. In this case it gets ignored but you don't want to do it in general.

The documentation for boxplot specifically says you can provide a vector of colors to the col parameter and it will use those accordingly. You can even see them do this in the examples. If you didn't know you can run the examples from the documentation directly using

Code:
example(boxplot)
So in your case you could just do something like

Code:
boxplot(airquality[,1:4], main='Multiple Box plots',col=c("red", "blue", "yellow", "green"))
and play with the colors you input.

If you want to know all of the colors that are available (by name)

Code:
colors()