<?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 James Gnanasekaran on Medium]]></title>
        <description><![CDATA[Stories by James Gnanasekaran on Medium]]></description>
        <link>https://medium.com/@jamespaultg?source=rss-329ca543a999------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*LN25ZCKSojSo9It_3dAV3Q.jpeg</url>
            <title>Stories by James Gnanasekaran on Medium</title>
            <link>https://medium.com/@jamespaultg?source=rss-329ca543a999------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 01:53:45 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jamespaultg/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[Why Copilot Agents Struggle With Operational Data (and What Actually Works)]]></title>
            <link>https://jamespaultg.medium.com/why-copilot-agents-struggle-with-operational-data-and-what-actually-works-246b95c00b4e?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/246b95c00b4e</guid>
            <category><![CDATA[microsoft-copilot-studio]]></category>
            <category><![CDATA[enterprise-ai]]></category>
            <category><![CDATA[microsoft-dataverse]]></category>
            <category><![CDATA[copilot-studio]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Sat, 24 Jan 2026 12:17:50 GMT</pubDate>
            <atom:updated>2026-01-24T12:17:50.924Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SH21KhW1vhVChbC1qZ3EVA.png" /></figure><p><em>A practical lesson in treating language and data differently when building Copilot Studio agents</em></p><p>When Copilot Studio was introduced, it unlocked an exciting idea: build agents that answer natural language questions over enterprise data.</p><p>In theory, this should be straightforward. In practice, it exposed a subtle but important mismatch.</p><h3>The initial assumption</h3><p>The data already existed in a spreadsheet used for planning and tracking initiatives over time.</p><p>It was:</p><ul><li>business-maintained</li><li>widely shared</li><li>already “structured”</li></ul><p>Copilot Studio supports Excel as a knowledge source, so the assumption was simple:</p><blockquote><em>If Copilot can read documents, surely it can read this too.</em></blockquote><p>Sometimes it did. Sometimes it didn’t. That inconsistency turned out to be the real problem.</p><h3>Why spreadsheets as knowledge sources fail quietly</h3><p>When spreadsheets are added as knowledge sources, they’re treated like retrieval content — not like a database.</p><p>According to <a href="https://www.microsoft.com/en-us/power-platform/blog/2025/03/27/knowledge-in-microsoft-copilot-studio/">Microsoft’s documentation</a>:</p><blockquote><em>“Uploaded files are chunked into smaller pieces for faster processing and vector-indexed to provide semantic match with the user query.”</em></blockquote><p>This is Retrieval Augmented Generation (RAG). It has important consequences:</p><ul><li>Only relevant chunks are retrieved</li><li>The model does not enumerate all rows</li><li>Aggregations are inferred, not computed</li></ul><p>Questions like:</p><ul><li><em>How many initiatives exist?</em></li><li><em>Which ones are at risk?</em></li></ul><p>…returned answers that looked plausible, but weren’t always repeatable.</p><p>This isn’t a bug. It’s a design mismatch.</p><h3>Trying tools instead of documents</h3><p>The next step was to retrieve the data explicitly using Power Automate connectors and let the agent reason over it.</p><p>That ran into a different limitation:</p><ul><li>Connector payload sizes</li><li>Token limits</li><li>Agent context constraints</li></ul><p>Large, wide datasets simply don’t fit comfortably into an LLM’s reasoning window. Returning all rows to answer “how many are at risk?” quickly exceeds the context window and results in errors.</p><p>At this point, the pattern was clear:</p><p><strong>LLMs are excellent at reasoning over language. They are not databases.</strong></p><h3>Evaluating other options</h3><p>Several alternatives were explored:</p><p>Option Strength Limitation <strong>Excel Copilot</strong> Great for individual analysis Not suitable for shared, agent-based use <strong>M365 data analysis agents</strong> Powerful capabilities Not embeddable into Copilot Studio agents <strong>Fabric Data Agent</strong> Strong enterprise option with NL2SQL/NL2DAX Requires Fabric capacity (F2+), cost-prohibitive for smaller teams</p><p>Each option had merit — but none aligned perfectly with the requirement: a reliable Copilot agent embedded in Teams, accessible to 70+ users.</p><h3>The turning point: Dataverse</h3><p>Loading the data into Dataverse changed everything.</p><p>Why? Because Dataverse as a knowledge source behaves fundamentally differently. From <a href="https://www.microsoft.com/en-us/power-platform/blog/2025/03/27/knowledge-in-microsoft-copilot-studio/">Microsoft’s documentation</a>:</p><blockquote><em>“When Dataverse is used as Knowledge, the user’s query is translated into runtime query against Dataverse… generating a PowerFX query from the natural language.”</em></blockquote><p>This is the critical distinction:</p><p>Knowledge Source Processing Method Behaviour Spreadsheet/File RAG Chunks content, retrieves fragments Dataverse NL → PowerFX Translates to structured queries</p><p>Once Dataverse was in place:</p><ul><li>Counts were correct</li><li>Filters were stable</li><li>Answers were consistent</li></ul><p>To keep existing workflows intact, dataflows were used to synchronize the spreadsheet into Dataverse on a schedule.</p><p>Dataverse became the system of record. Copilot became reliable.</p><h3>A note on other databases</h3><p>It’s worth noting that this NL-to-query capability is specific to the Dataverse integration with Copilot Studio.</p><p>According to <a href="https://www.microsoft.com/en-us/power-platform/blog/2025/03/27/knowledge-in-microsoft-copilot-studio/">Microsoft</a>:</p><blockquote><em>“For other enterprise systems such as Salesforce, ServiceNow, Zendesk, and Azure SQL Server, the real-time RAG approach is implemented.”</em></blockquote><p>So while you can add Azure SQL as a knowledge source, it won’t give you the same structured query behaviour. The NL → PowerFX translation is a Dataverse-specific capability within Copilot Studio.</p><h3>The principle that emerged</h3><p>This experience reinforced a design principle worth stating explicitly:</p><blockquote><strong><em>Copilot becomes reliable when it reasons over language and delegates computation to systems designed for data.</em></strong></blockquote><p>Or more simply:</p><ul><li>Use RAG for meaning</li><li>Use databases for aggregation</li></ul><p>Trying to blur that boundary leads to fragile systems.</p><h3>Where this goes next</h3><p>The natural evolution is to remove spreadsheets from the input path entirely:</p><ul><li>Use a Power App for structured data entry</li><li>Enforce validation and ownership</li><li>Enable automation and reminders</li></ul><p>At that point, Copilot isn’t just answering questions — it’s operating over a clean, governed data foundation.</p><h3>Final thought</h3><p>If your Copilot agent feels <em>almost right</em> — but not quite trustworthy — that’s a signal.</p><p>The problem is rarely the model.</p><p>It’s almost always the data architecture.</p><h3>References</h3><ol><li><a href="https://www.microsoft.com/en-us/power-platform/blog/2025/03/27/knowledge-in-microsoft-copilot-studio/">Knowledge in Microsoft Copilot Studio</a> — Microsoft Power Platform Blog</li><li><a href="https://learn.microsoft.com/en-us/microsoft-copilot-studio/knowledge-copilot-studio">Knowledge sources overview</a> — Microsoft Learn</li><li><a href="https://learn.microsoft.com/en-us/microsoft-copilot-studio/knowledge-add-dataverse">Add Dataverse tables as a knowledge source</a> — Microsoft Learn</li><li><a href="https://learn.microsoft.com/en-us/microsoft-copilot-studio/knowledge-unstructured-data">Unstructured data as a knowledge source</a> — Microsoft Learn</li></ol><p><em>Tags: Copilot Studio, Microsoft Dataverse, Power Platform, RAG, Enterprise AI, Agent Architecture</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=246b95c00b4e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding Power BI File Formats: PBIX, PBIP, and PBIR Explained]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://jamespaultg.medium.com/understanding-power-bi-file-formats-pbix-pbip-and-pbir-explained-957a26296c88?source=rss-329ca543a999------2"><img src="https://cdn-images-1.medium.com/max/1200/1*s4RJ0ipE1ddZhoaQnwYb2Q.png" width="1200"></a></p><p class="medium-feed-snippet">If you&#x2019;ve been working with Power BI for a while, you&#x2019;re probably familiar with the classic&#xA0;.pbix file format. But Microsoft has&#x2026;</p><p class="medium-feed-link"><a href="https://jamespaultg.medium.com/understanding-power-bi-file-formats-pbix-pbip-and-pbir-explained-957a26296c88?source=rss-329ca543a999------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://jamespaultg.medium.com/understanding-power-bi-file-formats-pbix-pbip-and-pbir-explained-957a26296c88?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/957a26296c88</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[version-control]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Sat, 22 Nov 2025 08:21:26 GMT</pubDate>
            <atom:updated>2025-11-22T08:21:26.612Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Becoming a Microsoft Learn Student Ambassador (MLSA) — that’s awesome!]]></title>
            <link>https://jamespaultg.medium.com/becoming-a-microsoft-learn-student-ambassador-mlsa-thats-awesome-ef00e78e7ea4?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/ef00e78e7ea4</guid>
            <category><![CDATA[career-growth]]></category>
            <category><![CDATA[student-ambassador]]></category>
            <category><![CDATA[techcommunity]]></category>
            <category><![CDATA[microsoft-learn]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Sun, 09 Mar 2025 19:41:10 GMT</pubDate>
            <atom:updated>2025-03-09T19:41:10.955Z</atom:updated>
            <content:encoded><![CDATA[<h3>Becoming a <strong>Microsoft Learn Student Ambassador (MLSA)</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*O1JOBZ25f8nzNq6G" /><figcaption>Photo by <a href="https://unsplash.com/@goian?utm_source=medium&amp;utm_medium=referral">Ian Schneider</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Becoming a <strong>Microsoft Learn Student Ambassador (MLSA)</strong> — that’s awesome! It’s a great opportunity to grow your skills, connect with tech experts, and impact your community.</p><p>The program is designed for students who are passionate about technology and want to develop their skills while helping their community.</p><h3>Eligibility Criteria:</h3><ul><li>Must be at least <strong>16 years old</strong>.</li><li>Enrolled in a <strong>full-time academic program</strong> at a university or college.</li><li>Passion for <strong>technology, learning, and sharing knowledge</strong> with others.</li><li>Basic knowledge of <strong>Microsoft technologies</strong> (not mandatory but beneficial).</li><li>Willingness to <strong>conduct workshops, hackathons, and community events</strong>.</li></ul><h3>Benefits of Being a Microsoft Learn Student Ambassador:</h3><ol><li><strong>Access to Microsoft Tools &amp; Technology</strong> — Free access to tools like Azure, GitHub, and Microsoft 365.</li><li><strong>Learning Opportunities</strong> — Exclusive training, workshops, and mentorship from Microsoft professionals.</li><li><strong>Networking</strong> — Connect with like-minded students, industry experts, and Microsoft engineers.</li><li><strong>Badges &amp; Recognition</strong> — Gain credibility with digital badges and LinkedIn recognition.</li><li><strong>Career Boost</strong> — Exposure to internship and job opportunities.</li><li><strong>Swag &amp; Events</strong> — Free Microsoft swag and invitations to exclusive events.</li></ol><h3>How to Apply:</h3><ol><li>Visit the<a href="https://studentambassadors.microsoft.com/"> <strong>MLSA application page</strong></a></li><li>Fill out the application form with details about interests, projects, and contributions.</li><li>Submit a <strong>short video</strong> explaining why he wants to be an MLSA.</li><li>Wait for the selection process (it may take a few weeks).</li></ol><p>I’ll guide you step by step on how to apply.</p><h3>Step 1: Register on Microsoft Learn</h3><p>First, go to<a href="https://learn.microsoft.com/"> <strong>Microsoft Learn</strong></a> and sign in with your <strong>Microsoft account</strong> (or create one if you don’t have it). Complete some <strong>free learning modules</strong> on AI, Cloud, or Development — this will make your application stronger.</p><h3>Step 2: Start Your Application</h3><ol><li>Visit the<a href="https://studentambassadors.microsoft.com/"> <strong>MLSA application page</strong></a> and sign in.</li><li>Fill in your details — name, university, email, and course details.</li><li>Show your <strong>tech skills</strong> (coding, AI, cloud, etc.) and <strong>community involvement</strong> (tech clubs, hackathons, events).</li></ol><h3>Step 3: Write Strong Answers</h3><p>You’ll be asked questions like:</p><ul><li>Why do you want to be an MLSA?</li><li>How have you helped others in tech?</li><li>How will you contribute to the student community?</li></ul><p><strong>How to answer:<br></strong> ✅ Show your passion for learning and sharing knowledge.<br> ✅ Mention any projects, hackathons, coding clubs, or workshops you’ve attended or organized.<br> ✅ Keep it <strong>clear, structured, and to the point</strong>.</p><h3>Step 4: Record a Short Video</h3><p>You need to submit a <strong>1–2 minute video</strong> explaining:<br> 🎤 Why you’re passionate about technology.<br> 🎤 How you’ve helped others learn tech.<br> 🎤 Why you want to be an MLSA.</p><p><strong>Tips for your video:<br></strong> 🎥 Speak clearly and confidently.<br> 🎥 Use good lighting and record with your phone.<br> 🎥 Keep it short, <strong>under 2 minutes</strong> — no need for fancy editing.</p><h3>Step 5: Submit and Wait for Review</h3><p>Once everything is done, submit your application. Microsoft will review it, and you’ll get an email if selected.</p><h3>Extra Tips to Boost Your Chances</h3><p>🚀 Start sharing tech knowledge (blog, YouTube, LinkedIn).<br> 🚀 Complete more <strong>Microsoft Learn modules</strong> to show your dedication.<br> 🚀 Participate in hackathons, coding competitions, or <strong>open-source projects</strong>.<br> 🚀 Connect with <strong>current MLSAs</strong> on LinkedIn to learn from their experiences.</p><p>If you need help with your answers or video, let me know — I’ll be happy to guide you. Go for it, and best of luck! 🚀💙</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ef00e78e7ea4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How I Used ChatGPT to Convert a Complex Nested JSON to a CSV File]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://jamespaultg.medium.com/how-i-used-chatgpt-to-convert-a-complex-nested-json-to-a-csv-file-with-custom-columns-fe5bf337aa00?source=rss-329ca543a999------2"><img src="https://cdn-images-1.medium.com/max/1915/1*hstyTLF1NPdZXvZSxxv2eg.png" width="1915"></a></p><p class="medium-feed-snippet">Introduction
Working with complex, nested JSON data is a challenge many data professionals face.</p><p class="medium-feed-link"><a href="https://jamespaultg.medium.com/how-i-used-chatgpt-to-convert-a-complex-nested-json-to-a-csv-file-with-custom-columns-fe5bf337aa00?source=rss-329ca543a999------2">Continue reading on Medium »</a></p></div>]]></description>
            <link>https://jamespaultg.medium.com/how-i-used-chatgpt-to-convert-a-complex-nested-json-to-a-csv-file-with-custom-columns-fe5bf337aa00?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/fe5bf337aa00</guid>
            <category><![CDATA[json]]></category>
            <category><![CDATA[csv]]></category>
            <category><![CDATA[prompt-engineering]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[chatgpt]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Fri, 08 Nov 2024 16:19:33 GMT</pubDate>
            <atom:updated>2024-11-09T09:40:47.986Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Validating SQL Query Changes: How to Compare Results Effectively]]></title>
            <link>https://jamespaultg.medium.com/validating-sql-query-changes-how-to-compare-results-effectively-4a9b306ea9ed?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/4a9b306ea9ed</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[testing]]></category>
            <category><![CDATA[change-management]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Fri, 30 Aug 2024 10:06:09 GMT</pubDate>
            <atom:updated>2024-08-30T10:06:09.998Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YYeNcdWx3ViGD1LJSu6rlg.jpeg" /></figure><p>“In the world of SQL, even the smallest change can have a significant impact. Ensuring that your modifications are accurate and don’t introduce new issues is essential for maintaining the integrity of your database.</p><p>A few years ago, I discovered <a href="https://www.scootersoftware.com/home">Beyond Compare</a>, a tool that quickly became indispensable for me. I’ve used it extensively to compare code, data tables, and entire folders. It’s especially useful for catching subtle issues, like non-printable characters, that a simple visual check might miss.</p><p>However, Beyond Compare is a licensed tool, and not every company may have access to it. So, what do you do if you need to ensure your changes are correct but don’t have a dedicated comparison tool?”</p><p>You can compare the records from the original and modified CTE queries directly in SQL itself. Here’s how you can do it:</p><h3>1. Using EXCEPT or MINUS (depending on the database)</h3><ul><li>You can use the EXCEPT (or MINUS in some databases like Oracle) operator to compare the results of two queries. This operator returns all rows from the first query that are not in the second query.</li></ul><pre>-- Differences in records present in original but not in modified<br>WITH original_cte AS (<br>    -- Original CTE query here<br>),<br>modified_cte AS (<br>    -- Modified CTE query here<br>)<br>SELECT *<br>FROM original_cte<br>EXCEPT<br>SELECT *<br>FROM modified_cte;</pre><h3>2. Using FULL OUTER JOIN</h3><ul><li>You can join the original and modified queries on all columns and filter out rows where there is a difference.</li></ul><pre>WITH original_cte AS (<br>    -- Original CTE query here<br>),<br>modified_cte AS (<br>    -- Modified CTE query here<br>)<br>SELECT *<br>FROM original_cte o<br>FULL OUTER JOIN modified_cte m<br>ON o.col1 = m.col1<br>AND o.col2 = m.col2<br>AND ... -- Continue for all columns you want to compare<br>WHERE o.col1 IS NULL OR m.col1 IS NULL<br>OR o.col2 IS NULL OR m.col2 IS NULL<br>OR o.col1 &lt;&gt; m.col1<br>OR o.col2 &lt;&gt; m.col2<br>-- Continue for all columns you want to compare</pre><h3>3. Using UNION ALL with a GROUP BY</h3><ul><li>Combine both the original and modified CTEs using UNION ALL and group the results. Rows that appear twice (once in each query) will have a count of 2, while rows that appear only once will have a count of 1. Filtering for counts of 1 will give you the differences.</li></ul><pre>WITH original_cte AS (<br>    -- Original CTE query here<br>),<br>modified_cte AS (<br>    -- Modified CTE query here<br>),<br>combined AS (<br>    SELECT *, &#39;original&#39; AS source FROM original_cte<br>    UNION ALL<br>    SELECT *, &#39;modified&#39; AS source FROM modified_cte<br>)<br>SELECT col1, col2, ... -- Select the relevant columns<br>FROM combined<br>GROUP BY col1, col2, ...<br>HAVING COUNT(*) = 1;</pre><h3>4. Using ROW_NUMBER for Row-Level Comparison</h3><p>If you want to compare rows based on their position/order, you can use the ROW_NUMBER function to assign row numbers and then compare them.</p><pre>WITH original_cte AS (<br>    -- Original CTE query here<br>),<br>modified_cte AS (<br>    -- Modified CTE query here<br>),<br>original_numbered AS (<br>    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM original_cte<br>),<br>modified_numbered AS (<br>    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM modified_cte<br>)<br>SELECT *<br>FROM original_numbered o<br>FULL OUTER JOIN modified_numbered m<br>ON o.rn = m.rn<br>WHERE o.col1 IS NULL OR m.col1 IS NULL<br>OR o.col1 &lt;&gt; m.col1<br>-- Continue for all columns you want to compare</pre><h3>Explanation:</h3><ul><li><strong>EXCEPT/MINUS</strong>: Useful for finding rows that are in one result but not the other.</li><li><strong>FULL OUTER JOIN</strong>: Compares row by row and identifies differences across all columns.</li><li><strong>UNION ALL with </strong><strong>GROUP BY</strong>: Identifies rows that do not match perfectly between the two queries.</li><li><strong>ROW_NUMBER</strong>: Compares rows based on their order.</li></ul><p>Choose the approach that best suits your needs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4a9b306ea9ed" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Data Manipulation in AWS Athena]]></title>
            <link>https://jamespaultg.medium.com/mastering-cc49fad14ca5?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/cc49fad14ca5</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[athena]]></category>
            <category><![CDATA[aws-athena]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Fri, 19 Apr 2024 23:03:58 GMT</pubDate>
            <atom:updated>2024-04-19T23:04:39.085Z</atom:updated>
            <content:encoded><![CDATA[<p>AWS Athena is a powerful serverless query service that allows you to analyze data directly from Amazon S3 using standard SQL. With Athena, you can easily query vast amounts of data without the need to set up or manage any infrastructure. However, to make the most out of Athena, it’s essential to understand advanced data manipulation techniques.</p><p>In this tutorial, we’ll dive deep into Athena’s capabilities and explore advanced data manipulation techniques that will empower you to extract valuable insights from your datasets. We’ll cover everything from splitting strings and leveraging Common Table Expressions (CTEs) to performing complex joins and aggregations.</p><p>Let’s embark on a journey to master data manipulation in AWS Athena and unlock the full potential of your data stored in Amazon S3.</p><h3>Background on AWS Athena:</h3><p>AWS Athena is a fully managed, interactive query service that allows you to analyze data directly in Amazon S3 using standard SQL. It’s ideal for ad-hoc analysis, data exploration, and querying large datasets without the need for complex infrastructure setup. Athena is serverless, which means you only pay for the queries you run, making it cost-effective and scalable.</p><p>Key features of Athena include:</p><ul><li>Support for Standard SQL: Athena supports standard SQL syntax, making it easy for SQL users to get started with querying data.</li><li>Integration with Amazon S3: Athena seamlessly integrates with data stored in Amazon S3, allowing you to query data directly without any data movement.</li><li>Schema-on-Read: With Athena, you can query data without having to define a schema upfront. Athena infers the schema from the data, enabling flexibility and agility in data analysis.</li><li>Performance and Scalability: Athena is highly scalable and can handle queries on datasets of any size, ranging from gigabytes to petabytes.</li></ul><p>Now that we have an overview of AWS Athena, let’s dive into advanced data manipulation techniques that will help you maximize your analysis capabilities.</p><h3>Splitting Comma-Separated Strings:</h3><p>Suppose you have a table abc.current in Athena, and one column, column_name, contains comma-separated values like &quot;ABC, XYZ, PQR&quot;. Let&#39;s split these values into separate rows for better analysis.</p><pre>SELECT<br>    sm_period,<br>    so,<br>    TRIM(split_value) AS trimmed_value<br>FROM<br>    abc.current<br>CROSS JOIN<br>    UNNEST(SPLIT(column_name, &#39;,&#39;)) AS t(split_value);</pre><p>Explanation:</p><ul><li>We use the SPLIT() function to split column_name by commas, generating an array of substrings.</li><li>UNNEST() transforms this array into separate rows.</li><li>TRIM(split_value) removes any leading or trailing spaces from each value.</li></ul><h3>Aggregating Data for Deeper Analysis</h3><p>Sometimes when we do a ‘group by’ operation it is useful to have the strings in several rows aggregated as well.</p><p>Here is an example of how to do that</p><p>from our data, let’s aggregate data from abc.current and table2.</p><pre>SELECT<br>    t1.sm_period,<br>    t1.so,<br>    ARRAY_AGG(DISTINCT t2.columnX ORDER BY t2.columnX) AS aggregated_values,<br>    SUM(t2.columnY) AS total_amount<br>FROM<br>    abc.current AS t1<br>JOIN<br>    table2 AS t2<br>ON<br>    t1.key_column = t2.key_column<br>GROUP BY<br>    t1.sm_period,<br>    t1.so;</pre><p>Explanation:</p><ul><li>We aggregate data from table2, aggregating values from columnX which contains string values, into an array and summing values from columnY.</li><li>We perform a join between abc.current and table2 based on a common key column.</li><li>Finally, we group the result by sm_period and so from abc.current.</li></ul><h3>Utilizing Common Table Expressions (CTEs)</h3><p>Let’s leverage CTEs to perform a complex data transformation. Suppose we want to extract specific data from another table table2 based on certain conditions.</p><pre>WITH subquery_data AS (<br>    SELECT<br>        column1,<br>        column2,<br>        ...<br>    FROM<br>        table2<br>    WHERE<br>        condition<br>)<br>SELECT<br>    t1.column1,<br>    t1.column2,<br>    ...<br>    t2.columnX,<br>    t2.columnY<br>FROM<br>    abc.current AS t1<br>JOIN<br>    subquery_data AS t2<br>ON<br>    t1.key_column = t2.key_column;</pre><p>Explanation:</p><ul><li>We define a CTE called subquery_data, executing a subquery to select specific data from table2 based on certain conditions.</li><li>We then join the result of the CTE (t2) with our original table abc.current (t1) on a common key column using the JOIN keyword.</li><li>Finally, we select the columns we need from both tables in the SELECT clause.</li></ul><p>Advanced data manipulation techniques in AWS Athena enable you to extract valuable insights from your datasets with ease. By mastering techniques like splitting strings, leveraging CTEs, performing complex joins, and aggregating data, you’ll be well-equipped to tackle diverse data analysis challenges and drive informed decision-making.</p><p>Happy querying!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cc49fad14ca5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Prompt engineering]]></title>
            <link>https://jamespaultg.medium.com/prompt-engineering-for-a-large-language-model-7e1006e6004c?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/7e1006e6004c</guid>
            <category><![CDATA[openai]]></category>
            <category><![CDATA[chatgpt]]></category>
            <category><![CDATA[prompt-engineering]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[llm]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Mon, 05 Jun 2023 08:11:53 GMT</pubDate>
            <atom:updated>2023-10-31T16:44:32.874Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Eiz2foSIJBNc9A0u" /><figcaption>Photo by <a href="https://unsplash.com/@iammottakin?utm_source=medium&amp;utm_medium=referral">Mojahid Mottakin</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h4><a href="http://deeplearning.ai/">Deeplearning.AI</a>, in partnership with OpenAI, presents a course (free for a limited time) on “ChatGPT prompt engineering for developers”</h4><p>Course Highlights:<br>✅ Short and concise curriculum<br>✅ Jupyter notebook for interactive practice with prompts<br>🔗 Enroll now:</p><p><a href="https://www.deeplearning.ai/short-courses/chatgpt-prompt-engineering-for-developers/">ChatGPT Prompt Engineering for Developers — DeepLearning.AI</a></p><p>Here is a quick summary of the key learnings from this course.</p><h3>How to converse with an AI Large Language Model(LLM)?</h3><p>The instructions given to the model is called a <strong>prompt</strong>.</p><p>Two key <strong>principles</strong> when providing a prompt:</p><ol><li>Use delimiters (tick marks, triple backticks). Also helps to avoid prompt injections</li></ol><ul><li>Ask for structured output (HTML, JSON, XML, etc.)</li><li>Check whether conditions are satisfied. Check the assumptions required to do the task.</li><li>Few shot prompting<strong> </strong>(Give successful examples of completed tasks)</li></ul><p>2. Give the model time to think</p><ul><li>Specify the steps<strong> </strong>required to complete a task</li><li>Instruct the model to work out its own solution before rushing to a conclusion</li></ul><h3>Watch out for hallucinations!</h3><p>The large language models are based on generative AI, which means that it tries to generate the next sequence of words based on what it has been trained on. If it has to respond to something which wasn’t part of the training data, it could come up with answers that sound plausible but are not true. In the AI world, this is called hallucination. Be aware of this limitation.</p><h3>Prompt development is iterative.</h3><p>You most likely won’t arrive at the perfect prompt the very first time. You need to see the results of the model and fine-tune your prompt. Keep the key principles in mind.</p><h3>Control the temperature</h3><p>Temperature — this parameter alters the degree of randomness in the response</p><ul><li>Zero temperature; use it when you require predictability.</li><li>Higher temperature, more variations</li></ul><h3>Interesting use cases</h3><p>Asking the model to <strong>generate a marketing product description</strong> from a product fact sheet for a chair, with the writing style suited for the intended audience, keeping a limit on the number of words, and also getting the dimensions in an HTML table</p><pre>prompt = f&quot;&quot;&quot;<br>Your task is to help a marketing team create a <br>description for a retail website of a product based <br>on a technical fact sheet.<br><br>Write a product description based on the information <br>provided in the technical specifications delimited by <br>triple backticks.<br><br>The description is intended for furniture retailers, <br>so should be technical in nature and focus on the <br>materials the product is constructed from.<br><br>At the end of the description, include every 7-character <br>Product ID in the technical specification.<br><br>After the description, include a table that gives the <br>product&#39;s dimensions. The table should have two columns.<br>In the first column include the name of the dimension. <br>In the second column include the measurements in inches only.<br><br>Give the table the title &#39;Product Dimensions&#39;.<br><br>Format everything as HTML that can be used in a website. <br>Place the description in a &lt;div&gt; element.<br><br>Technical specifications: ```{fact_sheet_chair}```<br>&quot;&quot;&quot;<br><br>response = get_completion(prompt)<br>print(response)</pre><p><strong>Summarizing product review</strong> focusing on aspects that are specific for a particular department, like focusing on comments related to pricing.</p><pre>prompt = f&quot;&quot;&quot;<br>Your task is to generate a short summary of a product \<br>review from an ecommerce site to give feedback to the \<br>pricing deparmtment, responsible for determining the \<br>price of the product.  <br><br>Summarize the review below, delimited by triple <br>backticks, in at most 30 words, and focusing on any aspects \<br>that are relevant to the price and perceived value. <br><br>Review: ```{prod_review}```<br>&quot;&quot;&quot;<br><br>response = get_completion(prompt)<br>print(response)</pre><p><strong>Inferring topics </strong>from a given text. We can restrict the model to also choose from a set of topics. Imagine if we are interested to find out from a set of customer reviews which team needs to take action whether the review addresses product, price, shipping</p><p>Furthermore, it can do <strong>translation, spellcheck/Grammar check</strong>, expand a given text and generate customer service emails that are tailored to each customer’s review</p><h3>Chatbot</h3><p>I have experience building chatbots with Google Dialogflow. A few years back, it was one of the easiest ways to build a bot. You need to give example phrases to train the model to identify the intent of the conversation.</p><p>However, with LLMs, building chatbots has become really easy and way faster. The course gives an example of an order bot for a pizza restaurant. The bot collects the order by clarifying options, extras, and sizes and then confirms the order. The menu is given as text within the prompt itself.</p><p>Here is an example of an Insurance claim bot that assists the customer in knowing the status of their claims. I built this bot with Google Dialogflow a few years back, which was one of the easiest ways to build a bot.</p><p>It took me a few days to build the bot then, and now with LLM, the same bot can be built in less than an hour.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UwMKKB5FqLe4WlmefIW-Vg.jpeg" /></figure><p>As you can see,</p><ul><li>You don’t have to define each and every intent</li><li>You can just tell the model the task, and it gets it</li><li>No need to define ‘small talks’; handles out of context conversation gracefully</li><li>Performs validation of the data; it validated the postcode based on the regex I gave</li></ul><p>Hope you are inspired to take up this prompt engineering course. Curious to know what you build, share in the comments.</p><p>Remember, <strong>always use the model responsibly and in a way that helps people</strong>!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7e1006e6004c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[My learning path for Power BI]]></title>
            <link>https://jamespaultg.medium.com/my-learning-path-for-power-bi-cdc51b01fb5c?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/cdc51b01fb5c</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[learning-and-development]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Tue, 27 Dec 2022 11:14:05 GMT</pubDate>
            <atom:updated>2022-12-27T11:14:05.838Z</atom:updated>
            <content:encoded><![CDATA[<p>Power BI is a data visualization tool from Microsoft. It has been positioned as a Leader in the <a href="https://info.microsoft.com/ww-landing-2022-gartner-mq-report-on-bi-and-analytics-platforms.html?lcid=en-us">2022 Gartner Magic Quadrant for Analytics and Business Intelligence Platforms</a>.</p><p>The basics of Power BI are easy to learn in a short period of time. However, to master Power BI, you need to invest time and effort consistently. There are a lot of free resources available for learning Power BI and sometimes it could be difficult to know where to start. I have given here the learning path that worked for me.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ILO8S9RrjkG8WES0dsrpwQ.png" /></figure><h3>Learning the basics of Power BI</h3><p>You can start with the <a href="https://docs.microsoft.com/en-us/learn/certifications/exams/pl-300">Microsoft Learn pathway</a> for Power BI. This gives a good foundation for Power BI. The documentation on Power BI from Microsoft is also quite good.</p><h4><strong>Youtube channels</strong></h4><p>There are a lot of suitable quality materials on Power BI available for free on youtube from experts. It is worth watching any videos from the below-listed channels. These are my go-to resources whenever I need answers to a specific question. Of course, during your Power BI journey, you will also figure out additional valuable resources; please do share them in the comments.</p><p><strong>Curbal by </strong><a href="https://www.linkedin.com/in/ACoAAAE_YHgBAYNul_9Kxqe5X3p_m2jDVP3vwFU"><strong>Ruth Pozuelo Martinez</strong></a></p><p>Power BI monthly feature updates, visualization, DAX</p><p><a href="https://www.youtube.com/@CurbalEN">https://www.youtube.com/@CurbalEN</a></p><p><a href="https://www.youtube.com/@GuyInACube"><strong>Guy in a Cube</strong></a><strong> by </strong><a href="https://www.linkedin.com/in/ACoAAABLRvkBf8m1be1RhvMXYV_FcNcU5trYOdE"><strong>Patrick Leblanc</strong></a><strong> &amp; </strong><a href="https://www.linkedin.com/in/ACoAAAFimqwBfLDwIgUQIgdl7j5xE-oXc1T0fm8"><strong>Adam Saxton</strong></a></p><p><a href="https://www.youtube.com/@GuyInACube">Guy in a Cube</a></p><p>Has videos almost every week with analysis and visualization challenges and solutions.</p><p>Whenever you get an opportunity, you can pick any videos from the above two channels, and there is always something new you will learn that will help you in gaining a strong foundation.</p><p>You probably don’t need the below channels at the start, but these will be beneficial resources when you start advancing your knowledge on Power BI</p><p><strong>Data modeling and DAX — SQL BI by gurus </strong><a href="https://www.linkedin.com/in/ACoAAAATjlEBEZfrtOn4n5_HfQ5nwrAxJhurjOs"><strong>Alberto Ferrari</strong></a><strong> &amp; </strong><a href="https://www.linkedin.com/in/ACoAAAATYAYBlQe9idRcIdaYwDWRQR6z6wx9BvQ"><strong>Marco Russo</strong></a></p><p><a href="https://www.youtube.com/@SQLBI">SQLBI</a></p><p><strong>Power Query BI Gorilla by </strong><a href="https://www.linkedin.com/in/ACoAAAUQS_QBR5uE1mMubB4kuxqJcDJmXrWVfpI"><strong>Rick de Groot</strong></a></p><p><a href="https://www.youtube.com/@BIGorilla">BI Gorilla</a></p><p><strong>Goodly by </strong><a href="https://www.linkedin.com/in/chandeepchhabra/"><strong>Chandeep Chabra</strong></a></p><p>I used this channel extensively to learn about Power Query. Clearly explains how the different functions work. I stumbled upon this channel when I tried to learn list.accumulate and found the explanation clear and easy to understand.</p><p><a href="https://www.youtube.com/@GoodlyChandeep">Goodly</a></p><p><strong>Datasets, Dataflow, Usage metrics Radacad by </strong><a href="https://www.youtube.com/c/Radacad"><strong>Reza Rad</strong></a></p><p><a href="https://www.youtube.com/@RADACAD">https://www.youtube.com/@RADACAD</a></p><p><strong>DAX, Time Intelligence calculations Enterprise DNA by </strong><a href="https://www.linkedin.com/in/ACoAAAPHGb4BEiWUCtHJP_WdMD8G_EVWgmbjzok"><strong>Sam McKay, CFA</strong></a></p><p><a href="https://www.youtube.com/@EnterpriseDNA">https://www.youtube.com/@EnterpriseDNA</a></p><h4>Data visualization challenges</h4><p>One way to apply your knowledge and gain experience are to participate in data visualization challenges. You can also build your portfolio of projects which you can showcase. You also learn a lot from your peers.</p><p>Here are a few that I recommend:</p><ul><li><a href="https://www.makeovermonday.co.uk/">Makeover Monday | A weekly social data project</a></li><li><a href="https://www.enterprisedna.co/challenges/">Enterprise DNA Challenges</a></li><li><a href="https://www.mavenanalytics.io/data-playground">Free Data Sets &amp; Dataset Samples | Maven Analytics</a></li></ul><p>After completing the Microsoft learning path and working with Power BI on various data visualization challenges, you will be confident enough to take the PL-300 exam and get the Microsoft-certified Power BI Data analyst associate title.</p><h3>How to further deepen the knowledge?</h3><p>I will address them in a separate post.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cdc51b01fb5c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The easiest way to get Rstudio Shiny server in an AWS EC2 instance]]></title>
            <link>https://medium.com/data-science/the-easiest-way-to-get-rstudio-shiny-server-in-an-aws-ec2-instance-62ed37cb4aef?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/62ed37cb4aef</guid>
            <category><![CDATA[r]]></category>
            <category><![CDATA[ec2]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[shiny]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Thu, 11 Feb 2021 06:07:49 GMT</pubDate>
            <atom:updated>2021-02-11T12:43:25.269Z</atom:updated>
            <content:encoded><![CDATA[<p>If you are a data scientist who has an R shiny app running locally and you want to share it with the users in your organization, hosting it on a server that the users can access would be a good choice.</p><p>Here we will look at how we can do it in AWS.</p><p>The easiest option to spin up an EC2 instance with R is to use the <strong>Amazon Machine Image(AMI)</strong> for R-studio / Shiny. The first step when we launch an EC2 instance would be to choose an AMI, which is a template with a set of software configurations. <a href="https://www.louisaslett.com/">Louis Aslett</a> is maintaining AMIs for RStudio server. He has done a great job of developing and maintaining this. Thanks to him. We can choose the AMI based on the AWS region we want to launch the instance from here <a href="https://www.louisaslett.com/RStudio_AMI/">https://www.louisaslett.com/RStudio_AMI/</a>.</p><p>I am not explaining here the steps to launch an instance as there are many tutorials you can find on the internet. Once the EC2 instance is launched successfully, navigating to the IP address of the instance would take us directly to RStudio. You should see the below screen.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/598/1*uNqVML-5yLBnCB92wFjWpA.png" /></figure><p>You can log in with <br>- Username: rstudio <br>- Password: &lt;EC2 Instance ID&gt;</p><p><strong>Shiny app<br></strong>Shiny apps can be accessed at /shiny/username <br>For example, with the default user rstudio, the Shiny App will be in /shiny/rstudio</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ardCa7o2htNZ4LwRmwnELQ.png" /></figure><p>We can copy our Shinyapp code to a folder named ShinyApps in the user’s home directory.</p><p>Hope this quick tip was helpful. I find this the easiest way to launch RStudio in AWS for data scientists, who aren’t comfortable with Linux to SSH and install the required software.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=62ed37cb4aef" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/the-easiest-way-to-get-rstudio-shiny-server-in-an-aws-ec2-instance-62ed37cb4aef">The easiest way to get Rstudio Shiny server in an AWS EC2 instance</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to clean up messy data?]]></title>
            <link>https://medium.com/data-science/how-to-clean-up-messy-data-9a8376475c67?source=rss-329ca543a999------2</link>
            <guid isPermaLink="false">https://medium.com/p/9a8376475c67</guid>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[text-analytics]]></category>
            <category><![CDATA[data-cleansing]]></category>
            <dc:creator><![CDATA[James Gnanasekaran]]></dc:creator>
            <pubDate>Mon, 20 Jul 2020 14:23:10 GMT</pubDate>
            <atom:updated>2020-08-27T13:23:02.714Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tIZCqlp5_UUoF65Z2lOXVg.png" /></figure><h4>with OpenRefine</h4><figure><img alt="Philadelphia spelled in 57 different ways" src="https://cdn-images-1.medium.com/max/96/1*IbN4AUtmaZVbs5NTpZVMJg.png" /><figcaption>Image credit: <a href="https://www.linkedin.com/posts/keith-mcnulty_datascientists-datascience-rstats-activity-6686524168160219136-Lxeb">Keith McNulty</a></figcaption></figure><p>Came across a Linkedin post that mentioned a dataset that had the city <strong>Philadelphia spelled in 57(!) different ways</strong>, and asked data scientists on their views to approach this data quality issue. Any connection with 57 varieties of Heinz sauce coming from the same Pennsylvania state? :-)</p><p>I came across two other <a href="https://twitter.com/dataeditor/status/1280278987797942272?s=21">posts</a> which again was talking about this issue, comments ranging from there should be better validation in the application that collects this data to people should learn how to spell. Well, sometimes in your job as a data scientist/data engineer you do come across such data quality issues and you just need to deal with it. Maybe you can implement better controls so that you get better data in the future, but the data that is already collected has to be cleaned if you want to use it. In this post, I will show how to fix this issue with <strong>OpenRefine, a free data wrangling tool</strong>.</p><h4>OpenRefine</h4><p>OpenRefine(formerly known as Google Refine) is a powerful tool that can transform data easily without any coding. And if you are a programmer, you can use GREL(Google Refine Expression Language, similar to Excel formulas) to do some complex data transformations. The interesting aspect about this tool is that it is free, open-source, and can be installed locally on your laptop, and all the data processing is done locally. The <strong>data never leaves your computer</strong>. I have used OpenRefine in data migration projects where the legacy applications had free text fields containing an address, names, etc that needs to be standardized and transformed before it can be migrated to a target system, and it worked very well.</p><h4>Where can we use OpenRefine?</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/222/1*w2f4j3uTEZLtQXX3sYH4SQ.png" /><figcaption>Examples of messy text</figcaption></figure><p>When we have text data that is messy, —like in this example (top entry is the correct one), the same word spelled incorrectly, a mix of lower and upper case characters, some having leading or trailing spaces, white space characters, extended western characters, first name and last name order mixed up and dates are written in different ways — we could use Open Refine to clean it up.</p><p><strong>OpenRefine on the PPP dataset</strong></p><ul><li>Dataset: The data comes from the PPP(Paycheck Protection Plan) in the US, has data on the loans to small businesses during the COVID-19 crisis. Let’s take the <a href="https://www.sba.gov/funding-programs/loans/coronavirus-relief-options/paycheck-protection-program?utm_medium=email&amp;utm_source=govdelivery#section-header-11">dataset for the state of Pennsylvania</a>, for loans below $150K.</li><li>OpenRefine: Download <a href="https://openrefine.org/download.html">OpenRefine v3.3</a>. Once installed run OpenRefine.exe file, which opens up a window in the browser pointing to 127.0.0.1:3333.</li></ul><p><strong>Create a project</strong><br>The tool opens up with the option to create a Project. We can import data from different file formats(JSON, CSV, fixed-width, etc) and sources(locally from our computer as well as directly from the web).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L2UkCFvNWU3ZnY9rY7FOHg.png" /><figcaption>Create Project — Load the data</figcaption></figure><p>OpenRefine reads in the file contents and tries to automatically identify the different columns and headers and shows a preview. We can modify the settings if needed. Once we are happy with the preview, we can click on ‘Create Project’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Fa1d_MlTx7df2VTzRQjoUA.png" /><figcaption>File parsing options</figcaption></figure><p>OpenRefine then loads the data and shows a view similar to a spreadsheet. We can click on the down arrows at the top of the column to see the various data transformations that we can do on the data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EY3Xv6qPBqUlPKXFGN0rGg.png" /><figcaption>Data is loaded into the project</figcaption></figure><p>Let us take a look at the column city. We are interested to see the entries where Philadelphia is spelled incorrectly. To easily view the distribution we could filter the rows that start with ph(assuming that at least the first two characters are spelled correctly :-)). We can use a regular expression ‘^ph’. The character ‘^’ ensures that only the cities that start with ph are shown. This is not strictly necessary, but this helps to limit the variations in entries that we can easily see in the facet window.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*r5rSsW7PUWPwOz9hTy9M7Q.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/626/1*YgJ1lbHDFHukePoDzgJhAw.png" /><figcaption>Text filter</figcaption></figure><p>We then add a text facet, which shows the number of rows per distinct values in the column ‘city’. As you can see there are quite a number of variations in the way Philadelphia is spelled.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4iqMM1EhEK1MH6c4xQ7hPw.png" /></figure><figure><img alt="Philadelphia spelled incorrectly" src="https://cdn-images-1.medium.com/max/142/1*2i6jIYzgpeI0GwYwPdad8Q.png" /><figcaption>Add a Text facet</figcaption></figure><p><strong>Edit the text inline</strong><br>If there is a single entry that needs correction, we can go ahead and directly click on edit, next to the entry and replace the value. This would be fine if we are replacing a few values.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/296/1*FBL5T1oVa5xdZ6fb5fuZSQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/1*_HDeeDm3hpBIynzELbwyQA.png" /><figcaption>Edit a single entry</figcaption></figure><p><strong>Cluster and edit<br></strong>But if we have too many variations that need to be replaced with a single value, we have an option to cluster the entries based on some in-built methods and then edit them at one-go.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/292/1*H3G6_TVwLejpV4NUBElVaw.png" /><figcaption>Cluster and edit</figcaption></figure><p>Clicking on the Cluster button, opens a window with the entries clustered together based on a default method ‘key collision’ and function ‘fingerprint’. We can change the keying function and see the entries clustered differently.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*CVPXtqfblPNG0CEQRW2hGw.png" /><figcaption>Clustering methods and functions</figcaption></figure><p>Once we are happy with the cluster, we could go ahead and provide a new value for these clusters. If ‘key collision’ doesn’t give the desired results, we could try out another method ‘nearest neighbor’. We can also recluster and edit iteratively. We don’t need to know all the inner workings of the functions to use it, though the <a href="https://github.com/OpenRefine/OpenRefine/wiki/Clustering-In-Depth">documentation</a> is an interesting read if you are curious.</p><p>Feel free to play around with the different methods and choose the one that works best for the given data. For this dataset, Levensthein’s distance with a radius 8 gives the best results.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/772/1*Ro4B_jhw1S3asWnNlbRhmA.png" /></figure><p>After a couple of re-clustering, we have corrected all the incorrect entries of Philadephia.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/240/1*xp-fp0ZSpuGKxlVbIwnzVg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/240/1*Om90fbzDccIqiQPeKh92Mg.png" /></figure><p>All the manual <strong>cleaning actions</strong> that we have done so far are <strong>recorded</strong>. We can see them listed under ‘Undo/Redo’ as a sequence of steps. We can go back to any step and see the results exactly at the end of that step. If we are not happy with a particular step, we can just do another cleaning action and that overwrites the step.</p><p>Once we are happy with our results, we could<strong> export the cleaned data</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jkY5zUvsR-pklezbbwRCJg.png" /><figcaption>Export cleaned data in different formats</figcaption></figure><p>To conclude, OpenRefine is an effective data wrangling tool</p><ol><li>You don’t need to be a programmer to use it. It is <strong>like a spreadsheet</strong>, easy to work with.</li><li><strong>Interactive visual specification of data cleaning rules</strong> helps to jointly come up with data correction rules on the fly. Very helpful when you need to get inputs from a subject matter expert from business, who is comfortable looking at data than code.</li><li>All cleaning actions are recorded as JSON script which helps in <strong>reproducibility</strong> and <strong>automation</strong> (will address this specifically in another post)</li></ol><h4>Sounds good for ad-hoc data cleaning, but can we use it in an automated data cleaning pipeline?</h4><p>Sure, you can!.</p><p>OpenRefine has some client libraries that we could use. I will write a post on how to use <strong>Python and OpenRefine</strong> to set up a data cleaning pipeline. Stay tuned…</p><h4>Resources</h4><p>We covered only a small feature of OpenRefine. It has a lot more to offer. The University of <a href="https://guides.library.illinois.edu/openrefine/home">Illinois</a> and <a href="https://libjohn.github.io/openrefine/preamble.html">Duke</a> have good tutorials to get you started.</p><blockquote><strong>The way to get started is to quit talking and start doing — Walt Disney</strong></blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9a8376475c67" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/how-to-clean-up-messy-data-9a8376475c67">How to clean up messy data?</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>