<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Zaishan Weng on Medium]]></title>
        <description><![CDATA[Stories by Zaishan Weng on Medium]]></description>
        <link>https://medium.com/@zaishanweng?source=rss-4c27aa18efe8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*jYCr1gEjIHRaoPtmEzHyzQ.jpeg</url>
            <title>Stories by Zaishan Weng on Medium</title>
            <link>https://medium.com/@zaishanweng?source=rss-4c27aa18efe8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 04:44:25 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@zaishanweng/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Simple implementation of a meeting transcription solution locally]]></title>
            <link>https://medium.com/@zaishanweng/simple-implementation-of-a-meeting-transcription-solution-locally-85957c936900?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/85957c936900</guid>
            <category><![CDATA[whisper]]></category>
            <category><![CDATA[rsa]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[speech-recognition]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Sun, 14 Apr 2024 12:14:06 GMT</pubDate>
            <atom:updated>2024-04-14T12:14:06.778Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/270/1*y8zHfsHgAC_K8YXAqtaq9Q.jpeg" /></figure><h3>Introduction</h3><p>In a recent engagement with a team that provides secretariat service, there is an opportunity to optimize the existing process of creating meeting minutes. In the current context there is a significant time spent in listening to recorded meeting captured on a voice recorder to ensure comprehensiveness of the points captured.</p><p>At some of these offline meetings, confidential issues and sensitive data are often shared. While recording these discussions is necessary for reference, sharing such recordings over the cloud or with third-party transcription services poses security risks. Leakage of confidential information could have severe consequences. As such, we decided to experiment with a local implementation rather than services available in the Cloud.</p><p>Based on our initial exploration of the pre-trained open source models available on Hugging Face Hub, we find that models from the Whisper family can be easily setup and has a high accuracy of transcription. We refer to the comparison of different Hugging Face models at their <a href="https://huggingface.co/spaces/hf-audio/open_asr_leaderboard">leaderboard page</a></p><h3>Initial Setup</h3><h4>Installation and setup of ffmpeg</h4><p>In order to use the Whisper family of transformer models, there is a need to install ffmpeg first. The steps to setup ffmpeg which I found most straightforward are as follows:</p><ol><li>Download installation package from the official site <a href="https://ffmpeg.org/download.html">https://ffmpeg.org/download.html</a></li><li>Follow the guide in this online article to setup ffmpeg <a href="https://www.wikihow.com/Install-FFmpeg-on-Windows">https://www.wikihow.com/Install-FFmpeg-on-Windows</a></li><li>Create a new environment for installation just to be safe</li></ol><p>Generally, this should be sufficient. In case any additional python bindings might be required, I would typically check on libraries.io to find the packages based on a combination of most stars, highest source ranks and updated relatively recently if possible.</p><h4>Setting up Pytorch, HuggingFace and loading the pre-trained transformers model</h4><p>Install the pre-requisite python libraries required</p><ul><li>Pytorch installation: <a href="https://pytorch.org/">https://pytorch.org/</a></li><li>Transformers: <a href="https://huggingface.co/docs/transformers/installation">https://huggingface.co/docs/transformers/installation</a></li></ul><h3>Transcribe the audio with Whisper Models</h3><p>First we import the necessary libraries.</p><pre>#Load Required Libraries<br>import torch<br>from transformers import AutoModelForSpeechSeq2Seq, AutoProcessor, pipeline</pre><p>Second, we load the pre-trained model. For the purpose of testing, we load the smallest and fastest distil-whisper small model but the same codes can be used for other models under the Whisper family.</p><pre>#Load pre-trained model<br>device = &quot;cuda:0&quot; if torch.cuda.is_available() else &quot;cpu&quot;<br>torch_dtype = torch.float16 if torch.cuda.is_available() else torch.float32<br><br>model_id = &quot;distil-whisper/distil-small.en&quot;<br><br>model = AutoModelForSpeechSeq2Seq.from_pretrained(<br>    model_id, torch_dtype=torch_dtype, use_safetensors=True<br>)<br>model.to(device)<br><br>processor = AutoProcessor.from_pretrained(model_id)</pre><p>Third, we load in our sample file and generate the output.</p><pre>pipe = pipeline(<br>    &quot;automatic-speech-recognition&quot;,<br>    model=model,<br>    tokenizer=processor.tokenizer,<br>    feature_extractor=processor.feature_extractor,<br>    max_new_tokens=128,<br>    torch_dtype=torch_dtype,<br>    device=device,<br>)<br><br>sample_audio =  &quot;Sample Board Recording.wav&quot;<br>pipe(sample_audio)</pre><p>Here is how the sample output looks like based on a simple audio recording. Even based on the distil-small model, the transcription looks to be very accurate as least for the simple sample.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/959/1*8en8xhAbZ7k5paA9OhIu6Q.png" /></figure><h3>Conclusion and next steps</h3><p>In this short article, we explored one of the simple ways to implement a meeting transcription solution through the use of pre-trained Whisper family of models available on Hugging Face. Further enhancements could include capabilities of speaker identification and fine-tuning in a more localized settings if the pre-trained models are not able to pick up on the local slangs.</p><p>There is also a good course on Hugging Face that delves into applications of transformers on audio data. <a href="https://huggingface.co/learn/audio-course/chapter0/introduction">https://huggingface.co/learn/audio-course/chapter0/introduction</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=85957c936900" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Quick start guide to build a Collaborative Filtering Recommendation System with implicit library in…]]></title>
            <link>https://medium.com/@zaishanweng/quick-start-guide-to-build-a-collaborative-filtering-recommendation-system-with-implicit-library-in-c5e79e35dfb8?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/c5e79e35dfb8</guid>
            <category><![CDATA[towards-data-science]]></category>
            <category><![CDATA[recommendation-system]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[recommender-systems]]></category>
            <category><![CDATA[collaborative-filtering]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Thu, 09 Feb 2023 13:25:45 GMT</pubDate>
            <atom:updated>2023-02-09T13:25:45.239Z</atom:updated>
            <content:encoded><![CDATA[<h3>Quick start guide to build a Collaborative Filtering Recommendation System with implicit library in 4 steps</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*ZNSS3ID2DEt0uuC5.png" /><figcaption>Image generated from Stability AI Stable Diffusion with prompt “Recommender System”</figcaption></figure><h3>Introduction</h3><p>While there are various guides, articles and lessons available on building a recommendation system, the implicit library package is discussed less often. In the course of my work, I have found the Implicit library package easy to install, straight forward to use and has fast processing. In this article, I would like to walk through the basic steps to implement a recommendation system with the Implicit package library.</p><h3>Set up</h3><p>The implicit library can be installed with the following code. More details can also be found from the PyPI page: <a href="https://pypi.org/project/implicit/">https://pypi.org/project/implicit/</a></p><pre>pip install implicit</pre><p>Dataset can be found from the following link: <a href="https://archive.ics.uci.edu/ml/datasets/online+retail">https://archive.ics.uci.edu/ml/datasets/online+retail</a></p><p>Notebook and data can also be found at the following github link:</p><p><a href="https://github.com/ZS-Weng/Machine_Learning/tree/main/RecommenderSystem">https://github.com/ZS-Weng/Machine_Learning/tree/main/RecommenderSystem</a></p><h3><strong>Step 1: Data Preprocessing</strong></h3><p>The key data preprocessing step which was found to be useful in building recommender systems is to filter out the outliers e.g. customers who only bought one item, items which are only bought by one customer as it will skew the model and subsequent result.</p><p>For our example, we will first identify transactions belonging customers who bought less than the threshold number of items.</p><pre>df_items_per_cust = (<br>    df_raw.groupby([&quot;CustomerID&quot;]).agg({&quot;StockCode&quot;: &quot;nunique&quot;}).reset_index()<br>)<br>df_items_per_cust.columns = [&quot;CustomerID&quot;, &quot;Count_item_cust&quot;]<br># Setting of Threshold<br>item_in_cust_threshold = 6<br># Filtering Results<br>mask = df_items_per_cust[&quot;Count_item_cust&quot;] &gt;= item_in_cust_threshold<br>valid_cust = set(df_items_per_cust.loc[mask, &quot;CustomerID&quot;].tolist())<br>df_filter_cust = df_raw[df_raw[&quot;CustomerID&quot;].isin(valid_cust)].copy()<br>invoiceno_filter_cust = set(df_filter_cust[&quot;InvoiceNo&quot;].tolist())</pre><p>Next , we will first identify transactions containing items which are bought by more than the threshold number of customers. Thereafter, transactions which meet both conditions will be used as the filtered dataset to perform subsequent operations.</p><pre>df_custs_per_item = (<br>    df_raw.groupby([&quot;StockCode&quot;]).agg({&quot;CustomerID&quot;: &quot;nunique&quot;}).reset_index()<br>)<br>df_custs_per_item.columns = [&quot;StockCode&quot;, &quot;Count_cust_item&quot;]<br>df_custs_per_item[&quot;Count_cust_item&quot;].value_counts()<br># Set threshold<br>cust_in_item_threshold = 6<br>#Filter Results<br>mask = df_custs_per_item[&quot;Count_cust_item&quot;] &gt;= cust_in_item_threshold<br>valid_stockcode = set(df_custs_per_item.loc[mask, &quot;StockCode&quot;].tolist())<br>df_filter_item = df_raw[df_raw[&quot;StockCode&quot;].isin(valid_stockcode)].copy()<br>invoiceno_filter_item = set(df_filter_item[&quot;InvoiceNo&quot;].tolist())<br>invoiceno_intersect = set.intersection(invoiceno_filter_item, invoiceno_filter_cust)<br>df_filter_cust_item = df_raw[df_raw[&quot;InvoiceNo&quot;].isin(invoiceno_intersect)].copy()</pre><h3>Step 2: Data Preparation to Sparse Matrix Format</h3><p>The implicit library requires data to be in a scipy sparse matrix format. The processed data from step 1 is used as the starting point. We first convert the customer and item IDs into a into a unique id format. Next we group the data by each customer — item pair with a corresponding metrics to be used. In our example, the metrics used is the total quantity purchased. In the final step, we convert the data frame into a scipy csr matrix with the customer, item dimensions and selected met</p><pre><br>#Generating Customer and Item IDs<br>unique_customers = df_filter_cust_item[&quot;CustomerID&quot;].unique()<br>cust_ids = dict(<br>    zip(unique_customers, np.arange(unique_customers.shape[0], dtype=np.int32))<br>)<br><br>unique_items = df_filter_cust_item[&quot;StockCode&quot;].unique()<br>item_ids = dict(zip(unique_items, np.arange(unique_items.shape[0], dtype=np.int32)))<br><br>df_filter_cust_item[&quot;cust_id&quot;] = df_filter_cust_item[&quot;CustomerID&quot;].apply(<br>    lambda i: cust_ids[i]<br>)<br>df_filter_cust_item[&quot;item_id&quot;] = df_filter_cust_item[&quot;StockCode&quot;].apply(<br>    lambda i: item_ids[i]<br>)<br>#Use Groupby to get customer in rows, item_id in columns and quantity as the value<br>df_cust_item_qty = (<br>    df_filter_cust_item.groupby([&quot;cust_id&quot;, &quot;item_id&quot;])<br>    .agg({&quot;Quantity&quot;: &quot;sum&quot;})<br>    .reset_index()<br>)<br># Create Sparse Matrix<br>sparse_customer_item = sparse.csr_matrix(<br>    (<br>        df_cust_item_qty[&quot;Quantity&quot;].astype(float),<br>        (df_cust_item_qty[&quot;cust_id&quot;], df_cust_item_qty[&quot;item_id&quot;]),<br>    )<br>)</pre><h3><strong>Step 3: Train Model</strong></h3><p>There are various algorithms which can be used in the implicit library. For our example we use the Alternating Least Square method and fit the model to the scipy csr matrix created in step 2.</p><pre>import implicit<br>model = implicit.als.AlternatingLeastSquares(num_threads=1)<br>model.fit(sparse_customer_item)</pre><h3>Step 4: Generate Results</h3><p>For a collaborative filtering recommender system, there are two types of results which can be generated from the model:</p><ul><li>Finding Similar Items</li><li>Recommending Items for a User</li></ul><h4>Finding Similar Items</h4><p>Given a particular item, a list of closest items can be generated and the number can be adjusted with the N parameter. In the code below, we pass the entire list of unique items as an array for the similar items to be generated.</p><pre>ref_item_id = df_filter_cust_item[&quot;item_id&quot;].unique()<br>item_arr, score_arr = model.similar_items(ref_item_id, N=10)<br></pre><p>From the array of items and scores, we convert them into Data Frames and unpivot the values using pd.melt method.</p><pre>df_item_temp = pd.DataFrame(item_arr)<br>df_item_temp[&quot;Ref Item ID&quot;] = ref_item_id<br>df_item_rank = pd.melt(<br>    df_item_temp,<br>    id_vars=[&quot;Ref Item ID&quot;],<br>    var_name=[&quot;Item Rank&quot;],<br>    value_name=&quot;Related Item ID&quot;,<br>)<br>df_score_temp = pd.DataFrame(score_arr)<br>df_score_temp[&quot;Ref Item ID&quot;] = ref_item_id<br>df_score_rank = pd.melt(<br>    df_score_temp, id_vars=[&quot;Ref Item ID&quot;], var_name=[&quot;Item Rank&quot;], value_name=&quot;Score&quot;<br>)<br>df_item_score = df_item_rank.merge(<br>    df_score_rank, how=&quot;inner&quot;, on=[&quot;Ref Item ID&quot;, &quot;Item Rank&quot;]<br>)</pre><p>The resulting details will be a list of N items for every reference item that was passed in. The item in rank 0 will always be the reference item itself by default.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*A6FQIBP0UZZDcwPcLrKLug.png" /><figcaption>First 20 records of the Item Similarity Result</figcaption></figure><h4>Recommending Items for a User</h4><p>Given a user and the user preferences based on items purchased, the model can generate a list of recommending items bought by customers with similar preferences. Below is the code used to generate the items and convert them into a data frame.</p><pre>ids, scores = model.recommend(<br>    cust_id, sparse_customer_item[cust_id], N=20, filter_already_liked_items=False<br>)<br>list_stockcode = df_item_desc[df_item_desc[&quot;item_id&quot;].isin(ids)][&quot;StockCode&quot;].tolist()<br>list_desc = df_item_desc[df_item_desc[&quot;item_id&quot;].isin(ids)][&quot;Description&quot;].tolist()<br>df_recommendations = pd.DataFrame(<br>    {<br>        &quot;Stockcode&quot;: list_stockcode,<br>        &quot;Description&quot;: list_desc,<br>        &quot;score&quot;: scores,<br>        &quot;already_liked&quot;: np.in1d(ids, sparse_customer_item[cust_id].indices),<br>    }<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/533/1*WVi1iG0Xvi75ds67P4_gqg.png" /><figcaption>Item Recommendations for a given user</figcaption></figure><h3>Conclusion</h3><p>This article aims to provide a quick start guide covering the essential steps in building an initial recommender system with collaborative filtering. The implicit library package is what I personally found to be easy to install, user friendly and has robust performance.</p><p>Thank you for reading and hope this was useful in some way !</p><h3>References</h3><ul><li><a href="https://medium.com/@actsusanli/building-a-recommender-system-with-implicit-feedback-datasets-using-alternating-least-squares-64d4f5ba3c57">https://medium.com/@actsusanli/building-a-recommender-system-with-implicit-feedback-datasets-using-alternating-least-squares-64d4f5ba3c57</a></li><li><a href="https://github.com/benfred/implicit/">https://github.com/benfred/implicit/</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c5e79e35dfb8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Hierarchical Clustering can be more suitable compared to KMeans when grouping customers based on…]]></title>
            <link>https://medium.com/@zaishanweng/hierarchical-clustering-can-be-more-suitable-compared-to-kmeans-when-grouping-customers-based-on-14f10595b6b?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/14f10595b6b</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[clustering]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Fri, 03 Feb 2023 03:04:18 GMT</pubDate>
            <atom:updated>2023-02-03T14:12:37.273Z</atom:updated>
            <content:encoded><![CDATA[<h3>Hierarchical Clustering can be more suitable compared to KMeans when grouping customers based on purchase behaviors and detecting outliers</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x53QpDlQiiqfZfr3Xy733Q.png" /><figcaption>Image generatedd from DALL E with prompt “artistic clusters and outliers”</figcaption></figure><h3>Introduction</h3><p>Clustering can be a particularly useful starting point to embark on a Machine Learning journey especially when data labels are yet to be built up. One of the easy way to start is to employ clustering based on the purchase behaviors of customers or departments. This can be done with with any datasets which contains sales data based on product categories. Based on the clustering results, customers can be grouped into meaningful clusters for further analysis. In addition, outliers can be detected for clusters with few customers within.</p><p>KMeans and Hierarchical Clustering are two of the most common and popular techniques used today. In this article, I would like to examine in more details on the end to end process from data preprocessing to post processing of clustering results and highlight some of the differences and usefulness of the two techniques based on my experiences deploying them on projects.</p><h3>Links to code and data</h3><ul><li>The sample dataset used for illustration can be downloaded from Kaggle <a href="https://www.kaggle.com/datasets/vivek468/superstore-dataset-final">https://www.kaggle.com/datasets/vivek468/superstore-dataset-final</a></li><li>The full codes in notebook and the dataset can be found from the following GitHub link: <a href="https://github.com/ZS-Weng/Machine_Learning/tree/main/Clustering">https://github.com/ZS-Weng/Machine_Learning/tree/main/Clustering</a></li></ul><h3>Standard Data Preprocessing</h3><p>The dataset contains 9994 rows and 21 columns. The overview of the dataset is as shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/383/1*9OOc0seli1JnZL4sHHT-UQ.png" /><figcaption>Overview of the Store Dataset</figcaption></figure><p>To perform the initial clustering analysis, we will only be making use of the Customer, Sub-Category and Sales Categories. We will first transform the data by performing a pandas pivot_table operation so that we can get the amount spent by each customers on the sub categories.</p><pre>df_key_fields = df_raw[[&quot;Customer ID&quot;, &quot;Sub-Category&quot;, &quot;Sales&quot;]].copy()<br>df_cust_sales = df_key_fields.pivot_table(<br>    values=&quot;Sales&quot;, columns=&quot;Sub-Category&quot;, aggfunc=np.sum, index=&quot;Customer ID&quot;<br>).reset_index()</pre><p>After the pivot_table operation, a sample of the output is as shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1010/1*L3mScqd7mtWtjkrbOfpyCg.png" /><figcaption>Sample of data showing the total spend on each sub category based on each customer</figcaption></figure><p>There are many instances where there might be some customers who spend and unproportionate larger amount compared to most of the other customers, causing the results to skew. To prevent that, we will normally apply a log transformation to the sales amount to stabilize the variance of the variable and make the relationship between the variable and the target variable more linear.</p><pre>df_cust_sales.iloc[:, 1:] = df_cust_sales.iloc[:, 1:].applymap(lambda x: np.log(x + 1))</pre><h3>PCA (Principal Component Analysis) preprocessing before KMeans Clustering</h3><p>It is usually best practice to perform PCA (Principal Component Analysis) before KMeans clustering as PCA could help to reduce dimensions and noise and remove correlated features to help the algorithm.</p><p>For PCA, the number of components will need to be defined and we usually would choose the number of components based on a a percentage of explained variance e.g. 90%.</p><p>For visualization, we can run pca based on the range of n_components and we can visualize how the amount of variability in the dataset is increasingly covered with larger n_components set.</p><pre>pca = PCA()<br>df_pca = pca.fit_transform(df_for_pca)<br>exp_var = np.cumsum(pca.explained_variance_ratio_)<br>plt.bar(range(1, df_pca.shape[1] + 1), pca.explained_variance_ratio_, align=&quot;center&quot;)<br>plt.step(<br>    range(1, df_pca.shape[1] + 1), np.cumsum(pca.explained_variance_ratio_), where=&quot;mid&quot;<br>)<br>plt.ylabel(&quot;Explained variance ratio&quot;)<br>plt.xlabel(&quot;Principal Components&quot;)<br>plt.show()<br></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/423/1*tsfpbQfsgM134ZHuZey0qA.png" /><figcaption>Plot on the amount of variability covered against number of components selected for PCA</figcaption></figure><p>To directly get the number of components based on the ratio threshold, we can apply a mask to mask values smaller than the threshold and find the location of the smallest value using argmin.</p><pre>exp_var = np.ma.MaskedArray(exp_var, exp_var &lt; 0.9)<br>n_components = np.argmin(exp_var)</pre><h3>KMeans Clustering</h3><p>The first method to explore would be KMeans clustering where the number of clusters need to be predetermined. The common way to find the optimal number of clusters would be to iterate through multiple clusters and find the optimal number of clusters where the distortion decreases the least with an increase in K.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/719/1*TNjgTw33hEScFh2VZzMmXw.png" /><figcaption>Image of selection of the optimal number of clusters K using the Elbow method</figcaption></figure><p>In many of the real life datasets, the plot of distortion against number of clusters, K might not be so visually straightforward. Below is a sample code to generate the plot and the results based on the superstore dataset.</p><pre>distortions = []<br>for i in range(1, 30):<br>    km = KMeans(n_clusters=i, init=&quot;k-means++&quot;, max_iter=300, random_state=0)<br>    km.fit(pca_arr)<br>    distortions.append(km.inertia_)<br><br>print(len(distortions))<br>plt.plot(range(1, len(distortions) + 1), distortions, marker=&quot;o&quot;)<br>plt.xlabel(&quot;Number of Clusters&quot;)<br>plt.ylabel(&quot;Distortion&quot;)<br>plt.tight_layout()<br>plt.show()</pre><p>From the graph, we can see that there is not a very straightforward point seen as the elbow point.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*E23MGSjnJDBn6p_3GU-dpw.png" /></figure><p>Another way to find the optimal number of clusters, k for KMeans is calculating the average silhouette score for different number of k which can be achieved based on the code below.</p><p>More details on sihouette score and plots can be found from the scikit learn code example page: <a href="https://scikit-learn.org/stable/auto_examples/cluster/plot_kmeans_silhouette_analysis.html">https://scikit-learn.org/stable/auto_examples/cluster/plot_kmeans_silhouette_analysis.html</a></p><pre>from sklearn.metrics import silhouette_score<br><br>list_i = []<br>list_score = []<br>for i in range(2, n_clusters):<br>    # Initialize the clusterer with n_clusters value and a random generator<br>    # seed of 10 for reproducibility.<br>    km = KMeans(n_clusters=i, init=&quot;k-means++&quot;, max_iter=500, random_state=0)<br>    cluster_labels = km.fit_predict(pca_arr)<br><br>    # The silhouette_score gives the average value for all the samples.<br>    # This gives a perspective into the density and separation of the formed<br>    # clusters<br>    silhouette_avg = silhouette_score(pca_arr, cluster_labels)<br>    list_i.append(i)<br>    list_score.append(silhouette_avg)</pre><p>From the graph, generated we can see the the optimal number of clusters with the highest score will be 2 and 4. In many cases, such small number of clusters might not provide useful and meaningful grouping and larger cluster values might also not be so straightforward to be selected.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/923/1*EZ_wFid_elJnCFt_w7daHw.png" /><figcaption>Plot of Avg Silhouette Score against number of clusters</figcaption></figure><h3>Hierarchical Clustering</h3><p>There are instances where we found Hierarchical clustering to be more effective. Below are some of the hierarchical clustering packages in Scikit Learn.</p><pre>from scipy.cluster.hierarchy import linkage<br>from scipy.cluster.hierarchy import dendrogram<br>from sklearn.cluster import AgglomerativeClustering</pre><p>Hierarchical clustering is akin more to a bottom up approach where records are clustered together based on the distance away from each other. Eventually, all customers will be merged into a single cluster. We can see the details of how the cluster forms with each merge by using linkage.</p><pre>row_clusters = linkage(df_for_pca.values, method=&quot;ward&quot;, metric=&quot;euclidean&quot;)<br>df_hc = pd.DataFrame(<br>    row_clusters,<br>    columns=[&quot;row label 1&quot;, &quot;row label 2&quot;, &quot;distance&quot;, &quot;no. of items in clust.&quot;],<br>    index=[f&quot;Merge {(i + 1)}&quot; for i in range(row_clusters.shape[0])],<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/432/1*UhAQySeKWQZ9cHvsUKpFSA.png" /><figcaption>Table showing cluster components based on merges</figcaption></figure><p>From there we can determine the number of merges e.g. 85% on the data and find the corresponding distance metrics which we can use to apply clustering. From experience, a lower distance threshold can be better used to find outliers while a higer threshold can help to achieve larger clusters for analysis or further actions.</p><pre># Finding the merge based on 85% of merges<br>merge_threshold = 0.85<br>row = int(merge_threshold * df_hc.shape[0])<br>distance_threshold = df_hc.iloc[row, 2]<br>print(row, distance_threshold)</pre><p>From the distance threshold obtained, we can visualize how customers are grouped together using dendrogram.</p><pre>labels = df_cust_sales.iloc[:, 0].values<br>plt.figure(figsize=(300, 12))<br>row_dendr = dendrogram(row_clusters, labels=labels, color_threshold=distance_threshold)<br>plt.ylabel(&quot;Euclidean distance&quot;)<br>plt.xlabel(&quot;Customer ID&quot;)<br>plt.show()</pre><p>In the dendrogram generated below, different colors represent different clusters and the x-axis labels are the customer ids from the dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/916/1*pnvRqeDi9lcvGb6oD5RiOw.png" /><figcaption>Grouping of Customers</figcaption></figure><p>Once the result looks satisfactory, the clusters can be generated using AgglomerativeClustering.</p><pre># Use Agglomerative Clustering based on Threshold<br>from sklearn.cluster import AgglomerativeClustering<br><br>ac = AgglomerativeClustering(<br>    n_clusters=None,<br>    distance_threshold=distance_threshold,<br>    affinity=&quot;euclidean&quot;,<br>    linkage=&quot;ward&quot;,<br>)<br><br>hc_cluster = ac.fit_predict(df_for_pca.values)</pre><h3>Data Post Processing and Application of Clustering Information</h3><p>When the cluster details are generated, the cluster number is usually random in nature and it might take a lot of effort to provide a meaningful description for each cluster. Some post processing of the data which are found to be helpful include extracting details based on the attributes of the clusters (sub category in this example), sort the clusters based on a metric like total sales or number of customers and assign a cluster/group number based on the rank to provide a more meaningful cluster/group number. The aggregation for a cluster can be easily achieved with groupby.</p><pre>df_cluster_cat_count = (<br>    df_cluster_original_amount.groupby([&quot;Cluster&quot;, &quot;Sub-Category&quot;])<br>    .agg({&quot;Sales&quot;: &quot;sum&quot;, &quot;Customer ID&quot;: &quot;nunique&quot;})<br>    .reset_index()<br>)<br>df_cluster_cat_count.columns = [<br>    &quot;Cluster&quot;,<br>    &quot;Sub-Category&quot;,<br>    &quot;Sales&quot;,<br>    &quot;Count of Customers in Cluster SubCat&quot;,<br>]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/632/1*LScDAa0PctDllOwN7oJIGQ.png" /><figcaption>Additional Information to enhance Cluster Information</figcaption></figure><h3>Conclusion</h3><p>From my experience, clustering can be a very useful in many situations especially as an unsupervised learning ML method when there is a lack of data labels for supervised learning. For transactional data, one of the first steps which can be taken to apply clustering is to group customers by their purchasing behavior on product categories.</p><p>Hierarchical Clustering can be a useful and flexible method to cluster the customers to find both outliers and generate groups of customers for follow up actions. There can also be standardization based on the percentage of merge to form the clusters.</p><p>The cluster id assigned from clustering algorithms will change between each runs and does not provide useful information. Data Post Processing through aggregation of relevant metrics and description can be useful to add more useful information to describe the clusters.</p><p>Thank you for reading and hope the information was useful in some way!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=14f10595b6b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Applications of Regex and Python in data transformation for masking of sensitive information and…]]></title>
            <link>https://medium.com/@zaishanweng/applications-of-regex-and-python-in-data-transformation-for-masking-of-sensitive-information-and-1598b1f15cee?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/1598b1f15cee</guid>
            <category><![CDATA[regex]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[towards-data-science]]></category>
            <category><![CDATA[data-masking]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Tue, 18 Oct 2022 13:05:25 GMT</pubDate>
            <atom:updated>2023-02-08T03:28:32.377Z</atom:updated>
            <content:encoded><![CDATA[<h3>Applications of Regex and Python in data transformation for masking of sensitive information and extraction of date details from free text</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*idpPeyOKwEkj6qVn.png" /><figcaption>Image generated from Stability AI Stable Diffusion with prompt “colourful artistic patterns made up of text”</figcaption></figure><h3>Introduction</h3><p>There are many useful applications of Regex. In this article, I would like to cover two of them commonly used for my projects in Singapore. They are</p><ul><li>Masking of sensitive Singapore National ID information</li><li>Extraction of relevant date details from a text field</li></ul><p>The full set of codes in a Jupyter Notebook format can be found in the following link:</p><p><a href="https://github.com/ZS-Weng/Data_Engineering/tree/main/Regex">Data_Engineering/Regex at main · ZS-Weng/Data_Engineering</a></p><p>The full code for the various functions can also be found at the end of the article.</p><h4>1. Masking of Singapore National ID information</h4><p>In Singapore, the National ID starts with one of the alphabets, S,T,F or G followed by 7 digits and ends with another alphabet e.g. S1234567A. With a simple Regex function, we can detect the occurrence of text matching the pattern and mask the information automatically to a format such as SXXXX567A.</p><pre>import re<br>def mask_nric(text):</pre><pre>    nric_regex = re.compile(&#39;([STFG])\d{4}(\d{3}[A-Z])&#39;)</pre><pre>    return nric_regex.sub(r&#39;\1XXXX\2&#39;, text)</pre><p>Below is the example of the original text and processed text where the sensitive information have been masked:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/805/1*dI7ENqomtArQyRuWtDghjw.png" /><figcaption>Masking of National ID details within Text</figcaption></figure><h4>2. Extraction of date details from Service Text Field</h4><p>There are several formats which can represent date and time and I will be highlighting the common scenarios for date representation. The code can be customized to the different date formats accordingly.</p><h4>2a. Extraction of date details where month is represented by abbreviated or long month name</h4><p>In this section, the date pattern to be extracted is where the month is an abbreviated or full month name e.g. 22 Dec 2021, 18 October 22 etc. The Regex Pattern for matching the date and output with some additional processing are shown below.</p><pre>string_date_pattern = re.compile(r&#39;&#39;&#39;(\d{1,2})?[\s-]?((?:(?:Jan(?:uary)?|Feb(?:ruary)?|Mar(?:ch)?|Apr(?:il)?|May|Jun(?:e)?|Jul(?:y)?|Aug(?:ust)?|Sep(?:tember)?|Oct(?:ober)?|Nov(?:ember)?|Dec(?:ember)?)))[\w]?[&#39;\s-]?[&#39;\s-]?(20\d{2}|\d{2})(?:\s|[A-Za-z]|[\(\)\.\]]|[-_/]|$)&#39;&#39;&#39;, re.VERBOSE | re.IGNORECASE)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/744/1*j8rJQ1Ra0UpVu5NZJe1WOg.png" /><figcaption>Output for Date Pattern Extraction based on Abbreviated and Long Month Names</figcaption></figure><h4>2b. Extraction of date details where the date is represented in numeric format</h4><p>Dates are also commonly represented entire in numbers and there are various formats where the date can start with the year, month or day e.g. YYYY-MM-DD, MM.DD.YY, DD/MM/YY etc. For the example, I am using a day-first date pattern and the Regex code and sample output are as below.</p><pre>day_first_pattern = re.compile(r&#39;&#39;&#39;([0-3]?[0-9])[./]([01]?[0-9])[./](20\d{2}|\d{2})(?:\s|[A-Za-z]|[\(\)\.\]]|[-_]|$)&#39;&#39;&#39;, re.VERBOSE | re.IGNORECASE)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/679/1*0nimocxuiHlseaBIbsMkrA.png" /><figcaption>Output for Date Pattern Extraction based on Day First Numeric Date Pattern</figcaption></figure><h4>2c. Extraction of date details where month is represented by short and long spelling</h4><p>There are other instances where date are represented in a more abstract level, in this example by Quarter and Year. In this case, the first date of the quarter is used to represent the date extracted from the Quarter Year format. The Regex code and sample output are as shown below.</p><pre>q_year_pattern = re.compile(r&#39;&#39;&#39;Q([1-4])[\s-]?(20\d{2}|\d{2})&#39;&#39;&#39;, re.VERBOSE | re.IGNORECASE)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/928/1*7B9vFLlg4p9MjKl7gBZQbA.png" /><figcaption>Output for Date Pattern Extraction based on Quarter Year Pattern</figcaption></figure><h3>Full Codes including Regex Pattern and Functions</h3><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5e9391c7b1e63f012e581710acc6ea2c/href">https://medium.com/media/5e9391c7b1e63f012e581710acc6ea2c/href</a></iframe><h3>Recommended Resource to learn more about Regex</h3><p>Out of the different resources I have used to learn about Regex, I found the materials from Al Sweigart most engaging. You can find the free chapter covering Regex from his book here: <a href="https://automatetheboringstuff.com/2e/chapter7/">https://automatetheboringstuff.com/2e/chapter7/</a></p><p>Thanks for reading and hope the information was useful in some way!</p><p>Zaishan is the Data Analytics and AI Lead at IBM focusing on leading digital transformation, data analytics and business process re-engineering initiatives across multiple industries.</p><p>He is passionate about leveraging technology to make a difference, simplify the complex and optimize the mundane with the hope that we can have more time and energy to pursue our interests and dreams.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1598b1f15cee" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Impressive yet easy to implement Document Understanding system with OCR-free Donut Transformers…]]></title>
            <link>https://medium.com/@zaishanweng/impressive-yet-easy-to-implement-document-understanding-system-with-ocr-free-donut-transformers-c95b37323ab9?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/c95b37323ab9</guid>
            <category><![CDATA[transformers]]></category>
            <category><![CDATA[donutvqa]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[document-understanding]]></category>
            <category><![CDATA[towards-data-science]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Thu, 06 Oct 2022 14:26:57 GMT</pubDate>
            <atom:updated>2022-10-09T03:18:58.696Z</atom:updated>
            <content:encoded><![CDATA[<h3>Impressive yet easy to implement Document Understanding system with OCR-free Donut Transformers Model in Python</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*-x6Ts-a9Dh_asf_v2Dr7xg.jpeg" /><figcaption>Image Generated from Stable Diffusion with the prompt “A colorful donut speaking to a document”</figcaption></figure><h3>INTRODUCTION</h3><p>Recently, with the release of the stable version of transformers v4.22 in September 2022, there were some new interesting models which were released. The Donut Transformers model, an OCR-Free Visual Document Understanding (VDU), was particularly interesting as I was looking for a image to text data extraction model to deploy in one of the current projects. More details about the model can be found here: <a href="https://arxiv.org/abs/2111.15664">OCR-free Document Understanding Transformer</a> by Geewook Kim, Teakgyu Hong, Moonbin Yim, Jeongyeon Nam, Jinyoung Park, Jinyeong Yim, Wonseok Hwang, Sangdoo Yun, Dongyoon Han, Seunghyun Park.</p><p>Overall, I found the version fine-tuned on DocVQA (visual question answering on documents) to be the most versatile and easy to implement and use.</p><ul><li>Text extracted is highly accurate based on the pretrained model and it can even recognize hand written digits</li><li>The same data can be extracted from images of different file formats and structure as long as the identifying segment e.g. total, balance etc. is present</li><li>Data to be extracted from images can be customized based on the questions being fed to the model</li><li>It is a very magical and amazing experience to interact with scanned copies of receipts, invoices with questions =)</li></ul><h3>OPTIMIZING ON A TYPICAL DOCUMENT UNDERSTANDING PIPELINE</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*YXEJHKOahJ3Y-FdD.png" /><figcaption>A typical Document Understanding Process as illustrated by UiPath (<a href="https://www.uipath.com/blog/product-and-updates/introducing-document-understanding-process-documents-intelligently">https://www.uipath.com/blog/product-and-updates/introducing-document-understanding-process-documents-intelligently</a>)</figcaption></figure><p>The typical document understanding work flow will be very similar as the process flow illustrated above where the first step would using OCR (Optical Character Recognition) library or software to extract the raw text from the document. Common popular libraries for implementing OCR include pytesseract and tesserocr. Based on the raw text extracted, additional Machine Learning Language Modeling or Regex Text Extraction will need to be employed to retrieve useful information from the raw text.</p><p>However, with the Donut DocVQA model, this end to end process can be greatly simplified and optimized.</p><h3>INSTALLATION</h3><p>There are two main libraries required to implement the Donut VQA model:</p><ul><li>PyTorch (<a href="https://pytorch.org/TensorRT/tutorials/installation.html">https://pytorch.org/TensorRT/tutorials/installation.html</a>)</li><li>Transformers and Sentence Piece</li></ul><pre><strong>pip install transformers</strong>[<strong>sentencepiece</strong>]</pre><h3>SAMPLE NOTEBOOK</h3><p>The Donut related documentation on Hugging Face (<a href="https://huggingface.co/docs/transformers/main/en/model_doc/donut">https://huggingface.co/docs/transformers/main/en/model_doc/donut</a>) and the tutorial by <strong><em>Niels Rogge</em></strong> (<a href="https://github.com/NielsRogge/Transformers-Tutorials/tree/master/Donut/DocVQA">https://github.com/NielsRogge/Transformers-Tutorials/tree/master/Donut/DocVQA</a>) provided the complete starter code for using the transformers model.</p><p>Referencing from the materials, I adapted the code to apply the transformer model to work with multiple images within a specified folder. A sample of my code can be found at:</p><p><a href="https://github.com/ZS-Weng/Machine_Learning/tree/main/DonutDocVQA">https://github.com/ZS-Weng/Machine_Learning/DonutDocVQA</a></p><p>The core of the code function is as below:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7ad99068a367b7398dc62dffb1daa0ea/href">https://medium.com/media/7ad99068a367b7398dc62dffb1daa0ea/href</a></iframe><h3><strong>RESULTS</strong></h3><h4>1. Testing on a general receipt</h4><p>DonutDocVQA was able to pick up both the title and the total amount on a general receipt.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/1*Zt8Ug-J4oFowcrpSEZ8Zwg.png" /><figcaption>DonutDocVQA information extraction on a generic receipt</figcaption></figure><h4>2. Testing on a receipt with hand written details</h4><p>DonutDocVQA was able to perform well to detect even information based on hand written digits.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/657/1*rzp2k-NPJB0EHumnC7Hipg.png" /><figcaption>DonutDocVQA information extraction on a receipt with hand written information</figcaption></figure><h4>3. Testing on a F1 Poster</h4><p>Even though the digits were not that clear on a red background and the price of the categories “Pit Combination” is not right beside the category text, DonutDocVQA was able to pick up the correct prices.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dIkXLaZnsgwgBbX6IgeBow.png" /><figcaption>DonutDocVQA information extraction on Singapore F1 2022 poster</figcaption></figure><h4>4. Extracting Similar Information from Multiple Documents</h4><p>For multiple documents with different formats as show below where there is a mix of different receipts, invoices, etc. a loop can be setup to extract similar information and consolidated into a tabular data structure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/714/1*W9UepBV9Qz1OeorH8VCAEA.png" /><figcaption>Input documents fed into DocVQA</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/728/1*3WeknnIYMNMSs-MRP4y-wg.png" /><figcaption>Output results from the documents above</figcaption></figure><h3>Conclusion</h3><p>The Donut Transformers model is a versatile and impressive tool for document understanding which can be used out of the box leveraging on the pre-trained weights. I feel that this could be a game changer which can potentially save a significant amount of time and resources for a variety of document understanding tasks expediting the end to end development process. Lastly, the intuitive way of using questions to query images would enhance user experience and drive more collaborations and interesting ideas in applying technology in novel ways.</p><p>Thanks for reading and hope the information was useful in some way!</p><p>Zaishan is the Data Analytics and AI Lead at IBM focusing on leading digital transformation, data analytics and business process re-engineering initiatives across multiple industries.</p><p>He is passionate about leveraging technology to make a difference, simplify the complex and optimize the mundane with the hope that we can have more time and energy to pursue our interests and dreams.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c95b37323ab9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Handle various input data source formats (csv, xlsx, xlsb, txt, parquet) with Python and Pandas]]></title>
            <link>https://medium.com/@zaishanweng/collection-of-useful-solutions-to-handle-csv-and-excel-files-in-python-2d7994a7a5f1?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/2d7994a7a5f1</guid>
            <category><![CDATA[python3]]></category>
            <category><![CDATA[csv]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Fri, 12 Aug 2022 09:02:30 GMT</pubDate>
            <atom:updated>2023-02-08T05:44:15.776Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/768/1*8quVe1ufAlnsXUn1t-lkPg.jpeg" /><figcaption>Image generated from Hugging Face Stable Diffusion with the prompt “multitude of sources coming together in a mural”</figcaption></figure><h3>Introduction</h3><p>In the process of ingesting and cleaning raw data in various formats using python, there were some tricks found to be useful based on the issues which were encountered. Types of source files covered in the article so far are as follows:</p><ul><li>xlsb (excel binary format)</li><li>csv files where encoding are not UTF-8</li><li>excel files with multiple tabs</li><li>raw data files in .txt format</li><li>parquet files</li></ul><p>The corresponding notebook and sample data can be found at <a href="https://github.com/ZS-Weng/Data_Engineering/tree/main/Data_Cleaning">https://github.com/ZS-Weng/Data_Engineering/tree/main/Data_Cleaning</a></p><h4>1. Reading in excel files in .xlsb format</h4><p>There are various formats of excel files e.g. .xls, .xlsx, .xlsb, .xlsm etc. Based on the out of the box functionality from Pandas, the formats .xlsx and .xls are supported and the .xlsb (binary) format is not supported out of the box.</p><p>The most efficient solution I found is to install the pyxlsb extension. More details can be found at the PyPI project website:</p><p><a href="https://pypi.org/project/pyxlsb/">pyxlsb</a></p><p>The code to execute including installation of the library will be as follows:</p><pre>pip install pyxlsb<br>df = pd.read_excel(file, engine=”pyxlsb”)</pre><h4>2. Resolve UnicodeDecodeError during CSV file read</h4><p>Most of the files are encoded in “UTF-8” which is the default encoding. However, there are times when when the encoding in not “UTF-8” and there will be an error message similar to this:</p><blockquote>UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0xff in position 0: invalid start byte</blockquote><p>The most effective method I have encountered is to first read the file as binary using ‘rb’ mode to determine the encoding, then apply the encoding which was detected.</p><pre>import chardet<br>with open(file, &#39;rb&#39;) as f:<br>    encoding = chardet.detect(f.read())<br>print(encoding)</pre><p>The result will be something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1005/1*PLVAzN_56EahTojNjE3gSg.png" /><figcaption>Detecting encoding in a file</figcaption></figure><p>Based on the encoding identified, we can use the encoding in pd.read_csv()</p><pre>df = pd.read_csv(file, encoding=encoding[&#39;encoding&#39;])</pre><p>The result will look something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MFoMOrAKskwZaGj85b2ikw.png" /><figcaption>Reading the csv file based on updated encoding</figcaption></figure><h4>3. Extracting data from multiple sheets in excel</h4><p>There are times where the data in excel might come from multiple sheets and we need to read in the data from all the sheets. The most straightforward way which I have found is using pd.read_excel with the parameter <em>sheet_name=None</em>. This will read all of the sheets in excel into a dictionary with the sheet name as the key and the data in DataFrames tagged to each sheet name.</p><p>Here we use an example where a sample SAP data dictionary with the relevant data schema. The details for each table are shared in each sheet while each sheet in excel represents the respective tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PVwD-TG7l2HLbZ9y3idcTw.png" /><figcaption>Sample SAP Data Dictionary shared via Excel</figcaption></figure><p>We can read in the data in all the sheets with the parameter sheet_name set to None. Here instead of a single data frame, we will have a dictionary with each sheet name as key and the data frame in the sheet as the corresponding value of the dictionary.</p><pre># Reading in the data<br>sample_SAP_data_dict = &quot;./data/SAP Data Dictionary Selected Tables.xlsx&quot;<br>data_sheet_dict = pd.read_excel(sample_SAP_data_dict, sheet_name=None)<br># Display the names of each sheet in excel<br>list_sheets = list(data_sheet_dict.keys())<br>print(f&quot;There are {len(list_sheets)} sheets in the excel file&quot;)<br>print(f&quot;The sheets are {list_sheets}&quot;)</pre><p>As we print out the keys, we can see the sheet names being listed. In our example, this corresponds with some of the SAP table names.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/820/1*J8Qwa-50dEQ6YV7Phb4cLA.png" /><figcaption>Description of excel file</figcaption></figure><p>Below is an example when the key “BSEG” is applied for the dictionary, we can get the corresponding data frame which was read from the sheet containing the schema of the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/732/1*IhPPC5GALSIPZfH5BvZBtQ.png" /><figcaption>Sample Data Frame for BSEG table</figcaption></figure><p>Subsequent analysis can be run at scale using the normal python dictionary and pandas functions and methods.</p><p>One of the analysis performed for our sample Data Dictionary is to find out which tables houses the similar data fields. We will first use a pivot_table method to create a new table where the column fields are shown as columns for each table. From there we join the tables together using pd.concat.</p><pre>def extract_columns_info(df, sheet_name, list_column_field):<br>    df_temp = df.copy()<br>    df_temp[&quot;SAP_Table&quot;] = sheet_name<br>    df_temp[&quot;Present&quot;] = 1<br>    columns_select = [&quot;SAP_Table&quot;] + list_column_field + [&quot;Present&quot;]<br>    <br>    return df_temp[columns_select].pivot_table(<br>        columns=list_column_field, values=&quot;Present&quot;, index=[&quot;SAP_Table&quot;]<br>    )</pre><pre>list_column_field = [&quot;Field&quot;]<br>list_df = []<br>for sheet_name, df in data_sheet_dict.items():<br>    df_processed = extract_columns_info(df, sheet_name, list_column_field)<br>    list_df.append(df_processed)<br><br>df_consolidated = pd.concat(list_df)<br>df_consolidated.fillna(&quot;&quot;, inplace=True)</pre><p>In the final result, we can we can see the common fields across various SAP tables. Below is a sample of the output.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/660/1*3GchDdwgC1BphXSsyPkhsg.png" /><figcaption>Sample output of final table displaying common fields across different tables</figcaption></figure><h3>4. Reading .txt source files</h3><p>For input data sources in .txt format, we can use the python open function directly to read the files. There will usually be text processing required depending on the delimiters used in the files.</p><p>I wrote a seperate article for the cleaning of SAP .txt input file format. The article can be found here: <a href="https://medium.com/@zaishanweng/data-cleaning-on-sap-data-extracts-with-regex-and-python-cd2afe73be14">https://medium.com/@zaishanweng/data-cleaning-on-sap-data-extracts-with-regex-and-python-cd2afe73be14</a></p><p>The .read() method can be used to read in the entire data source. There are usually some processing involved e.g. all types of new line characters are represented with \n.</p><pre># Converts any type of new line to \n<br>with open(&quot;data/Sample SAP Format.txt&quot;, encoding=&quot;utf-8&quot;) as f:<br>    content_raw = f.read()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*bcm8dzJbDZuHCDEHdqqjGg.png" /><figcaption>Data with only new line character</figcaption></figure><p>There are times where the type of new line characters are important. In this case, we set the parameter newline= “” and we can see below that the original Carriage Return Line Feed (\r\n) in the content.</p><pre># Alternative way to read the file with CRLF (Carriage Return Line Feed intact)<br>with open(&quot;data/Sample SAP Format.txt&quot;, encoding=&quot;utf-8&quot;, newline=&quot;&quot;) as f:<br>    content_raw_2 = f.read()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1001/1*Sz1uUy8_BBAawqXFTwKXxw.png" /><figcaption>Data Maintaining original carriage return linefeed</figcaption></figure><p>Another method common used is .readlines() which will break up each individual lines at the line breaks and provide the data in a list. An example is shown below. From the individual lines, the data can be further split based on established delimiters or other business rules.</p><pre>with open(&quot;data/Sample SAP Format.txt&quot;, encoding=&quot;utf-8&quot;) as f:<br>    content_split_lines = f.readlines()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/483/1*wIH3Say0FOG3K6hg93ObZw.png" /><figcaption>Output list per line</figcaption></figure><h3><strong>5. Writing and reading Parquet Files</strong></h3><p>Parquet is a type of file format for storing and organizing large amounts of data in a efficient and organized manner. The files are in the .parquet extension.</p><p>It is a columnar storage format, meaning that data is stored by columns instead of rows. This allows for faster data retrieval, especially for queries that only need to access a small subset of columns. The format is optimized for use with big data technologies like Apache Hadoop and Apache Spark and is widely used in data warehousing and big data analytics applications.</p><p>For setting up analytics data lakes parquet has the advantages of being smaller in size as it is compressed and being able to retain the data schema types compared to storing the data in csv. However, as the format is in a binary format, it will not be human readable.</p><h4>Writing of Data Frame into Parquet Files</h4><p>To write a data frame into parquet files, we first need to install the pyarrow libarary.</p><pre>pip install pyarrow</pre><p>After installation we can use pandas to_parquet method to store the data from a data frame into parquet format. Please see the example below on the code comparison between writing to a csv and parquet file and notice how much faster the writing data in parquet format is as compared to csv. Also, we can see the difference in file size of the parquet file vs csv.</p><pre>#Writing to CSV<br>df_retail_original.to_csv(&quot;./data/Online_Retail.csv&quot;, index=False)<br>#Writing to Parquet<br>df_retail_original.to_parquet(&quot;./data/Online_Retail.parquet&quot;)<br></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/525/1*riOKGhNc0qWKEEx-vEZpeQ.png" /><figcaption>Time taken to write data to parquet vs to csv</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/613/1*xAwAuLeOCmPESH45G1fDhA.png" /><figcaption>Difference in size between CSV and parquet</figcaption></figure><h4>Reading of Data Frame from parquet files</h4><p>We can use pandas read_parquet method with pyarrow engine to read data from existing parquet file. Using the same method, we read the parquet and csv files previously written.</p><pre>#Reading from CSV<br>df_from_csv = pd.read_csv(&quot;./data/Online_Retail.csv&quot;)<br>#Reading from Parquet<br>df_from_parquet = pd.read_parquet(&quot;./data/Online_Retail.parquet&quot;, engine=&quot;pyarrow&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/652/1*OGopNAy0PgCEWMOG3GSNfg.png" /><figcaption>Time taken to load data from parquet vs csv files</figcaption></figure><p>Thanks for reading and hope this was useful in some way!</p><h3>References</h3><ul><li><a href="https://www.kaggle.com/code/paultimothymooney/how-to-resolve-a-unicodedecodeerror-for-a-csv-file/notebook">https://www.kaggle.com/code/paultimothymooney/how-to-resolve-a-unicodedecodeerror-for-a-csv-file/notebook</a></li><li><a href="https://www.the-analytics.club/pandas-excel-multiple-sheets">https://www.the-analytics.club/pandas-excel-multiple-sheets</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2d7994a7a5f1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Some ideas on enhancing User Experience on Qlik Sense]]></title>
            <link>https://medium.com/@zaishanweng/some-ideas-on-enhancing-user-experience-on-qlik-sense-508c6146f9cb?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/508c6146f9cb</guid>
            <category><![CDATA[user-interface]]></category>
            <category><![CDATA[qlik-sense]]></category>
            <category><![CDATA[user-experience]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[qlik]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Fri, 22 Jul 2022 15:37:35 GMT</pubDate>
            <atom:updated>2022-07-22T15:37:35.606Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>After the successful delivery of a data analytics product, a common challenge faced is to enhance user adoption. One of the definitions of user adoption from an Indeed article is as follows:</p><blockquote>User adoption rates are important because they tell a company or business how many users like the new product or version and how many don’t like it or do not try it. Most often, a higher adoption rate means a customer finds both value and ease in using the new product or version. Conversely, if a customer finds it requires too much effort to use or doesn’t add value, they may abandon the product altogether or stay with an older version.</blockquote><blockquote><a href="https://www.indeed.com/career-advice/career-development/user-adoption">https://www.indeed.com/career-advice/career-development/user-adoption</a></blockquote><p>One of the necessary ingredients to a successful adoption is the user experience on the interface. In this article, I would like to cover on ways we have found useful to make dashboards on Qlik Sense Apps more intuitive and user friendly.</p><p>The three areas I would like to cover are as follows:</p><ul><li>Menu Page for holistic view of overall health of business</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bLQPxTZjVTNiRE-JSPGE1Q.png" /><figcaption>Example of Menu Page with KPIs on Qlik Sense</figcaption></figure><ul><li>Navigation Bars for navigating between apps and within each app</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Nej_ooupqK-ZkYbPfhxECA.png" /><figcaption>Navigation between App and Within App</figcaption></figure><ul><li>Online Help and Information</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SMjmJuMNOnVoSUtQZ-Tffw.png" /><figcaption>Help Page Built In Online with the App</figcaption></figure><p><strong>The Qlik Sense demo app can be found in the following Github link:</strong></p><p><a href="https://github.com/ZS-Weng/QlikSense/blob/main/QlikSense%20Navigation.qvf">https://github.com/ZS-Weng/QlikSense/blob/main/QlikSense%20Navigation.qvf</a></p><h3>Menu Page for holistic view of overall health of business</h3><p>In a data analytics / business intelligence product, it usually can consist of several apps or reports based on different aspects of the business. Inspired by Fitbit’s health analysis charts, the menu page for a Qlik Sense dashboard is customized with icons and the most important KPI. Based on the thresholds set, users can have an holistic understanding on the overall health summary from various aspects of the business.</p><p>The picture below illustrates how the color of the KPI and icon will change based on the changing value. The threshold set is 33 and 66.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*PxE49dGceZ6XsCUYm4f6Dg.gif" /><figcaption>Menu Page showing how the color of the KPI and Icons will change based on the KPI value and threshold</figcaption></figure><h4>Qlik Sense Codes:</h4><p>The thresholding can easily be setup in Qlik Sense with if statements.</p><ul><li>Code for controlling the colors on the KPI and Icons in Qlik Sense:</li></ul><pre>=if(v_KPI3 &lt;= 33, ‘#e8291c’,<br>if( v_KPI3 &gt; 33 and v_KPI3 &lt;= 66 , ‘#abaa20’, <br>if( v_KPI3 &gt; 66, ‘#25af2f’)))</pre><ul><li>Code for controlling the Icon Image in Qlik Sense:</li></ul><pre>=if(v_KPI3 &lt;= 33, ‘Warning triangle’,<br>if( v_KPI3 &gt; 33 and v_KPI3 &lt;= 66 , ‘View’, <br>if( v_KPI3 &gt; 66, ‘Tick’)))</pre><p><strong>Other resources for creating the icons</strong></p><ul><li>Free icons can be downloaded from: <a href="https://icons8.com/">https://icons8.com/</a></li><li>The websites that provides reference on beautiful gradient color combinations: <a href="https://uigradients.com/">https://uigradients.com/</a> , <a href="https://digitalsynopsis.com/design/beautiful-color-ui-gradients-backgrounds/">https://digitalsynopsis.com/design/beautiful-color-ui-gradients-backgrounds/</a></li><li>Online tools that can invert a black and white icon: <a href="https://www.google.com/search?q=image+invert">https://www.google.com/search?q=image+invert</a></li><li>Python Code that can invert a black and white icon:</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f91046babc9b905642ca27957ab785ef/href">https://medium.com/media/f91046babc9b905642ca27957ab785ef/href</a></iframe><h3>Navigation Bars between apps and within each app</h3><p>To make the navigation across apps and within an app more intuitive, buttons can be used to create the respective navigations. In our projects, we aim to create an experience similar to navigating websites with the side bars providing navigation between apps and top bars within each app. The top bars for our case are standardized to enable users to drill down from Summary &gt; Analysis &gt; Details.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*l3F-u7vHEKQpDur6RdS6Ag.gif" /><figcaption>Example of Navigation between the Apps</figcaption></figure><p>Sample Code in Qlik Sense on Variable Setup:</p><pre>Set v_TopBarSelectedColor = ‘#8351a8’;<br>Set v_TopBarUnSelectedColor = ‘#4d7b93’;</pre><pre>Set v_SideBarSelectedColor = ‘#8351a8’;<br>Set v_SideBarUnSelectedColor = ‘#4d7b93’;</pre><pre>Set v_SideBar1Text = ‘App 1’;<br>Set v_SideBar2Text = ‘App 2’;<br>Set v_SideBar3Text = ‘App 3’;<br>Set v_SideBar4Text = ‘Help Page’;</pre><pre>Set v_SideBar1Link = ‘a0d70b49–7a21–44d0-b9a9-daaa0701c175’;<br>Set v_SideBar2Link = ‘e03b4a67–89c1–425b-852c-44d112e48d7c’;<br>Set v_SideBar3Link = ‘adc68de9–5a42–40ef-bdcf-dffa2981d83c’;<br>Set v_SideBar4Link = ‘f3e454e2-ea27–46ef-bc5a-d1ddd280aaf9’;</pre><pre>Set v_helppageLink = ‘f3e454e2-ea27–46ef-bc5a-d1ddd280aaf9’;</pre><pre>Set v_menupagelink = ‘be0a8400–5df7–4783–8339–1e44e66b04e5’;</pre><h3>Online Help and Information</h3><p>In many projects, often training materials are shared in PowerPoint decks or word documents. These documents might not be easily accessible to every user and it may become challenging at times for users to find the right reference guide when they need help while navigating an analytics app. In our projects, we recommend for the help pages to be built in within as an accompanying sheet or app so that it will be online together with the analytics product. My colleague, <a href="https://medium.com/u/6fd630ce14eb">Rachel Chia</a>, used containers to effectively create step by step guide.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*NgZ8FaDr4G3o-wWpDmFTWQ.gif" /><figcaption>Navigation to Help Page and use of Containers as Step by Step Guide</figcaption></figure><h3>Conclusion</h3><p>In this article, I have aimed to share how user experience can be enhanced on Qlik Sense applications with out of the box capabilities.</p><p>Any further enhancements will likely require tools beyond the application builder like Mashups or D3.js custom visualizations.</p><p>Thanks for reading and hope the information was useful in some way!</p><p>Zaishan is the Data Analytics and AI Lead at IBM focusing on leading digital transformation, data analytics and business process re-engineering initiatives across multiple industries.</p><p>He is passionate about leveraging technology to make a difference, simplify the complex and optimize the mundane with the hope that we can have more time and energy to pursue our interests and dreams.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=508c6146f9cb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Quick Start Guide on incorporating design thinking artifacts for requirements gathering during…]]></title>
            <link>https://medium.com/@zaishanweng/quick-start-guide-on-incorporating-design-thinking-artifacts-for-requirements-gathering-during-data-615e05f23ade?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/615e05f23ade</guid>
            <category><![CDATA[requirements-gathering]]></category>
            <category><![CDATA[design-thinking]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Fri, 08 Jul 2022 07:13:37 GMT</pubDate>
            <atom:updated>2022-07-13T08:55:06.900Z</atom:updated>
            <content:encoded><![CDATA[<h3>Quick Start Guide on incorporating design thinking artifacts for requirements gathering during Project Initiation Phase for Agile Data Analytics Projects</h3><h3><strong>Introduction</strong></h3><p>From my recent involvement in data analytics project engagements as the Data Science and Analytics Lead at IBM, it was observed that proper requirements gathering in the project initiation phase can make a big difference to the eventual success of the project.</p><p>One of the common challenges faced during this phase revolves around translating high level management visions at 50,000ft (e.g. Embark on Digital Transformation, Employ AI) into definitive and objective project requirements for implementation. From my experience, the use of appropriate design thinking artifacts can help to guide the process more effectively.</p><p>In this article, I would like to share a high level overview on the the design thinking artifacts which worked well on our projects and an example of how they are typically woven together during the project initiation phase. Most of these artifacts shared are aligned to IBM Design Thinking Methodology and additional details are available on the IBM Design Thinking website. <a href="https://www.ibm.com/design/thinking/">https://www.ibm.com/design/thinking/</a> (signing up for a free IBM account might be required to access the materials)</p><h3>Breaking down the Initial Requirements Gathering Process</h3><p>Typically the initial requirements gathering process can be broken down three main steps starting with crystallizing the overall business case of the project to the generation of project requirements. We will go through more details in each of the main steps:</p><ul><li>Step 1- Defining goals and objectives of the project</li><li>Step 2- Understanding users, their current work process and envisioned to-be state</li><li>Step 3- Initial formulation of analytics solution requirements</li></ul><h4>Step 1: Defining Goals and Objectives of the Project</h4><p>The first step usually involves connecting with the project sponsor and key stakeholders who will be responsible for the outcome of the project. It is critical for the stakeholders and project team to come together and define the overall direction of the project.</p><blockquote><strong>Typical Stakeholders from Client:</strong> <br>Project Sponsor, Product Owner, Project Manager, SMEs, Users</blockquote><blockquote><strong>Key Objectives:<br></strong>- Establish overall business case<strong> </strong>for the project including potential benefits and how success of the project will be measured<br>- Identify and understand key stakeholders on the project <br>- Identify risks and potential road blocks</blockquote><blockquote><strong>Key Design Thinking Artifacts:</strong><br>- Hopes and Fears<br><strong>- </strong>Opportunity Canvas<br>- Stakeholder Map<br>- Stakeholder Matrix<br>- Assumptions &amp; Questions</blockquote><ul><li><strong>Hopes and Fears</strong></li></ul><p>The Hopes and Fears artifact is useful as a warm up exercise to gather inputs from the stakeholders about their hopes for the project and gain insights to their worries and concerns. At times, the fears section might also help to uncover potential pitfalls and lessons learnt from previous projects.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*LZok2BpVWzINsh2m.jpg" /><figcaption>Sample of a completed Hopes &amp; Fears artifact from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/hopes-and-fears">https://www.ibm.com/design/thinking/page/toolkit/activity/hopes-and-fears</a></figcaption></figure><ul><li><strong>Opportunity Canvas</strong></li></ul><p>Opportunity Canvas is a very helpful artifact to provide holistic view of a project. The canvas can provide assessment on the overall readiness and identify areas with potential gaps and challenges. It is a working document that will be constantly refined throughout the requirements gathering phase.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*OkTktEqRCdQ4aI3U.png" /><figcaption>Opportunity Canvas image from <a href="https://www.jpattonassociates.com/opportunity-canvas/">https://www.jpattonassociates.com/opportunity-canvas/</a></figcaption></figure><p>In our engagements, we have further expanded and customized the sections of the opportunity canvas:</p><blockquote>1. Problems/Goals to be Solved<br>2. Solution Ideas<br>3. Value Proposition<br>4. Key Metrics<br>5. Target Customer/User Type Segments<br>6. High Level Business Benefits and Impact<br>7. Competitive Landscape<br>8. Distribution Channel/Adoption Strategy<br>9. Key Partners/Alliances<br>10. Cost Structure<br>11. Assumptions/Questions<br>12. Constraints<br>13. Impediments<br>14. Squad Members Required<br>15. BU Support Required<br>16. Assets &amp; Accelerators</blockquote><ul><li><strong>Stakeholder Map &amp; Stakeholder Matrix</strong></li></ul><p>A better understanding of stakeholders involved is critical to garner support for the project and ensure all relevant stakeholders are sufficiently engaged. The first part of the exercise will involve listing down all potential stakeholders, grouping them and identifying the relationships.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/0*xr7B0yfwZNnaMZSW.jpg" /><figcaption>Sample Stakeholder Mapping from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/stakeholder-map">https://www.ibm.com/design/thinking/page/toolkit/activity/stakeholder-map</a></figcaption></figure><p>In addition to the stakeholder map, the stakeholder matrix provides another useful perspective by locating each stakeholder in the 2 by 2 matrix of Influence vs Interest.</p><p>The stakeholder map and matrix can also help to customize the communications and engagement plan for the various stakeholder groups. (e.g. type of updates, frequency of updates, attendance at meetings etc.)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*YSWsJNS-XhE_kzk1DzBYqQ.png" /><figcaption>2 X 2 Stakeholder Matrix of Influence vs Interest</figcaption></figure><ul><li><strong>Assumptions and Questions</strong></li></ul><p>The Assumptions and Questions chart can be prefilled with relevant information throughout the earlier discussions. At this stage, the chart can help to recap on existing assumptions, add new details that were missed and perform an initial assessment on the impact and probability as a group.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*GvScWl7S6-tP76ff.jpg" /><figcaption>Sample Assumptions and Questions artifact from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/assumptions-and-questions">https://www.ibm.com/design/thinking/page/toolkit/activity/assumptions-and-questions</a></figcaption></figure><h4>Step 2: Understanding users, their current work process and pain points</h4><p>With an alignment on the overall business case and strategic direction of the project, we will shift gears in Step 2 to understand more about the end users and details around their work processes in the current and future context.</p><blockquote><strong>Typical Stakeholders from Client:</strong> <br>SMEs and Users from the different groups who will be the end users</blockquote><blockquote><strong>Key Objectives:<br></strong>- Understanding the end users, their current work process and how they are expected to interact with the solution in their work process<br>- Identify wish lists and pain points</blockquote><blockquote><strong>Key Design Thinking Artifacts:</strong><br>- Empathy Map<br><strong>- </strong>To-Be<strong> </strong>Scenario Map</blockquote><ul><li><strong>Empathy Map</strong></li></ul><p>The empathy map aids in the understanding of the profile for the users. It is best practice to generate empathy maps for each group of users. The collection of details can be either in the context of the problem statement or in the wider context of their day to day work. The details of what they say, do, think and feel will help to paint a representative user profile of the group.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*I1J1eEP_s2TbCQMG.jpg" /><figcaption>Sample Empathy Map artifact from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/empathy-map">https://www.ibm.com/design/thinking/page/toolkit/activity/empathy-map</a></figcaption></figure><ul><li><strong>To-Be Scenario Map</strong></li></ul><p>Building upon the empathy map for each user group, additional details on each phase of the to-be working process can be consolidated to form the To-Be scenario map. The discussion with the users in the dimensions of doing, thinking and feeling for each of the phases will help to uncover pain points, gaps and potential benefits.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*A63rnape5q3cyvaD.jpg" /><figcaption>Sample To-Be Scenario Map artifact from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/to-be-scenario-map">https://www.ibm.com/design/thinking/page/toolkit/activity/to-be-scenario-map</a></figcaption></figure><h4>Step 3- Initial formulation of analytics solution requirements</h4><p>In Step 3, we will build upon the established direction and understanding of users and their needs &amp; wants. The key goal in this phase is to develop the solution requirements in sufficient details to start the project.</p><blockquote><strong>Typical Stakeholders from Client:</strong> <br>SMEs and Users from the different groups who will be the end users</blockquote><blockquote><strong>Key Objectives:<br></strong>- Developing high level requirements in hills or user stories format<br>- Brainstorm on functional features<br>- Prioritize features for development</blockquote><blockquote><strong>Key Design Thinking Artifacts:</strong><br>- Hills (User Stories) <br><strong>- </strong>Big Idea<strong><br>- </strong>Prioritization Map</blockquote><ul><li><strong>User Stories or Hills Writing (Who, What, Wow)</strong></li></ul><p>The gathering of User Stories or Hills captures user requirements the following format:</p><p>- Who (In the capacity of a certain Role), <br>- What (I would like to take some action), <br>- Wow (To achieve a certain outcome).</p><p>This will be useful to identify target users, identify user interactions with the solution and understand the underlying purpose and outcome to be achieved.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*AIRtEc-2goevlx9S.jpg" /><figcaption>Sample Hills Writing Exercise output from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/writing-hills">https://www.ibm.com/design/thinking/page/toolkit/activity/writing-hills</a></figcaption></figure><ul><li><strong>Big Idea</strong></li></ul><p>Based on the user stories &amp; hills captured, we can conduct a brainstorming session to draw out ideas on features required in each of the user stories &amp; hills. Similar features can be categorized and grouped together to form a coherent functionality set.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*5NIfpiRj8uBf8tfk.jpg" /><figcaption>Sample Big Idea Collections from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/big-idea-vignettes">https://www.ibm.com/design/thinking/page/toolkit/activity/big-idea-vignettes</a></figcaption></figure><ul><li><strong>Prioritization Map</strong></li></ul><p>The next step involves placing the features and functionality on the prioritization map. Features in the No Brainers section with the highest impact and feasibility will be planned with the highest priority for development first. This will help the project to deliver benefits early on. There is usually some deliberation involve in the prioritization between the big bets section (High value but might be less feasible) and utilities section (Higher feasibility but might deliver less value). The features in the un-wise section with low impact and feasibility will have least lower priority and are usually parked for future review.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*CA5kYPk5x1IbRDWi.jpg" /><figcaption>Sample Prioritization Grid from <a href="https://www.ibm.com/design/thinking/page/toolkit/activity/prioritization">https://www.ibm.com/design/thinking/page/toolkit/activity/prioritization</a></figcaption></figure><h3>Transiting from Project Initiation Phase to Project Planning Phase</h3><p>A logical transition point from the project initiation phase to the project planning phase will be the step where the details from the prioritization map is converted into the product backlog.</p><h4>Product Backlog Hierarchy</h4><p>A typical product backlog can consist of various layers depending on the complexity of the project and terminology that the team or existing tool follows. Using an example from Azure DevOps board, a typical structure on the product backlog will be:<br>- Epic<br>- Feature<br>- User Story (Product Backlog Item)<br>- Task</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/452/0*eIoECZfTUMkdMvLr" /><figcaption>Azure Dev Ops Board Standard Scrum Backlog Structure</figcaption></figure><p>Using the inputs from the prioritization map, the team will work closely with the product owner and users to develop the product backlog. Based on experience, most of the information captured will be at the level of features and product backlog items. The features can be grouped into Epics signifying features for each product release and broken down into product backlog items.</p><p>The rest of the project planning activities will follow on from this phase.</p><h4><strong>Example of Product Backlog Items for Data Visualization Dashboard</strong></h4><p>For data visualization features, there are three standard components to be captured as part of the product backlog as shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DJU6O21fKvWMt-8KjrwXjA.jpeg" /><figcaption>Standard Components of a Data Visualization Workflow</figcaption></figure><p>For most dashboard visualization requirements on BI (Business Intelligence) tools such as Tableau, Qlik, PowerBI, the standard functional requirements that needs to be defined are as follows:</p><p><strong>Analytics Flow</strong>: The usual analytics process usually presents information in a top-down approach, moving from a big picture summary to detailed report with many rows of data. An example of this is Qlik’s DAR(Dashboard, Analysis, Reporting) methodology where information in presented from the highest granularity to the lowest granularity.</p><p><strong>Dimensions:</strong> Identifying the dimensions /features is a critical component of the function visualization requirements. From the identified dimensions, the lowest granularity of the data and a hierarchy can be derived. In addition, selected dimensions can be defined as filters. A subset of commonly used dimensions and their hierarchies in a retail context is shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rOj-jepkF0bLvfUkxF-5ig.jpeg" /><figcaption>Sample of common dimensions in the retail context</figcaption></figure><p><strong>KPIs: </strong>KPIs are any of the columns that can provide a quantitative measure against the dimensions. It is important to capture the business rules and transformations clearly and ensure that it is aligned across the different group of stakeholders. A subset of commonly used metrics in a retail context is as shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UVs9CuMDI8q5MHZQG8IZkA.jpeg" /><figcaption>Sample of commonly used KPIs in the retail context</figcaption></figure><p>Thanks for reading and hope the information was useful in some way!</p><p>Zaishan is the Data Analytics and AI Lead at IBM focusing on leading digital transformation, data analytics and business process re-engineering initiatives across multiple industries.</p><p>He is passionate about leveraging technology to make a difference, simplify the complex and optimize the mundane with the hope that we can have more time and energy to pursue our interests and dreams.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=615e05f23ade" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data cleaning on SAP data extracts in .txt format with Regex and Python]]></title>
            <link>https://medium.com/@zaishanweng/data-cleaning-on-sap-data-extracts-with-regex-and-python-cd2afe73be14?source=rss-4c27aa18efe8------2</link>
            <guid isPermaLink="false">https://medium.com/p/cd2afe73be14</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[towards-data-science]]></category>
            <category><![CDATA[sap]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Zaishan Weng]]></dc:creator>
            <pubDate>Sat, 25 Jun 2022 13:08:06 GMT</pubDate>
            <atom:updated>2022-10-15T14:12:20.506Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Introduction</strong></h3><p>During one of our recent projects involving the procure to pay process, our team encountered SAP raw data extracted from the system in .txt format which proved to be difficult to clean using traditional methods like readlines() or split() by delimiters due to some inherent data inconsistencies.</p><p>Regex matching proved to be helpful for such scenarios to clean the data.</p><h3><strong>Common SAP tables in Procure to Pay process</strong></h3><p>First, lets go through what are common data tables extracted from SAP as part of the Procure to Pay process.</p><ul><li>Accounting Related Tables <br>- BKPF: Accounting Document Header<br>- BSAK: Accounting: Secondary Index for Vendors<br>- BSEG: Accounting Document Segment</li><li>Purchase Order related Tables<br>- EKKO: Purchasing Document Header<br>- EKPO: Purchasing Document Item<br>- EKBE: History per Purchasing Document<br>- EKKN: Account Assignment in Purchasing Document</li><li>Material Tables<br>- MAKT: Material Descriptions</li></ul><p>There are various websites which provide additional information about the SAP tables.</p><ul><li><a href="https://www.se80.co.uk/training-education/sap-tables/">https://www.se80.co.uk/training-education/sap-tables/</a></li><li><a href="https://www.tcodesearch.com/sap-tables/detail?id=BSEG">https://www.tcodesearch.com/sap-tables/detail?id=BSEG</a> (Might need to enter through changing the id in URL without requiring premium membership)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/795/1*WdBn8dZI1sxJ6IBGtbQBOw.png" /><figcaption>Example of Data Dictionary for BSEG Table Source: <a href="https://www.se80.co.uk/training-education/sap-tables/">https://www.se80.co.uk/</a></figcaption></figure><h3><strong>Format of SAP data extract in .txt file</strong></h3><p>For our project, the output SAP data extracts is in a .txt format and with the typical structure as shown below:</p><ul><li>The column header details starts at line 4</li><li>The width of each column is consistent between the column headers and the data for each file extracted</li><li>Actual data content starts at line 5 till the end</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J5IwUB3FoeNOfNeDufeGzA.png" /><figcaption>Sample SAP TXT Data Extract Structure with Mock Data</figcaption></figure><p>The sample SAP data in txt format and Jupyter Notebook can be found on GitHub: <a href="https://github.com/ZS-Weng/Data_Engineering/tree/main/Data_Cleaning">https://github.com/ZS-Weng/Data_Engineering/tree/main/Data_Cleaning</a></p><p>The two major data discrepancies encountered are:</p><ul><li>Newline character inserted in some of the fields</li><li>Pipe (|) delimiters found within the actual data</li></ul><h3>Full Code and Output</h3><p>The full working code for the data cleaning is as shown below</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/77a9aa119e5d3011372e9bda8cbd4772/href">https://medium.com/media/77a9aa119e5d3011372e9bda8cbd4772/href</a></iframe><p>Final pandas data frame output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/579/1*8PAmIRBUMOmXsoOvLcxRNw.png" /><figcaption>Final pandas tabular output after data cleaning</figcaption></figure><h3>Detailed Walk-through of the codes</h3><h4><strong>Cleaning of newline character prior to splitting lines</strong></h4><p>In order to clean the \n (newline) characters which are not valid, we first use the read() instead of readline() method as the readline() method will split the lines by the \n character automatically.</p><pre>with open(&quot;Sample SAP Format.txt&quot;, encoding=&quot;utf-8&quot;) as f:        <br>    content_raw = f.read()</pre><p>Next, we use the Regex pattern “([1|-])[\n](.)|(.)[\n]([^|-])&quot; to find and the invalid \n characters. The pattern basically detects newline that do not start with the characters “1” , “|” or “-” and do not end with the characters “|” or “-”.</p><p>For the invalid newline characters found, we replace them with a space using the .sub() method.</p><pre>new_line_pattern = re.compile(&quot;([^1|-])[\n](.)|(.)[\n([^|-])&quot;)<br>content_cleaned_newline = new_line_pattern.sub(r&quot;\1 \2&quot;,content_raw)<br>content_split_line = content_cleaned_newline.split(&quot;\n&quot;)</pre><p>Output after cleaning and splitting lines:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*VtP-KnOL9I_PyQelgieQZw.png" /><figcaption>List of Data After the Line Split</figcaption></figure><h4>Data Cleaning Steps for Individual Line</h4><p>Based on the above, we can see that the data we are interested in is the column header (4th line) and the rest of the data content (6th to 2nd last lines).</p><p>We will first split the column by the pipe “|” delimiter and getting the columns width to create the Regex matching string pattern.</p><pre>header_string = content_split_line[3]</pre><pre>column_header = [token.strip() for token in header_string.split(&quot;|&quot;)][1:-1]</pre><pre>list_column_width = [&quot;(.{&quot; + str(len(column)) + &quot;})&quot; for column in header_string.split(&quot;|&quot;)][1:-1]</pre><pre>column_string_pattern = &quot;[|]&quot; + &quot;[|]&quot;.join(list_column_width) + &quot;[|]&quot;</pre><p>The column_string_pattern to match that is generated for the sample data will be as follows:</p><blockquote>“[|](.{10})[|](.{4})[|](.{3})[|](.{10})[|](.{20})[|]”</blockquote><p>This column pattern matching pattern is dynamically generated and will be unique for each of the data extract file even for the same tables as the width is adjusted during data extract according to the content.</p><p>We will then use the matching pattern to extract the rest of the data (6th to 2nd last line) with re.match().groups() instead of using the str.split() method.</p><pre>column_pattern = re.compile(column_string_pattern)</pre><pre>cleaned_content = [[token.strip() for token in column_pattern.match(row).groups()] for row in content_split_line[5:-2]]</pre><p>The output after content splitting:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/688/1*HFCDy2xsSt3uh6kBlkLLlg.png" /><figcaption>Data in Nested List format after splitting of content of each individual line</figcaption></figure><p>Here we can see that the extra delimiters e.g. ‘Item A|B|C’ does not affect the content splitting .</p><p>Finally, we combine the cleaned header columns and data into a pandas data frame.</p><pre>df_clean = pd.DataFrame(cleaned_content, columns=column_header)</pre><p>Final pandas Data Frame output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/574/1*mXFwk_UZYDW1f4dgEICkpQ.png" /><figcaption>Final cleaned data output in pandas Data Frame</figcaption></figure><h3>Useful Learning Resource for Python and Regex</h3><p>When I was starting out, I found the book <em>Automate the Boring Stuff with Python by Al Sweigart</em> one of the best resources to learning about Python and Regex with many practical examples.</p><p>There is a free access option to the book on his website: <a href="https://automatetheboringstuff.com/">https://automatetheboringstuff.com/</a> and this is the link to the specific chapter on Regex which formed the foundation on some of the implementation: <a href="https://automatetheboringstuff.com/2e/chapter7/">https://automatetheboringstuff.com/2e/chapter7/</a></p><p>Thanks for reading and hope the information was useful in some way!</p><p>Zaishan is the Data Analytics and AI Lead at IBM focusing on leading digital transformation, data analytics and business process re-engineering initiatives across multiple industries.</p><p>He is passionate about leveraging technology to make a difference, simplify the complex and optimize the mundane with the hope that we can have more time and energy to pursue our interests and dreams.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cd2afe73be14" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>