How to get trend values from different time series in one data frame?

Question

Here is sample data:

data<-read.table(textConnection('customer_ID transaction_num sales
                                    Josh         1              $35
                                    Josh         2              $50
                                    Josh         3              $65
                                    Ray          1              $65
                                    Ray          2              $52
                                    Ray          3              $49
                                    Eric         1              $10 
                                    Eric         2              $13
                                    Eric         3              $9'),header=TRUE,stringsAsFactors=FALSE)
    
  
  data$sales<-as.numeric(sub('\\$','',data$sales))

It is clear how to get trend values for one of customers's id:

  dataTransformed<-dcast(data, transaction_num ~ customer_ID, value.var="sales", fun.aggregate=sum)
  transaction_num Eric Josh Ray
               1   10   35  65
               2   13   50  52
               3    9   65  49
  fitted(lm(dataTransformed$Eric ~ dataTransformed$transaction_num))
    1        2        3 
11.16667 10.66667 10.16667 

But i want to get a data frame with 'trend values' column for every customer id instead of 'sales' column or just near it. To get something like that:

                                   customer_ID transaction_num trend 
                                    Josh         1              35
                                    Josh         2              50
                                    Josh         3              65
                                    Ray          1              63.3
                                    Ray          2              10.6
                                    Ray          3              10.2
                                    Eric         1              11.2 
                                    Eric         2              10.7
                                    Eric         3              10.2

Any help would be aprreciate. Thank you

Answer 1

You could simply do the lm with an interaction term:

data$trend <- fitted(lm(sales ~ customer_ID * transaction_num, data))
data
#>   customer_ID transaction_num sales    trend
#> 1        Josh               1    35 35.00000
#> 2        Josh               2    50 50.00000
#> 3        Josh               3    65 65.00000
#> 4         Ray               1    65 63.33333
#> 5         Ray               2    52 55.33333
#> 6         Ray               3    49 47.33333
#> 7        Eric               1    10 11.16667
#> 8        Eric               2    13 10.66667
#> 9        Eric               3     9 10.16667