Background Information

I work for a fire department in Florida and the fire chief posed a question to me; At any given moment in time during the calendar year 2018, how many fire trucks are busy, how many are available, how many are standing by and how many are out of service? Using the SQL Server database I was able to distill this information into tabular data.

Software available to me.

Also, because the data is in SQL Server I can parse out the date time stamp by hour, weekday, month, etc. Then Using Python or Excel I can generate statistical tests and univariate statistics and charts. I have modest experience with R. Therefore my tools are SQL Server, R, Python and Excel.

The data

For the first record with the primary key 21546912 at the time of january 1, 2018 at 00:02:31.800 there was 1 fire truck working, 9 fire trucks are available for work and 0 are standing by or out of service. For the entire year I have 104,179 observations showing at exactly that time stamp how many are working, available or standing by or out of service. I have enclosed 30 records as a comma separated values file below my question for you to see a sample.

The Question !

So what do I do with this data? I have already generated histograms showing the frequency of FireTrucksWorking, etc. Is there a statistical test such as regression that I can perform on this data? How do I bring meaning to this dataset? Are there any general patterns to the dataset that I can discover? Can I create a probability model out of this data such as; at 08:00 during the month of July there is x% probability of N fire trucks working ? Would you use queueing theory on this dataset?

I am open to all suggestions. At this point I have a large dataset but it is really just a jumble of numbers, how do I generate meaning out of these numbers?

This is cross-posted in Mathematics Exchange. But here I make the following revision. I am looking for some type of knowledge discovery that will show me something about my data set the naked eye cannot see or the human brain cannot discern. For example: "there is a much higher likelihood of working during the hours of 08:00 to 12:00 than the rest of the day". "Are there patterns in my data I do not recognize"? Do you have any thoughts on this?

Thirty sample records of data

21546912,2018-01-01 00:02:31.800,1,9,0,0
21546921,2018-01-01 00:04:46.720,1,9,0,0
21546950,2018-01-01 00:09:39.400,1,9,0,0
21546951,2018-01-01 00:09:47.320,2,8,0,0
21546955,2018-01-01 00:11:16.780,3,7,0,0
21546959,2018-01-01 00:12:04.840,2,8,0,0
21546962,2018-01-01 00:12:09.030,3,7,0,0
21546963,2018-01-01 00:12:14.470,3,7,0,0
21546966,2018-01-01 00:12:17.790,3,7,0,0
21546967,2018-01-01 00:12:21.240,2,8,0,0
21546970,2018-01-01 00:12:40.240,2,8,0,0
21546973,2018-01-01 00:12:46.720,2,8,0,0
21546990,2018-01-01 00:14:24.610,2,8,0,0
21547002,2018-01-01 00:16:03.130,2,8,0,0
21547036,2018-01-01 00:19:59.450,1,9,0,0
21547043,2018-01-01 00:21:21.950,0,10,0,0
21547064,2018-01-01 00:24:50.470,0,10,0,0
21547065,2018-01-01 00:25:13.000,0,10,0,0
21547165,2018-01-01 00:43:31.130,0,10,0,0
21547344,2018-01-01 01:15:00.980,1,9,0,0
21547361,2018-01-01 01:16:58.320,1,9,0,0
21547383,2018-01-01 01:21:38.130,1,9,0,0
21547421,2018-01-01 01:30:42.250,0,10,0,0
21547436,2018-01-01 01:33:30.320,1,9,0,0
21547442,2018-01-01 01:33:48.470,1,9,0,0
21547449,2018-01-01 01:33:58.780,1,9,0,0
21547474,2018-01-01 01:37:30.550,1,9,0,0
21547484,2018-01-01 01:39:12.350,1,9,0,0
21547487,2018-01-01 01:40:41.290,0,10,0,0
21547510,2018-01-01 01:47:02.420,0,10,0,0
  • $\begingroup$ IMO avoid Excel, if you know Python Pandas R is very similar. statsmodel Python looks the ticket $\endgroup$
    – M__
    Jun 4 '19 at 6:42
  • 1
    $\begingroup$ I would visualize the data (especially as it has few features). To remove some datapoints, check whenever a firetruck was working, using something like np.nonzero(np.diff). As the amount of data still might be to large, you may want to use multiple plots. $\endgroup$ Jun 4 '19 at 8:38

There are a lot of different possibilities. It is a bit hard to get the message through here in the forum. But I try and focus on one thing that is relatively easy to implement:

One of the easiest things you can do is to do a linear regression. You dependent variable (y) would be „fire trucks working“ (or the share of working ones). You can estimate this dependent on time. So you can (for example) generate indicator variables (=1 if something is true vs =0 if not) which indicate things like weekday, hour of the day. Use this indicators as explanatory (x) variables in the regression.

The result will be, that each indicator shows you the average truck use at some point in time. Say indicator A is „monday“, B is „16:00“, you will see how many trucks are on average dispatched in this time window. You can also easily see if the difference is significant or not.

In the past such models were used to estimate the number of callers in callcenters. The thing is called „dummy variable regression“ (at least in some statistic fields). https://en.m.wikipedia.org/wiki/Dummy_variable_(statistics)

Note that you observe effects from each „dummy“ variable in contrast to a baseline. Imagine you have truck use (y) and only 1 indicator/dummy, which is „monday“. Say the intercept of the model (the result with all dummies =0) is 5, meaning on average 5 trucks are employed. If the effect/coefficient of your dummy is 2, it means there are 2 more trucks employed (on average) on mondays. So each dummy/indicator (viz in case it is 1) shows you an effect in contrast to the base category (where all the dummies are 0).

Here is some script in Python which I guess refers to a similar problem, maybe it helps for orientation: https://stackoverflow.com/questions/50733014/linear-regression-with-dummy-categorical-variables

Also this may help: https://songhuiming.github.io/pages/2017/01/21/linear-regression-in-python-chapter-3-regression-with-categorical-predictors/

Another note: In principle I would try to get many different dummies/indicators from the timestamp, e.g weekday, hour of day, special days or seasons (Christmas, winter etc). You need to try combinations which work for you and the model. But don‘t plug too many indicators into the model. Keep things easy in a first step and try to improve the model later.

In order to check the „quality“ of your model you can look at R2 (a measure that shows how well you are able to explain the data which is between 0,1). You can also randomly (!) set aside some 10-20% of the data (do not use them to estimate the model in the first place) to check how well you model can explain real truck use at some point in time.

I would use R for such a task (I find it more intuitive than Python). Here is some example code for R: https://stats.idre.ucla.edu/r/modules/coding-for-categorical-variables-in-regression-models/

Hope this helps: Good luck with your interesting project!

  • $\begingroup$ Peter: Thank you very much for your clearly written and well thought out reply. I am starting to dig into the hyperlinks you provided. Again, thank you! $\endgroup$ Jun 4 '19 at 15:10
  • 1
    $\begingroup$ @DavidFortMyers: Good luck with the cool project. If my answer helped you, would you mind to give a vote. Let me know if you have questions. $\endgroup$
    – Peter
    Jun 4 '19 at 19:21

I would start with a nice and simple decision tree regression to predict the number of trucks working based on date, time and trucks out of service (features). Visualizing this tree could give some decent insight on the big patterns at play, and applying it answers questions such as "Can I create a probability model out of this data such as: at 08:00 during the month of July there is x% probability of N fire trucks working ?"

It might make sense to look at time-based methods, that is taking into account the sequential nature of the data. In the most simple form you can just add a feature "how many trucks were available in average in the past N hours?", and that will certainly already increase the predictive power of the model.

  • $\begingroup$ thank you very much Erwan. I am going to look into this. I still have my statistics books from Wayne State U. I am going to crack open the books and take a deep look at all the different types of regression. Again - thank you very much. $\endgroup$ Jun 11 '19 at 12:38

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.