Importing excel and graphing

[noetsi]

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]

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]

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].

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

 

[trinker]

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

install.packages('readxl')

Then when you want to use it:

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

 

[noetsi]

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.

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.

 

[gianmarco]

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:

mydata <- read_excel(file.choose())

 

[noetsi]

I don't understand how that works gianmarco. How does it know which file to choose?

 

[gianmarco]

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]

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]

Anyone know how to export a df from R into an excel table?

 

[Buckeye]

https://www.rdocumentation.org/packages/xlsx/versions/0.6.3/topics/write.xlsx

install the xlsx package and then:

library(xlsx)

write.xlsx( df,
filepath,
sheetName = "Sheet1",
col.names = TRUE,
row.names = TRUE,
append = FALSE,
showNA = TRUE,
password = NULL)

I prefer showNA=F and row.names=F

 

[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]

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]

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.

 

[noetsi]

Never mind

 

[noetsi]

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

 

[Buckeye]

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/

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

 

[Dason]

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

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

example(boxplot)

So in your case you could just do something like

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)

colors()

 

[trinker]

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