All Roads Lead to Kdb: the technical counterpart

This post serves as a follow-up to our prior article, All Roads Lead to PyKX, where we introduced the utility of the PyKX library from the perspective of Emma Monad, the CTO of a large fictional company known as Mad Flow. In addition to this high-level perspective, the focus of this post is on Python programmers who are eager to explore the technical intricacies that were left unexplained in the aforementioned article. Consequently, the post will provide them with a highly detailed, step-by-step example of how to migrate an existing Python codebase into PyKX. Similarly to the outcomes presented in the post Accelerating Python Workflows using PyKX, which we highly recommend reading, we will observe a significant performance advantage of the resulting PyKX code compared to the initial pandas implementation. More references are available in the bibliography at the end of the post.

The structure of the post will be as follows:

• The Use Case: We will begin by explaining the traffic improvement use case itself, encompassing its objectives, data sources, and anticipated outcomes. This section will also include references to the necessary data sources required to execute all the code provided in this blog post.
• PyKX migration: Subsequently, we will illustrate the process of delegating resource-intensive tasks to kdb+/q using PyKX, all while staying within the Python environment. This kind of PyKX-boosted Pythonic version is probably the fastest way to begin harnessing the capabilities of the kdb+/q environment.
• pykx.q migration: Lastly, we will delve into the process of transitioning the code to pure kdb+/q, while continuing to utilize PyKX to retain the Pythonic code you wish to preserve. This kdb+/q version enhanced by PyKX is presented for those interested in a more extensive adoption of kdb+/q.

The initial section regarding the use case is independent of programming languages and is primarily included for reference purposes. If you're eager to delve directly into the code and begin learning how to migrate pure pandas-based Python code into PyKX, you can proceed to the second section now and revisit the first section as necessary.

Use Case

With the aim of predicting traffic congestion in the presence of rain, Mad Flow dedicated significant effort to preparing and integrating weather and traffic data into an LSTM model. This endeavor aligned with other studies that sought to forecast traffic patterns using LSTM models based on air pollution. Just like findings from studies in cities such as Manchester, Shenzhen and Belgrade, our results project a weekday traffic volume increase of 5-15% during peak hours in the presence of rain.

Like in most projects related to smart cities, the used data is notable diverse, so it needed a lot of preparatory work. The following sections will detail the data sources, the cleansing and interpretation processes, as well as the used model.

Data sources

The Madrid City Council provides weather and traffic data, including both real-time and historical records. For the purpose of model training, only the historical data is pertinent. These datasets, formatted as CSV files, are categorized by months spanning from the year 2018 to the present.

Traffic data

The traffic sensors are strategically positioned at traffic lights throughout the city of Madrid. The collected data gather together diverse measurements of road conditions, including speed and traffic volume. All of these metrics are encapsulated in the load metric, quantified in percentage terms to denote levels of congestion. The format of the traffic table is presented as follows:

This table amass information from various traffic stations, detailing elements such as traffic intensity, occupancy, congestion load, and other relevant data, all correlated with specific dates and times.

The table also includes a column for the date and another column that identifies the sensor. This identification will be used to establish a link with its corresponding coordinates by utilizing the subsequent traffic stations table:

Weather data

The table below provides a snapshot of the type of information available in the weather dataset:

This dataset contains hourly meteorological observations, including temperature, humidity, wind speed, and precipitation measurements.

Each row documents various details about a specific meteorological station:

• The 'magnitude' field denotes the type of meteorological data captured in the corresponding row (e.g., 81 - wind, 82 - direction, ..., 89 - rainfall).
• In the 'H01' and 'V01' columns, the value for the data type specified in the 'magnitude' column is provided, along with its validity status, respectively.
• The 'year', 'month', and 'day' columns indicate the timestamp of the measurement recording.

Furthermore, we will require the geographical coordinates of the various stations, which are available in a distinct table provided by the Madrid City Council.

Data Cleansing

Upon loading the data, the subsequent task involved its preparation for analysis and modeling. The eventual consolidation of this cleaned data into a unified table will occur during a subsequent phase.

After loading the data, the next step was to get it ready for analysis and modeling. Given the diverse nature of the datasets, the process of data cleansing will be conducted individually for each dataset. We'll bring all this cleaned data together into a unified table in a later phase.

Traffic Data

The process employed to clean the traffic dataset involves the following steps:

1. Initially, values characterized by measurement errors are excluded from consideration. The documentation designates these values using the symbol "N".

2. Subsequently, solely the load measurements, which constitute the focus of our analysis, are retained.

3. The data is then grouped based on each date and station, subsequently computing the average load value for each group:

Weather Data

Concerning the weather dataset, the procedures undertaken were as follows:

1. In the weather dataset, the date is split into separate columns for day, month, and year. However, in the traffic dataset, this information is combined. So, we merged the columns to create a single 'date' column with the format:" day-month-year.

2. Additionally, given that each individual row contains all 24 daily measurements taken at a weather station, along with their respective magnitudes, we need a transformation where each hourly measurement value and its corresponding validity status are discrete entries within separate rows. This presents an opportunity to remove any measurements that are marked as invalid.

1. To standardize the time measurement format and align it with the traffic table, we merge the date with the time. As a result, the updated date format becomes:date-month-year hour:minute:second

2. Lastly, we restructure the diverse types of measurements into distinct columns, enhancing the organizational coherence of the dataset:

Location Data

To make it easier to combine the tables we mentioned, we need to connect the traffic sensors with the weather stations. To do this, we can use a distance matrix that helps us find the closest pairs of both types of stations. Instead of directly measuring the distance using coordinates, we decided to go with the Haversine distance. This method calculates the distance in meters between coordinate pairs, which gives a clearer idea of the distances.

This visualization is best shown using a heatmap, where the distances are displayed on a range from 0 to 20 kilometers:

If we look at the shortest distances between each type of station, we end up with a mapping between these two tables:

The Final Table

After we've done all the setup for the three tables - weather, traffic, and distance - we can now join them together. Because the weather table has data every hour, while the traffic data is available every 15 minutes, we'll combine them using an 'asof' join method. Then, we'll add time and day-of-the-week details to the dataset, which will help us study how these factors are connected to traffic congestion.

Data interpretation

Traffic patterns exhibit a pronounced dependency on time. To dig deeper into the data, a filtering process will be applied to extract instances of peak traffic. This focused dataset will help us really get a better grip on traffic dynamics.

The next figures illustrates the outstanding seasonality within the dataset:

Note: In Figure 3, zero represents Monday.

It's clear that weekdays have more traffic compared to holidays. Likewise, during the daytime, there's a higher traffic flow than at nighttime.

Concerning the relationship between Rainfall and Load, preliminary observations indicate a limited occurrence of rainy days within our dataset:

After looking closely at the percentiles of the precipitation column, it's clear that recorded instances of rainfall are quite scarce. To address this limitation, the measurements were divided into distinct categories based on the intensity of the rainfall. This led to a separate analysis for datasets involving heavy rain, moderate rain, and times when there was no rain. This analysis was done hourly, to minimize the impact of time on traffic patterns.

The results of this analysis show that on average, traffic congestion increases by around 5% to 14% during rainy hours. This consistently matches the findings discussed in the Use Case section.

To confirm the statistical importance of the differences between these groups, we conducted an analysis of variance (ANOVA) test. Notably, the ANOVA results strongly suggest significant variations in load levels among the different rain intensity categories for all hours. For example, let's take a look at the results for hour 12.

We chose to include rainfall as a factor in the model. However, the other meteorological conditions didn't show similarly clear results, so we decided to leave them out of the model.

The Model

For the purpose of performance evaluation, a preliminary model was developed employing a basic Long Short-Term Memory (LSTM) architecture with a memory span of 5 steps. The model's objective is to forecast the load at a specific traffic station. The model input encompasses historical load data, rainfall measurements, as well as the hour and day of the week.

Preliminary results derived from a single station exhibit promising outcomes, as demonstrated in Figure 6. In this figure, we compare the actual traffic load with the predictions made by the LSTM model. Furthermore, an analysis of the training and validation loss curves (Figure 5) indicates that the model is not experiencing issues related to overfitting or underfitting.

PyKX migration

Despite the promising forecasting results yielded by the LSTM model, certain considerations arose concerning the efficiency of the Python code employed for project implementation. After profiling the entirity of the process, we found 4 key areas in which the code was behaving worse than expected:

At this point, a fundamental decision had to be made regarding the migration strategy. Let's delve into the two alternatives at our disposal, along with their positive and negative aspects:

• Partial migration: This approach involves identifying the specific points where pandas experiences the greatest strain. Subsequently, these segments can be migrated to q using PyKX, while leaving the rest of the code intact. This alternative capitalizes on the compatibility features of PyKX, which ensure a seamless interaction between pure pandas/NumPy and PyKX. For instance, we may use the .pd() method, which allows us to convert a PyPX table object into a pandas dataframe. This strategy can be particularly effective if the demarcation between computationally demanding and less complex segments is evident. However, this isn't always the case, leading to multiple conversions between Python and q. Furthermore, as the documentation recommends, we should minimize these Python-to-Q conversions whenever feasible.

  To test how much time is lost in these conversions, we moved the weather and traffic tables, as well as a 12-million-row table from a later stage in the process, to Python using the .pd() method. The outcomes displayed in the subsequent table led us to dismiss this option.

  	Translation time
  Traffic table	2.78 s ± 84.9 ms
  Weather table	24.6 ms ± 821 µs
  Final table	1 minute 33 s

  A promising alternative to this intermediate transformations method is the PyKX implementation of the pandas API. However, as we will explore later, even this had to be discarded for our particular case.



• Complete Migration: Completely migrating the code to PyKX allows for the full use of kdb+/q processing capabilities without the need to switch between memory spaces. This strategy is particularly pertinent for managing substantial volume of data. In our case, a complete migration to PyKX became indispensable. Although it might seem challenging at first, as we will soon realize, having a basic understanding of q will make this process straightforward.

Let's get started!

First of all we need to install and import PyKX:

Datasets

An excellent starting point for the migration process involves transferring our data to the q environment. We can even revert these objects to pandas and reuse all our existing code. This approach ensures that our data remains stored within the kdb environment, thus benefitting from its rapid and scalable database capabilities. However, it's important to acknowledge that we might sacrifice the processing power of kdb+/q. As a result, we will proceed with deeper steps in the migration process.

Before continuing, be sure to download the datasets required for running the next pieces of code.

Traffic

The preprocessing of the traffic table was one of the most critical parts in terms of time. Later on, we will showcase the improvement in execution time compared to our pure pandas implementation.

The data loading will be executed employing the utilities facilitated by PyKX:

As a result of this process, our table is now a PyKX object:

Let's examine the initial few rows of this table:

We have the kx.q.qsql interface integrated in PyKX, which allows us to query into q tables concisely.

To preprocess the traffic table our objective is to ascertain the average load based on date and season, while eliminating measurement errors. The power of qSQL enables us to accomplish this feat through a singular query:

Now traffic is a keyed table. Keyed tables indexing is a bit different; that's why if we want to see the first values of the table, we need to use the sublist method.

Weather

This table, serving as the traffic table, will also be imported into the Python environment. Consequently, it becomes accessible as a Python object, albeit not within q scopes.

To display a table in markdown format, we can transfer it to pandas:

As is evident, these objects are not currently within the q memory space. Let's proceed to explore how we can access and use q features on these objects. First, we will examine the straightforward xcol function, which affords us the ability to rename columns:

With the following code we have removed several columns that are not relevant to the analysis.

The next step involves merging the year, month, and day columns into a single unified date column. To initiate this process, we begin by accessing these three individual columns. This can be accomplished through indexing:

We observe that the outcome consists of three lists, each containing data corresponding to the sample size. The objective is to form a single list of the sample size, wherein the three date elements are combined:

We appear to be approaching the desired outcome. Currently, we possess a list of sample size, wherein each position contains a sub-list comprising three elements: the day, the month, and the year. To consolidate each sub-list into a singular, unified element, the each iterator can be used:

The final step entails converting the resultant data from string format to a date format. However, it's worth noting that certain functions, particularly the overloaded glyphs, have yet to be implemented. For example cast ($), take (#), concat (,)... So we are forced to abandon the pythonic way of calling q functions and perform this casting writing kdb+/q code using the pykx.q() method:

Finally, we add this column to our table:

Shortly after, some team members started using q code instead of PyKX functions in the pythonic way, as they found the resulting code to be sleeker and more concise. As their familiarity with q grew, they put forth a suggestion to create a function entirely written in q.

Once again, both solutions are entirely compatible and can even be combined. It's at the programmer's discretion to opt for either approach.

Now, the three columns that have already been incorporated into the date can be eliminated:

The current state of the weather table is as follows:

Now, let's shift our focus towards deconstructing the H* and V* queries into multiple rows, while simultaneously introducing a time column to prevent information gaps. In q, the conventional approach would involve leveraging functional qSQL to extract columns conforming to the aforementioned patterns. However, we will capitalize on the advantage that PyKX offers by incorporating q code via strings to sidestep this method:

The function above accepts a list of columns, a designated pattern for searching, and a q function represented as a string. This function takes the columns found following the specified pattern in qSQL format (where columns are accessed by their names, not symbols) as an argument. When applied to all columns commencing with "H", it yields these columns as a vector of symbols, alongside a string representation of these columns in qSQL format:

This capability is remarkably powerful, as it enables the utilization of qSQL alongside variables, obviating the need for functional forms which can often prove intricate for individuals new to kdb+/q development.

Now, let's apply the above methodology to the columns commencing with H, which correspond to the measurement values, and to the columns commencing with V, which indicate the validity of the measurements. The function employed to transform the measurement column into rows is flip:

Our next step involves feeding our internally constructed functions, represented as strings, into the qSQL update function. This is done in conjunction with the repetition of the 24-hour sequence to match the initial number of rows:

To remove columns beginning with H or V, we can employ the same approach as earlier to circumvent the need for functional qSQL:

Let's see the result:

Finally, the remaining task involves expanding the table so that each element within the lists corresponds to a distinct row:

We can further streamline the table by eliminating rows that lack validity and merging the date with the time:

The magnitude column plays a pivotal role in attributing meaning to the value. As outlined by the dataset creators, the diverse magnitudes align with the elements encapsulated within the following dictionary:

We just have to change the key by the value of the dictionary.

Finally, all we have to do is separate the different weather conditions into different columns:

The exec query provides us with the capability to transform a dictionary, stored within a column, into multiple columns. In this transformation, the keys become the column names, and the values constitute the data within those respective columns. This proves particularly beneficial when dealing with a dictionary that represents the weather conditions of each entry, linked with their respective values. By implementing this approach and subsequently grouping the data, we effectively disseminate the weather conditions across distinct columns for each weather entry and weather station.

Location

Both traffic and weather station tables will be loaded into the q memory space:

We are now able to access these objects within q functions without the necessity of passing them as PyKX or Python objects. To illustrate, let's proceed to modify the column names in both tables in order to establish a uniform naming convention:

Our objective is to merge these two tables. Currently, there appears to be no identifier that readily facilitates a conventional join. Nevertheless, it's worth noting that both the weather and traffic stations are situated by coordinates. We can exploit the spatial proximity between stations designated for measuring traffic and weather to facilitate the join. To compute the distance between two sets of coordinates, the Harvesine distance as previously discussed, can be employed. It's important to acknowledge that while this distance function is readily available in Python, it is not natively accessible within q.

One potential approach would involve re-implementing the Haversine distance function in q. However, this might become impractical for more complex libraries. Alternatively, although slower, we could transmit our q objects to Python and work with them there. Nonetheless, it's strongly recommended to avoid switching objects between q and Python. However, the data we had to move between memory spaces wasn't very large (and we were careful about this) and we didn't see any noticeable drop in performance.

The features we've elucidated earlier, allowing us to transition between Python and q objects, empower us to temporarily reuse Python code. This is particularly pertinent given the scale of tables we are currently dealing with. To incorporate our q objects into this function, we can use certain PyKX tools to convert them into Python objects:

We've reached a point where we have a matrix detailing the distance in kilometers for every combination of traffic and weather stations. Our upcoming task is to pinpoint pairs of station identifiers that exhibit the minimum distance:

With this we have a mapping that relates every traffic station to its nearest weather station:

Final Table

Integrating the three tables is a relatively straightforward process. The distances table can be seamlessly merged with either of the other two using a simple left join. However, when joining the traffic and weather tables, an asof join (aj) is necessary due to their disparate time intervals. To conclude, two columns, time and day of the week, should be appended to furnish the model with the data's inherent seasonality:

Let's look at this last table:

Model

For the model's input, we chose to cherry-pick only the essential columns. Moreover, we apply normalization to the rainfall column using a straightforward MinMax scaler. This function can be included within the q memory space, ready for use whenever necessary:

Throughout this transition from pandas, the primary challenge emerged while migrating the time_window function, given its reliance on loops. Our approach involved first comprehending the input data, defining the desired output, and then formulating an idiomatic q implementation rather than a direct 1:1 migration. This method proved more time-efficient.

In this scenario, our input consisted of a table, and our desired output was a list of matrices for each station. To facilitate this process, we devised multiple functions that proved invaluable:

• sliding window: given a matrix, x, divides it into chunks of length y:

• get target: For a given matrix, x, it takes the target located in the column z and the first lines are eliminated to match with the chunks length y:

• to Matrix: Transform the table x to a matrix:

• prepareData: This function is designed to create the training and testing datasets. It begins by selecting the necessary columns (columns) from the table, grouped by traffic station. For each traffic station, it retains ntest rows for testing purposes. The data is divided into chunks of length chunkLen. Finally, the function constructs a list of dictionaries for training data and training targets (the load column), as well as test data and test targets.

  To start, regardless of whether we require the training or testing output, we must retrieve either the last ntest records or up to the last ntest records. Given the necessity of performing this operation for the designated columns, a functional statement emerged as the most suitable approach.

  Once we've molded the data to align with our requirements, the subsequent step involves constructing the list of matrices. Achieving this entails employing a sliding window approach. In order to return a list of matrices, rather than a list of tables, we used the toMatrix function, which facilitates the transformation of a table into a matrix. Additionally, we useed the sw (sliding window) function to apply the sliding window concept to a singular large matrix.

  In terms of the y data, we enlisted the gt (get target) function. This function retrieves the target data (traffic load), with the exclusion of the first chunkLen+1 entries.

Lets test this function in action with only one station:

That wraps up our migration to PyKX. Next, we proceed to confirm that the model is working as intended with the data we acquired through PyKX.

Firstly, we install Tensorflow:

And now we create and train a simple LSTM model:

Now we can observe the loss curves for both training and validation over epochs.

And finally, we can see the performance of the model in the following graph:

Performance gains

As discussed earlier, all 4 bottlenecks were solved by migrating to the kdb+/q environment taking advantage of PyKX. Overall, the final picture looks like this:

So we got a 10x decrease across the board. We went from a total of 36 seconds spent on these bottlenecks down to just shy of 4 seconds, which is really impressive on itself. If a migration like this one was on the horizon, we would strongly suggest profiling the code to see where it struggles the most and try to think of a way to migrate those parts alone to PyKX. Then, as a second step, we recommend to port the rest of the code to PyKX in order to circumvent the overhead that moving data between memory spaces inherently adds to the process.

pykx.q migration

After gaining proficiency in the q language, the team progressively shifted towards utilizing kx.q() extensively, drawn by its brevity. Yet, this approach introduced a level of monotony due to the requirement of employing strings. As a remedy, a decision was made to transition into a q environment. However, as highlighted in the previous chapter, certain Python code proved challenging to migrate to q. Consequently, the team chose to remain within PyKX, this time operating within a q environment.

pykx.q facilitates the execution of Python code within a q environment, thereby presenting new avenues for addressing existing codebases. This flexibility enables the incorporation and utilization of Python libraries, both those installed on the system and those available as .py files.

In our scenario, we harness both of these options. We emphasize the potential provided by these opportunities to integrate Python code within pykx.q, while also providing a link to the project's q script for readers seeking further insights.

Our initial step involves executing the haversine_vector function for calculating coordinate distances. Given its unavailability in q, we opted to employ pykx.q to seamlessly integrate this library directly into our q code. This was achieved through the following lines:

.pykx.pyexec"from haversine import haversine_vector, Unit";

This function expects two pandas DataFrames as input, so we need to change the default conversion type from NumPy to pandas:

.pykx.setdefault"pd";

Having done this, we can "move" our input variables to the Python memory space using .pykx.set

.pykx.set[`a;`longitude`latitude#a];
.pykx.set[`b;`longitude`latitude#b];

And finally execute our function:

(.pykx.eval"haversine_vector(a, b, Unit.KILOMETERS, comb=True)")`

Notice the backtick at the end, this is for converting back to a q type.

The other way we can run Python code is to load a .py (renamed to .p) file using \l. This could be done as follows:

system"l kerasmodel.p";

Here we have included the functions fit and predict previously defined in last section. We can load them and use them like this:

modelfit:.pykx.get`fit;
modelfit[train[0][3403];train[1][3403];test[0][3403];test[1][3403]];
modelpredict:.pykx.get`predict;
res:modelpredict[train[0][3403]];

In terms of performance, we noticed a slight decrease in execution time comparing to using PyKX:

These little timing shifts might happen because of changes in memory or processor activity, but it's not really noticeable as far as we could see.

Final thoughts

Overall this project turned out smoother than expected thanks to PyKX. It allowed us to get a foothold on the kdb+/q ecosystem before making the full transition to using q code, which on itself it's impressive. We achieved our goals of reducing execution time, as we saw throughout this post, and learned a lot about the kdb+/q ecosystem and its technologies.

It wasn't all smooth and sail though. For instance, we hit a fundamental obstacle when using the pandas API. In an ideal world, the transition from pandas to PyKX using this API would be as simple as importing PyKX, enabling a flag and getting the input tables as PyKX objects. However, since we relied on operations such as group_by and melt, it ended up being unfeasible since these operations were not yet implemented and we would have to manually code them, which would take a long time on itself. We should note, however, that this feature is still on beta, so we look forward to future improvements in this regard since it would make migrations like this one much simpler once it becomes a drop-in replacement for pandas calls.

In summary, with the experience we gained we dare to recommend you following these steps as a PyKX migration guide:

1. Migrate the original data to a kdb+/q environment or PyKX objects.
2. Profile the original code to locate bottlenecks. This allows us to put the focus on the heavier parts of our process.
3. Once located, migrate those parts alone to PyKX. You will take a performance hit when moving data from memory spaces, but it's important to know the potential gains that are possible, so measure the migrated bottlenecks alone to see the difference.
4. If your tables are not that large, you may be able to get away with it as-is, not needing to do a full migration. If that's the case then great! You are done. However, if you notice that the performance hit when moving data between memory spaces is actually hindering the process, consider a full migration to PyKX.
5. If a full migration to PyKX is needed, then first take a look at the pandas API. By the time you read this, it may have already improved compatibility and could be a drop-in replacement for pandas. If it's not the case you will need to familiarise yourself with PyKX and get your hands dirty as we had to.

We hope this post could serve as a guide of sorts for those that want to familiarise themselves with PyKX and the kdb+/q ecosystem since it brings a lot to the table. In the end we were able to achieve a full 1:1 migration to PyKX and even to q with pykx.q, which is impressive given the differences between the languages and their philosophy all while notably cutting execution time. Below you will find some additional references to continue learning PyKX. Have fun with PyKX!

Acknowledgments