<?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 Alexandre Stamm on Medium]]></title>
        <description><![CDATA[Stories by Alexandre Stamm on Medium]]></description>
        <link>https://medium.com/@alestamm?source=rss-bdc9fb848ad5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*VB-2ppzDDRBVB2kyIPlesA.jpeg</url>
            <title>Stories by Alexandre Stamm on Medium</title>
            <link>https://medium.com/@alestamm?source=rss-bdc9fb848ad5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 23:39:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@alestamm/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[How We Reduced a Large Spark ETL Job Runtime by 80%: A Practical Databricks Optimization Guide]]></title>
            <link>https://medium.com/@alestamm/how-we-reduced-a-large-spark-etl-job-runtime-by-80-a-practical-databricks-optimization-guide-ca2968a7b340?source=rss-bdc9fb848ad5------2</link>
            <guid isPermaLink="false">https://medium.com/p/ca2968a7b340</guid>
            <category><![CDATA[apache-spark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[databricks]]></category>
            <dc:creator><![CDATA[Alexandre Stamm]]></dc:creator>
            <pubDate>Wed, 11 Mar 2026 20:09:45 GMT</pubDate>
            <atom:updated>2026-03-11T20:09:45.313Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8gyksXNY7B76wnl5qqgKvA.png" /></figure><p>Large-scale Spark pipelines rarely fail suddenly. Instead, they degrade slowly as data volume grows, until one day a job that used to run in 30 minutes suddenly takes three hours.</p><p>This article walks through a real-world optimization journey where a heavy Spark ETL pipeline running on <strong>Databricks</strong> was improved by <strong>~80%</strong>, reducing runtime from hours to minutes <strong>without changing business logic or cluster size</strong>.</p><p>Instead of theoretical tuning, this guide focuses on <strong>practical debugging techniques using Spark UI, execution plans, and simple SQL improvements</strong>.</p><p>We’ll cover:</p><p>• Fixing expensive join predicates<br>• Simplifying aggregations<br>• Understanding shuffle spill<br>• Diagnosing data skew<br>• Using salting to fix skewed workloads</p><p>All optimizations described here are broadly applicable to <strong>Spark, Databricks, and large-scale ETL pipelines</strong>.</p><p>This article is also focused on ETL pipeline running in Databricks. I belive that by leveraging Databricks, organizations can not only implement these optimizations effectively but also realize substantial benefits in Total Cost of Ownership (TCO) reduction, increased productivity, and enhanced scalability.</p><p>Note: <strong>For each optimization step described below, we will provide a general runtime improvement benchmark. Consolidated data, covering various table and input sizes, will be presented at the conclusion of this article.</strong></p><h4><strong>The Problem: When Data Growth Breaks Assumptions</strong></h4><p>After a data growth event that roughly doubled the number of rows, a critical ETL step began taking anywhere from 30 minutes to almost 3 hours on large datasets.</p><p>At a high level, the pipeline looked like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D0qt6xRk8BMWIYSvkXmG6A.png" /></figure><p>The <strong>Large Aggregation Step</strong> proved to be the performance bottleneck, characterized by several complex operations:</p><ul><li>Joining extremely large fact tables with numerous lookup tables.</li><li>Executing range joins.</li><li>Grouping the data based on dozens of dimensions.</li><li>Involving costly aggregations, such as COUNT(DISTINCT).</li></ul><p>This is a very common pattern for a team building a star schema, and we find it often in our industry.</p><p><strong>Step 1: Removing Calculations from Join Conditions</strong></p><p>The first red flag was complex logic inside join predicates. Joins are among the most expensive operations in Spark. When join conditions include calculations, casts, or null handling, Spark cannot efficiently use optimized join strategies, loses partitioning benefits, and struggles to estimate cardinality, leading to suboptimal execution plans. Databricks’ highly optimized Spark runtime, coupled with its advanced query optimizer, is designed to mitigate these issues, but fundamental SQL best practices remain crucial.</p><p><strong>Before: Calculations Inside the Join</strong></p><pre>SELECT …<br>FROM fact f<br>JOIN lookup l <br>  ON CAST(IFNULL(f.value / f.rate, 0) AS DECIMAL(10,2)) = l.key</pre><p><strong>After: Pre-Computed Join Keys</strong></p><p>The fix was simple but powerful: Since the source table was being created in the same pipeline, we just moved all calculations upstream. This change alone reduced <strong>query runtime by ~40% in early benchmarks, </strong>using a 300 million row input table (we will show the details about each of these optimizations at the end of the article).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/416/1*hqTliViffsqKjDi6SwMyaQ.png" /></figure><pre>-- Pre-compute join key on upstream table<br>SELECT *,<br>  CAST(IFNULL(value / rate, 0) AS DECIMAL(10,2)) AS join_key<br>FROM fact</pre><pre>-- Join using clean keys<br>SELECT …<br>FROM fact_prepared f<br>JOIN lookup l<br>  ON f.join_key = l.key</pre><p><strong>Step 2: Simplifying COUNT(DISTINCT) with CASE Logic</strong></p><p>The next issue involved COUNT(DISTINCT) combined with CASE WHEN. The calculation was something like this:</p><pre>COUNT(DISTINCT<br>  CASE<br>    WHEN column1 = 0<br>    AND column2 &gt; 1<br>      THEN id_column<br>    ELSE NULL<br>  END<br>) AS column3</pre><p>COUNT(DISTINCT) already forces a shuffle so Spark can group identical values. When you embed CASE logic inside it, Spark must evaluate the CASE statement for every row during aggregation, and more data is shuffled than necessary.</p><p>The solution in this case was pre-labeling the rows by moving the CASE WHEN statement upstream and creating a new column, so in the downstream query we can just count the new column:</p><pre>CASE<br>  WHEN column1 = 0<br>  AND column2 &gt; 1<br>    THEN id_column<br>  ELSE NULL<br>END AS countable_id_column</pre><p>By pre-labeling the rows and then aggregating, we reduced CPU overhead and <strong>improved runtime by another ~30% </strong>(with the same 300 million row dataset as before).</p><p><strong>Step 3: A Cautionary Tale on Broadcasting Small Tables</strong></p><p>A common recommendation is to broadcast small lookup tables. We tried manually forcing broadcast joins, and performance got <strong>four times worse: </strong>going from around 30 minutes to almost 2 hours in our 300 million rows dataset. Modern Spark versions are excellent at deciding when to broadcast, and forcing it can break better execution plans. The lesson: trust the optimizer unless you have clear evidence not to.</p><p><strong>Step 4: Statistics and Z-Ordering + Delta Optimizations</strong></p><p><strong>As a general recommendation,</strong> it’s better to use Unity Catalog managed tables with default settings for all new tables. In Databricks Runtime 13.3 and above, Databricks recommends using clustering for table layout. See <a href="https://docs.databricks.com/aws/en/delta/clustering">Use liquid clustering for tables</a>.</p><p>Databricks also recommends using predictive optimization to automatically run OPTIMIZE and VACUUM for tables. See <a href="https://docs.databricks.com/aws/en/optimizations/predictive-optimization">Predictive optimization for Unity Catalog managed tables.</a></p><p>Since our tables were external (and not managed) at the time of the optimization, we had to do some manual tuning.</p><p>The following configurations are simple to implement — a key advantage of Databricks, which reduces manual effort and increases productivity compared to traditional Spark/Delta workflows. <strong>These configurations improve read speed and minimize small files.</strong></p><p>Furthermore, leveraging up-to-date table statistics is crucial for Spark to select optimal join strategies and execution plans. Finally, Z-ordering physically co-locates related data values, which enables effective data skipping during queries.</p><p>We added the following Spark/Databricks configurations to our notebook:</p><pre>SET spark.databricks.delta.optimizeWrite.enabled = true;<br>SET spark.databricks.delta.autoCompact.enabled = true;</pre><pre>ANALYZE TABLE intermediate_table COMPUTE STATISTICS;<br>OPTIMIZE intermediate_table ZORDER BY (key_col_1, key_col_2);</pre><p>Combined, these steps yielded <strong>another ~10% improvement.</strong></p><p><strong>Step 5 &amp; 6: Understanding Spill and Increasing Partitions</strong></p><p>Despite improvements, some runs were still slow. The Spark UI revealed heavy memory and disk <strong>spilling</strong>, where intermediate shuffle data exceeds executor memory and is written to disk.</p><p>This Memory/Disk Spill means that:</p><ul><li>Intermediate data generated by operations (like sorts, aggregations, joins) exceeds available executor memory.</li><li>Spark writes this excess data to disk to free up RAM for continued processing.</li><li>This disk I/O significantly slows down task execution and job performance.</li></ul><p>One of the quick fixes we can try is increasing spark.sql.shuffle.partitions, because:</p><ul><li>It increases the number of post-shuffle partitions, distributing the data more thinly across more tasks.</li><li>This reduces the amount of data processed by each individual task after the shuffle.</li><li>With less data per task, the intermediate data generated is more likely to fit within the available executor memory, reducing the need to spill to disk.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AKMArVtxQgcjRs2je2t0Bg.png" /></figure><p>Increasing spark.sql.shuffle.partitions from 200 to 600 created smaller data chunks for each task, reducing memory pressure and <strong>improving runtime by another ~30%.</strong></p><p><strong>Step 7 &amp; 8: The Real Culprit — Data Skew and Salting</strong></p><p>Even with more partitions, spilling persisted. The issue was <strong>data skew</strong>: a few keys dominated the dataset, overloading specific tasks.</p><p>Increasing partitions doesn’t completely fix skew, as the “hot” keys still land in the same tasks.</p><p><strong>Fixing Skew with Salting</strong></p><p><strong>Salting</strong> is an advanced but extremely effective technique to combat data skew. It involves adding a random suffix to skewed keys to distribute them across more partitions, followed by a partial and then final aggregation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aAqvV8w-nc5KNsMwxRY7ww.png" /></figure><p>Here’s a code sample on how we can implement salting in our SQL queries. In this case, since we need to perform a COUNT(DISTINCT), and adding a salt key can mess with the count, we circumvent that by using the COLLECT_SET and ARRAY operations.</p><p><strong>Step 1 - Add a Salt to Skewed Keys:</strong></p><pre>SELECT<br>  *,<br>  CASE<br>    WHEN key IN (<br>      &#39;skewed_key_1&#39;,<br>      &#39;skewed_key_2&#39;<br>    )<br>    THEN CONCAT(key, &#39;_&#39;, FLOOR(RAND() * 10))<br>    ELSE key<br>  END AS salted_key<br>FROM source;<br> <br>-- Step 2: Partial Aggregation<br>SELECT<br>  dim1,<br>  dim2,<br>  salted_key,<br>  COLLECT_SET(id) AS partial_ids<br>FROM salted_data<br>GROUP BY dim1, dim2, salted_key;<br> <br>-- Step 3: Final Aggregation (Remove Salt)<br>SELECT<br>  dim1,<br>  dim2,<br>  SIZE(ARRAY_DISTINCT(FLATTEN(COLLECT_LIST(partial_ids)))) AS distinct_count<br>FROM partial_agg<br>GROUP BY dim1, dim2</pre><p>This approach <strong>reduced runtime by ~40% and eliminated disk and memory spill entirely.</strong></p><p><strong>Other approaches worth noting</strong></p><p>There are other possible optimizations that we didn’t try for this exercise, which are:</p><p><strong>Databricks Photon:</strong></p><ul><li>Photon is the next generation engine on the Databricks Lakehouse Platform that provides extremely fast query performance at low cost — from data ingestion, ETL, streaming, data science and interactive queries — directly on your data lake. Photon is compatible with Apache Spark™ APIs, so getting started is as easy as turning it on — no code changes and no lock-in.</li><li><strong>See </strong><a href="https://www.databricks.com/product/photon"><strong>Databricks Photon</strong></a></li></ul><p><strong>Liquid clustering:</strong></p><ul><li>Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER. It simplifies table management and optimizes query performance by automatically organizing data based on clustering keys.</li><li>Unlike traditional partitioning, you can redefine clustering keys without rewriting existing data. This allows your data layout to evolve alongside changing analytic needs. Liquid clustering applies to both streaming tables and materialized views.</li><li><strong>See </strong><a href="https://docs.databricks.com/aws/en/delta/clustering"><strong>Use liquid clustering for tables</strong></a></li></ul><p><strong>Final Results</strong></p><p>The cumulative effect of these optimizations across different client workloads demonstrates the power of a systematic optimization approach on the Databricks platform. The table below provides a detailed breakdown of the improvements observed:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rHJZgEo20dhbFZ1VsnK3gg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K5Idpai5G7fmxWS3O8vwUg.png" /></figure><p>Across all optimizations, we achieved:</p><ul><li><strong>~80% average runtime reduction, which accounts for thousands of dollars saved monthly in job costs</strong></li><li>Same business logic</li><li>Same cluster size</li><li>Much lower cost and higher reliability</li></ul><p><strong>Key Takeaways</strong></p><ul><li>Avoid calculations and casts in join conditions.</li><li>Simplify aggregations before expensive operations.</li><li>Don’t fight Spark’s optimizer without evidence.</li><li>Always inspect spill and skew in Spark UI.</li><li>Salting is one of the most powerful tools for skewed workloads.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ca2968a7b340" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automating reports with Google Sheets API & Jupyter Notebook connection]]></title>
            <link>https://medium.com/@alestamm/automating-reports-with-google-sheets-jupyter-notebook-connection-8f9cfa5e8588?source=rss-bdc9fb848ad5------2</link>
            <guid isPermaLink="false">https://medium.com/p/8f9cfa5e8588</guid>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Alexandre Stamm]]></dc:creator>
            <pubDate>Sat, 26 Nov 2022 16:42:43 GMT</pubDate>
            <atom:updated>2022-11-26T16:43:08.597Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iPchLpOYBd6tqbSD" /><figcaption>Photo by <a href="https://unsplash.com/@homajob?utm_source=medium&amp;utm_medium=referral">Scott Graham</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Hello everyone!</p><p>Today I’m going to talk about how we can integrate a Jupyter Notebook with the Google Sheets API so we can make imports &amp; exports easier, and maybe automate a report that we need to update on a weekly basis, for example.</p><h3>Setting up Google API Credentials</h3><p>To make this connection work, we first need to setup our Google API Credentials. Here’s a step-by-step guide on how to do this:</p><ul><li>If you don’t have a Google cloud account yet, go to <a href="https://console.cloud.google.com/">Google Cloud</a> and create one</li><li>In the Cloud Console, on the project list, select a existing project or create a new one following these steps:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jY0qhBxfQBLOFvwP7t5AXQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/755/1*rce770k8loIHg6BkOz1E0A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/586/1*ZA6G32kfxbZ58NSakyGpAw.png" /></figure><p>After you create your project:</p><ol><li>Navigate to <a href="https://console.cloud.google.com/apis/library">Google API Service</a></li><li>Search for the Google Sheets API in the search bar, and enable it.</li><li>Select Create Credentials (located on the top right of the screen)</li><li>Follow the steps for creating the credentials, selecting application data as the data will you be accessing</li><li>For the “Are you planning to use this API with Compute Engine, Kubernetes Engine, App Engine, or Cloud Functions?” Select “No, I’m not using them”</li><li>Name the Service Account, and assign it the Owner role for your project, then click <strong>Done.</strong></li></ol><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.loom.com%2Fembed%2F78fe1f5a1760496c9a0b46395e98e8fb&amp;display_name=Loom&amp;url=https%3A%2F%2Fwww.loom.com%2Fembed%2F78fe1f5a1760496c9a0b46395e98e8fb&amp;image=https%3A%2F%2Fcdn.loom.com%2Fsessions%2Fthumbnails%2F78fe1f5a1760496c9a0b46395e98e8fb-00001.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=loom" width="1280" height="960" frameborder="0" scrolling="no"><a href="https://medium.com/media/eb34e7c36e175e0737176673f9f7695d/href">https://medium.com/media/eb34e7c36e175e0737176673f9f7695d/href</a></iframe><p>From the <strong>Service Accounts</strong> page, click on the Service Account you just created, and go to the Keys tab:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/857/1*aOR6_LuP_3A0isQ_eTei4Q.png" /></figure><ol><li>Click <strong>Add Key</strong> &gt; <strong>Create new key</strong></li><li>Choose JSON, then click <strong>Create</strong>. The JSON file will download automatically.</li></ol><h3>Connecting our Jupyter Notebook to Google Sheets:</h3><p>Now that we have our Service Account and JSON with our account information, we can load this into our notebook. To do this, we have to follow some steps:</p><ol><li>Install <a href="https://docs.gspread.org/en/v5.7.0/">gspread</a>, that is a Python API for Google Sheets.</li></ol><pre>!pip install gspread</pre><p>2. Now, we import the packages that we will use:</p><pre>import pandas as pd<br>import gspread<br>from google.oauth2 import service_account<br>import json<br>import seaborn as sns</pre><p>3. Import credentials information from our json file (the file should be in the same directory as your Jupyter Notebook):</p><pre>credentials = service_account.Credentials.from_service_account_file(<br>    &#39;google_credentials.json&#39;)</pre><p>One important thing to keep in mind is that by importing our credential data like this, the information will be<strong> saved into a variable</strong>. That means if someone access your notebook, that person can visualize your credentials information (which is not good security-wise). There are a number of ways to avoid a possible “leak”, one of them is using a .env file, which I explained as a part of <a href="https://medium.com/@alestamm/importing-data-from-a-postgresql-database-to-a-pandas-dataframe-5f4bffcd8bb2">this</a> guide.</p><p>4. Define an API scope, assign it to our credentials, and authorize a new client with the credentials:</p><pre>scope = [&#39;https://spreadsheets.google.com/feeds&#39;]<br>creds_scope = credentials.with_scopes(scope)<br>client = gspread.authorize(creds_scope)</pre><p>5. To be able to access our spreadsheet from our Notebook, we have to share it with our Service Account. You can do this by copying the client_email link that is inside our json file and pasting it on the <strong>“Share”</strong> button in our Google Sheet. Remember to assign it the <strong>“Editor”</strong> permission.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nS7iGVx4gluWAP8v7KLIvA.png" /></figure><p>6. Now, we can create a connection using the link of our Google Sheet:</p><pre>sheet = client.open_by_url(https://medium.com/&#39;paste_your_google_sheet_link_here&#39;)</pre><p>7. Then we create a new variable with our worksheet, using it’s name or index:</p><pre># using the worksheet name<br>sheet_export = sheet.worksheet(&quot;export_example&quot;)<br><br># using the worksheet index (starting with 0)<br>sheet_export = sh.get_worksheet(0)</pre><h3>Exporting information from pandas to Google Sheets:</h3><p>Let’s import a sample dataset to test how exporting information from our Notebook to Google Sheets works:</p><pre>penguins = sns.load_dataset(&#39;penguins&#39;)<br>penguins.info()</pre><pre>Data columns (total 7 columns):<br> #   Column             Non-Null Count  Dtype  <br>---  ------             --------------  -----  <br> 0   species            344 non-null    object <br> 1   island             344 non-null    object <br> 2   bill_length_mm     342 non-null    float64<br> 3   bill_depth_mm      342 non-null    float64<br> 4   flipper_length_mm  342 non-null    float64<br> 5   body_mass_g        342 non-null    float64<br> 6   sex                333 non-null    object</pre><p>We can see that there are some nulls in our dataset. Trying to export a dataframe with null values sometimes results in an error, so, for the sake of this example, let’s just drop them:</p><pre>penguins.dropna(inplace=True)</pre><p>Now, if we just and to insert the values in our worksheet, we can use <a href="https://docs.gspread.org/en/v3.7.0/api.html#gspread.models.Worksheet.insert_rows">insert_rows</a>, that takes a list and an index as arguments, and adds multiple rows to the worksheet at the specified index and populates them with values.</p><pre>sheet_export.insert_rows(penguins.values.tolist())<br>sheet_export.insert_rows([penguins.columns.tolist()])</pre><p>Since the default index is 0, it inserts the newest values at the top (that’s why we’re adding the column names after the values).</p><p>If we want to replace the entire worksheet with new values, we can use <a href="https://docs.gspread.org/en/v3.7.0/api.html#gspread.models.Worksheet.update">update</a>:</p><pre>sheet_export.update(<br>    [penguins.columns.tolist()]<br>    + penguins.values.tolist()<br>)</pre><p>Using .update(), the values go in the order presented to the function. That’s why, in this case, we add the column names first.</p><p>And that’s how we do simple export operations using the gspread API!</p><h3>Importing Data from Google Sheets to Jupyter Notebook:</h3><p>Now, let’s try the other way around: importing information from Google Sheets to a Jupyter Notebook!</p><p>First, let’s set a new “sheet” variable with our import tab:</p><pre>sheet_import = sheet.worksheet(&quot;import_example&quot;)</pre><p>Now, we will use the get_all_records() feature to extract data from the worksheet:</p><pre>penguins_records = sheet_import.get_all_records()</pre><p>This will create a dictionary with all the information in the worksheet, and we can use pandas to turn it into a DataFrame:</p><pre>penguins_2 = pd.DataFrame.from_dict(penguins_records)<br>penguins_2.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/615/1*QyNVCaInAZ6urmHs1ceGDg.png" /></figure><p>And that’s it for importing data from Google Sheets!</p><p>There are also plenty other features in the API. You can see them all in the <a href="https://docs.gspread.org/en/v5.7.0/">gspread documentation</a>.</p><h3>Automating your reports</h3><p>Now that we know how to import and export information from Google Sheets into our Jupyter Notebook, we can combine this with a connection to our database (if you want to learn how to do this, check my <a href="https://medium.com/@alestamm/importing-data-from-a-postgresql-database-to-a-pandas-dataframe-5f4bffcd8bb2"><strong>Importing data from a PostgreSQL database to a Pandas DataFrame</strong></a><strong> </strong>guide) and then automate a report we have to update on a daily/weekly/monthly basis, for example. We can import the data, transform it using Python, and export it to our worksheet every time we run our notebook!</p><h3>Conclusion</h3><p>In this tutorial, we learned how to import and export data from Google Sheets into a Jupyter Notebook, by setting up our Google Cloud account and gspread Python API!</p><p>Thanks for reading!</p><h4><strong>Sources:</strong></h4><ul><li><a href="https://docs.gspread.org/en/latest/user-guide.html">Examples of gspread Usage - gspread 5.7.0 documentation</a></li><li><a href="https://docs.gspread.org/en/latest/oauth2.html">Authentication - gspread 5.7.0 documentation</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8f9cfa5e8588" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Useful SQL Window functions for Business/Data Analysts]]></title>
            <link>https://medium.com/@alestamm/useful-sql-window-functions-for-business-data-analysts-32420009ec21?source=rss-bdc9fb848ad5------2</link>
            <guid isPermaLink="false">https://medium.com/p/32420009ec21</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[analysis]]></category>
            <dc:creator><![CDATA[Alexandre Stamm]]></dc:creator>
            <pubDate>Fri, 09 Sep 2022 12:08:45 GMT</pubDate>
            <atom:updated>2022-09-09T14:36:23.187Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8VKYvZ_9ZnOLZbb9" /><figcaption>Photo by <a href="https://unsplash.com/@casparrubin?utm_source=medium&amp;utm_medium=referral">Caspar Camille Rubin</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Today, I’m going to talk about some of the functions that I often use when I’m working with SQL to query information for my analysis and dashboards.</p><p>When using SQL for data analysis, some functions are very useful for dealing with everyday business problems.</p><p>First, some basic functions don’t require much explanation, and most SQL practitioners know how to use them. These functions are aggregate/mathematical functions (like sum, max/min, average, count, ceil), string manipulation functions (like lower, concat), or date manipulation functions (like extract or date_trunc).</p><p>I’m not going to go into detail about these functions since they are very straightforward, and you can learn their syntax by reading the documentation:</p><p><a href="https://www.w3schools.com/sql/sql_ref_mysql.asp">My SQL</a>, <a href="https://www.w3schools.com/sql/sql_ref_sqlserver.asp">SQL Server</a>, <a href="https://docs.snowflake.com/en/">Snowflake</a>, or <a href="https://www.postgresql.org/docs/current/functions.html">PostgreSQL functions</a>. For other DBMS functions, you can look for their documentation on Google.</p><p>There are other types of very useful functions called <strong>Window functions</strong>. I’m going to talk about these functions and their syntax, and also give examples of how to use them.</p><h4>SELECT — OVER Clause:</h4><p>Select — OVER, according to <a href="https://docs.microsoft.com/pt-br/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16">Microsoft documentation</a>, determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set.</p><p>You can combine over() with aggregate functions like sum, avg/max/min, or with functions like rank and lag, which I’ll explain below.</p><h4>row_number(), rank() and dense_rank():</h4><p>These functions are useful when you need to “rank” your results based on a partition (or not). Row_number, as the name says, will enumerate the rows based on the order defined, as will rank and dense_rank, with some differences:</p><p>When using row_number, after ordering and enumerating the rows, it doesn’t matter if the results are “tied” (like when you have two rows with the same values), each row will have a different and increasing number. When using rank(), duplicate values will have the same number but will count toward the total rank, and the rank number will “leap” when it reaches a non-duplicated row, leaving a gap in the rank numbers.</p><p>Lastly, with dense_rank(), duplicated rows will have the same number, but the rank will not “leap” when it reaches the next non-duplicated row, instead, it will continue with the number right next to the last rank number, leaving no gaps in the rank numbers, like the following example:</p><pre>| STATE  | BUSHELS | RANK() | DENSE_RANK()|  ROW_NUMBER () |<br><br>| Kansas |  130    |   1    |      1      |      1         |<br>| Kansas |  120    |   2    |      2      |      2         | <br>| Iowa   |  110    |   3    |      3      |      3         | <br>| Iowa   |  110    |   3    |      3      |      4         |<br>| Iowa   |  100    |   5    |      4      |      5         |</pre><h4>lag () and lead() functions:</h4><p>Lag () window functions are useful when you need to access or perform a calculation based on a previous row, like when doing a Month over Month, WoW, or YoY comparison. Here’s the syntax:</p><pre>LAG (scalar_expression [,offset] [,default]) <br>    OVER ( [ partition_by_clause ] order_by_clause )</pre><p>The first argument is the value to be returned, offset is the number of rows back from the current row from which to obtain a value, and default is the value to return when <em>offset</em> is beyond the scope of the partition.</p><p>Following is an example of calculating a difference in revenue from past years from the Snowflake <a href="https://docs.snowflake.com/en/sql-reference/functions/lag.html">documentation</a>:</p><pre>select emp_id, year, revenue, <br>       revenue - lag(revenue, 1, 0) over (partition by emp_id order by year) as diff_to_prev <br>    from sales <br>    order by emp_id, year;</pre><pre>+--------+------+----------+--------------+<br>| EMP_ID | YEAR |  REVENUE | DIFF_TO_PREV |<br>|--------+------+----------+--------------|<br>|      0 | 2010 |  1000.00 |      1000.00 |<br>|      0 | 2011 |  1500.00 |       500.00 |<br>|      0 | 2012 |   500.00 |     -1000.00 |<br>|      0 | 2013 |   750.00 |       250.00 |<br>|      1 | 2010 | 10000.00 |     10000.00 |<br>|      1 | 2011 | 12500.00 |      2500.00 |<br>|      1 | 2012 | 15000.00 |      2500.00 |<br>|      1 | 2013 | 20000.00 |      5000.00 |<br>|      2 | 2012 |   500.00 |       500.00 |<br>|      2 | 2013 |   800.00 |       300.00 |<br>+--------+------+----------+--------------+</pre><p>Lead is similar to lag(), but you can access a subsequent row instead of accessing previous rows.</p><h4>Qualify</h4><p>When working with modern RDBMS like <a href="https://docs.snowflake.com/en/sql-reference/constructs/qualify.html">Snowflake</a> and <a href="https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-select-qualify.html">Databricks</a>, you can use <em>Qualify</em> to filter out the results of a window function, just like using HAVING for filtering results of group by. The syntax is very simple:</p><pre>SELECT <em>&lt;column_list&gt;</em><br>  FROM <em>&lt;data_source&gt;</em><br>  [GROUP BY ...]<br>  [HAVING ...]<br>  QUALIFY boolean expression<br>  [ ... ]</pre><p>That’s all for today!</p><p>If you reached this far, thank you for reading, and follow me for more articles about SQL, Python, and Data Analytics!</p><h4>Sources:</h4><ul><li><a href="https://docs.snowflake.com/en/">Welcome to Snowflake Documentation - Snowflake Documentation</a></li><li><a href="https://docs.microsoft.com/pt-br/sql/">Documentação do Microsoft SQL - SQL Server</a></li><li><a href="https://docs.databricks.com/">Databricks documentation</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=32420009ec21" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data visualization with Python & Plotly]]></title>
            <link>https://medium.com/@alestamm/data-visualization-with-python-plotly-770bfd7e5854?source=rss-bdc9fb848ad5------2</link>
            <guid isPermaLink="false">https://medium.com/p/770bfd7e5854</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[plotly]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Alexandre Stamm]]></dc:creator>
            <pubDate>Sun, 24 Jul 2022 23:23:07 GMT</pubDate>
            <atom:updated>2023-01-09T20:15:45.389Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*JpZNn_BdJLDyw039" /><figcaption>Photo by <a href="https://unsplash.com/@goumbik?utm_source=medium&amp;utm_medium=referral">Lukas Blazek</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>How to make beautiful charts &amp; dashboards using Plotly</h3><p>Hello everyone!</p><p>Lately, I’ve been using a lot more <a href="https://plotly.com/">Plotly</a> than <a href="https://seaborn.pydata.org/">Seaborn</a> for my Data Visualizations, for a few reasons:</p><p>· Plotly charts are interactive, and you can make dashboards easily;</p><p>· Some things are much simpler to do in Plotly compared to Seaborn, like putting legend numbers in a line plot;</p><p>· Usually when you provide an invalid parameter to a Plotly function/object, it returns a useful error message, with suggestions on what you might have missed;</p><p>· Also, I think the default style of Plotly is better than the default style of Seaborn.</p><p>It’s not like Seaborn doesn’t have its advantages either, we all know how powerful and complete the Seaborn library is for data visualization. But lately, when I just need to plot some simple and beautiful charts, Plotly has been my go-to library.</p><p>That being said, today I’m going to write some general tips &amp; tricks about Plotly, and some examples of what kind of visualizations we can make with this amazing library!</p><p>First, if you would like to learn Plotly more in-depth, from how the figure data works, to publishing a Dashboard app using <a href="https://dash.plotly.com/">Dash</a>, I suggest reading the <a href="https://plotly.com/python/">documentation</a>, which is very well written and complete.</p><p>Now let’s get started!</p><p>First, there are two main ways to plot graphs in Plotly, using <a href="https://plotly.com/python/plotly-express/">Plotly Express</a> or <a href="https://plotly.com/python/graph-objects/">Plotly Go</a> (Graph objects).</p><p>I mostly use Plotly express when I need to plot simple charts, and use Go when I need more customization options.</p><p>Plotting charts using Plotly Express is very simple, here are some examples:</p><h4><a href="https://plotly.com/python/bar-charts/">Bar charts</a>:</h4><p>To plot a bar chart with Plotly Express, first, we import a sample dataframe from the library, and then call the px.bar argument:</p><pre>import plotly.express as px<br>data_canada = px.data.gapminder().query(&quot;country == &#39;Canada&#39;&quot;)<br>fig = px.bar(data_canada, x=&#39;year&#39;, y=&#39;pop&#39;)<br>fig.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/880/1*ue0hT2jri-oeD8BYGRjhFQ.png" /></figure><p>Now, say we want to add legend text inside our bars, it’s as simple as adding the <strong>text_auto=True</strong> argument.</p><pre>import plotly.express as px<br>df = px.data.medals_long()<br><br>fig = px.bar(df, x=&quot;medal&quot;, y=&quot;count&quot;, color=&quot;nation&quot;, text_auto=<strong>True</strong>)<br>fig.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/887/1*yzwrS7mqQjq0reNXlVVjKw.png" /></figure><h4><a href="https://plotly.com/python/line-charts/">Line charts</a>:</h4><p>Just like with our bar charts, we’re going to import a sample df and use px.line to plot our chart:</p><pre>import plotly.express as px<br><br>df = px.data.gapminder().query(&quot;country==&#39;Canada&#39;&quot;)<br>fig = px.line(df, x=&quot;year&quot;, y=&quot;lifeExp&quot;, title=&#39;Life expectancy in Canada&#39;)<br>fig.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/864/1*KYG4a9MlMajULmnJW0qYhA.png" /></figure><p>Now, to show text legends on our line chart, the code is a little different from the bar chart, but it’s also very simple, we just have to specify where Plotly should get our numbers from (in this case, the “year” column), and it’s also good to use update_traces to specify where we want our text to show:</p><pre>import plotly.express as px<br><br>df = px.data.gapminder().query(&quot;country in [&#39;Canada&#39;, &#39;Botswana&#39;]&quot;)<br><br>fig = px.line(df, x=&quot;lifeExp&quot;, y=&quot;gdpPercap&quot;, color=&quot;country&quot;, text=&quot;year&quot;)<br>fig.update_traces(textposition=&quot;bottom right&quot;)<br>fig.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/923/1*PdISTUlo0YaMGxpE5SO5Kw.png" /></figure><p>You can also make all other sorts of charts, like <a href="https://plotly.com/python/histograms/">histograms</a>, <a href="https://plotly.com/python/distplot/">distplots</a>, <a href="https://plotly.com/python/box-plots/">boxplots</a>, <a href="https://plotly.com/python/heatmaps/">heatmaps</a>, <a href="https://plotly.com/python/waterfall-charts/">waterfall</a>… You can find a list of all kinds of charts you can plot <a href="https://plotly.com/python/">here</a>.</p><h4>Using Plotly Graph Objects for more customizable graphics:</h4><p>Now let’s try something harder!</p><p>Say we want to plot a Bar &amp; Line chart, but using a secondary Y axis for our line chart. Here’s where the customization of Plotly Go comes in hand!</p><p>First, let’s create a sample dataframe, with data from visitors in a public park.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8409f19b4a2683eba236cb5fe14fe6c7/href">https://medium.com/media/8409f19b4a2683eba236cb5fe14fe6c7/href</a></iframe><p>Let’s code our chart! I’m going to explain how to do it step-by-step in the code below:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/886a8491594eff9dc5b98fdd6cc77a66/href">https://medium.com/media/886a8491594eff9dc5b98fdd6cc77a66/href</a></iframe><p>Here’s how our chart looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zHkRQe9HrMCqxeee4px0FA.png" /></figure><p>And we’re done!</p><p>Since we did our chart with Plotly, we can also use Dash to turn it into a dashboard/interactive chart!</p><p>There’s a very useful guide on how to deploy your dashboard for free using Heroku that you can find <a href="https://dash.plotly.com/deployment">here</a>.</p><p>That’s it for today! If you liked this guide, please leave your comment and follow me for more Data Visualization tips/tutorials!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=770bfd7e5854" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Checking and cleaning data in Python with Pandas]]></title>
            <link>https://medium.com/@alestamm/checking-and-cleaning-data-in-python-with-pandas-c955f45f0f7?source=rss-bdc9fb848ad5------2</link>
            <guid isPermaLink="false">https://medium.com/p/c955f45f0f7</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Alexandre Stamm]]></dc:creator>
            <pubDate>Sun, 19 Jun 2022 23:33:27 GMT</pubDate>
            <atom:updated>2022-06-20T13:57:02.202Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*N3e_Ft7TrzAVcZp0" /><figcaption>Photo by <a href="https://unsplash.com/@towfiqu999999?utm_source=medium&amp;utm_medium=referral">Towfiqu barbhuiya</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>Hello everyone! In my previous articles, we learned about how to <a href="https://medium.com/@alestamm/how-to-load-data-into-a-pandas-dataframe-2f1a3586e415">load into a Pandas DataFrame</a>, and how to <a href="https://medium.com/@alestamm/importing-data-from-a-postgresql-database-to-a-pandas-dataframe-5f4bffcd8bb2">Import data from a PostgreSQL database to a Pandas DataFrame</a>.</p><p>You may have heard many times that Data scientists spend most of their time cleaning and organizing data. That may change depending on what type of data you work on, but regardless of how messy the data of your company/project is, organizing our data is one of the most important parts of a Data Scientist/Data Analyst job.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hCYX87DYpXJ5pzby" /><figcaption>Photo by <a href="https://unsplash.com/@dav420?utm_source=medium&amp;utm_medium=referral">David Pupaza</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>According to a <a href="https://hbr.org/2016/09/bad-data-costs-the-u-s-3-trillion-per-year">report released by IBM in 2016</a>, the cost of bad data to U.S. businesses and organizations was around 3.1 trillion dollars per year, and a big source of bad data is, guess what? Human error!</p><p>So today we’re going to dive a little deeper into our data and use Pandas and some Python functions to clean and organize our data.</p><p>First, when working on a dataset, before you start cleaning and organizing your data, it’s good practice to get a first look at what we’re dealing with.</p><p>To do this, I generally use the following functions, and I’m going to pass over them quickly since they are really basic:</p><pre>import pandas as pd</pre><pre># Print the first 5 rows (or whatever number you pass inside the parentheses) of the DataFrame<br>df.head()<br>df.tail() # same as .head() but returns the last rows</pre><pre># Summarize the central tendency, dispersion, and shape of a dataset’s distribution, excluding NaN values<br>df.describe()</pre><pre># Checking the dimensionality of the DataFrame<br>df.shape</pre><pre># Checking data types of the DataFrame<br>df.dtypes</pre><pre># Printing a concise summary of a DataFrame<br>df.info()</pre><p>From here, I usually plot some graphics to get a feeling of what we’re dealing with. Graphics are a good way to check the distribution and possible outliers of a dataset. A good library to plot data in python is <a href="https://seaborn.pydata.org/index.html">Seaborn</a>. Seaborn is a Python data visualization library based on <a href="https://matplotlib.org/">matplotlib</a>. It provides a high-level interface for drawing attractive and informative statistical graphics.</p><p>The canonical import of seaborn:</p><pre>import seaborn as sns</pre><p>We can use <a href="https://seaborn.pydata.org/generated/seaborn.histplot.html">sns.histplot()</a> to plot a histogram and check distribution, <a href="https://seaborn.pydata.org/generated/seaborn.scatterplot.html#seaborn.scatterplot">sns.scatterplot() </a>and <a href="https://seaborn.pydata.org/generated/seaborn.lineplot.html#seaborn.lineplot">sns.lineplot</a> to check for statistical relationships, or <a href="https://seaborn.pydata.org/generated/seaborn.boxplot.html#seaborn.boxplot">sns.boxplot()</a>, <a href="https://seaborn.pydata.org/generated/seaborn.barplot.html#seaborn.barplot">sns.barplot()</a>, and <a href="https://seaborn.pydata.org/generated/seaborn.countplot.html#seaborn.countplot">sns.countplot()</a> for categorical variables.</p><p>Ok! After we’re done with all that, we can start cleaning our data and preparing our analysis.</p><p>First, everyone that works as a Data/Business Analyst or Data Scientist knows that a lot of problems when dealing with data are caused by null values.</p><p>Pandas has a lot of useful functions to deal with Na’s values. The most common ones are:</p><p><a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.dropna.html">.dropna()</a>: Remove missing values.</p><p><a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.fillna.html">.filllna()</a>: Fill NA/NaN values using the specified method.</p><p>Using .fillna(), you can pass the value directly to the function:</p><pre>df.fillna(‘None’)<br>or<br>df.fillna(0)<br>or<br>df.fillna(&#39;2022-06-01&#39;)</pre><p>Or you can use forward(ffill) and back fill(bfill) methods:</p><pre># Ffill propagates the last valid observation forward to the next valid one.<br>df.fillna(method=ffill)</pre><pre># Bfill uses the next valid observation to fill the gap.<br>df.fillna(method=”bfill”):</pre><p>After dealing with nulls, I usually check the data types of our dataset so we have every column with its correct type. Sometimes dates and numbers can come as strings, and it’s better to deal with this right at the beginning.</p><p>For dealing with data types, I mostly use the following functions:</p><p><a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.astype.html">.astype()</a>:</p><p>Cast a pandas object to a specified dtype.</p><p><a href="https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html">pd.to_datetime()</a>:</p><p>Convert argument to datetime.</p><p>Now, after dealing with nulls and wrong data types, we usually begin to dive deeper into the data itself. Here we have a lot of options on how to clean/organize our data.</p><p>Sometimes it’s useful when dealing with categorical data to create clusters so we can aggregate our data in a way that makes sense.</p><p>One of these days I was dealing with some data from Google Analytics and had to narrow down our access sources for the analysis. When doing something like this, you can create a custom function and apply it to a column of your DataFrame. Here’s an example:</p><pre>def clustering(x):</pre><pre>   if &#39;google / cpc&#39; in x or &#39;Google / CPC&#39; in x or &#39;adwords&#39; in x:</pre><pre>      return &#39;Google&#39;</pre><pre>   elif &#39;fb&#39; in x or &#39;facebook&#39; in x:</pre><pre>      return &#39;Facebook&#39;</pre><pre>``` Here we&#39;re going to use a list created beforehand and #check our dataframe for instances of our list. Since filtering strings like this is case-sensitive, we use .lower() to convert all strings to lowercase ```</pre><pre>   elif any([k.lower() in x.lower() for k in list]):<br>      return &#39;In list&#39;</pre><pre>   elif &#39;insta&#39; in x or &#39;ig&#39; in x:</pre><pre>      return &#39;Instagram&#39;<br>   else: <br>      return &#39;Others&#39;</pre><p>Now that we have our function, we can use <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html">.apply</a> to apply it to our DataFrame/column:</p><pre>df[&#39;column&#39;] = df[&#39;column&#39;].apply(lambda x: clustering(x))</pre><p>And just like that, our function will go over each value on our column and apply the clustering we defined.</p><p>From here, I usually start organizing, grouping, and feature engineering our data, and I’m going to write about those steps in the near future.</p><p>If you read this far, thank you for your time and I hope I’ve managed to give you some insights on how to start dealing with data before doing your analysis!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*PEbapZA-1ZLZ9dkd" /><figcaption>Photo by <a href="https://unsplash.com/@craft_ear?utm_source=medium&amp;utm_medium=referral">Jan Tinneberg</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c955f45f0f7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to load data into a Pandas DataFrame]]></title>
            <link>https://medium.com/@alestamm/how-to-load-data-into-a-pandas-dataframe-2f1a3586e415?source=rss-bdc9fb848ad5------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f1a3586e415</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Alexandre Stamm]]></dc:creator>
            <pubDate>Thu, 16 Jun 2022 20:33:16 GMT</pubDate>
            <atom:updated>2022-06-16T20:33:16.423Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*X2Bfp-_R-oLBySt1" /><figcaption>Photo by <a href="https://unsplash.com/@fantasyflip?utm_source=medium&amp;utm_medium=referral">Philipp Katzenberger</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>In my next articles, I’m going to write some tips &amp; tricks about Pandas, from basic to advanced!</p><p>I’m going to keep it short and refer to the documentation if you need a more detailed explanation of how things work!</p><p>Today we’re going to start with the basics: How to load data into a Pandas DataFrame!</p><h4><strong>Loading data into a Pandas DataFrame:</strong></h4><p>In Pandas, you can create DataFrames using many different ways, like importing data from CSV, JSON, Excel, HTML, XML, and many other file types, or loading data from a SQL or Google BigQuery database.</p><p>You can check all the functions for loading DataFrames on this page of the Pandas <a href="https://pandas.pydata.org/pandas-docs/stable/reference/io.html">documentation</a>.</p><p>In this tip, I’m going to focus on the 2 main ways I load data into Pandas DataFrames: reading from CSV or connecting directly to an SQL database.</p><h4>Importing data from an csv file:</h4><p>To import data from a csv file use <a href="https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html#:~:text=If%20True%20and%20parse_dates%20is,parsing%20speed%20by%205-10x.">pd.read_csv()</a>:</p><p>If your file is in the same folder as your notebook, the easiest way to use pd.read_csv() is just to pass the filename directly to the function:</p><pre>df = pd.read_csv (‘file_name.csv’)</pre><p>In case your file is not in the same folder as your notebook, you pass the file path (absolute or relative) to the function:</p><pre>#Absolute path:<br>df = pd.read_csv(‘C:/path/to/you/file/file_name.csv’)</pre><pre>#Relative path:<br>df = pd.read_csv(‘…/ file_name.csv’)</pre><p>When passing just the file name to the function, Pandas will infer most of the information it needs, like separators, columns data types, and indexes. But you can also use the function parameters to better deal with the data you’re going to import.</p><p>There are a series of parameters you can pass to <strong>read_csv</strong>, but the ones I use the most are:</p><blockquote><strong>header:</strong> Row number(s) to use as the column names, and the start of the data. The default behavior is to infer the column names.</blockquote><blockquote><strong>sep: </strong>Delimiter to use, default is a comma.</blockquote><blockquote><strong>index_col: </strong>Column(s) to use as the row labels of the DataFrame.</blockquote><blockquote><strong>dtype: </strong>Data type for data or columns, when you want to explicitly inform the dtype of columns when importing,<strong> E.g.</strong> {‘a’: np.float64}.</blockquote><blockquote><strong>na_values: </strong>Additional strings to recognize as NA/NaN. Say you are importing a CSV where there are “None” values, you can pass na_values=[‘None’] and pandas will convert them to NaN when importing.</blockquote><blockquote><strong>parse_dates and infer_datetime_format: </strong>Useful when dealing with date columns. To keep this short I suggest looking at the <a href="https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html#:~:text=If%20True%20and%20parse_dates%20is,parsing%20speed%20by%205%2D10x.">documentation</a> if you have to use these parameters.</blockquote><h4><strong>Importing data from an SQL Database:</strong></h4><p>The two main ways I import data from SQL databases are:</p><p><strong>Importing from PostgreSQL databases:</strong></p><p>Follow my guide for <a href="https://medium.com/@alestamm/importing-data-from-a-postgresql-database-to-a-pandas-dataframe-5f4bffcd8bb2">importing data from a PostgreSQL database to a Pandas DataFrame</a>.</p><p><strong>Importing data from SQLite databases:</strong></p><p>To import data from an SQLite Database, I use a really helpful library called <strong>sqlite3</strong>(<a href="https://docs.python.org/3/library/sqlite3.html#module-sqlite3">docs</a>).</p><p>You can read the documentation and also use the guide I made for <a href="https://medium.com/@alestamm/importing-data-from-a-postgresql-database-to-a-pandas-dataframe-5f4bffcd8bb2">importing data from PostgreSQL database </a>as a reference since the functions are very similar.</p><h4>Aside from importing data, you can also create DataFrames using these functions:</h4><p>Using pd.DataFrame.from_dict() and passing a dictionary:</p><pre>data = {‘col_1’: [3, 2, 1, 0], ‘col_2’: [‘a’, ‘b’, ‘c’, ‘d’]}<br>pd.DataFrame.from_dict(data)</pre><p>If you are not running your notebook on the cloud (like on <a href="https://colab.research.google.com/notebooks/intro.ipynb?hl=pt_BR">Google Colab</a>), you can read the data directly from your clipboard using <strong>pd.read_clipboard().</strong></p><p>You can also create an DataFrame from a list or numpy array using <strong>pd.DataFrame():</strong></p><pre>pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])</pre><pre>or</pre><pre>pd.DataFrame(list)</pre><p>That’s all for today!</p><p>Follow me to read my upcoming tips for working with Pandas!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f1a3586e415" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Importing data from a PostgreSQL database to a Pandas DataFrame]]></title>
            <link>https://medium.com/@alestamm/importing-data-from-a-postgresql-database-to-a-pandas-dataframe-5f4bffcd8bb2?source=rss-bdc9fb848ad5------2</link>
            <guid isPermaLink="false">https://medium.com/p/5f4bffcd8bb2</guid>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[pandas-dataframe]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Alexandre Stamm]]></dc:creator>
            <pubDate>Sat, 11 Jun 2022 20:47:21 GMT</pubDate>
            <atom:updated>2022-08-29T23:46:58.927Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vE2qMiigkkMGdNf1vqNHbw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@purzlbaum">Claudio Schwarz</a> on <a href="https://unsplash.com/">unsplash</a></figcaption></figure><h3>In this article, we’ll go over how to create a pandas DataFrame using a simple connection and query to fetch data from a PostgreSQL database that requires authentication.</h3><p>Most people that work with data know that SQL is great for working with tabular data.</p><p>Depending on what company you are working for, you may not have access to no-code BI tools like Tableau or PowerBI. That means that when doing your analysis, you will probably have to perform all transformations &amp; aggregations inside your DBMS and export data to a .csv file and do the visualizations in Excel or Google Sheets. But if you know Python, you’ll probably want to work with the dataset on Jupyter notebook with Pandas, right?</p><p>To work with PostgreSQL databases in Python I use <a href="https://pypi.org/project/psycopg2/"><strong>psycopg2</strong></a>, which is the most popular PostgreSQL database adapter for Python.</p><p>The documentation for psycopg2 is found here: <a href="https://www.psycopg.org/docs/">https://www.psycopg.org/docs/</a></p><h3>Installing psycopg2 and connecting to the database</h3><p>To use psycopg2, we first need to install it:</p><pre>pip install psycopg2</pre><p>Then, we need to connect to the database using the connect() function (<a href="https://www.psycopg.org/docs/connection.html">docs</a>):</p><pre>conn = psycopg2.connect(“dbname=test user=postgres password=secret”)</pre><p>The basic connection parameters are:</p><pre><em>· dbname — the database name</em></pre><pre><em>· user — user name used to authenticate</em></pre><pre><em>· password — password used to authenticate</em></pre><pre><em>· host — database host address</em></pre><pre><em>· port — connection port number (defaults to 5432 if not provided)</em></pre><p>After creating the connection, we need to open a cursor to perform database operations (<a href="https://www.psycopg.org/docs/connection.html#connection.cursor">docs</a>):</p><pre><strong>cur = conn.cursor()</strong></pre><p>Then we need to execute a command using the cursor. We can do all sorts of commands (<strong>CREATE TABLE, INSERT INTO, SELECT…</strong>). Here we are going to focus on using the cursor to fetch data:</p><pre><strong>cur.execute(&quot;SELECT * FROM test;&quot;):</strong> Execute the query;</pre><pre><strong>cur.fetchone()</strong>: Fetch the next row of a query result set, returning a single tuple, or None when no more data is available;</pre><pre><strong>cur.fetchmany([size=cursor.arraysize]):</strong> Fetch the next set of rows of a query result, returning a list of tuples.</pre><pre><strong>cur.fetchall():</strong> Fetch all (remaining) rows of a query result, returning them as a list of tuples</pre><p>If any changes were made to the database we need to commit:</p><pre>conn.commit()</pre><p>When we are done, we need to close the communication with the database:</p><pre>cur.close()</pre><pre>conn.close()</pre><h3>Wrapping all this up in a function for convenience:</h3><p>First, let’s say we want to connect to our work database and it requires authentication. To avoid writing our credentials directly in our notebook and exposing them if we need to upload our code into GitHub or share our notebook with a colleague after our analysis is done, we can create a dotenv file with our credentials, and load it into our connect function. Then we can add our dotenv file to gitignore and also directly share our notebook without having our credentials leaked.</p><p>Here’s how to do it:</p><p><strong>Create a .env file and write our credentials on it:</strong></p><blockquote>Since I’m using Linux, I use a simple “touch .env” command to create the file. On other OS, just create the file whichever way is easier for you.</blockquote><p>Then, open the file and write our credentials:</p><pre>DB_USERNAME=name</pre><pre>DB_PASSWORD=password</pre><pre>DB_PATH=path_to_database(if it’s on AWS for example, you can type in the address like: yourcompany-database-address.us-east.rds.amazonaws.com)</pre><pre>DB_NAME=db_name</pre><p>Save and close the file.</p><p>Then, we are going to use the dotenv python library to load our credentials:</p><pre>pip install python-dotenv<br>from dotenv import load_dotenv<br>load_dotenv() # take environment variables from .env.</pre><p>Now, for the function I use to connect to the database:</p><pre>import psycopg2</pre><pre>import os</pre><pre>import sys</pre><pre>def connect():<br><br>   “”” Connect to database “””</pre><pre>   conn = None</pre><pre>   try:</pre><pre>      print(‘Connecting…’)</pre><pre>      conn = psycopg2.connect(</pre><pre>                   host=os.environ[‘DB_PATH’],</pre><pre>                   database=os.environ[‘DB_NAME’],</pre><pre>                   user=os.environ[‘DB_USERNAME’],</pre><pre>                   password=os.environ[‘DB_PASSWORD’])</pre><pre>    except (Exception, psycopg2.DatabaseError) as error:</pre><pre>       print(error)</pre><pre>       sys.exit(1)</pre><pre>   print(“All good, Connection successful!”)</pre><pre>   return conn</pre><p>And now we write a function to create a pandas DataFrame using a SELECT query:</p><pre>def sql_to_dataframe(conn, query, column_names):</pre><pre>   “”” <br>   Import data from a PostgreSQL database using a SELECT query <br>   “””</pre><pre>   cursor = conn.cursor()</pre><pre>   try:</pre><pre>      cursor.execute(query)</pre><pre>   except (Exception, psycopg2.DatabaseError) as error:</pre><pre>      print(“Error: %s” % error)</pre><pre>   cursor.close()</pre><pre>   return 1</pre><pre>   # The execute returns a list of tuples:</pre><pre>   tuples_list = cursor.fetchall()</pre><pre>   cursor.close()</pre><pre>   # Now we need to transform the list into a pandas DataFrame:</pre><pre>   df = pd.DataFrame(tuples_list, columns=column_names)</pre><pre>   return df</pre><p>For better convenience, I also recommend writing our functions into a .py file so we can reuse it whenever we want.</p><h3>Loading information from the database as a pandas DataFrame</h3><p>Now that we have everything we need, let’s finally put it all together and load our DataFrame from the database in our Jupyter notebook. We’re going to do that by importing Pandas, psycopg2, and our custom-made function for connecting and loading the data. Here’s the whole code:</p><pre>#imports<br>import pandas as pd<br>import NumPy as np<br>import os<br>import psycopg2<br>from dotenv import load_dotenv<br>from functions import sql_to_dataframe, connect<br>load_dotenv()</pre><pre><em>#creating a query variable to store our query to pass into the function</em></pre><pre>query = “”” SELECT column1, <br>                   column2, <br>                   column3 <br>            FROM database<br>        ”””<br><em>#creating a list with columns names to pass into the function</em></pre><pre>column_names = [‘column1’,‘column2’, ‘column3’]</pre><pre><em>#opening the connection</em></pre><pre>conn = connect()</pre><pre><em>#loading our dataframe</em></pre><pre>df = sql_to_dataframe(conn, query, column_names)</pre><pre><em>#closing the connection</em></pre><pre>conn.close()</pre><pre># Let’s see if we loaded the df successfully</pre><pre>df.head()</pre><h3>Conclusion</h3><p>Finally, we’re done!</p><p>In this tutorial, you learned how to import data from a PostgreSQL database to a pandas DataFrame, while also keeping your credentials safe and writing a useful function that you can reuse in the future!</p><p>Thanks for reading!</p><h3>Sources:</h3><p><a href="https://www.psycopg.org/docs/connection.html#connection.cursor">https://www.psycopg.org/docs/connection.html</a></p><p><a href="https://www.postgresqltutorial.com/postgresql-python/connect/">https://www.postgresqltutorial.com/postgresql-python/connect/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5f4bffcd8bb2" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>