<?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 Uma Rajagopalan on Medium]]></title>
        <description><![CDATA[Stories by Uma Rajagopalan on Medium]]></description>
        <link>https://medium.com/@umaraj_datascientist?source=rss-247fdb415b52------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*cORI1XD21T-vZHw8qiUVVw@2x.jpeg</url>
            <title>Stories by Uma Rajagopalan on Medium</title>
            <link>https://medium.com/@umaraj_datascientist?source=rss-247fdb415b52------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 11:19:36 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@umaraj_datascientist/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[The most simple Data Engineering Project ever!]]></title>
            <link>https://medium.com/@umaraj_datascientist/the-most-simple-data-engineering-project-ever-b0257a747df0?source=rss-247fdb415b52------2</link>
            <guid isPermaLink="false">https://medium.com/p/b0257a747df0</guid>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[portfolio-project]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Uma Rajagopalan]]></dc:creator>
            <pubDate>Thu, 28 Dec 2023 04:15:54 GMT</pubDate>
            <atom:updated>2023-12-28T04:15:54.385Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hJ7HBfnmzUnhbECt69dK-A.jpeg" /></figure><p>Welcome to the world of Data engineering! I’m going to walk you through the creation of the most simple Data engineering project ever!. Whether you are new to the tech world, learning data science or are here for generations, you can include this in your portfolio list easily.</p><p>Why is this project important? I was once asked to generate number of active users per day at the end of each month. I would fetch the data from the API URL at the end of each month for 6 months to generate the report and send an email to the stakeholders. I wish I had this project made back then so I could automate this kind of task. The below project helps you easily automate such tasks by also giving you an idea of how data is extracted, transformed and loaded into the system.</p><p>I will be walking you through the temperature data in this project since I found a Public API that helps extract weather data</p><p><strong>Step 1: Define the Project</strong></p><p>The first step as usual is to actually understand what we are making.</p><p><strong>Objective:</strong> Collect temperature data from different cities and generate hourly analysis.</p><p><strong>Step 2: Choose a Data Source</strong></p><p>We are going to use OpenWeatherMap API for weather data.</p><p><em>Urgh! API! What’s that: API stands for Application Programming Interface. It’s a set of rules to let applications communicate with one another. It defines the methods and data formats that applications can use to request and exchange information.</em></p><p>Think of it as a menu in a restaurant. The menu lists all the dishes you can order, along with a description of each dish. You (the user) don’t need to know how the kitchen prepares the food; you just need to know how to order from the menu. In this analogy, the menu is like the API, and you (the user) are like a software application. The API provides a way for different software applications to “order” or request services from each other without needing to understand the internal details of how those services are implemented.</p><p>Using the OpenWeatherMap API for weather data means interacting with their set of rules (API) to request specific weather information, just like ordering food from a menu at a restaurant. It allows developers and applications to access up-to-date weather data without having to understand the intricate details of how OpenWeatherMap gathers and processes that data internally.</p><p><strong>Step 3: Set up a Data Storage</strong></p><ul><li>Create a new directory/folder for your project. I named mine temperature_repo</li><li>Create a new file temperature_data.csv and save it in the temperature_repo folder</li></ul><p><strong>Step 4: Retrieve Data from API</strong></p><ol><li>Get API Key</li></ol><ul><li>Sign up for a free account on OpenWeatherMap (<a href="https://openweathermap.org/">https://openweathermap.org/</a>)</li><li>Go to Tab called ‘API Keys’. Copy the Default Key</li></ul><p>2. Write a Python script</p><ul><li>Open notepad/notepad++</li><li>Create a new file. Save it in temperature_repo folder as fecth_data.py</li><li>Write the following code in it:</li></ul><pre>import requests<br>import json<br><br>api_key = &quot;YOUR_OPENWEATHERMAP_API_KEY&quot;<br>cities = [&quot;City1&quot;, &quot;City2&quot;, &quot;City3&quot;]  # Add the cities you&#39;re interested in<br><br>for city in cities:<br>    url = f&quot;http://api.openweathermap.org/data/2.5/weather?q={city}&amp;appid={api_key}&quot;<br>    response = requests.get(url)<br>    data = response.json()<br><br>    # Extract temperature information from the response<br>    temperature = data[&quot;main&quot;][&quot;temp&quot;]<br><br>    print(f&quot;The temperature in {city} is {temperature} Kelvin.&quot;)</pre><blockquote>Don’t forget to paste your API key and change City1, City2, City3 to appropriate cities. I used San Jose, Santa Clara, Mountain View</blockquote><ul><li>Save your code</li></ul><p>Code explained:</p><ul><li>We are importing requests python library that will hit the OpenWeatherMap API URL</li><li>We are importing json python library to read the output JSON object of the data we receive after hitting the API URL</li><li>We store the response JSON object and extract the temperature value for the city name we are passing in the API URL request.</li><li>We run the for loop for 3 city names.</li><li>We then print the city and temperature values</li></ul><p>3. Run the Python Code</p><p>Open temperature_repo folder -&gt; Right click -&gt; Open in Terminal -&gt; Enter the following:</p><pre>python fetch_data.py</pre><ul><li>Click on enter</li><li>If you run into an error with import requests like I did, just write:</li></ul><pre>pip install requests</pre><ul><li>Click Enter and enter python fetch_data.py to run the code</li><li>You will see the weather information retrieved in the terminal. Here’s what it should look like:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7mCWnYsuMjm4qrG0v6dKaA.png" /></figure><ul><li>This fetches and prints the current temperature for each city.</li></ul><p><strong>Step 5: Transform and Clean Data</strong></p><ol><li>Modify the Python script fetch_data.py</li></ol><pre>import os<br>import requests<br>import json<br>import pandas as pd<br>from datetime import datetime<br><br>api_key = &quot;YOUR_API_KEY&quot;<br>cities = [&quot;San Jose&quot;, &quot;Santa Clara&quot;, &quot;Mountain View&quot;]  # Add the cities you&#39;re interested in<br><br>data_list = []<br><br>for city in cities:<br>    url = f&quot;http://api.openweathermap.org/data/2.5/weather?q={city}&amp;appid={api_key}&quot;<br>    response = requests.get(url)<br>    data = response.json()<br><br>    # Extract temperature information from the response<br>    temperature = data[&quot;main&quot;][&quot;temp&quot;]<br>    current_time = datetime.now().strftime(&quot;%Y-%m-%d %H:%M:%S&quot;)<br><br>    print(f&quot;The temperature in {city} is {temperature} Kelvin.&quot;)<br>    data_list.append({&quot;City&quot;: city, &quot;Temperature&quot;: temperature, &quot;Timestamp&quot;: current_time})<br><br>    <br><br>df = pd.DataFrame(data_list)<br><br>existing_csv_file = &quot;temperature_data.csv&quot;<br><br># Check if the CSV file exists<br>if not os.path.isfile(existing_csv_file) or os.stat(existing_csv_file).st_size == 0:<br>    # If the file doesn&#39;t exist or is empty, write the header<br>    df.to_csv(existing_csv_file, index=False)<br>else:<br>    # If the file exists and is not empty, append the data along with the header<br>    df.to_csv(existing_csv_file, mode=&quot;a&quot;, header = False,index=False)<br><br>print(f&quot;Data appended to {existing_csv_file}&quot;)</pre><p>Code explained:</p><ul><li>We are now importing os module to interact with the Windows Operating System</li><li>We are also importing datetime to store the datetime when the report is executed for our hourly analysis</li><li>I am also saving the data in temperature_data.csv.</li><li>If temperature_data.csv doesn’t exist, it is created &amp; data is inserted upon execution</li><li>If temperature_data.csv is empty, then data along with column names are added to the file</li><li>If temperature_data.csv is not empty, then only the data but not the column names are added to the file</li></ul><p><strong>Step 6: Store the data</strong></p><p>Run the code like above in Step 4.3. This will save the temperature data in the temperature_data.csv file we created</p><ul><li>You can open the temperature_data.csv file to see what it looks like:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/308/1*G-Ai5ngXfIPWQu9yMBC2LQ.png" /></figure><ul><li>What happens when you re-rerun fetch_data.py, open the temperature_data.csv file again. The following output is seen.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/310/1*rZPAAafb6i3gNl9efR66Cw.png" /></figure><p><strong>Step 7: Analyze Data</strong></p><ol><li>Install matplotlib if you don’t have by entering the below in the terminal:</li></ol><pre>pip install matplotlib</pre><p>2. Create Data visualization script:</p><ul><li>Create a new file called analyze_data.py in your code editor (Notepad/Notepad++) and save it in temperature_repo</li><li>Use the following code:</li></ul><pre>import pandas as pd<br>import matplotlib.pyplot as plt<br><br>df = pd.read_csv(&quot;temperature_data.csv&quot;)<br><br>plt.bar(df[&quot;City&quot;], df[&quot;Temperature&quot;])<br>plt.title(&quot;Temperature in Different Cities&quot;)<br>plt.xlabel(&quot;City&quot;)<br>plt.ylabel(&quot;Temperature (Kelvin)&quot;)<br>plt.show()</pre><ul><li>This creates a simple bar chart of our temperature data using the matplotlib python library</li></ul><p>3. Run the visualization script</p><p>In the terminal, enter the following</p><pre>python analyze_data.py</pre><p>Code Output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/958/1*QKsOZdzSAXix2LvdBPU9CA.png" /></figure><p><strong>Step 8: Schedule the Process</strong></p><p>If you remember, our objective was to continue this process to analyze trends over time. So I’m going to schedule this process to generate me a temperature_data.csv which will retrieve temperature of 3 cities over a duration of every hour for 5 hours.</p><p>To schedule the process to collect data every one hour using Task Scheduler on Windows:</p><ol><li>Open Task Scheduler:</li></ol><ul><li>Press Win + S to open the search bar.</li><li>Type “Task Scheduler” and select it from the search results.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eV-FGk3LQZaAIwHcuYvOoQ.png" /></figure><p>2. Create a Basic Task:</p><ul><li>In the Actions pane on the right, click “Create Task.”</li></ul><p>3. Set Task Name and Description:</p><ul><li>Provide a name and description for your task, then click “Next.”</li></ul><p>4. Choose Trigger:</p><ul><li>Select “Daily” as the trigger since you want the task to run every day.</li><li>Set the “Recur every” option to “1” days.</li><li>Choose the start date and time. Set the start time to the current time or the time you want the task to begin.</li><li>Select “Repeat task every” and set it to “1 hours” with a duration of “Indefinitely.”</li></ul><p>5. Choose Action:</p><ul><li>Select “Start a program” as the action and click “Next.”</li></ul><p>6. Specify Program/Script:</p><ul><li>Click “Browse” and navigate to the location of your Python executable. This is usually in the Scripts folder within your Python installation directory. Select the python.exe executable.</li><li>If you don’t know where your python executable is present, Go to terminal and enter</li></ul><pre>where python</pre><ul><li>Copy the path displayed</li><li>In the “Add arguments” field, enter the full path to your Python script</li><li>Click OK</li></ul><p>Output:</p><p>I ran my program with this automatic trigger and now temperature_data.csv has the following data:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/303/1*e9ToCsjjCqzNFXCcIczHMA.png" /></figure><p>I can now either automate analyze_data.csv or generate a manual report at the end of the day to see the average temperature in the 3 cities.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/974/1*Qr2H4vpVV6nOtTqFHOR6Jw.png" /></figure><p><strong>Conclusion:</strong></p><p>This article helps you easily create a data engineering project on getting hourly weather report, automating the ETL process, generating an easy visualization.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b0257a747df0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Ford vs Ferrari of bar chart race]]></title>
            <link>https://medium.com/@umaraj_datascientist/the-ford-vs-ferrari-of-bar-chart-race-b32867cfbc2?source=rss-247fdb415b52------2</link>
            <guid isPermaLink="false">https://medium.com/p/b32867cfbc2</guid>
            <category><![CDATA[bar-chart-race]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[raceplotly]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[plotly]]></category>
            <dc:creator><![CDATA[Uma Rajagopalan]]></dc:creator>
            <pubDate>Thu, 19 Jan 2023 20:24:09 GMT</pubDate>
            <atom:updated>2023-01-19T20:45:01.604Z</atom:updated>
            <content:encoded><![CDATA[<p>I’ve always enjoyed looking at sliders and those cool charts that keep changing with value. If you are like me and enjoy looking at your data move in a chart, this post is for you!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9xsU6C3UrDVkxOhy.jpg" /></figure><p><strong>Theory</strong>:</p><p>A bar chart race is a type of animated data visualization that shows how the values of a variable change over time for a set of categories. It typically consists of a sequence of bar charts, where each chart represents a specific point in time, and the bars are ordered by size.</p><p>As time progresses, the bars move up or down based on changes in their values, creating an animation effect. The animation can be used to visualize trends over time.</p><p><strong>DataSet</strong>:</p><p>With bar chart races, you can take any dataset that contains a time column, a numerical column, and an Item column. GDP over time, Population over time, Covid Deaths by Date, etc.</p><p>I chose the baby names dataset for the following reasons:</p><ol><li>It is very popular in bar chart races</li><li>I recently saw it on the subreddit: dataisbeautiful and wanted to recreate it</li><li>Why not? (I ran out of reasons!)</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*37LpJnPI-RkgyDgCwHYLGg.gif" /></figure><p><strong>Dataset link:</strong> You can find the dataset <a href="https://www.kaggle.com/datasets/kaggle/us-baby-names">here</a>. I chose the NationalNames.csv file from the dataset for this code.</p><pre>#Importing the csv<br>baby_names = pd.read_csv(&#39;NationalNames.csv&#39;)</pre><p><strong>About the Dataset: </strong>The dataset has about 1825433 rows and 5 columns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/382/1*pW97RSw9TGwkI3aYDhyDLA.png" /></figure><p><strong>Data Cleaning:</strong></p><ol><li>I’m going to separate male and female names.</li></ol><pre>#Seperating female and male baby names<br>baby_names_female = baby_names[baby_names[&#39;Gender&#39;] == &#39;F&#39;]<br>baby_names_male = baby_names[baby_names[&#39;Gender&#39;] == &#39;M&#39;]</pre><p>2. Once that’s done, we don’t need the ID and Gender columns. So let’s get rid of them</p><pre>#Dropping Id and Gender columns in both dataframes<br>baby_names_female.drop(columns = [&#39;Id&#39;,&#39;Gender&#39;],inplace = True)<br>baby_names_male.drop(columns = [&#39;Id&#39;,&#39;Gender&#39;],inplace = True)</pre><p>3. Rearranging the columns cause I like them to look neat.</p><pre>#Rearranging columns in both dataframes<br>baby_names_female = baby_names_female[[&#39;Year&#39;,&#39;Name&#39;,&#39;Count&#39;]]<br>baby_names_male = baby_names_male[[&#39;Year&#39;,&#39;Name&#39;,&#39;Count&#39;]]</pre><p><strong>Bar Chart Race:</strong></p><ol><li><strong><em>Using raceplotly:- (The Ford)</em></strong></li></ol><p>raceplotly is a Python library that allows you to create interactive animated race plots using Plotly. The library provides a simple and intuitive API for creating and customizing race plots and can be integrated with other Plotly libraries for creating more complex visualizations.</p><p>I hardly had to do anything to create the bar chart race using raceplotly.</p><ol><li>To use raceplotly, you will first need to install it by running</li></ol><p>pip install raceplotly in your command line.</p><pre>#If raceplotly is not yet installed<br>pip install raceplotly</pre><p>2. Import the library and construct the bar chart</p><pre>from raceplotly.plots import barplot<br>my_raceplot_female = barplot(baby_names_female,<br>                      item_column=&#39;Name&#39;,<br>                      value_column=&#39;Count&#39;,<br>                      time_column=&#39;Year&#39;)</pre><p>3. Make the chart look pretty by setting parameters</p><pre>fig = my_raceplot_female.plot(title = &#39;Top 10 Female Baby names from 1880 to 2014&#39;,<br>                 item_label = &#39;Top 10 female baby names&#39;,<br>                 value_label = &#39;Count of babies&#39;,<br>                 frame_duration = 800)<br>fig.show()</pre><p>4. Enjoy the output</p><ul><li>Import chart studio in your code. If you don’t have it installed already use, pip install chartstudio</li><li>Create an account in <a href="https://chart-studio.plotly.com/feed/#/">chartstudio</a></li><li>Go to Settings under your Profile icon and copy your username, api key</li><li>Paste your username and api_key in the below code and export your chart</li></ul><pre>#To export the interactive charts<br>import chart_studio<br>username=&#39;your_usename&#39;<br>api_key=&#39;your_api_key&#39;<br>chart_studio.tools.set_credentials_file(username=username,<br>                                        api_key=api_key)<br>import chart_studio.plotly as py<br>py.plot(fig, filename=&quot;raceplotly_bar_chart_race&quot;, auto_open = True)</pre><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fplotly.com%2F%7Eumaraj%2F1.embed%3Fautosize%3Dtrue&amp;display_name=Plotly&amp;url=https%3A%2F%2Fchart-studio.plotly.com%2F%7Eumaraj%2F1%2F&amp;image=https%3A%2F%2Fchart-studio.plotly.com%2Fstatic%2Fwebapp%2Fimages%2Fplotly-logo.8d56a320dbb8.png&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=plotly" width="600" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/53aaa81f59362e835fceabae7bb12c81/href">https://medium.com/media/53aaa81f59362e835fceabae7bb12c81/href</a></iframe><p><strong><em>2. Using matplotlib (The Ferrari)</em></strong></p><p>I have multiple reasons to like matplotlib. The main being it’s customizable to my needs. I don’t like it because it throws me an error when using the entire dataset. The error is unrelated but it is mainly caused due to the scale of the data.</p><ol><li>Create a pivot table to make the data look clean for matplotlib. I had to reduce the dataset to have the count of names only greater than 1000 because of the error</li></ol><pre>#Let&#39;s take the count values only greater than 1000 in both dataframes<br>baby_names_female = baby_names_female[baby_names_female[&#39;Count&#39;]&gt;=1000]<br>#Creating pivot tables for both dataframes<br>baby_names_female_pivot = pd.pivot_table(baby_names_female,<br>                       values = &#39;Count&#39;,<br>                       index = [&#39;Year&#39;],<br>                       columns = &#39;Name&#39;)</pre><p>2. Replace missing values</p><pre>#Replacing missing values<br>baby_names_female_pivot.fillna(0, inplace=True)</pre><p>3. bar_chart_race is a matplotlib library to create bar chart race</p><pre>#Importing matplotlib&#39;s bar_chart_race<br>import bar_chart_race as bcr<br>#Animation of bar_chart_race for top 10 female baby names from 1880 to 2014<br>animation_female = bcr.bar_chart_race(df = baby_names_female_pivot,<br>                                      filename=&#39;FemaleBabyNamesPopularity.mp4&#39;,<br>                                      title=&#39;Top 10 Female Baby names in the US from 1880 to 2014&#39;,<br>                                      orientation=&#39;h&#39;,<br>                                      fixed_order = False,<br>                                      n_bars=10,<br>                                      sort=&#39;desc&#39;,<br>                                      cmap=&#39;dark12&#39;,<br>                                      #By default, steps_per_period = 20 which means 20 frames are used per time period <br>                                      #with the entire period lasting period_lenght = 500 milliseconds (half of a second)<br>                                      steps_per_period=20,<br>                                      period_length=500,<br>                                      period_label={&#39;x&#39;: .98, &#39;y&#39;: .3, &#39;ha&#39;: &#39;right&#39;, &#39;va&#39;: &#39;center&#39;}, <br>                                      period_fmt = &#39;Year: {x:.0f}&#39;,<br>                                      bar_size=.95, <br>                                      shared_fontdict=None, <br>                                      scale=&#39;linear&#39;, <br>                                      fig=None, <br>                                      writer=None, <br>                                      bar_kwargs={&#39;alpha&#39;: .7},<br>                                      filter_column_colors=True)</pre><p>4. Enjoy the Output after converting your mp4 to gif.</p><p>(I tried using .gif in the filename parameter in bar_chart_race but ended up getting the error: “MovieWriter imagemagick unavailable; using Pillow instead.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*0iIYKU5ugkvHN-FRT2EuZw.gif" /></figure><p><strong>Comparison</strong>:</p><ul><li>bar_chart_race takes 3 minutes to execute whereas raceplotly took 2 seconds to execute</li><li>raceplotly conversion to a demo-able visualization was much easier than bar_chart_race</li><li>Interactive charts are easier with plotly and raceplotly than bar_chart_race(matplotlib)</li></ul><p>That’s why I declared raceplotly as the Ford of bar chart races (Spoiler Alert!)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/560/0*1Scxjz_-EMQhU5sO.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b32867cfbc2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Automating Excel Dashboard Creation with Python]]></title>
            <link>https://medium.com/@umaraj_datascientist/automating-excel-dashboard-creation-9c60acb1ba0c?source=rss-247fdb415b52------2</link>
            <guid isPermaLink="false">https://medium.com/p/9c60acb1ba0c</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[automation]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[openpyxl]]></category>
            <dc:creator><![CDATA[Uma Rajagopalan]]></dc:creator>
            <pubDate>Fri, 13 Jan 2023 02:07:59 GMT</pubDate>
            <atom:updated>2023-01-13T02:28:35.465Z</atom:updated>
            <content:encoded><![CDATA[<p>To all those learning about data science and analytics out there, if you haven’t learned automation yet, this article is going to make your life easy!</p><p><strong>A Little Backstory:</strong></p><p>I’ve been attending interviews for 6 months now for data analyst and data science positions. All my interviews usually have the same pattern or rather I started observing them like how any data enthusiast does. There’s one “tell me about yourself” that’s common and based on my answer, there are further questions on it or my resume.</p><p>I interned at a company as part of my capstone project where I helped the company with its data analytics and data science requirements. A major aspect of my role was to generate Key Performance Indicators (KPIs) to measure the performance of the company on a monthly basis. To anyone who does not know what KPI is, this is what Wikipedia says:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/535/1*YlpPN9keitzFcKh-7VajEA.png" /></figure><p>Now, this statement is enough for the person interviewing me to get intrigued and tell me how that aligns with what is to be done for the position I’m interviewed for and ask me further about it. And then, the question I dread would be asked.</p><p>“Did you implement automation for generating your monthly reports?”</p><p>At this moment, I just want to be honest with them and tell them that I have not and how I really regret not doing it! A person who knows Python, SQL, and Excel does not automate to make their life easy? Really? Plus, I have a software engineering background!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*xAHSlKTwDC3HXkjHt7REEw.gif" /></figure><p>So, I decided to change that today. I automated generating my excel reports. I finally did it!🥹</p><p><strong>The Real Stuff and just a little Fluff</strong></p><p>Through this article, you’ll see how we can automate generating an excel dashboard and creating a simple data pipeline with just a click of a button.</p><p>Well, it’s not just one button — it’s a few lines of code!</p><p>Like anyone who starts their data analytics dashboard creation journey, I started with my man Alex the Analyst’s easy excel dashboard on youtube.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FopJgMj1IUrc%3Flist%3DPLUaB-1hjhk8H48Pj32z4GZgGWyylqv85f&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DopJgMj1IUrc&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FopJgMj1IUrc%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/7bf46db205124ce51342727a589a49f5/href">https://medium.com/media/7bf46db205124ce51342727a589a49f5/href</a></iframe><p>This 40 min video has helped me so much in understanding the beauty of data. I’m going to use the dataset used in this video and automate the creation of the dashboard made.</p><p>I’ll be using the python library — openpyxl</p><ol><li>Let’s read the excel file first.</li></ol><pre>#Reading the Excel file and the sheet name<br>file_name = &#39;Bike_Sales_Playground.xlsx&#39;<br>bike_df = pd.read_excel(file_name,sheet_name=&#39;bike_buyers&#39;)</pre><p>2. Alex then creates a working sheet to not mess with the raw data.</p><pre>#We don&#39;t want to mess with our raw data, thus, making a copy of it into a sheet called Working_Sheet.<br> with pd.ExcelWriter(file_name,#Name of the Workbook<br>                    engine=&#39;openpyxl&#39;,#Name of the engine<br>                    mode=&#39;a&#39;,#Append mode<br>                   if_sheet_exists=&quot;replace&quot; #Replacing the sheet if it already exists<br>                   ) as writer:<br>  bike_df.to_excel(writer, sheet_name=&#39;Working_Sheet&#39;,index = False)#Setting index to False to avoid the unnecessary column Unnamed:0<br><br> #Let&#39;s read the working sheet data into our dataframe<br> bike_df = pd.read_excel(file_name,sheet_name=&#39;Working_Sheet&#39;)</pre><p>3. Data Cleaning</p><pre>#Dropping duplicates from the data<br>bike_df.drop_duplicates(keep=&#39;first&#39;, inplace=True, ignore_index=False)<br>    <br>#Replacing M to Married and S to Single in Marital Status column<br>bike_df[&#39;Marital Status&#39;] = bike_df[&#39;Marital Status&#39;].replace(&#39;M&#39;,&#39;Married&#39;).replace(&#39;S&#39;,&#39;Single&#39;)<br>    <br>#Replacing F to Female and M to Male in Gender column<br>bike_df[&#39;Gender&#39;] = bike_df[&#39;Gender&#39;].replace(&#39;F&#39;,&#39;Female&#39;).replace(&#39;M&#39;,&#39;Male&#39;)<br><br>#Viewing the changed column values<br>bike_df.head()<br><br>#Age is better in brackets<br>bike_df[&#39;Age brackets&#39;] = bike_df[&#39;Age&#39;].apply(lambda x: &#39;Less than 30&#39; if x&lt;=30 else(&#39;Greater than 55&#39; if x&gt;55 else &#39;31 to 55&#39;))<br><br>#Replacing Commute Distance value 10+ Miles to More than 10 Miles<br>bike_df[&#39;Commute Distance&#39;] = bike_df[&#39;Commute Distance&#39;].replace(&#39;10+ Miles&#39;,&#39;More than 10 Miles&#39;)</pre><p>4. Exploratory Data Analysis</p><p>Alex created 3 pivot tables and charts in his video. Below is the code for the bar chart. The 2 other line charts have similar codes which can be found on my <a href="https://github.com/UmaTheDataScientist/AutomatingExcelDashboard">GitHub</a> repository.</p><pre>#Pivot table 1<br>#Average Income per Gender based on Purchased Yes or No<br>avg_gender_income_df = np.round(pd.pivot_table(bike_df,<br>               values = &#39;Income&#39;,<br>               index = [&#39;Gender&#39;],<br>               columns = [&#39;Purchased Bike&#39;],<br>               aggfunc = np.mean<br>              ),2)<br><br>#Now that we have made all changes in the dataframe, let&#39;s load it into the excel file<br>with pd.ExcelWriter(file_name,#Name of the Workbook<br>                    engine=&#39;openpyxl&#39;,#Name of the engine<br>                    mode=&#39;a&#39;,#Append mode<br>                   if_sheet_exists=&quot;replace&quot; #Replacing the sheet if it already exists<br>                   ) as writer:  <br>    <br>    avg_gender_income_df.to_excel(writer, sheet_name=&#39;Average_Gender_Income&#39;)<br><br># loading workbook and selecting sheet<br>wb = load_workbook(file_name)<br>sheet = wb[&#39;Average_Gender_Income&#39;]<br><br># Bar chart creation<br>chart1 = BarChart()<br>chart1.type = &quot;col&quot;<br>chart1.style = 10<br>chart1.title = &quot;Average Income by Gender and Purchase Data&quot;<br>chart1.y_axis.title = &#39;Gender&#39;<br>chart1.x_axis.title = &#39;Income&#39;<br><br>#Attach the chart to the worksheet<br>data1 = Reference(sheet, min_col=2, min_row=1, max_row=3, max_col=3)#Including Headers<br>cats1 = Reference(sheet, min_col=1, min_row=2, max_row=3)#Not including headers<br>chart1.add_data(data1, titles_from_data=True)<br>chart1.dataLabels = DataLabelList() <br>chart1.dataLabels.showVal = True<br>chart1.set_categories(cats1)<br>chart1.shape = 4<br>sheet.add_chart(chart1, &quot;A10&quot;)<br>wb.save(file_name)</pre><p>5. Creating the Dashboard</p><pre>#Creating an empty Dataframe<br>title_df = pd.DataFrame()<br><br>#Now that we have made all changes in the dataframe, let&#39;s load it into the excel file<br>with pd.ExcelWriter(file_name,#Name of the Workbook<br>                    engine=&#39;openpyxl&#39;,#Name of the engine<br>                    mode=&#39;a&#39;,#Append mode<br>                   if_sheet_exists=&quot;replace&quot; #Replacing the sheet if it already exists<br>                   ) as writer:  <br>    <br>    title_df.to_excel(writer, sheet_name=&#39;Dashboard&#39;)<br><br># loading workbook and selecting sheet<br>wb = load_workbook(file_name)<br>sheet = wb[&#39;Dashboard&#39;]<br>for x in range(1,22):<br>    sheet.merge_cells(&#39;A1:R4&#39;)<br>    <br>cell = sheet.cell(row=1, column=1)  <br>cell.value = &#39;Bike Sales Dashboard&#39;  <br>cell.alignment = Alignment(horizontal=&#39;center&#39;, vertical=&#39;center&#39;)   <br>cell.font  = Font(b=True, color=&quot;F8F8F8&quot;,size = 46)<br>cell.fill = PatternFill(&quot;solid&quot;, fgColor=&quot;2591DB&quot;)<br><br>#Adding all our pivot charts to the dashboard<br>sheet.add_chart(chart1,&#39;A5&#39;)<br>sheet.add_chart(chart2,&#39;J5&#39;)<br>chart3.width = 31<br>sheet.add_chart(chart3,&#39;A20&#39;)<br>wb.save(file_name)</pre><p>6. Let’s look at our dashboard</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NAqQaDBz-VmXEqEFSHsHdg.jpeg" /><figcaption>Bike Sales Dashboard</figcaption></figure><p>7. It’s time to put all this into a function and execute it</p><pre>import Bikes_Sales_Report_Automation as auto<br><br>#Enter your filename here<br>auto.automate_excel_dashboard(&#39;Bike_Sales_Playground.xlsx&#39;)</pre><p>8. Click on the button to execute line 7 and voila! Your dashboard is created</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/250/1*gRq1x-u2dVTiE8PbmdXcog.gif" /></figure><p><strong>Summary:</strong></p><p>We created a simple function that when executed generates an excel dashboard automatically. You can run this function anytime you want to generate a report on a periodic basis like every month for generating KPIs in a dashboard.</p><p><strong>Limitations:</strong></p><p>I wish I could figure out how to add the cool excel slicer that Alex had in his video but openpyxl has a limitation to adding a slicer. If you know how to add excel slicers using python, do let me know!</p><p>Check out the code and final output <a href="https://github.com/UmaTheDataScientist/AutomatingExcelDashboard">here</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9c60acb1ba0c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Curse of Dimensionality — Pirates of the Caribbean Way!]]></title>
            <link>https://medium.com/@umaraj_datascientist/the-curse-of-dimensionality-pirates-of-the-caribbean-way-e49fae5d6d2c?source=rss-247fdb415b52------2</link>
            <guid isPermaLink="false">https://medium.com/p/e49fae5d6d2c</guid>
            <category><![CDATA[curse-of-dimensionality]]></category>
            <category><![CDATA[principal-component]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Uma Rajagopalan]]></dc:creator>
            <pubDate>Mon, 29 Aug 2022 21:35:34 GMT</pubDate>
            <atom:updated>2022-08-29T23:12:45.241Z</atom:updated>
            <content:encoded><![CDATA[<h3>The Curse of Dimensionality — Pirates of the Caribbean Way!</h3><p><strong>Let’s understand Dimensions:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*mlZtOMJqYDgPqe3h" /><figcaption>Photo by <a href="https://unsplash.com/@antonov?utm_source=medium&amp;utm_medium=referral">Serg Antonov</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>This is a Rubix cube. This is in 3 dimensions (It has length, breadth, and height).</p><p><a href="https://en.wikipedia.org/wiki/Four-dimensional_space">Four-dimensional space - Wikipedia</a></p><p>This is a 4-D Tesseract hypercube which by just adding one additional dimension is itself challenging to comprehend.</p><p><strong>So what’s the problem?</strong></p><p>It turns out that many things behave very differently in high-dimensional space. Not only that, there’s so much space in higher dimensions that if you pick any two points in a hypercube, the average distance between them will be much higher than the average distance between two points in a 2-D square.</p><p>To simplify, more dimensions = more average distance between 2 points.</p><p><strong>Connect this to data science:</strong></p><p>We know that to predict a test instance, we need a couple of training instances and each of those training instances has a couple of features.</p><p>Example: To predict the price of a used ship with features ship age, length, and displacement, we need to apply Linear Regression to a model with training instances having ship age, length, displacement, and dependence variable price.</p><p>Here, you have 3 features (age, length, displacement) model. Much similar to your 3-D cube. Training your model with fewer features is going to be really easy and your prediction will most likely be accurate.</p><p>If you have a training instance with 100 features, it is similar to a 100 Dimension figure. The distance between the training instance and the test instance is going to be high based on our above theory. This would make prediction difficult, and less reliable.</p><p><strong>This is the curse of Dimensionality.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/667/0*dVmRwsMaW9-eyPMD" /></figure><p>Much like our Pirates of the Caribbean movie, there’s only one way the curse can be lifted.</p><p>By increasing the number of training instances to match the density of the number of features or by decreasing the number of features. <br><strong>Spoiler alert:</strong> Decreasing features is called dimensionality reduction! 😅</p><p>“Unfortunately, for just a 100 features model, we need to have more training instances than atoms in the observable universe.” — Hands-on Machine Learning with Scikit Learn, Keras &amp; Tensor Flow.</p><p>So our only way is to apply Dimensionality Reduction!</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Ftenor.com%2Fembed%2F11342483&amp;display_name=Tenor&amp;url=https%3A%2F%2Ftenor.com%2Fview%2Fsavvy-captain-jack-sparrow-johnny-depp-gif-11342483&amp;image=https%3A%2F%2Fc.tenor.com%2FUGglViCURtIAAAAC%2Fsavvy-captain-jack-sparrow.gif&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=tenor" width="600" height="400" frameborder="0" scrolling="no"><a href="https://medium.com/media/6ce87a48ad22a8983879c25750818ba3/href">https://medium.com/media/6ce87a48ad22a8983879c25750818ba3/href</a></iframe><p>More on Dimensionality Reduction later🤠✌️</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e49fae5d6d2c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>