Welcome to the second part of the Data Science with TM1/Planning Analytics article. In Part 1, we uploaded weather data from a web service into our TM1 cubes. Now that we have all the data we need in TM1, we can start to analyse it. The Python community provides lots of tools which make data science easy and fun.
Why using Python for Data Science?
Top-notch, free data analysis libraries
Free (and good) libraries to access data from systems or web
Lots of research in new disciplines like Machine Learning (Tensorflow) or Natural Language Processing (NLTK)
TM1 (as the Information Warehouse) is the ideal data source for Data Science
Objective
The objective of this article is to explore the impact of seasonal effects, weather and public holidays on our operative business by following these steps:
Load and visualize monthly bike shares by city
Explore seasonal and regional trends
Analyze relationship between average temperatures and bike shares by day
Analyze the impact of Non-working days vs. Working days
Prerequisites
To run this example on your machine, you will need: Windows C++ compilers, Pandas, Numpy, SciPy, Matplotlib, Plotly, PyStan, Prophet, and TM1py.
Step 1: Load and visualize 2017 monthly bike shares by city
First, define the TM1 connection settings at the top of the notebook. Then load data from the cube view into Python:
Rearrange the dataframe to reduce dimensionality from 4 (Time, Version, City, Measure) to 2 (Time, City):
Using Plotly, create an interactive barplot with just 7 lines of code:
Conclusion: As expected, the seasons have a massive impact on our bike sharing business. While the relation between Summer and Winter months in NYC and Washington DC is approximately 1/2, in Chicago it is close to 1/5!
Step 2: Explore seasonal and regional trends
Bring in the daily data from 2014 to 2017 and use the Pandas corr() function to calculate correlations:
Conclusion: Temperature and Bike shares are strongly correlated in every city. The intensity with which temperature affects the bike shares varies by city, so we will need to create different forecasting models by city.
Step 3: Analyze relationship between average temperature and bike shares by day
Visualize the relationship between temperature and bike shares in a Scatterplot:
Conclusion: Among the three cities, the distribution in Chicago is the closest to a linear model. For Washington DC, temperatures of approx. 25 degrees and higher show stagnating bike counts. The distribution for NYC is less homogeneous.
Step 4: Analyze the impact of Non-working days vs. Working days
Create a linear regression between the average temperatures and the bike shares using SciPy:
Query Public Holidays and Weekends from TM1 through MDX:
Conclusion: In all three cities the vast majority of non-working day points lay under the fitted line. On Non-working days there is generally less usage than our fitted linear regression model predicts. For effective forecasting we will need to take weekdays and public holidays into account.