Pandas for KPIs in InterSystems IRIS BI
Tags: python, sql, deepsee, programming
Published: 2024-04-24 16:29:13

<p>Pandas is not just a popular software library. It is a cornerstone in the Python data analysis landscape. Renowned for its simplicity and power, it offers a variety of data structures and functions that are instrumental in transforming the complexity of data preparation and analysis into a more manageable form. It is particularly relevant in such specialized environments as ObjectScript for Key Performance Indicators (KPIs) and reporting, especially within the framework of the InterSystems IRIS platform, a leading data management and analysis solution.<br>&nbsp;<br>In the realm of data handling and analysis, Pandas stands out for several reasons. In this article, we aim to explore those aspects in depth:<br>&nbsp;</p>
<ul>
<li><strong>Key Benefits of Pandas for Data Analysis:</strong></li>
</ul>
<p>In this part, we will delve into the various advantages of using Pandas. It includes intuitive syntax, efficient handling of large datasets, and the ability to work seamlessly with different data formats. The ease with which Pandas integrates into existing data analysis workflows is also a significant factor that enhances productivity and efficiency.</p>
<ul>
<li><strong>Solutions to Typical Data Analysis Tasks with Pandas:</strong></li>
</ul>
<p>Pandas is versatile enough to tackle routine data analysis tasks, ranging from simple data aggregation to complex transformations. We will explore how Pandas can be used to solve these typical challenges, demonstrating its capabilities in data cleaning, transformation, and exploratory data analysis. This section will provide practical insights into how Pandas simplifies these tasks.</p>
<ul>
<li><strong>Using Pandas Directly in ObjectScript KPIs in IRIS:</strong></li>
</ul>
<p>The integration of Pandas with ObjectScript for the development of KPIs in the IRIS platform is simply a game-changer. This part will cover how Pandas can be utilized directly within ObjectScript, enhancing the KPI development process. We will also explore practical examples of how Pandas can be employed to analyze and visualize data, thereby contributing to more robust and insightful KPIs.</p>
<ul>
<li>&nbsp;<strong>Recommendations for Implementing Pandas in IRIS Analytic Processes:</strong></li>
</ul>
<p>Implementing a new tool in an existing analytics process can be challenging. For that reason, this section aims to provide best practices and recommendations for integrating Pandas into the IRIS analytics ecosystem as smoothly as possible. From setup and configuration to optimization and best practices, we will cover essential guidelines to ensure a successful integration of Pandas into your data analysis workflow.<br>&nbsp;<br>Pandas is a powerful data analytics library in the Python programming language. Below, you can find a few benefits of Pandas for data analytics:<br>&nbsp;</p>
<ol>
<li><strong>Ease of use:</strong> Pandas provides a simple and intuitive interface for working with data. It is built on top of the NumPy library and provides such high-level data structures as DataFrames, which makes it easy to work with tabular data. &nbsp;</li>
<li><strong>Data Structures:</strong> The principal data structures in Pandas are Series and DataFrame. Series is a one-dimensional array with labels, whereas DataFrame is a two-dimensional table representing a set of Series. These data structures combined allow convenient storage and manipulation of data. &nbsp;</li>
<li><strong>Handling missing data:</strong> Pandas provides convenient methods for detecting and handling missing data (NaN or None). It includes some methods for deleting, filling, or replacing missing values, simplifying your work with real data. &nbsp;</li>
<li><strong>Data grouping and aggregation:</strong> With Pandas it is easy to group data by features and apply aggregation functions (sum, mean, median, etc.) to each data group. &nbsp;</li>
<li><strong>Powerful indexing capabilities:</strong> Pandas provides flexible tools for indexing data. You can use labels, numeric indexes, or multiple levels of indexing. It allows you to filter, select, and manipulate data efficiently. &nbsp;</li>
<li><strong>Reading and writing data:</strong> Pandas supports multiple data formats, including CSV, Excel, SQL, JSON, HTML, etc. It facilitates the process of reading and writing data from/to various sources. &nbsp;</li>
<li><strong>Extensive visualization capabilities:</strong> Pandas is integrated with such visualization libraries as Matplotlib and Seaborn, making it simple to create graphs and visualize data, especially with the help of DeepSeeWeb through integration via embedded Python. &nbsp;</li>
<li><strong>Efficient time management:</strong> Pandas provides multiple features for working with time series, including powerful tools for working with timestamps and periods. &nbsp;</li>
<li><strong>Extensive data manipulation capabilities:</strong> The library provides various functions for filtering, sorting, and reshaping data, as well as joining and merging tables, which makes it a powerful tool for data manipulation. &nbsp;</li>
<li><strong>Excellent performance:</strong> Pandas is purposefully optimized to handle large amounts of data. It provides high performance by using Cython and enhanced data structures.</li>
</ol>
<p>&nbsp;<br>Let's look at an example of Pandas' implementation in an ObjectScript environment. We will employ VSCode as our development environment. The choice of IDE in this case was determined by the availability of InterSystems ObjectScript Extension Pack, which provides a debugger and editor for ObjectScript.<br>First of all, let's create a KPI class:</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos">&nbsp; <span class="hljs-keyword">Class</span> BI.KPI.pandasKpi <span class="hljs-keyword">Extends</span> <span class="hljs-built_in">%DeepSee.KPI</span>
{
}</code></pre>
<p><br>&nbsp;<br>Then, we should make an XML document defining the type, name, and number of columns and filters of our KPI:<br>&nbsp;</p>
<pre class="codeblock-container" idlang="10" lang="XML" tabsize="4"><code class="language-xml hljs">XData KPI [ XMLNamespace = "http://www.intersystems.com/deepsee/kpi" ]
{
<span class="hljs-comment">&lt;!-- 'manual' KPI type will tell DeepSee that data will be gathered from the class method defined by us--&gt;</span>
&nbsp;
<span class="hljs-tag">&lt;<span class="hljs-name">kpi</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"MembersPandasDemo"</span> <span class="hljs-attr">sourceType</span>=<span class="hljs-string">"manual"</span>&gt;</span>
&nbsp;
&nbsp;<span class="hljs-comment">&lt;!-- we are going to need only one column for our KPI query --&gt;</span>
&nbsp;
<span class="hljs-tag">&lt;<span class="hljs-name">property</span> <span class="hljs-attr">columnNo</span>=<span class="hljs-string">"1"</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"Members"</span> <span class="hljs-attr">displayName</span>=<span class="hljs-string">"Community Members"</span>/&gt;</span>
&nbsp;
<span class="hljs-comment">&lt;!-- and lastly we should define a filter for our members --&gt;</span>
&nbsp;
<span class="hljs-tag">&lt;<span class="hljs-name">filter</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"InterSystemsMember"</span>
<span class="hljs-attr">displayName</span>=<span class="hljs-string">"InterSystems member"</span>
<span class="hljs-attr">sql</span>=<span class="hljs-string">"SELECT DISTINCT ISCMember from Community.Member"</span>/&gt;</span>
&nbsp;
&nbsp;<span class="hljs-tag">&lt;/<span class="hljs-name">kpi</span>&gt;</span>
}</code></pre>
<p><br>&nbsp;<br>The next step is to define the python function, write the import, and create the necessary variables:<br>&nbsp;</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos"><span class="hljs-keyword">ClassMethod</span> MembersDF(sqlstring) <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Library.DynamicArray</span> [ Language = python ]
{
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;# First of all, we import the most important library in our script: IRIS.
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;# IRIS library provides syntax <span class="hljs-keyword">for</span> calling ObjectScript classes.
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;# It simplifies Python-ObjectScript integration.
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;# With the help of the library we can call any <span class="hljs-keyword">class</span> and <span class="hljs-keyword">class</span> method, and
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;# it returns whatever data type we like, and ObjectScript understands it.
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;import iris
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;# Then, of course, import the pandas itself.
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;import pandas <span class="hljs-keyword">as</span> pd
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;# Create three empty arrays:
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;Id_list = []
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;time_list = []
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;ics_member = []</code></pre>
<p><br>&nbsp;<br>Next step: define a query against the database:<br>&nbsp;</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos"># Define SQL query <span class="hljs-keyword">for</span> fetching data.
# The query can be <span class="hljs-keyword">as</span> simple <span class="hljs-keyword">as</span> possible.
# All the work will be done by pandas:
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;query = <span class="hljs-string">"""
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; id as ID, CAST(TO_CHAR(Created, 'YYYYMM') as Int) as MonthYear, ISCMember as ISC
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM Community.Member
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; order by Created DESC
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; """</span></code></pre>
<p><br>Then, we need to save the resulting data into an array group:</p>
<p>&nbsp;</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos"># Call the <span class="hljs-keyword">class</span> specified <span class="hljs-keyword">for</span> executing SQL statements.
# We <span class="hljs-keyword">use</span> embedded Python library to call the <span class="hljs-keyword">class</span>:
sql_class = iris.sql.prepare(query)
&nbsp;
# We <span class="hljs-keyword">use</span> it again to call dedicated <span class="hljs-keyword">class</span> methods:
rs = sql_class.execute()
&nbsp;
# Then we <span class="hljs-keyword">use</span> pandas directly on the result <span class="hljs-keyword">set</span> to make dataFrame:
data = rs.dataframe()</code></pre>
<p><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp;<br>We also can pass an argument to filter our data frame.</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos"># Filter example
# We take an argument sqlstring which, in this case, contains boolean data.
# With a handy function .loc filtering all the data
<span class="hljs-keyword">if</span> sqlstring is not False:
&nbsp; &nbsp; &nbsp; &nbsp; data = data.loc[data[<span class="hljs-string">"ISC"</span>] == int(sqlstring)]</code></pre>
<p><br>&nbsp;<br>Now, we should group the data and define x-axis for it:<br>&nbsp;<br>&nbsp;</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos"># Group data by date displayed like MonthYear:
grouped_data = data.groupby([<span class="hljs-string">"MonthYear"</span>]).count()</code></pre>
<p>&nbsp;</p>
<blockquote>
<p>Unfortunately, we cannot take the date column directly from grouped data DataFrame,<br>so, instead, we take the date column from the original DataFrame and process it.</p></blockquote>
<p><br>&nbsp;</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos"># Filter out duplicate dates and append them to a list.
# After grouping by MonthYear, pandas automatically filters off duplicate dates.
# We should <span class="hljs-keyword">do</span> the same to match our arrays:
sorted_filtered_dates = [item <span class="hljs-keyword">for</span> item in <span class="hljs-keyword">set</span>(data[<span class="hljs-string">"MonthYear"</span>])]
# Reverse the dates from left to right:
date = sorted(sorted_filtered_dates, reverse=True)
# Convert dict to a list:
id = grouped_data[<span class="hljs-string">"ID"</span>].id.tolist()
# Reverse values according to the date array:
id.reverse()
&nbsp;
# In order to <span class="hljs-keyword">return</span> the appropriate object to ObjectScript so that it understands it,
# we call '<span class="hljs-built_in">%Library.DynamicArray</span>' (it is the closest one to python and an easy-to-<span class="hljs-keyword">use</span> type of array).
# Again, we <span class="hljs-keyword">use</span> IRIS library inside python code:
OBJIDList = iris.cls('<span class="hljs-built_in">%Library.DynamicArray</span>')._New()
OBJtimeList = iris.cls('<span class="hljs-built_in">%Library.DynamicArray</span>')._New()
&nbsp;
# Append all data to DynamicArray <span class="hljs-keyword">class</span> methods Push()
<span class="hljs-keyword">for</span> i in date:
&nbsp; &nbsp; &nbsp; &nbsp; OBJtimeList._Push(i)
<span class="hljs-keyword">for</span> i in ID:
&nbsp; &nbsp; &nbsp; &nbsp; OBJIDList._Push(i)
<span class="hljs-keyword">return</span> OBJIDList, OBJtimeList
}</code></pre>
<p><br>&nbsp;<br>Next step is to define KPI specific method for DeepSee to understand what data to take:<br>&nbsp;</p>
<pre class="codeblock-container" idlang="0" lang="ObjectScript" tabsize="4"><code class="language-cls hljs cos"><span class="hljs-comment">// Define method. The method must always be %OnLoadKPI(). Otherwise, the system will not recognise it.</span>
&nbsp;
Method <span class="hljs-built_in">%OnLoadKPI</span>() <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Status</span>
{
&nbsp;
&nbsp;<span class="hljs-comment">//Define string for the filter. Set the default to zero</span>
&nbsp;<span class="hljs-keyword">set</span> sqlstring = <span class="hljs-number">0</span>
&nbsp; &nbsp; <span class="hljs-comment">//Call %filterValues method to fetch any filter data from the widget.</span>
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">if</span> <span class="hljs-built_in">$IsObject</span>(..<span class="hljs-built_in">%filterValues</span>) {
&nbsp; &nbsp; &nbsp; &nbsp; <span class="hljs-keyword">if</span> (..<span class="hljs-built_in">%filterValues.InterSystemsMember</span>'=<span class="hljs-string">""</span>)
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class="hljs-keyword">set</span> sqlstring=..<span class="hljs-built_in">%filterValues</span>.<span class="hljs-built_in">%data</span>(<span class="hljs-string">"InterSystemsMember"</span>)
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;<span class="hljs-comment">//Call pandas function, pass filter value if any, and receive dynamic arrays with data.</span>
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">set</span> sqlValue = <span class="hljs-built_in">..MembersDF</span>(sqlstring)
&nbsp;
&nbsp;&nbsp; &nbsp;<span class="hljs-comment">//Assign each tuple to a variable.</span>
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">set</span> idList = sqlValue.GetAt(<span class="hljs-number">1</span>)
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">set</span> timeList = sqlValue.GetAt(<span class="hljs-number">2</span>)
&nbsp;&nbsp;
&nbsp;&nbsp; &nbsp;<span class="hljs-comment">//Calculate size of x-axis. It will be rows for our widget:</span>
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">set</span> rowCount = timeList.<span class="hljs-built_in">%Size</span>()
&nbsp;
&nbsp;&nbsp; &nbsp;<span class="hljs-comment">//Since we need only one column, we assign variable to 1:</span>
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">set</span> colCount = <span class="hljs-number">1</span>
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">set</span> ..<span class="hljs-built_in">%seriesCount</span>=rowCount
&nbsp;&nbsp;
&nbsp; &nbsp; <span class="hljs-comment">//Now, for each row, assign time value and ID value of our members:</span>
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">for</span> rows = <span class="hljs-number">1</span>:<span class="hljs-number">1</span>:..<span class="hljs-built_in">%seriesCount</span>
&nbsp;&nbsp; &nbsp;{
&nbsp; &nbsp; &nbsp; &nbsp; <span class="hljs-keyword">set</span> ..<span class="hljs-built_in">%seriesNames</span>(rows)=timeList.<span class="hljs-built_in">%Get</span>(rows-<span class="hljs-number">1</span>)
&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; <span class="hljs-keyword">for</span> col = <span class="hljs-number">1</span>:<span class="hljs-number">1</span>:colCount
&nbsp; &nbsp; &nbsp; &nbsp; {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <span class="hljs-keyword">set</span> ..<span class="hljs-built_in">%data</span>(rows,<span class="hljs-string">"Members"</span>)=idList.<span class="hljs-built_in">%Get</span>(rows-<span class="hljs-number">1</span>)
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp;&nbsp; &nbsp;}
&nbsp;&nbsp; &nbsp;<span class="hljs-keyword">quit</span> <span class="hljs-built_in">$$$OK</span></code></pre>
<p><br>At this point, compile the KPI and create a widget on a dashboard using KPI data source.</p>
<p>&nbsp;<br><span style="font-size:11pt; font-variant:normal; white-space:pre-wrap"><span style="font-family:Arial,sans-serif"><span style="color:#000000"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="border:none"><span style="display:inline-block"><span style="overflow:hidden"><span style="width:602px"><span style="height:263px"><img height="263" src="https://lh7-us.googleusercontent.com/-k4NKy9yHgENyawgKa0LMZgxZrWoJIaGHCJ_Qdh1XfjYDIwz3uA7quoijOeoAy7iBLBMzCZa4Mfoe4sp0_eueX1cOTrDsjVdZn_70_4jRUmyVdfnVqpFCrrGS0mC1EDxeLXCvgJqSKnp2w7Flmw3TQo" width="602"></span></span></span></span></span></span></span></span></span></span></span><br>That's it! We have successfully navigated through the process of integrating and utilizing Pandas in our ObjectScript applications on InterSystems IRIS. This journey has taken us from fetching and formatting data to filtering and displaying it, all within a single, streamlined function. This demonstration highlights the efficiency and power of Pandas in data analysis. Now, let's explore some practical recommendations for implementing Pandas within the IRIS environment and conclude with insights on its transformative impact.<br>Recommendations for Practical Application of Pandas in IRIS</p>
<ul>
<li>Start with Prototyping:</li>
</ul>
<p>Begin your journey with Pandas by using example datasets and utilities. This approach helps you understand the basics and nuances of Pandas in a controlled and familiar environment. Prototyping allows us to experiment with different Pandas functions and methods without the risks associated with live data.</p>
<ul>
<li>Gradual Implementation:</li>
</ul>
<p>Introduce Pandas incrementally into your existing data processes. Instead of a complete overhaul, identify the areas where Pandas can enhance or simplify data handling and analysis. It could be some simple tasks like data cleaning aggregation or a more complex analysis where Pandas capabilities can be fully leveraged.</p>
<ul>
<li>&nbsp;Optimize Pandas Use:</li>
</ul>
<p>Prior to working with large datasets, it is crucial to optimize your Pandas code. Efficient code can significantly reduce processing time and resource consumption, which is especially important in large-scale data analysis. Such techniques such as vectorized operations, using appropriate data types, and avoiding loops in data manipulation can significanlty enhance performance.</p>
<p><br>Conclusion</p>
<p>The integration of Pandas into ObjectScript applications on the InterSystems IRIS platform marks a significant advancement in the field of data analysis. Pandas brings us an array of powerful tools for data processing, analysis, and visualization, which are now at the disposal of IRIS users. This integration not only accelerates and simplifies the development of KPIs and analytics but also paves the way for more sophisticated and advanced data analytical capabilities within the IRIS ecosystem.<br>&nbsp;<br>With Pandas, analysts and developers can explore new horizons in data analytics, leveraging its extensive functionalities to gain deeper insights from their data. The ability to process and analyze large datasets efficiently, coupled with the ease of creating compelling visualizations, empowers users to make more informed decisions and uncover trends and patterns that were previously difficult to detect.<br>&nbsp;<br>In summary, Pandas integration into the InterSystems IRIS environment is a transformative step, enhancing the capabilities of the platform and offering users an expanded toolkit for tackling the ever-growing challenges and complexities of modern data analysis.</p>