# Importing excel and graphing

#### noetsi

##### No cake for spunky
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
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

##### No cake for spunky
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 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 ##### No cake for spunky 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
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())

#### noetsi

##### No cake for spunky
I don't understand how that works gianmarco. How does it know which file to choose?

#### gianmarco

##### TS Contributor
I don't understand how that works gianmarco. How does it know which file to choose?
Once you issue that command, a window should pop up allowinfg you to browse through your directories and to choose your file.

#### Buckeye

##### Active Member
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.

#### noetsi

##### No cake for spunky
Anyone know how to export a df from R into an excel table?

#### Dason

If you don't need to export tables to multiple tabs you can always export as csv using write.csv fairly easily too.

#### noetsi

##### No cake for spunky
Where I work they are going to want to see excel tables. I won't be exporting to multiple tabs, however.

#### Dason

I mean... Csvs open in Excel just fine

#### noetsi

##### No cake for spunky
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.

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

Never mind

Last edited:

#### noetsi

##### No cake for spunky
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.

#### Buckeye

##### Active Member
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")

Last edited:

#### Dason

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.

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()

#### trinker

##### ggplot2orBust
Hmm would have thought airquality[,0:4] would throw an error