I confront a problem where one data source is a "normal" DF with customers as rows (each customer occurs once) and static customer features as columns. The other DF other hand is a big pile of timestamped data of the aforementioned customers with event features and event timestamps (each customer occurs many times according their activity). The task is a simple classification by customer.

My problem is how to integrate the time series data into the first DF as columns? Should I simply compute somekind of aggregations from the second DF? If so, what kind of? If not, what is the canonical way to merge these two kind of data sets?


Assuming: DF1 and DF2 look something like:

enter image description here

So going to make some assumptions about what you want to do:

(1) you want a classification model like: I want to classify buyers/non buyers based on their overall attributes as well as their historical activity for a given time window: - then you first have to create features based on the transaction data frame DF2, which would rollup transactions and performance information to customer id level for each customer. Some metrics that you can create from the detail transactions table DF could be recency of visits, frequency of visit, average purchase amount, recency of conversions etc.., so you would have customer id's that did not buy anything over the time period and customer that bought at least once over the time window.

  • join the first table and the rolled up data by customer id to have all features and label (buyer,non buyer) given an appropriate time window

(2) if you want a model for classification at each timestamp for each customer id then most likely your first data frame contains some sort customer characteristics and demographics that do not change overtime or at least over the chosen time window. In that case you join the two data frames by customer id. Here each customer could be buyer or non buyer depending on the each timestamp


In general, you can apply a variety of transformations that reduce each time series down to something more manageable.

  • Summary stats like mean, median, standard deviation.
  • "Bucket" the data into, e.g. monthly aggregates of any of the above
  • Number of observed instances, if they vary.
  • Various "bucketized" statistics like observation frequency per month, if the observations are not evenly-sampled.
  • Compute test statistics for classic time series hypothesis tests like the ADF, PP, and KPSS tests for unit roots.
  • Fit a model to each series and use its ARIMA coefficients as features.
  • Apply dimension reduction. Eg. use PCA on the time series space and take the first few dimensions (e.g. look for an elbow in the eigenvalues); but this only works if the time series are both evenly-spaced and equal-length.
  • Apply one of many time series clustering algorithms (and another reference) and use cluster assignments or probabilities as features. You can use Dynamic Time Warping to compare time series sampled at different rates.

Note that handling unevenly-sampled time series can be much harder than sampling even-spaced time series. See here for insight.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.