I'm trying to create a set of charts for reports in R using ggplot2 but I'm still struggling to get used to ggplot2.
The chart data is coming from SQL tables that are laid out like this:
| Name | Version | Category | Value | Date | Number | Build | Error |
|:-----:|:-------:|:--------:|:-----:|:------:|:------:|:---------:|:-----:|
| File1 | 0.01 | Time | 123 | 1-1-12 | 1 | Iteration | None |
| File1 | 0.01 | Size | 456 | 1-1-12 | 1 | Iteration | None |
| File1 | 0.01 | Final | 789 | 1-1-12 | 1 | Iteration | None |
| File2 | 0.01 | Time | 312 | 1-1-12 | 1 | Iteration | None |
| File2 | 0.01 | Size | 645 | 1-1-12 | 1 | Iteration | None |
| File2 | 0.01 | Final | 978 | 1-1-12 | 1 | Iteration | None |
| File3 | 0.01 | Time | 741 | 1-1-12 | 1 | Iteration | None |
| File3 | 0.01 | Size | 852 | 1-1-12 | 1 | Iteration | None |
| File3 | 0.01 | Final | 963 | 1-1-12 | 1 | Iteration | None |
| File1 | 0.02 | Time | 369 | 1-1-12 | 2 | Iteration | None |
| File1 | 0.02 | Size | 258 | 1-1-12 | 2 | Iteration | None |
| File1 | 0.02 | Final | 147 | 1-1-12 | 2 | Iteration | None |
| File2 | 0.02 | Time | 753 | 1-1-12 | 2 | Iteration | None |
| File2 | 0.02 | Size | 498 | 1-1-12 | 2 | Iteration | None |
This table is data from running various files and recording output. The name column is the file name, the version column is the software version, the category column is the kicker here and is the different types of data that is recorded, value column is the value of the data, the date column is the date the data was recorded, the number was the number of "runs" where this data was recorded, the build column is whether the software is a development build or a release, and the error column is for recording errors during runs.
The category column is what I want to look at. I want to generate a series of charts in ggplot2 that chart the value (y) (one chart for each category for each file) vs the version number (x). So the y axis will show the value of data for a specific category and the x-axis shows the build version. here's the code I've got so far.
dbhandle <- SQLConn_remote(DBName = "DBName", ServerName = "SERVER")
Tabledf<-sqlQuery(dbhandle, 'select * from ExampleTable', stringsAsFactors = FALSE)
Tabledf$Name<-str_trim(Tabledf$Name)
Tabledf$Version<-str_trim(Tabledf$Version)
Tabledf$Category<-str_trim(Tabledf$Category)
Tabledf$Value<-as.numeric(Tabledf$Value)
scenarios<-unique(Tabledf$Name)
for (i in 1:3){
p <- ggplot(subset(Tabledf, Name == scenarios[i]), aes(x=Number, y=Value, group=Name))
p <- p + geom_line(aes(color=Date)) +
geom_point(size = 1.2, shape = 19, colour = 'red') +
facet_grid(Category ~ ., scales = "free", space = "free")
print(p)
}
I would like to know how I could add a label for which file is showing up and this is only generating 1 set of charts for one file. I'm not sure whats wrong. I'm guessing that my loop isn't set up correctly. I'm still sort of new to R coding and this was pulled from an example. I've got upwards of 30ish files that I want to have charts displayed for. Ideally, it would have pages where you'd have 1 group of 5-8 charts that are faceted for each Category all for the first file. Then you'd have the same thing but for the 2nd file and so on and so forth.
EDIT: Even if someone could simply point me in the direction of resources to figure this out on my own I would be grateful. I've been looking and I'm only finding resources that are lengthy on qplot.
EDIT2: Okay so I'm feeling pretty dumb and can't figure out how to create a dataset to upload using dput but here's the code I've used to create a usable dataframe.
rw1 <- c("File1", "File1", "File1", "File1", "File1", "File1", "File1", "File1", "File1", "File2", "File2", "File2", "File2", "File2", "File2", "File2", "File2", "File2")
rw2 <- c("0.01", "0.01", "0.01", "0.01", "0.01", "0.01", "0.01", "0.01", "0.01", "0.02", "0.02", "0.02", "0.02", "0.02", "0.02", "0.02", "0.02", "0.02")
rw3 <- c("Time", "Size", "Final", "Time", "Size", "Final", "Time", "Size", "Final", "Time", "Size", "Final", "Time", "Size", "Final", "Time", "Size", "Final")
rw4 <- c(123, 456, 789, 312, 645, 978, 741, 852, 963, 369, 258, 147, 753, 498, 951, 753, 915, 438)
rw5 <- c("01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12", "01/01/12")
rw6 <- c(1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2)
rw7 <- c("Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration", "Iteration")
rw8 <- c("None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None", "None")
df = data.frame(rw1, rw2, rw3, rw4, rw5, rw6, rw7, rw8)
colnames(df) <- c("Name", "Version", "Category", "Value", "Date", "Number", "Build", "Error")
That will generate a chart that is nearly identical to the chart shown above. Ideally I'd like to create a set of charts from this data set. The format would be a group or set of charts grouped together by Filename. So for example:
Might be the group of charts only for File1. Each of the chart facets would be a single "Category" from the dataframe/table. There would be another group like this for File2 and so on and so forth.
I'd like to clean this up though. There are category labels on the right sides of each facet I've just elected to remove those in the picture. However, I'd like to add a label that shows "Name" as well as "Category" and "Value". The code I've provided above also only generates these for the first file. Well I believe its the first file. Without any Name label, there's no way of knowing.
