Anandu KB
Anandu KB

Reputation: 58

Plot Timeseries Data where the hourly variations are values in different columns

I have a DataFrame as shown below. The Columns 0:00, 1:00.. till 24:00 represents the hours. How can I plot the hourly variation of both Items 'foo' and 'bar' for all the dates?

Date        Item  0:00   1:00  2:00  3:00  4:00  5:00  6:00 

1/1/2022    foo   2      3    4       1      5     1.5   2.5 
1/2/2022    foo   1.5    1    3       2      2.5   6     4
1/3/2022    foo   1      2    3       1      2     5     4
1/4/2022    foo   2      1    3       4      1     2.4   3
1/1/2022    bar   3      1    0       1.5    5     1.5   2.5
1/2/2022    bar   2.5    4    3       1      2.5   0     1
1/3/2022    bar   1      2    1       1      2     1.5   4
1/4/2022    bar   2      1    3       2      1     2.5   3

I tried the following

g = sns.FacetGrid(df,row='Item',col='Date')
g.map(sns.#someplot,) # within map not sure what plot should I use and how to represent x axis as the columns

Upvotes: 2

Views: 447

Answers (3)

AndrewGB
AndrewGB

Reputation: 16876

With R and ggplot2, you have to pivot to long format for plotting.

library(tidyverse)

df %>%
  pivot_longer(-c(Date, Item), names_to = "time", values_to = "value") %>%
  mutate(time = ymd_hm(paste("0000-01-01",time, sep = " "))) %>%
  ggplot(aes(x = time, y = value)) +
  geom_line(aes(colour = Date)) +
  scale_x_datetime( breaks=date_breaks("60 min"), labels = date_format("%H:%M")) +
  theme(axis.text.x=element_text(size=8, angle = 90),
        axis.text.y=element_text(size=8),
        axis.title=element_text(size=10)) +
  facet_grid(~Item, scale='free_x')

enter image description here

Another option if you want both series on the same plot.

library(tidyverse) 

df %>%
  pivot_longer(-c(Date, Item), names_to = "time", values_to = "value") %>%
  mutate(Date = as.Date(Date, "%m/%d/%Y")) %>%
  mutate(time = as.POSIXct(paste(ok$Date, ok$time), format="%Y-%m-%d %H:%M")) %>%
  ggplot(aes(x = time, y = value, colour = Item, group = Item)) +
  geom_line()

*Note: Since it is just a sample of the data, there are time gaps, hence the sharp angles.

enter image description here

Or if you want them separate, then you can facet the plot (also using bar for another alternative).

df %>%
  pivot_longer(-c(Date, Item), names_to = "time", values_to = "value") %>%
  mutate(Date = as.Date(Date, "%m/%d/%Y")) %>%
  mutate(time = as.POSIXct(paste(ok$Date, ok$time), format="%Y-%m-%d %H:%M")) %>%
  ggplot(aes(x = time, y = value)) +
  geom_bar(stat = "identity", aes(colour = Item, fill = Item)) +
  facet_grid(~Item, scale='free_x')

enter image description here

Data

df <- structure(list(Date = c("1/1/2022", "1/2/2022", "1/3/2022", "1/4/2022", 
"1/1/2022", "1/2/2022", "1/3/2022", "1/4/2022"), Item = c("foo", 
"foo", "foo", "foo", "bar", "bar", "bar", "bar"), `0:00` = c(2, 
1.5, 1, 2, 3, 2.5, 1, 2), `1:00` = c(3L, 1L, 2L, 1L, 1L, 4L, 
2L, 1L), `2:00` = c(4L, 3L, 3L, 3L, 0L, 3L, 1L, 3L), `3:00` = c(1, 
2, 1, 4, 1.5, 1, 1, 2), `4:00` = c(5, 2.5, 2, 1, 5, 2.5, 2, 1
), `5:00` = c(1.5, 6, 5, 2.4, 1.5, 0, 1.5, 2.5), `6:00` = c(2.5, 
4, 4, 3, 2.5, 1, 4, 3)), class = "data.frame", row.names = c(NA, 
-8L))

Upvotes: 1

jay.sf
jay.sf

Reputation: 73842

In R just use matplot, no need for data wrangling beforehand.

par(mfrow=c(1, 2)); by(df[3:9], df$Item, \(x) matplot(t(x), type='l'))

Or, more publishable:

par(mfrow=c(1, 2))
by(df, df$Item, \(x) {
  matplot(t(x[1:4, 3:9]), type='l', main=el(x$Item), xaxt='n', xlab='t', ylab='y')
  axis(1, axTicks(1), colnames(df)[3:9])
  legend('topleft', leg=x$Date, col=seq_len(nrow(x)), lty=seq_len(nrow(x)), 
         cex=.8, ncol=2)
  })

enter image description here


Data:

df <- read.table(text="
                 Date Item 0:00 1:00 2:00 3:00 4:00 5:00 6:00
1 1/1/2022  foo  2.0    3    4  1.0  5.0  1.5  2.5
2 1/2/2022  foo  1.5    1    3  2.0  2.5  6.0  4.0
3 1/3/2022  foo  1.0    2    3  1.0  2.0  5.0  4.0
4 1/4/2022  foo  2.0    1    3  4.0  1.0  2.4  3.0
5 1/1/2022  bar  3.0    1    0  1.5  5.0  1.5  2.5
6 1/2/2022  bar  2.5    4    3  1.0  2.5  0.0  1.0
7 1/3/2022  bar  1.0    2    1  1.0  2.0  1.5  4.0
8 1/4/2022  bar  2.0    1    3  2.0  1.0  2.5  3.0
           ", check.names=F)

Upvotes: 1

jezrael
jezrael

Reputation: 863801

If need bars with separate plots for Items use:

df1 = df.melt(['Date','Item'])
g = sns.FacetGrid(df1, row='Item', col="Date")
g.map_dataframe(sns.barplot, x="variable", y="value")

Or if need both Items in one graph use:

df1 = df.melt(['Date','Item'])
g = sns.FacetGrid(df1, col="Date")
g.map_dataframe(sns.barplot, x="variable", y="value", hue="Item")

Upvotes: 2

Related Questions