<?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 Sumanth Munnangi on Medium]]></title>
        <description><![CDATA[Stories by Sumanth Munnangi on Medium]]></description>
        <link>https://medium.com/@sumanthmunnangi?source=rss-66a0cc5e6241------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*YJNhVJRtk3E4ZjNKrCjGHw.png</url>
            <title>Stories by Sumanth Munnangi on Medium</title>
            <link>https://medium.com/@sumanthmunnangi?source=rss-66a0cc5e6241------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 12:28:41 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sumanthmunnangi/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[A brief introduction to PySpark with Databricks | Big Data]]></title>
            <link>https://blog.devgenius.io/a-brief-introduction-to-pyspark-with-databricks-big-data-1b16300e9d12?source=rss-66a0cc5e6241------2</link>
            <guid isPermaLink="false">https://medium.com/p/1b16300e9d12</guid>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Sumanth Munnangi]]></dc:creator>
            <pubDate>Sat, 21 Jan 2023 15:15:23 GMT</pubDate>
            <atom:updated>2023-03-15T07:21:29.323Z</atom:updated>
            <content:encoded><![CDATA[<p>When you use python for ETL, all your data must fit on your PC’s internal memory. Have you ever wondered what happens when you have lots of data and can’t process all that on your RAM? You can try reading smaller data chunks and processing them sequentially as a workaround. However, that takes a lot of time and sometimes is super inefficient; imagine trying to perform a group-by to get a maximum value for each group this way.</p><p>This article will introduce Apache Spark, an open-source analytics engine that helps process data faster in a distributed fashion. We will primarily focus on PySpark, a python API to Apache Spark.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f0L4_m6w4ZsrIT-pOxR0oQ.jpeg" /><figcaption>While we get to it, do enjoy a picture of Bixby Bridge, CA 😃</figcaption></figure><p>I am using the databricks community edition, and I strongly encourage you to <a href="https://community.cloud.databricks.com/">signup</a> for it so that you can follow along. If you prefer to use google collab, please pip-install PySpark (you should be good with a pip install). I strongly discourage you from installing spark locally, but if you absolutely need to — it is out of the scope of this article.</p><pre># to install PySpark on Google Collab, run the following command<br>!pip install pyspark</pre><blockquote>Note: If your data is small and can fit on the RAM, you can always use python. However, the Spark data frame assures fault tolerance, while the pandas data frame doesn’t. I will cover a few details necessary to get the context of big data and distributed computing later in the article; let’s not worry about all that for now.</blockquote><p>Once you sign up for the databricks community edition, you can create a cluster and create a new notebook. These notebooks are similar to the jupyter notebooks. All you need to do is attach a cluster to the notebook.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/1*fa849d-pC7XVkQGRRRjEZw.png" /><figcaption>Create a new databricks cluster</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/450/1*FsXF3awCkVghLMFkIkiNjQ.png" /><figcaption>Create a notebook in your workspace</figcaption></figure><p>Let’s get some data to the databricks environment. The data will eventually be stored in DBFS (Databricks File System). Let’s use a Kaggle dataset, <a href="https://www.kaggle.com/competitions/amex-default-prediction/data">Amex — Default Prediction</a> <a href="https://www.kaggle.com/datasets/odins0n/amex-parquet">data</a>. The data is in <a href="https://parquet.apache.org/">parquet</a> format — a columnar data file format that works wonders with distributed computing. Let’s not get into details here, but know that you are working on one of the best big-data file formats :).</p><p>Let’s install Kaggle and use its API to download the data needed. You will need your Kaggle username and key to access the API from any terminal. Please create the credentials from your account settings.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*21z6aPtlxETzEhGvh-CWbQ.png" /><figcaption>Create your Kaggle API Token</figcaption></figure><pre>%sh pip install kaggle<br><br>%sh<br>export KAGGLE_USERNAME= &lt;username&gt;<br>export KAGGLE_KEY= &lt;your_kaggle_key&gt;</pre><pre>kaggle datasets list -s &quot;Amex Competition Data in Parquet Format&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*flqotnauJMlWGByOH-t4VQ.png" /><figcaption>The above code results in the following output; let’s download the first datasets</figcaption></figure><pre>kaggle datasets download -d &quot;odins0n/AMEX-parquet&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/896/1*LFqu-zS9jBYMMNq_RIChPw.png" /><figcaption>You should see the data getting downloaded to your databricks driver folder</figcaption></figure><p>Since the data is in your driver location, we should move it to DBFS FileStore to read/write and process it. Please follow the below code that does the same.</p><pre># unzip the data - make sure you that before copying the data to filestore<br>%sh <br>unzip amex-parquet.zip </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/496/1*j3WWVHKKgpkgWoXKPLzW4w.png" /><figcaption>Unzipping the data downloaded</figcaption></figure><pre># Make a directory in FileStore<br>dbutils.fs.mkdirs(&quot;dbfs:/FileStore/amexdata/&quot;)<br><br># Copy relevant data from the driver folder to FileStore<br>dbutils.fs.cp(&quot;file:/databricks/driver/train_data.parquet&quot;,&quot;dbfs:/FileStore/amexdata/&quot;)<br>dbutils.fs.cp(&quot;file:/databricks/driver/test_data.parquet&quot;,&quot;dbfs:/FileStore/amexdata/&quot;)<br><br># Clean the driver folder<br>dbutils.fs.rm(&quot;file:/databricks/driver/amex-parquet.zip&quot;)<br>dbutils.fs.rm(&quot;file:/databricks/driver/test_data.parquet&quot;)<br>dbutils.fs.rm(&quot;file:/databricks/driver/train_data.parquet&quot;)</pre><blockquote>dbutils are databricks utilities — They help you perform basic tasks on databricks. They are super convenient and are extensively used to automate and parameterize code/notebooks. For example, you can loop through a root folder and transform all the data in the subfolders.</blockquote><blockquote>Using the utils, you can also “mount” your databricks to AWS S3 and Azure ADLS (Blob). More on this <a href="https://docs.databricks.com/dev-tools/databricks-utils.html">here</a>.</blockquote><p>Finally, we have the data we need under <em>dbfs:/FileStore/amexdata/. </em>So, let’s get started with some super basic transformations using PySpark.</p><p>Read data and describe it — this will run for a while, given the data size. The data read command itself won’t take much time — more on it later.</p><pre>train_df = spark.read.parquet(&quot;dbfs:/FileStore/amexdata/train_data.parquet&quot;)<br><br># description of the dataset -&gt; this is not a good practice :(<br># If your data is huge, you don&#39;t want to describe it. Instead, work on a sample.  <br><br>train_df.describe().display()<br><br># .display() works on Databricks;if you are on collab, you can try .show() <br><br>train_df.show(10)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/975/1*aQOaE_w6GlbZlfVe5La6iw.png" /><figcaption>The summary of the Spark dataframe -&gt; .describe()</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/975/1*nFhMzlXbHv0nxEAF6W9l8g.png" /><figcaption>.show() results in the above output; not very reader-friendly</figcaption></figure><pre>train_df.printSchema()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/975/1*eaaR8vN1xbBRdWUrP-Sv4A.png" /><figcaption>printSchema() results in the schema of the table in the format above</figcaption></figure><p><strong>Selecting a subset of columns from the dataframe —</strong> There are a few ways to select the relevant columns using “.select()”; let’s go through each of them.</p><pre># using .select()<br><br>train_df.select([&#39;customer_ID&#39;,&#39;P_2&#39;]).show(10)<br><br># using a list <br><br>list_of_cols = [&quot;customer_ID&quot;, &quot;P_2&quot;]<br><br>train_df[list_of_cols].show(10)<br><br># using .columns, similar to pandas<br><br>list_of_cols = train_df.columns[:3]<br><br>train_df[list_of_cols].show(10)</pre><p>All the above methods are similar, and I prefer using .select with a predefined list to modularize the code better. A final party trick that is super useful is <strong>colRegex()</strong>. When you have a thousand features, colRegex helps you select the relevant features using Regex operations; how convenient, especially while dealing with high dimensional data.</p><pre># select all columns that start with D_ and have two digits<br><br>train_df.select(train_df.colRegex(&quot;`[D_]{2}[\d]{2}`&quot;)).columns</pre><p><strong>Renaming columns — </strong>There are a couple of ways you can rename the columns. Let’s take a closer look at the <strong>spark execution plan</strong> here to get some context on why Spark is soo good.</p><blockquote>Note: Let’s do some random stuff from now on, focusing on the transformations and how Spark executes them. Don’t worry about the data, insights, or why we do what we do.</blockquote><p>We use .withColumnRenamed(current_name, new_name) to rename columns</p><pre>train_df = train_df.withColumnRenamed(&quot;Customer_ID&quot;, &quot;User_ID&quot;)\<br>.withColumnRenamed(&quot;S_2&quot;, &quot;S_new_2&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/465/1*rANEARKkyEmZ-Mk8TpFuLA.png" /><figcaption>The Schema is updated with new column names</figcaption></figure><p>You can rename many columns at the same time in two methods toDF and select</p><pre># Method 1<br><br># new temporary table to create some features<br>temp_train_df = train_df.select([&quot;User_ID&quot;,&quot;S_new_2&quot;,&quot;P_2&quot;,&quot;B_1&quot;])<br><br># you can replace all columns at a time<br>new_columns = (&#39;User_ID&#39;, &#39;S_new_2&#39;, &#39;P_new_2&#39;, &#39;B_new_1&#39;)<br><br># use toDF to achieve this<br>temp_train_df = temp_train_df.toDF(*new_columns)</pre><pre># Method 2<br><br>new_new_columns = (&#39;Primary_key&#39;, &#39;S_old_2&#39;, &#39;P_old_2&#39;,&#39;B_old_1&#39;)<br><br>cols_map = dict(zip(temp_train_df.columns, new_new_columns))<br><br>temp_train_df = temp_train_df.select(*[col(c).alias(cols_map.get(c, c)) for c in temp_train_df.columns])</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/466/1*OeRkRtuc1aGytX8Qn6YEBQ.png" /><figcaption>The final column names</figcaption></figure><p><strong>Execution plan</strong></p><p>We did a few things, so let’s trace back our operations. Let’s focus on the below renames and look at the spark execution plan.</p><ol><li>Customer_ID -&gt; User_ID -&gt; Primary Key</li><li>S_2 -&gt; S_new_2 -&gt; S_old_2</li></ol><pre>temp_train_df.explain()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wSajeDEz39RqLEvnp9R7gw.png" /><figcaption>Spark’s execution plan :) — Spend some time staring at it</figcaption></figure><p>This is where all the fun stuff is; look closely, and you will see only four columns are read from train_data.parquet table in DBFS. Moreover, the columns are renamed from <strong>Customer_ID directly to Primary_key</strong>. Does the syntax look familiar? Doesn’t it look like SQL?</p><blockquote>ReadSchema: struct&lt;customer_ID:string,S_2:string,P_2:float,B_1:float&gt;</blockquote><p>This is possible because our data is in parquet format (columnar — can slice through columns just by filtering through rows — confusing ? look at the execution plan closely; You will see ColumnarToRow — makes sense?)</p><p>It doesn’t even matter which method we use to rename the columns. Spark is smart enough to build the most optimal plan and executes it without you even realizing it 😃.</p><p>Okay, let’s move on for now,</p><p><strong>Adding new columns — </strong>You can add columns using withColumn(name, formula). This should be easy to follow. But before that, we import some functions to help us transform data.</p><pre>from pyspark.sql.functions import (col, lit, substring, to_date)<br><br>from pyspark.sql.types import IntegerType</pre><pre># getting Day string and converting date string to date format<br><br>temp_train_df = temp_train_df.withColumn(&quot;Day&quot;, substring(col(&quot;S_old_2&quot;),9,2))\<br>.withColumn(&quot;S_old_2&quot;, to_date(col(&quot;S_old_2&quot;),&quot;yyyy-mm-dd&quot;))<br><br># Create a new column Integer_Day from Day<br>temp_train_df = temp_train_df.withColumn(&quot;Integer_Day&quot;, col(&quot;Day&quot;).cast(IntegerType()))<br><br># Let&#39;s add two columns are create a new column <br>temp_train_df = temp_train_df.withColumn(&quot;R_S_new&quot;, col(&quot;B_old_1&quot;) + col(&quot;P_old_2&quot;))<br><br># Cast string as date type                              <br>train_df = train_df.withColumn(&quot;S_new_2&quot;, to_date(col(&quot;S_new_2&quot;),&quot;yyyy-mm-dd&quot;))</pre><ol><li>We extracted a substring from the date.</li><li>Converted a string date to a date format</li><li>Cast a string as an integer type</li><li>Created a new column using athematic operations on two existing columns</li></ol><pre>temp_train_df.printSchema()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/472/1*0spcy1GrQx2bTlJFH4qJfg.png" /><figcaption>All the columns are created.</figcaption></figure><pre>grouped_df = temp_train_df.select([&quot;Day&quot;,&quot;P_old_2&quot;,&quot;B_old_1&quot;])\<br>                          .groupBy([&#39;Day&#39;]).agg({&quot;P_old_2&quot;:&quot;max&quot;,&quot;B_old_1&quot;:&quot;sum&quot;})<br><br>grouped_df.show(5)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/395/1*OjB322qN1xdezUh36bFIVg.png" /></figure><p><strong>Let’s get back to how great Spark is.</strong> First, all the transformations are optimized (like we saw above) and stored in DAGs (directed acyclic graphs), which are stages in a Spark job. They help Spark manage the workloads in a distributed manner accounting for memory usage and other resource constraints.</p><p>For this to work, the execution should not happen as we run the code. Instead, Spark should remember what is asked of it and wait for an “<strong>action</strong>” to be called. Then it generates a plan (builds DAGs) and executes them. So Spark, by design, is Lazy, resilient, and fault tolerant. It can trace back the steps and process the data again in case of a job failure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/1*M5lZoeblYYnybVJuUUV0sQ.png" /><figcaption>The DAG’s flow for the above groupBy</figcaption></figure><p><strong>Joins with PySpark — </strong>Joins are your best bet in PySpark. It will help if you think like a SQL engineer. Just like in SQL, most of your data wrangling can be solved by joins. But since data is huge, there are a few advanced concepts you need to keep in mind before blindly joining or grouping your data — Let’s keep it simple for now.</p><pre># prepare data for a join <br>train_df = train_df.withColumnRenamed(&quot;User_ID&quot;, &quot;Primary_key&quot;).withColumnRenamed(&quot;S_new_2&quot;, &quot;S_old_2&quot;)<br><br># I prefer to rename the columns to remove duplication <br><br>feature_df = train_df.select(*[&quot;Primary_key&quot;,&quot;S_old_2&quot;,&quot;D_39&quot;,&quot;D_41&quot;])<br><br># Lets join -&gt; pretty straight forward <br><br>temp_train_df = temp_train_df.join(feature_df, on=[&#39;Primary_key&#39;,&#39;S_old_2&#39;], how= &quot;left&quot;)<br><br>temp_train_df.show(5)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ega2SjkPfmNyS9lqX5fRRw.png" /><figcaption>The final result after the join :)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*NZBtwoEcln4di9QlVuzONg.png" /><figcaption>The join doesn’t look very simple; because everything is executed from the beginning.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*31JNU83WDSnjnmQMHka7Zw.png" /><figcaption>Feel free to explore the plan — There are a few steps that are not trivial and are out of the scope of this article :)</figcaption></figure><p>We have seen how Spark can boost performance by “planning better” and enabling distributed computing (not demonstrated in the article). While everyone can learn to code in PySpark, it is crucial to understand the Spark architecture and bottlenecks while dealing with large datasets.</p><p>A few basic concepts to be covered in upcoming articles</p><ol><li>Filter, where</li><li>Distinct, dropDuplicates</li><li>fill, fillna</li><li>when otherwise (just like the case when in SQL)</li><li>orderBy, sort (Similar to SQL)</li><li>Union, unionAll (Similar to SQL)</li><li>explode, split, array, and array_contains</li><li>collect, sample</li></ol><p>A few advanced topics that are not covered in the article but are super important (may cover in a different article)</p><ol><li>Window Functions</li><li>map(), flatMap()</li><li>anti joins and cross joins — use cases</li><li>Partitions and data shuffles</li><li>Broadcast joins</li><li>Repartitioning and Coalesce (not the same as the one in SQL 😄)</li><li>UDFs (User Defined Functions)</li></ol><p>I hope this article helped set the most basic understanding of Spark, more so on PySpark. In the coming articles, I will focus on deep-diving into Spark’s architecture and explore a few tricks on Databricks. Until next time, happy learning, and have a nice day 😃.</p><p>Feel free to connect with me on <a href="https://www.linkedin.com/in/sumanth-munnangi/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1b16300e9d12" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/a-brief-introduction-to-pyspark-with-databricks-big-data-1b16300e9d12">A brief introduction to PySpark with Databricks | Big Data</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[iloc and loc in Pandas | Data wrangling with python]]></title>
            <link>https://sumanthmunnangi.medium.com/iloc-and-loc-in-pandas-data-wrangling-with-python-c0aee3dac422?source=rss-66a0cc5e6241------2</link>
            <guid isPermaLink="false">https://medium.com/p/c0aee3dac422</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-wrangling]]></category>
            <dc:creator><![CDATA[Sumanth Munnangi]]></dc:creator>
            <pubDate>Thu, 12 Jan 2023 09:33:31 GMT</pubDate>
            <atom:updated>2023-01-12T09:33:31.563Z</atom:updated>
            <content:encoded><![CDATA[<p>If you work extensively with data, you would have realized you spend most of the time transforming and mapping data to the ‘right’ format. Of course, you can do all that in SQL. However, the pandas library for Python provides some of the most powerful features to play around with data.</p><p>This article covers the iloc and loc that use indexing to slice, filter, and update any panda DataFrame. I am using the Super Store Sales data set that should be available <a href="https://community.tableau.com/s/news/a0A8b00002HfTTbEAN/where-can-i-find-superstore-sales">here</a>. Of course, I am assuming you already have pandas installed :).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZSlZpAjGcrm3J4E0XxdnGg.jpeg" /><figcaption>Before we get to all that, enjoy a picture of Santa Monica Pier, CA 😄</figcaption></figure><p>Let’s import all the packages —</p><pre>import pandas as pd <br>import NumPy as np <br>import xlrd # to read excel files using pandas</pre><pre>sample_store_orders = pd.read_excel(&quot;(US) Sample - Superstore.xlsx&quot;, <br>                                    sheet_name= &quot;Orders&quot;)<br><br># let&#39;s confirm the data frame :)<br><br>sample_store_orders.head(5)</pre><p><strong>Selecting a subset of columns</strong></p><pre># let&#39;s look at all the column names<br><br>sample_store_orders.columns </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/887/1*Sk8ZqBj9cuHdwp0nBG5Qhw.png" /><figcaption>All the columns in the Super Store data frame</figcaption></figure><p>Let’s assume we need to look at the sales per day — so we must select two columns before aggregating the data.</p><p>We can do that by selecting the columns from the data frame or slicing the data frame using <em>loc</em> or <em>iloc</em>.</p><pre># method 1 <br><br>sample_store_orders[[&quot;Order Date&quot;, &quot;Sales&quot;]].head()</pre><p>Before we look at the code, let’s understand the <em>loc</em> and <em>iloc. </em>We will also proceed to define the code syntax they follow. <em>loc, </em>and<em> iloc </em>use position to slice data. You can filter (slice), assign a value, or compare subsets of a data frame using positions.</p><p>Imagine your data frame as a big m*n matrix. Where m is the number of rows and n is the number of columns. To identify an element, you can find its position i, j; given (0 ≤ i ≤ m and 0 ≤ j ≤ n). You can also specify a ‘subset matrix’ by defining its position in our matrix. Just like this → i:k, j:l; given ( 0 ≤ i ≤ k ≤ m and 0 ≤ j ≤ l ≤ n).</p><p>And that’s it; <em>loc</em> and <em>iloc </em>work precisely like our imaginary ‘subset matrix.’ They need i, k, j, and l values to slice our subset from the data frame. Now they are a little more flexible than that, but I hope you get the idea; let’s explore a few examples later in the article.</p><blockquote>Syntax: df.iloc [row position(s), column position(s)]</blockquote><blockquote>Where <strong>df </strong>is the data frame, <strong>row position(s) </strong>is the integer position(s) of rows, and <strong>column position(s) </strong>is the integer position(s) of columns</blockquote><blockquote>Syntax: df.loc [row label(s), column label(s)]</blockquote><blockquote>Where <strong>df </strong>is the data frame, <strong>row label(s) </strong>is the exact labels of row indexes, and <strong>column label(s) </strong>is the precise labels of column indexes.</blockquote><p><strong>iloc:</strong> Only takes integer values as positional values. It does not accept Boolean series (accepts a list of Boolean, though). It also doesn’t care about the values of indexes themselves.</p><pre># Method 2 -&gt; using iloc<br><br>order_date_index = list(sample_store_orders.columns).index(&quot;Order Date&quot;)<br>sales_index = list(sample_store_orders.columns).index(&quot;Sales&quot;)<br><br>sample_store_orders.iloc[:,[order_date_index, sales_index]].head()</pre><p><strong>loc:</strong> Takes the exact value of the indexes. Accepts Boolean series.</p><pre><br># Method 3 -&gt; using loc<br><br>sample_store_orders.loc[:,[&quot;Order Date&quot;, &quot;Sales&quot;]].head()</pre><p>All the above methods result in the same ‘subset.’</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/202/1*1aWV14Ld1RFdPaQcxLDdVA.png" /><figcaption>The output of the slicing</figcaption></figure><h4>Selecting a subset of rows</h4><p>Let’s assume, for whatever reason, you want to slice the data and subset the rows. Then, we can select the subset of rows as needed in the below format.</p><pre><br>sample_store_orders.iloc[100:120,:].head(2)<br><br># look at the screenshots below<br><br>sample_store_orders.set_index(keys= [&#39;Order Date&#39;]).iloc[100:120,:].head(2)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/648/1*uPPpfGL2lMDaqT4zI1TYCA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/613/1*IRFwMKWpY3TZ2Jku5MMNRg.png" /><figcaption>The iloc doesn’t care about the index labels; see how we got the same rows in both methods.</figcaption></figure><p>Let’s try the same with <em>loc</em></p><pre>sample_store_orders.set_index(keys= [&#39;Order Date&#39;]).loc[100:120,:].head(2)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-eakNYqGydFo-y2PCgreRg.png" /><figcaption>That’s sad :( — loc needs the index names as-is</figcaption></figure><p>This would work —</p><pre>sample_store_orders.set_index(keys= [&#39;Order Date&#39;]).loc[&quot;2021-08-29&quot;:&quot;2021-09-30&quot;,:].head(2)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WLlCzZtixz52sb8ylTlaoA.png" /><figcaption>Note how the index is not ordered. Does the index order matter? Explore this on your own :)</figcaption></figure><p>How about slicing both columns and rows at the same time?</p><pre>df = sample_store_orders.set_index(keys= [&#39;Order Date&#39;])<br><br>df.loc[&quot;2021-08-29&quot;:&quot;2021-09-30&quot;,[&#39;City&#39;,&#39;Sales&#39;]].head(2)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/266/1*PVrmfu04FV4wzzqBubOurg.png" /><figcaption>Filter for the row index range and two columns</figcaption></figure><p>loc and iloc are flexible — let’s look at a few examples of how you can leverage all that.</p><p>You can slice using a range of columns (the slicing happens in the order of columns in the df)</p><pre>df.loc[&quot;2021-08-29&quot;:&quot;2021-09-30&quot;,&#39;City&#39;:&#39;Category&#39;].head(2)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/662/1*Lvm6-URGRdSqwa9Q5nFGzg.png" /><figcaption>Slice from the City column till Category</figcaption></figure><p>Based on the above, can you guess the results for the below lines of code?</p><pre># case 1 <br><br>df.loc[&quot;2021-08-29&quot;:&quot;2021-09-30&quot;, &#39;City&#39;:]<br><br># Case 2 <br><br>df.loc[&quot;2021-08-29&quot;:&quot;2021-09-30&quot;, :&#39;City&#39;]<br><br># Case 3<br><br>df.loc[ : &quot;2021-09-30&quot;, :&#39;City&#39;]<br><br># Case 4<br><br>df.loc[&quot;2021-09-30&quot;: , :&#39;City&#39;]<br><br># Case 5 <br>df.loc[ :&quot;2021-09-30&quot;, :]<br><br># Case 6<br><br>df.loc[: , :]</pre><p>You can also pass a series/list of Boolean values in place of indexes.</p><pre>filter_condition = (df[&#39;Sales&#39;] &gt; 30) &amp; (df[&#39;Quantity&#39;] &gt; 10)<br><br>df.loc[filter_condition, &#39;City&#39;].unique()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/663/1*o2Vo1E_qP5Q6iK0ZiEEzrw.png" /><figcaption>The cities that have at least one order with Sales &gt; 30 and Quantity &gt; 10</figcaption></figure><p>You can pass a Boolean list for the column as well :).</p><h4>Updating values in a data frame</h4><p><em>loc</em> and <em>iloc </em>can also help you update your data. While this is a powerful feature, you must be careful here.</p><p>Imagine you want to assign a value of quantity 2 to a set of rows. The below code lets you do it.</p><pre>df.loc[&quot;2021-08-29&quot;:&quot;2021-09-30&quot;, &#39;Quantity&#39;] = 2<br><br>df.loc[&quot;2021-08-29&quot;:&quot;2021-09-30&quot;,&#39;Quantity&#39;].value_counts() <br><br># will result in the count of two&#39;s  </pre><p>And you can do the above using any combination of slicing as required.</p><p>How about multilevel indexing? Can we still use <em>loc </em>and <em>iloc</em>? The short answer is yes.</p><pre># Let&#39;s create a multilevel index <br><br># remove the existing index <br>df.reset_index(inplace= True)<br><br># set Region and Segment as new indexes<br>df.set_index(keys= [&#39;Region&#39;,&#39;Segment&#39;] , inplace= True)</pre><p>You must provide a tuple as the <em>loc </em>parameters. Note that a list would throw an error.</p><pre><br><br>df.loc[(&#39;West&#39;, &#39;Corporate&#39;),[&#39;Quantity&#39;,&#39;Sales&#39;]].shape<br><br># returns (960, 2)<br><br>df.loc[(&#39;West&#39;, [&#39;Consumer&#39;,&#39;Corporate&#39;]),[&#39;Quantity&#39;, &#39;Sales&#39;]].shape<br><br># returns (2632, 2)<br><br>df.loc[(&#39;West&#39;, [&#39;Consumer&#39;,&#39;Corporate&#39;]),[&#39;Quantity&#39;, &#39;Sales&#39;]].head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/337/1*3LhB5xvprzSDAqYcwy8mkQ.png" /><figcaption><em>loc with </em>multilevel indexes</figcaption></figure><p>You can also update the data for the multi-index data frame using the same methods we covered earlier.</p><blockquote>Note: You should always use the loc method while updating data, especially when working with multi-indexed data frames. Read the implications <a href="https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy">here</a>.</blockquote><pre>df.loc[(&#39;West&#39;, [&#39;Consum er&#39;,&#39;Corporate&#39;]),[&#39;Quantity&#39;]] = 3<br><br>df.loc[(&#39;West&#39;, [&#39;Consumer&#39;,&#39;Corporate&#39;]),[&#39;Quantity&#39;,&#39;Sales&#39;]].head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/297/1*ZL4M-pDpjYLLpZu9SYXl9g.png" /><figcaption>Yep, it works :)</figcaption></figure><p>In conclusion, <em>loc </em>and<em> iloc </em>allow some powerful transformations that are otherwise painful to accomplish. They are also tricky to master; however, once you are used to them, you can’t appreciate them enough :). I hope the article helped you get through the basics of working with <em>loc</em> and <em>iloc</em>. Happy learning, and have a nice day 😃.</p><p>Feel free to connect with me on<a href="https://www.linkedin.com/in/sumanth-munnangi/"> LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c0aee3dac422" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL with Python | SQLAlchemy]]></title>
            <link>https://blog.devops.dev/mysql-with-python-sqlalchemy-cfe829389e36?source=rss-66a0cc5e6241------2</link>
            <guid isPermaLink="false">https://medium.com/p/cfe829389e36</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Sumanth Munnangi]]></dc:creator>
            <pubDate>Thu, 05 Jan 2023 10:48:59 GMT</pubDate>
            <atom:updated>2023-01-09T13:55:02.545Z</atom:updated>
            <content:encoded><![CDATA[<p>Most of the data we interact with is stored remotely on a server. One such ‘remote server’ could be the popular RDBMS — MySQL. Now, who doesn’t like SQL? It’s easy and fun 😊. However, most of the time, it is necessary to read data to a pandas data frame for data wrangling and analytics. Furthermore, you may need to engineer data and build automated pipelines.</p><p>Sure, you can use Azure Data Factory, AWS glue, or something fancy that will cost you a lot of money. Instead, this article will examine a few examples of working with MySQL using Python. So you can build custom code blocks and use open-source packages to achieve what you need 😃.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kZk0tdw8GFe2TYgGZQTRWg.jpeg" /><figcaption>Before we get to it, enjoy a picture of Bodega Bay, CA 😄.</figcaption></figure><p>We will use the <a href="https://www.sqlalchemy.org/">SQLAlchemy</a> toolkit to connect and interact with the MySQL server. We need to install the relevant packages first — <em>sqlalchemy</em> and <em>pymysql</em>.</p><p>Setting up the environment — I like using Anaconda distribution to manage all the packages easily. Feel free to pip-install them if you prefer otherwise.</p><pre>conda activate &lt;my_env_name&gt;<br>conda install -c anaconda sqlalchemy<br>conda install -c anaconda pymysql</pre><p>Just say yes ‘y’ to complete the installation. Now that we installed the required packages, let’s get started.</p><p>Note: Install MySQL on your PC to follow the rest of the article. The relevant documentation should be <a href="https://dev.mysql.com/doc/">here</a>. If you already have access to a MySQL server, please use those credentials here. (Note that your machine also needs network-level access to the server, having the credentials alone may not get you access to the DB)</p><p>The first thing to do is to establish a connection to your MySQL server, which we will do post importing the packages and modules required.</p><pre>import pandas as pd<br>from sqlalchemy import (create_engine,<br>                        Table, Column, Integer, <br>                        String, MetaData, inspect) </pre><p>You need to provide some information to the SQLAlchemy <em>create_engine</em> API to establish a connection to the correct server. It is in the below format.</p><blockquote><strong>“mysql+pymysql://&lt;username&gt;:&lt;password&gt;@&lt;server_ip&gt;:&lt;port&gt;”</strong></blockquote><blockquote><strong>&lt;username&gt;</strong>: The username you use to log in to the MySQL server; it is usually ‘root’ if you use a local setup.</blockquote><blockquote><strong>&lt;password&gt;</strong>: Password</blockquote><blockquote><strong>&lt;server_ip&gt;</strong>: The IP address of the server. It’s usually 127.0.0.1 if you use a local setup.</blockquote><blockquote><strong>&lt;port&gt;</strong>: This will be 3306 for a local setup</blockquote><p>Let’s make a connection to the MySQL server now :)</p><pre>con_string = &quot;mysql+pymysql://&lt;username&gt;:&lt;password&gt;@&lt;server_ip&gt;:&lt;port&gt;&quot;<br><br>engine = create_engine(con_string)<br><br>conn = engine.connect()</pre><p>And done, we have a successful connection to the server. We can now execute queries on your server and source the results for future reference in your code.</p><pre>result = conn.execute(&quot;show databases;&quot;)<br><br>result.fetchall()</pre><p>The above code runs the command <em>show databases; fetchall()</em> returns the result as a list.</p><pre>[(&#39;accountspayable&#39;,),<br> (&#39;bikerideexample&#39;,),<br> (&#39;colonial&#39;,),<br> (&#39;entertainmentagencyexample&#39;,),<br> (&#39;information_schema&#39;,),<br> (&#39;mysql&#39;,),<br> (&#39;oact&#39;,),<br> (&#39;performance_schema&#39;,),<br> (&#39;recipesdatabase&#39;,),<br> (&#39;sakila&#39;,),<br> (&#39;sys&#39;,),<br> (&#39;world&#39;,)]</pre><p>You can also use sqlalchemy APIs to achieve the same.</p><pre>eng_ins = inspect(conn)<br><br>listofdbs = eng_ins.get_schema_names()<br><br>print(listofdbs)</pre><pre>[&#39;accountspayable&#39;,<br> &#39;bikerideexample&#39;,<br> &#39;colonial&#39;,<br> &#39;entertainmentagencyexample&#39;,<br> &#39;information_schema&#39;,<br> &#39;mysql&#39;,<br> &#39;oact&#39;,<br> &#39;performance_schema&#39;,<br> &#39;recipesdatabase&#39;,<br> &#39;sakila&#39;,<br> &#39;sys&#39;,<br> &#39;world&#39;]</pre><pre># create a dummy data frame <br><br>schemas = pd.DataFrame(columns = [&#39;db_name&#39;, &#39;table_name&#39;,&#39;column_names&#39;])<br><br># Loop through the databases and tables to get the schema<br><br>for schema in listofdbs: # for each DB, we get the table names<br><br>    for tb_name in eng_ins.get_table_names(schema = schema):<br>        # for each table, we get the column names<br>        column_names = [x[&#39;name&#39;] for x in eng_ins.get_columns(tb_name, schema = schema)]<br>        # Append the result to the data frame     <br>        schemas = pd.concat([schemas,pd.DataFrame({&#39;db_name&#39; : schema, <br>                        &#39;table_name&#39; : tb_name, <br>                        &#39;column_names&#39; : column_names})],<br>                      ignore_index = True)<br><br>schemas.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/453/1*gJTwLzqnrCzCVdEv-jqJRA.png" /><figcaption>Pandas data frame with the schemas, it’s tables, and columns</figcaption></figure><p>What we do with it is up to you; A reference of table and column names acts like a repo of folder paths, and you can automate and parameterize your code as you wish; it makes your life a little easier.</p><p>Let’s create a DB and a table.</p><pre># Create a database<br><br>conn.execute(&quot;create database temp_db;&quot;)<br><br># We use MetaData to store details of our actions<br><br>meta_data = MetaData(schema = &#39;temp_db&#39;)<br><br># Table helps us in creating the table we need :)<br><br>a_new_table = Table(&#39;table_name&#39;, meta_data,<br>   Column(&#39;col_one_primary&#39;, Integer, primary_key = True), <br>   Column(&#39;col_two&#39;, String(20)), <br>   Column(&#39;col_three&#39;, String(20)),<br>   Column(&#39;col_four&#39;, String(20))<br>)<br><br>meta_data.create_all(engine)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xTZh0nnkYwryigG9FN4k1g.png" /><figcaption>The python script is interpreted to SQL query in the backend.</figcaption></figure><p>We can conveniently insert data into this table from a pandas data frame.</p><pre># Create a pandas data frame <br><br>a_new_table_df = pd.DataFrame({&#39;col_one_primary&#39;:list(range(10,15)),<br>                              &#39;col_two&#39;:[&quot;two_&quot;+str(x) for x in key], <br>                              &#39;col_three&#39;:[&quot;three_&quot;+str(x) for x in key],<br>                              &#39;col_four&#39;:[&quot;four_&quot;+str(x) for x in key]})<br><br># Wite the data to MySQL using pandas to_sql <br><br>a_new_table_df.to_sql(&#39;table_name&#39;, <br>                      con= conn, <br>                      schema= &#39;temp_db&#39;,<br>                      if_exists = &#39;append&#39;, <br>                      index = False)<br></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wZm3dM4OGGNWVe8eqyp5Rg.png" /><figcaption>A Dictionary is created in the backend to complete this task.</figcaption></figure><p>We can read the data into a pandas data frame using <em>pd.read_sql()</em></p><pre>get_result = pd.read_sql(&quot;select * from temp_db.table_name;&quot;,<br>                        con = conn)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xHei2b3v3o0jIoMZktXVOg.png" /><figcaption>The SQL ‘text’ in the python command is executed on the server, and the results are fetched to a data frame.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/372/1*5zys2z64yk2Que7wItM0CA.png" /><figcaption>Here is our data frame :)</figcaption></figure><p>The easiest way to work with SQL on python is to write the SQL queries as texts and use read_sql to get the desired result (Like the example above; you can write complex queries as well — not suggested). This is my preferred method while I explore the data. However, the most pythonic way of doing things is by leveraging all the APIs SQLAlchemy offers. Check out their <a href="https://docs.sqlalchemy.org/en/14/contents.html">documentation</a> for more.</p><p>Time to clean up 😃.</p><pre>a_new_table.drop(conn)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/722/1*dRJUikgSzpJJitI5do9B2g.png" /><figcaption>And done; see how the python command is interpreted to SQL query?</figcaption></figure><pre>conn.execute(&quot;drop database temp_db;&quot;);</pre><p>SQLAlchemy is quite extensive, powerful, and super fast; however, data analysts or data scientists do not need to master all of it. Instead, knowledge of establishing connections, reading, writing, and transforming data using pandas would suffice in most cases. With that said, have an excellent year ahead 😃.</p><p>Feel free to connect with me on <a href="https://www.linkedin.com/in/sumanth-munnangi/">LinkedIn</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cfe829389e36" width="1" height="1" alt=""><hr><p><a href="https://blog.devops.dev/mysql-with-python-sqlalchemy-cfe829389e36">MySQL with Python | SQLAlchemy</a> was originally published in <a href="https://blog.devops.dev/">DevOps.dev</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>