Get trendline slope of multiple time series by group

Question

I've got a data set containing multiple time series for different groups in long format. I need to get the slope of a trendline for each one of the groups, so i can tell if the series is increasing or decreasing overtime.

My df looks like this:

Group   Date (M-YY) Value
Group A mar-18  0.733412181
Group A abr-18  0.698969331
Group A may-18  0.607338572
Group A jun-18  0.143834025
Group A jul-18  0.036449478
Group A ago-18  0.115030334
Group A sept-18 0.292299769
Group A oct-18  0.940266543
Group A nov-18  0.838158609
Group A dic-18  0.467750831
Group B mar-18  0.466147825
Group B abr-18  0.670841589
Group B may-18  0.605945948
Group B jun-18  0.492835505
Group B jul-18  0.840924612
Group B ago-18  0.70177121
Group B sept-18 0.377545249
Group B oct-18  0.968598222
Group B nov-18  0.480427212
Group B dic-18  0.681482744

Could you provide your code instead of the output? Than your example is reproducible — Thomas, Aug 20 '19 at 20:12

Brigadeiro · Answer 1 · 2019-08-20T20:21:12.690

0

To get a slope for each group:

summary(lm(Value ~ Date, df[df$Group == "A", ]))

summary(lm(Value ~ Date, df[df$Group == "B", ]))

To plot:

plot(df$Date, df$Value)
abline(lm(Value ~ Date, df[df$Group == "A", ]), col = "blue")
abline(lm(Value ~ Date, df[df$Group == "B", ]), col = "red")

A solution using the tidyverse:

df %>%
  group_by(Group) %>%
  do(glance(lm(Value ~ Date, data = .)))

edited Aug 20 '19 at 20:21

answered Aug 20 '19 at 20:13

Brigadeiro

2,649
13
30

score 0 · Answer 2 · answered Aug 20 '19 at 20:14

0

How about this?

#subset data
groupA = subset.data.frame(data, Group =="Group A")

#plot data
plot(groupA$Date, groupA$Value)
abline(lm(Value ~ 1:length(groupA$Date))

You can repeat the above for Group B too

answered Aug 20 '19 at 20:14

Not_Dave

491
2
8

score 0 · Answer 3 · answered Aug 20 '19 at 20:29

If you group by the Group column, then add a row_number() column, you can, for each group, do a linear regression of the Value vs rownum fields and select the slope coefficient using summarise.

library(dplyr)

df %>% 
  group_by(Group) %>% 
  mutate(rownum = row_number()) %>% 
  summarise(slope = lm(Value ~ rownum)$coefficients['rownum'])

# # A tibble: 2 x 2
#   Group     slope
#   <chr>     <dbl>
# 1 Group_A 0.00468
# 2 Group_B 0.0117

Data used:

structure(list(Group = c("Group_A", "Group_A", "Group_A", "Group_A", 
"Group_A", "Group_A", "Group_A", "Group_A", "Group_A", "Group_A", 
"Group_B", "Group_B", "Group_B", "Group_B", "Group_B", "Group_B", 
"Group_B", "Group_B", "Group_B", "Group_B"), Date = c("mar-18", 
"abr-18", "may-18", "jun-18", "jul-18", "ago-18", "sept-18", 
"oct-18", "nov-18", "dic-18", "mar-18", "abr-18", "may-18", "jun-18", 
"jul-18", "ago-18", "sept-18", "oct-18", "nov-18", "dic-18"), 
    Value = c(0.733412181, 0.698969331, 0.607338572, 0.143834025, 
    0.036449478, 0.115030334, 0.292299769, 0.940266543, 0.838158609, 
    0.467750831, 0.466147825, 0.670841589, 0.605945948, 0.492835505, 
    0.840924612, 0.70177121, 0.377545249, 0.968598222, 0.480427212, 
    0.681482744)), row.names = c(NA, -20L), class = "data.frame")

Get trendline slope of multiple time series by group

3 Answers3