DEV Community: Britny

Python for Data Analysts: A Random Use Case

Britny — Fri, 02 Dec 2022 03:14:30 +0000

Introduction

Let me start out by saying, this is not the post I had planned but it is the post that I am writing because I think it is interesting and potentially useful. In this post I will regale you with the story behind why I decided to use Python to solve my particular problem and how it came about. Following this tantalizing story, I will actually walk you through what I did in Python using a dummy data set that represents what I was exporting from SQL.

The Background Story

Let me set the stage for you…

I am working on creating a new SQL query that will eventually be adopted into views that my team use on a daily basis. I’ve been working on this for a few weeks, and I finally get to the point where I want to output a pivoted summary table of the data so I can do some validation, because looking at hundreds of thousands of rows of data isn’t going to be useful to figure out what I’m looking at.

I painstakingly write the hundreds of lines of code that I need to write in SQL to “pivot” my data, only to run into a memory issue. I try a variety of optimization techniques, but nothing works! What can I do?

Well, let’s be honest. I have a lot of options. I can put the SQL into a query in PowerBI and use the power of PowerBI to create my table and many other visuals. I can export the data to Excel and play with it in there. I could have explored other SQL options. Needless to say, I had options; however, my mind thought… “Can Python do this? What are its capabilities? Should I use this opportunity as a chance to explore Python a bit?”

At first, I thought I should just user PowerBI or Excel because I know them, I am familiar with them, I could probably do what I needed to relatively quickly. After thinking about it though I decided that the potential delay by trying it in Python was worth it because it was a learning opportunity!

One more quick tidbit of information that may be useful, I am trying, with this code, to create a linear funnel of my data. So, there is a category and then a few categories where the numbers are subtracted from the category above to then create a more directed category. This is repeated a few times to get to the ultimate goal category but outlining along the way why data points are being removed by putting them in categories. This may sound very abstract; I hope this will make more sense once we look at the data and code.

The Use Case

The Data

First, let’s take a look at the shape of the data. So, my base SQL code was exporting hundreds of thousands of lines of data, but as stated above I was interested in the summary data. SQL couldn’t pivot this summary data; this is the issue I am trying to solve with Python. My summary table has 10 rows and 26 columns. The first three columns are categories that will be used to pivot the data into columns later. The remaining columns are the metrics that I want to view in a linear fashion down a column. For simplicity, and the fact that I could not think of any other potential metric options, I have paired the sample data down to 13 columns.

Note the following concerning the dataset:

E_correct_options = A_all_options - B_wrong_color - C_wrong_brand - D_wrong_dimensions
H_just_right_options = E_correct_options - F_too_old - G_too_new
J_still_want_options = H_just_right_options - I_already_owned

The Code

Now that we have an idea of what the data looks like, let’s get this data into Python. If you have any questions on this part, refer to my previous post on accessing data in Excel.

Step 1:

Import your libraries.

# Import Libraries
import pandas as pd
import numpy as np

Step 2:

Store your Excel file path in an object.

# Create File Path Object
path = r'C:\Users\britny.sarver\Documents\Python\SampleData\DummyData_RandomUseCase.xlsx'

Step 3:

Store the data from your Excel file in an object.

# Create Data Frame from Excel File
df = pd.read_excel(path)

Step 4:

Check the shape of your data.

# Check the Shape of the Data (rows, columns)
df.shape

Using the provided sample data, you should get an output of (10, 13).

Step 5:

Check that you are importing the right data by looking at it.

# View the Data in a Table
df

Using the provided sample data, you should get an output that looks like:

Step 6:

Unpivot the metrics in the table. In order to accomplish this, utilize the melt function. This function requires a few inputs:

col_level: Allows you to specify the level at which to melt your data.
id_vars: A list of columns used as identifiers / categories.
value_vars: A list of columns to unpivot the data for.
var_name: Give the variable column a specific name.
value_name: Give the value column a specific name.

# Unpivot the Metrics
df_unpivoted = df.melt(
    col_level = 0
    , id_vars = ['sizes', 'availability', 'price_range']
   ,  value_vars = [
         'A_all_options'
       , 'B_wrong_color'
       , 'C_wrong_brand'
       , 'D_wrong_dimensions'
       , 'E_correct_options'
       , 'F_too_old'
       , 'G_too_new'
       , 'H_just_right_options'
       , 'I_already_owned'
       , 'J_still_want_options'
    ]
    , var_name = 'options_category'
    , value_name = 'number_of_options'
)

Step 7:

Check the shape of the new dataset.

# Check the Shape of the Data (rows, columns)
df_unpivoted.shape

Using the provided sample data, you should get an output of (100, 5).

Step 8:

Make sure that the Pandas settings will allow you to view all your data. You can do this by setting the max columns and rows to match the results from above. Then, view the new dataset to check it is what you are expecting.

# Set Pandas Settings to Display All Data in Table
pd.set_option('display.max_columns', 5)
pd.set_option('display.max_rows', 100)

# View the Data in a Table
df_unpivoted

Using the provided sample data, you should get an output that looks like:

Step 9:

Create a data frame that contains all of the rows where the size is S (small) and create da data frame that contains all of the rows where the size is L (large). Our goal is to have columns in our final table for each option in size, small and large, overall and for each of the other sub-categories. We will address this in the next step.
These data frames are created using the Pandas feature that allows us to filter rows by selecting a column and specifying the values in that column we want to keep.

# Create data frames for small and large options
df_s = df_unpivoted[df_unpivoted.sizes.isin(['S'])]
df_l = df_unpivoted[df_unpivoted.sizes.isin(['L'])]

Step 10:

Create a data frame for small options that are also available by utilizing the above created filtered data frame and the availability column. Similarly, create a data frame for large options that are also available.
In order to create these data frames, we will utilize the Pandas function pivot_table. For this to work we need a few inputs:

The name of the data frame to pivot.
values: The column we want to aggregate.
index: The column we want to group by and create rows.
columns: The column we want to group by and create columns.
aggfunc: The function in which we want to utilize to aggregate the values.
fill_value: The value in which we want to use if the aggregated value is null. In order for this to make sense later, I also utilized the Pandas function rename to rename the column created from availability.

# Create data frames for Small and Large Available options by option categories
df_s_available = pd.pivot_table(
    df_s
    , values = 'number_of_options'
    , index = ['options_category']
    , columns = ['availability']
    , aggfunc = np.sum
    , fill_value = 0
).rename(columns = {"Available" : "S Available"})

df_l_available = pd.pivot_table(
    df_l
    , values = 'number_of_options'
    , index = ['options_category']
    , columns = ['availability']
    , aggfunc = np.sum
    , fill_value = 0
).rename(columns = {"Available" : "L Available"})

Step 11:

Create a data frame for small options that are also average priced by utilizing the filtered data frames created in step 9 and the price_range column. Similarly, create a data frame for large options that are also average priced.
Similar to step 10, we will utilize the Pandas functions pivot_table and rename.

# Create data frames for Small and Large Average Priced options by option categories
df_s_average = pd.pivot_table(
    df_s
    , values = 'number_of_options'
    , index = ['options_category']
    , columns = ['price_range']
    , aggfunc = np.sum
    , fill_value = 0
).rename(columns = {"Average" : "S Avg Price"})

df_l_average = pd.pivot_table(
    df_l
    , values = 'number_of_options'
    , index = ['options_category']
    , columns = ['price_range']
    , aggfunc = np.sum
    , fill_value = 0
).rename(columns = {"Average" : "L Avg Price"})

Step 12:

Create a data frame for small options by utilizing the filtered data frames created in step 9 and the sizes column. Similarly, create a data frame for large options. Similar to step 10, we will utilize the Pandas function pivot_table.

# Create data frame for small and large options by option categories
df_s_ = pd.pivot_table(
    df_s
    , values = 'number_of_options'
    , index = ['options_category']
    , columns = ['sizes']
    , aggfunc = np.sum
    , fill_value = 0
)

df_l_ = pd.pivot_table(
    df_l
    , values = 'number_of_options'
    , index = ['options_category']
    , columns = ['sizes']
    , aggfunc = np.sum
    , fill_value = 0
)

Step 13:

Finally, combine the data frames created in steps 10 through 12 into one data frame that will display the data as desired. In order to accomplish this we will utilize Pandas concat function. This requires a few inputs:

A list of data frames we wish to combine, in the order we want to combine them.
axis: The column in which to join the datasets on. I also wanted to make the number output look nice, so I utilized the Pandas function style.format to add thousands separators to the numbers, where appropriate.

# Combine into the desired dataframe structure
df_final = pd.concat(
    [
          df_s_
        , df_s_available
        , df_s_average
        , df_l_
        , df_l_available
        , df_l_average
    ]
    , axis = 1
).style.format('{:,}')

# Display the summarized and pivoted data
df_final

Using the provided sample data, you should get an output that looks like:

Conclusion

I by no means am claiming this is the only, nor the best, way to accomplish the goal of formatting the data the way I wanted to see it; however, I found this exercise both interesting and educational. I thought it would be something interesting to share. If anything, maybe it can spark some creativity in all of you to go out and try something outside the box. At least, outside the box for you. I know this was a great experience for me! Hence why I am writing about this instead of my planned topic of cleaning data. Stick around for more posts in this series for some fun and educational posts about getting started with Python as a Data Analyst!

Associated Files:

Credits:
Photo by Christina Morillo

Python for Data Analysts: Accessing Data on a Website

Britny — Thu, 10 Nov 2022 23:06:27 +0000

Introduction

Are you a data analyst that has heard of Python, everything it is capable of, and would like to try it but you don’t even know where to get started? Then this article is for you. This is the third in a series of articles to help data analysts get started with using Python. In this article we will cover how to connect to data found on websites. If you do not already have Python installed, check out the first post in this series: Python for Data Analysts: Getting Started.

Before Accessing Data

Before you can access data, let’s cover a few basic concepts around Python that you need to be aware of before proceeding. This series walks you through getting started with and using Python step-by-step by providing code and Jupyter Notebooks, so you don’t have to be an expert at Python or understand everything to follow along. Take it slow and learn by example.

Libraries

Libraries are the bread and butter of Python. You can utilize libraries by importing them. For example:

import pandas as pd

If you followed the tutorial from the first post in this series, then you should already have Anaconda installed. Anaconda by default comes with a lot of useful libraries installed; however, you can also install them. There are a few ways to do this, but in this series, we will focus on accomplishing everything possible from Jupyter Notebooks. In a Notebook you would use the same command as in the Command Prompt, except you put a ‘!’ in front of it:

! pip install pandas

For more information on the pandas library, check out the official documentation.

Terminology

Below are some key terms used throughout this post, and others in the series, that are important to be aware of. These key terms may or may not be familiar to you, depending on your previous experience with coding and data analysis; therefore, I am including a list of terms used and their definitions in relation to this post so as to aid in your development and understanding.

Object: An object is something you assign an entity to.
Entity: An entity is something like a list, table, string, number, etc.
Assign: You assign entities to objects by using ‘=’.
Index: In Python, indexes start at 0.
Function: A call to Python to perform an action based on the arguments provided.
Arguments: Information passed to a function in order for the function to work as intended.

Data on a Website

So, there is a web page with data you want to play with or report on? You can do that with Python! There are many ways to accomplish this, just like with anything in Python. In this post we will be covering how to get this data using the pandas library.

Notes:

This method only works with websites written using HTML that also have tables on them.
The website must be public access, otherwise you will get an error saying you don’t have access.
For this I am pulling data from a Wikipedia page which contains a table of US States, along with some other tables.
You can access any public access HTML site you would like; you do not have to use the site I am using.

Step 1:

In order to get started, import the pandas library into your Jupyter Notebook:

import pandas as pd

Step 2:

Assign the web page you would like to gather data from to an object. As an example, I am going to pull in a table of US States from Wikipedia.

url = 'https://en.wikipedia.org/wiki/List_of_states_and_territories_of_the_United_States#:~:text=States%20of%20the%20United%20States%20of%20America%20,Feb%2014%2C%201912%20%2015%20more%20rows%20'

Step 3:

Using a built-in pandas function pull in a list of the tables on the web page. This step will create a list and assign it to an object.

website_df = pd.read_html(url)

Step 4:

In order to validate you have read in what you think you have, use the type function:

type(website_df)

In this case you should get the result, list. We can check how many items are in this list by using the len function:

len(website_df)

Step 5:

Since a website can have multiple tables and the pandas function brings in the tables as a list, it is worth doing some discovery into each item in the list to see which table we want to access.

In the case of the Wikipedia page above, there are 19 tables in the list. We can see what each of these tables looks like by utilizing the indexing system.

Checking the first item (index = 0) we can see it is the header at the top of the page, but if we pull the second table in the list (index = 1), we will get a table of US States:

website_df[1]

The output of this looks something like:

Conclusion

In this post we have covered how to access data on a website but there are many different locations data can be housed; for example, the previous post in this series covers how to access data housed in an Excel file. Now that we have accessed some data, I’m sure you’re asking, “What can we do with it?” Though we did not answer that question in this post, stick around over the next few posts, and I will provide more information on what you can do with the data that you now have.

Associated Files / Resources:

Credits:
Photo by Christina Morillo

Python for Data Analysts: Accessing Data in Excel

Britny — Wed, 02 Nov 2022 20:17:47 +0000

Introduction

Are you a data analyst that has heard of Python, everything it is capable of, and would like to try it but you don’t even know where to get started? Then this article is for you. This is the second in a series of articles to help data analysts get started with using Python. In this article we will cover how to connect to data housed in an Excel file. If you do not already have Python installed, check out the first post in this series: Python for Data Analysts: Getting Started.

Before Accessing Data

Libraries

Libraries are the bread and butter of Python. You can utilize libraries by importing them. For example:

import pandas as pd

! pip install pandas

For more information on the pandas library, check out the official documentation.

Terminology

Object: An object is something you assign an entity to.
Entity: An entity is something like a list, table, string, number, etc.
Assign: You assign entities to objects by using ‘=’.
Index: In Python, indexes start at 0.
Function: A call to Python to perform an action based on the arguments provided.
Arguments: Information passed to a function in order for the function to work as intended.

Data in Excel

We all have Excel files with data that we want to perform more in-depth analysis on, right? Well let’s talk about how we get that data into Python in the first place. There are many ways to accomplish this, just like with anything in Python. In this post we will be covering how to get this data using the pandas library.

Notes:

First and foremost, you are going to need the pandas library.
For this I am pulling a sample Excel file from Contextures, which I will also attach in this post.
You can utilize your own Excel file if you would like, you do not have to use the sample file I am using.

Step 1:

In order to get started, import the pandas library into your Jupyter Notebook:

import pandas as pd

Step 2:

Assign the path for the Excel file you would like to gather data from to an object.

excel_path = r'Path\FileName.FileExtension'

A few notes on the above step:

To test this yourself, replace the path value assigned to excel_path below with a path that points to your own excel file. In other words, replace ‘Path\FileName.FileExtension’ below with your own path and file name.
It is important to note that the r in front of the path is important. It tells Python how to interpret the special characters, such as \ in the path.
It is also important to note that this will not work if you don’t include both the file name and file extension.

Step 3:

If your Excel workbook has more than one sheet you need to identify which sheet has the data you want to look at. If your Excel workbook only has one sheet, then you can ignore this step.

excel_sheet = 'SalesOrders'

Step 4:

Using a built-in pandas function pull in the data from your Excel workbook into a data frame.

excel_df = pd.read_excel(excel_path, sheet_name = excel_sheet)

A few notes on the above step:

If your Excel workbook only has one sheet, then you do not need to include the second argument in the function.
In other words, if your Excel workbook only has one sheet, then delete , sheet_name = excel_sheet from the code above.
If your Excel workbook has multiple sheets but you do not specify which sheet should be read, then the function will look at the first sheet in the workbook.
If you would like to know more about the arguments you can pass into the pandas read_excel function, you can check out the documentation for the function.

Step 5:

Last, but certainly not least, look at your data.

excel_df

If you are using the example dataset, you should get an output that looks like this.

Conclusion

In this post we have covered how to access data housed in an Excel file but there are many different locations data can be housed, with this in mind the next few posts in this series will cover other avenues to access data. However, now that we have accessed some data, I’m sure you’re asking, “What can we do with it?” Though we did not answer that question in this post, stick around over the next few posts, and I will provide more information on what you can do with the data that you now have.

Associated Files:

Credits:
Photo by Christina Morillo

Python for Data Analysts: Getting Started

Britny — Tue, 25 Oct 2022 14:37:00 +0000

Introduction

Are you a data analyst that has heard of Python, everything it is capable of, and would like to try it but you don’t even know where to get started? Then this article is for you. This is the first in a series of articles to help data analysts break down the invisible barrier between themselves and Python. In this article we will cover how to install Python via Anaconda.

Installing Anaconda & Python

There are many ways to go about installing and getting started with Python. This article covers the option that utilizes Anaconda. Anaconda provides a friendly interface to access Python and Jupyter Notebooks, along with other tools. When you install Anaconda, Python will also be installed.

Step 1

To get started, you will need to install Anaconda from their website. Navigate to their downloads page https://www.anaconda.com/products/distribution#Downloads.

Step 2

Choose the appropriate installer for your system. In my case, that is Windows so I will be covering what this looks like in Windows but it is similar on other systems. Click on the installer to download it.

Step 3

Once the installer is downloaded, open the file. I am opening mine directly from my browser, but you can also open it from whichever folder you downloaded the file to.

Step 4

You should get a pop-up dialog box that says something like Welcome to Anaconda3 2022.10 (64-bit) Setup. Please note that you may see a different version depending on if you are installing this at a later date and/or on a different operating system.

Click on the Next > button.

Step 5

You should now see the License Agreement screen. In order to proceed you will need to read and agree to the License Agreement.

If you do, click on the I Agree button.

Step 6

You should now see the Installation Type screen. Select the radial button next to Just Me (recommended).

Click on the Next > button.

Step 7

You should now see the Installation Location window. Generally, you should leave this as is.

Click on the Next > button.

Step 8

You should now see the Advanced Installation Options window. Leave the first box unchecked and make sure the second box is checked.

Click on the Install button.

Step 9

Once the install is complete you should see the Installation Complete window.

Click on the Next > button.

Step 10

You should now see the Anaconda + JetBrains window. You can click the link for more information if you like, but for the purposes of this tutorial click on the Next > button.

Step 11

You should now see the Thank You for Installing screen.

I recommend going through both the Tutorial _and _Getting Started guides, especially if you aren’t familiar with Anaconda or Python at all; however, this is not required. For the purposes of this tutorial you can either leave the options checked or you can uncheck them.

Click on the Finish button.

Note: If you leave the options checked, tabs will open in your browser to the indicated articles.

Step 12

Now that you have finished installing Anaconda (and Python), you should check that they are working properly. Navigate to your Start **menu and find the folder titled **Anaconda3 (64-bit), or whatever version you installed for your system, and open it.

Click on Anaconda Navigator (Anaconda3).

Note: This will open Anaconda’s user interface so you can see what other tools you now have access to.

Step 13

When you open Anaconda Navigator, be patient it takes a few seconds. Also, note that you may see the command prompt flash a few times along with the Anaconda logo. This is normal.

Note: The first time you open the Navigator, you may be prompted to update. I recommend updating if you are given the option.

Click on the Yes button.

Step 14

You should now see the Quit Application window.

Click on the Yes button.

Step 15

You should now see the Anaconda Navigator Updater window.

Click on the Update now button.

Step 16

You should now see in the Anaconda Navigator Updater window that the update was successful.

Click on the Launch Navigator button.

Step 17

You should now see Anaconda Navigator.

From here you have access to many applications in services, including but not limited to JupyterLab, Jupyter Notebook, and RStudio.

Click on the Launch button for Jupyter Notebook.

Step 18

You should be brought to the Jupyter Notebook landing page in your default browser. The default landing page will show you a list of available folders and files that you can access. This includes files on your pc.

In order to test that python is truly working on your pc, click on the New button, and then the Python 3 (ipykernel) option.

Step 19

You should be brought to a new, blank Jupyter Notebook. Since we are only testing if python works on your pc, you do not need to name or save the notebook.

In the code block you see, type: print ("Hello, Python!").

Step 20

To run the cell and test your code, you have a few options:
A. Click on the Run button.
B. Press Ctrl + Enter (on Windows).

If your code successfully runs, then you should see

Hello, Python!

below your code block as your output.

Conclusion

Congratulations! You have successfully installed Anaconda, and therefore Python and Jupyter Notebooks (along with many other tools) as well. Now that you have successfully installed, check out the next post in this series to learn more!

Note: If you are interested in going straight to Jupyter Notebooks to play with Python and do not want to explore the other tools offered by Anaconda, you can open Jupyter Notebooks directly from your Start menu by clicking on Jupyter Notebook (Anaconda3) in the Anaconda3 (64-bit) folder mentioned in step 12.

Credits:

Photo by Christina Morillo: https://www.pexels.com/photo/woman-programming-on-a-notebook-1181359/