Plotting multiple columns in same graph with R

Question

I'm not familiar with R and I'm a little lost organising my data. My dataset looks as follows (typeof(mData) returns list):

       key_as_string                key     doc_count  colA   colB          types.buckets
2017-01-30T13:33:30.000+01:00 1.485780e+12     28       0      0        type1, type2, type3, 18, 5, 5
2017-01-30T13:34:00.000+01:00 1.485780e+12    175       0      0        type2, type1, type3, type4, 138, 19, 17, 1
...

I'd like to plot the count over time, i.e., I'd like to have a graph where the x-axis contains the timestamps (as POSIX) and the y-axis the count with one colour per type (+1 for the doc_count column). I found lots of answers saying I have to melt the data but all examples used an easy format such as this (preferably as dataframe):

timestamp(as POSIX)             doc_count   type1 type2 ... type n
2017-01-30T13:33:30.000+01:00       28      18      5       ...
2017-01-30T13:34:00.000+01:00       175     19      138     ...
...

The problem is that mData[["types"]][["buckets"]][[x]][[1]] contains asysmmetric data, i.e., I don't know how the order and amount of types per row (the order isn't important for plotting, though). If that's the case I want to add 0 to the respective column.

How do I transform mData to a form similar to the one above and plot it? That's where I am lost.

Here's a sample output of result[["types"]][["buckets"]]:

[[1]]
          key doc_count
1      type1        18
2      type2         5
3      type3         5

[[2]]
          key doc_count
1       type2       138
2       type1        19
3       type3        17
4       type4         1

Plotting just the total doc_count over time works fine:

dates <- as.POSIXct(mData[["key"]]/1000,origin="1970-01-01")

# returns NA. Problably because it doesn't match the seconds. How do I parse it correctly?
#dates <- as.POSIXct(mData[["key_as_string"]],format="%Y-%m-%dT%H:%M:%S+01:00 CET",tz="Europe/Paris")

mDf <- data.frame(date=dates,doc_count=mData[[3]])

ggplot(mDf,aes(date,doc_count))+geom_line(colour=sample(1:255255255, 1))+xlab("")+ylab("Events")+
scale_x_datetime(date_breaks="24 hours",date_labels = "%d.%m.%Y %H:%M:%S")+
theme(axis.title=element_text(size=24,face="bold"), axis.text.y = element_text(angle=90, hjust=0.5))+geom_area(fill=sample(1:255255255, 1))

EDIT:

This should repruduce a sample of my data:

structure(list(key_as_string = c("2017-01-30T13:33:30.000+01:00", 
"2017-01-30T13:34:00.000+01:00", "2017-01-30T13:34:30.000+01:00", 
"2017-01-30T13:35:00.000+01:00", "2017-01-30T13:35:30.000+01:00", 
"2017-01-30T13:36:00.000+01:00"), key = c(1485779610000, 1485779640000, 
1485779670000, 1485779700000, 1485779730000, 1485779760000), 
doc_count = c(28L, 175L, 122L, 526L, 160L, 1306L), types = structure(list(
    colA = c(0L, 0L, 0L, 0L, 0L, 0L
    ), colB = c(0L, 0L, 0L, 0L, 0L, 0L), buckets = list(
        structure(list(key = c("type1", "type2", "type3"
        ), doc_count = c(18L, 5L, 5L)), .Names = c("key", 
        "doc_count"), class = "data.frame", row.names = c(NA, 
        3L)), structure(list(key = c("type2", "type1", 
        "type3", "type4"), doc_count = c(138L, 19L, 17L, 
        1L)), .Names = c("key", "doc_count"), class = "data.frame", row.names = c(NA, 
        4L)), structure(list(key = c("type2", "type1", 
        "type3"), doc_count = c(60L, 42L, 20L)), .Names = c("key", 
        "doc_count"), class = "data.frame", row.names = c(NA, 
        3L)), structure(list(key = c("type1", "type2", 
        "type3", "type4"), doc_count = c(379L, 128L, 
        18L, 1L)), .Names = c("key", "doc_count"), class = "data.frame", row.names = c(NA, 
        4L)), structure(list(key = c("type2", "type3", 
        "type1"), doc_count = c(87L, 61L, 12L)), .Names = c("key", 
        "doc_count"), class = "data.frame", row.names = c(NA, 
        3L)), structure(list(key = c("type1", "type2", 
        "type3", "type4"), doc_count = c(1139L, 146L, 
        20L, 1L)), .Names = c("key", "doc_count"), class = "data.frame", row.names = c(NA, 
        4L)))), .Names = c("colA", 
"colB", "buckets"), row.names = c(NA, 6L), class = "data.frame")), .Names = c("key_as_string", 
"key", "doc_count", "types"), row.names = c(NA, 6L), class = "data.frame")

Provide a good reproducible example see here http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Koundy, Jan 31 '17 at 10:13

score 0 · Accepted Answer · answered Jan 31 '17 at 11:09

Here I extract the granular data and put it along with the date of measurement in a dataframe. You will get a long data format that is easy to plot with ggplot:

library(dplyr)
library(ggplot2)

tb <- mData[['types']][['buckets']]  
dt <- mData[['key']]

pdf <- do.call(rbind,lapply(seq_len(length(tb)), 
                            function(x) tb[[x]] %>% mutate(date = dt[x])))

ggplot(pdf, aes(x = date, y=doc_count, col=key)) + geom_line()

Thanks for the fast reply. Works :) – Rick Jan 31 '17 at 12:15 — Rick, Jan 31 '17 at 12:15

Plotting multiple columns in same graph with R

1 Answers1