Programming

Pandas dataframe ordering with examples using sort_values().

Often times, you need some form of ordering in a result set. In the SQL world, without an ORDER BY clause, query results order is not guaranteed. What if you are working in the pandas world? Fear not. You can order DataFrame results in a similar fashion as that of ORDER BY using the sort_values() function. Let’s learn together by example…

[Head this way for great PostgresSQL and Python blogging >>>]

PostgreSQL LEFT() and RIGHT() functions revisitied – String comparison use case.

In my day job (Pipeline Survey Data Analyst) I sometimes have the opportunity to write custom SQL queries in an MS Access database, which is the back end of one of the proprietary GIS solutions, my colleagues use. Although I feel that Access’s SQL implementation is not quite as robust as other SQL dialects, it does get the job done in certain situations (the visual interface continues to grow on me). For a learning experiment, I decided to reproduce – and solve – the same requirements using PostgreSQL, as that I had in the MS Access environment. However, I discovered an all-together different challenge.

Both MS Access and MySQL provide several string functions. One of those is a particularly useful string comparing function. MySQL has STRCMP() while in Access, there is a similar StrComp(). Postgres does not have its own version that I am aware of. I leaned heavily on this type of function in MS Access. Hopefully, readers will clue me in on what Postgres-specific string function I could use in its stead. Meanwhile, read on to see the workaround I used…

[Head this way for great PostgresSQL blogging >>>]

Basic CSV file import and exploration with Pandas – first steps.

Having recently begun to use the Python pandas library in my day job to mitigate mostly redundant tasks with CSV files, I thought to step outside of my normal – and more comfortable wheelhouse – SQL, to share some of the beginner pandas things I am learning and using. The simple fact remains: the more I use pandas, the more I like it. Although the data set used here is not the same as the one I interact with at work, the same principles carryover.

Photo by Mika Baumeister on Unsplash

OS and software used:

Xubuntu Linux 18.04.2 LTS (Bionic Beaver)
Python 3.7.4
pandas-0.25.0

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

It’s no secret that CSV files are used everywhere. Python itself has fantastic support for working with CSV files and data, out of the box, by way of the CSV module. However, I have really taken a liking to the pandas library for its CSV functionality.

I’ve had great success with pandas in scripting away redundant data cleaning work flows prior to importing CSV data into an AutoCADD points base application in my day job.

To use pandas, it must be installed, which can typically be accomplished with a simple:

1	pip install pandas

Once installed, pandas has to be loaded via importing the module. Below is a common convention when importing pandas:

1	>>> import pandas as pd

Now with pandas available, we can begin to work with it.

I have a CSV file, for the month of April, storing walking stats from my daily walks/hikes I track. Pandas provides a handy read_csv() function that accepts a multitude of different parameters. Consult the official documentation (linked below) for full descriptions on each of them as I won’t necessarily explore them all here.

Using the read_csv() function, I’ll create a pandas DataFrame by passing in the path to the CSV file as the lone argument in the function call:

1	>>> apr_data = pd.read_csv(r'/home/linux_user/Practice Data/Fitness_DB_Data/apr_2019_hiking_stats.csv')

We can see via printing out the ‘apr_data’ variables’ type in the console that it is indeed a DataFrame object:

1 2	>>> print(type(apr_data)) <class 'pandas.core.frame.DataFrame'>

Below are just a few common functions you can use to garner some quick insights and information about the data in a DataFrame.

Attaching shape to the DataFrame object, returns a tuple with the number of rows – including the header row – and columns:

1 2	>>> apr_data.shape (21, 6)

Passing in the skiprows parameter, you can skip any number of rows when reading in the CSV. Here I skip the first row, which is the header.

The shape construct confirms there is one less row in this DataFrame since we skipped the first row:

1
2
3

>>> no_header_data = pd.read_csv(r'/home/linux_user/Practice Data/Fitness_DB_Data/apr_2019_hiking_stats.csv', skiprows=1)
>>> no_header_data.shape
(20, 6)

To see more information about a DataFrame like: indexes, null values (if any), along with column data types, pandas has the info() method:

1
2
3
4
5
6
7
8
9
10
11
12

>>> apr_data.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 21 entries, 0 to 20
Data columns (total 6 columns):
day_walked 21 non-null object
cal_burned 21 non-null float64
miles_walked 21 non-null float64
duration 21 non-null object
mph 21 non-null float64
shoe_id 21 non-null int64
dtypes: float64(3), int64(1), object(2)
memory usage: 1.1+ KB

These tidbits of functionality are quite handy without a doubt, however, perhaps you just want to see some data. Instead of loading all the rows present in a DataFrame, use the head() function, which by default returns the first 5 rows:

1
2
3
4
5
6
7

>>> apr_data.head()
day_walked cal_burned miles_walked duration mph shoe_id
0 2019-04-01 217.7 2.18 00:40:01 3.3 4
1 2019-04-02 240.1 2.39 00:44:09 3.2 4
2 2019-04-03 152.7 1.51 00:28:04 3.2 4
3 2019-04-04 207.6 2.04 00:38:10 3.2 4
4 2019-04-05 247.8 2.43 00:45:34 3.2 4

Notice the call to the DataFrame we created that skipped the header row:

1
2
3
4
5
6
7

>>> no_header_data.head()
2019-04-01 217.7 2.18 00:40:01 3.3 4
0 2019-04-02 240.1 2.39 00:44:09 3.2 4
1 2019-04-03 152.7 1.51 00:28:04 3.2 4
2 2019-04-04 207.6 2.04 00:38:10 3.2 4
3 2019-04-05 247.8 2.43 00:45:34 3.2 4
4 2019-04-07 294.5 2.89 00:54:09 3.2 4

The head() function is not restricted to just 5 rows. You can pass a number value as the argument when calling it to see only that many rows:

1
2
3
4

>>> apr_data.head(2)
day_walked cal_burned miles_walked duration mph shoe_id
0 2019-04-01 217.7 2.18 00:40:01 3.3 4
1 2019-04-02 240.1 2.39 00:44:09 3.2 4

This function is handy when you want to get a sense of the dataset (DataFrame) you are working with.

Below you will find documentation links to many of the topics I have covered in the post for your convenience:

The power of pandas goes way beyond that of which I have covered here. If you have never used it, or just curious about it, do check it out. As I use and experiment with pandas, expect to see more blog posts in the future, especially those where I can integrate and leverage it in SQL development learning and work flows.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

LAG() Window Function – with examples in PostgreSQL.

Having recently covered the LEAD() Window Function here on Digital Owl’s Prose, it only makes sense to follow-up with a post on its counterpart: LAG(). By all means, don’t lag behind. Read on and learn something with me…

[Head this way for great PostgresSQL blogging >>>]

Conditional logic with SUM() and the IF() functions in MySQL…

Recently at work, I began hacking on a semi-personal/on-the-clock/side project, prototyping an idea I had for some “database stuff” on unfamiliar grounds.

MS Access or using it period is a foreign landscape to me as I tend to lean more towards open-source alternatives, namely MySQL (and PostgreSQL in the past). However, it is what it is so I dug in a put my SQL thinking cap on…

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:

Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
MySQL 8.0.15

Self-Promotion:

Due to the nature of the particular problem I wanted to solve, I knew I needed some form of conditional logic involving Aggregate functions, namely the SUM() function. What I ran into was the strange MS Access IIF() function.

To my knowledge, there is no CASE expression in MS Access (although there is a sort of switch construct I’ve yet to explore).

To get a better handle on the required logic I needed, during off hours, I used my favorite SQL dialect and began to wrap my head around the best approach.

Having previously written, Understanding MySQL’s IF() function – with examples, I knew enough about it to start from there since I would eventually have to reproduce (or as close as I could) the same functionality in the MS Access world.

Below are some discoveries along the way to getting something working in MySQL and then being able to translate what I learned there, over to the MS Access ecosystem.

Here is the setup of tables. I have a ‘worker’ table with ‘name’, ‘id’ and ‘pay_rate’ columns, used in conjunction with a ‘worker_hours’ table for logging hours worked with the accompanying id for the worker. I mainly use this setup for an understanding of calculating rates and hours. To be crystal clear, I work with and handle Pipeline Survey data. Not HR-type data (E.g., employee pay, hours, rates, etc…). However, I wanted to get a handle on this type of functionality in case I became (my dream and hopes) the ‘Database Person’ asked to implement something along these lines. (To Note: This probably is not the best-normalized structure but serves for the purpose of this post and learning – for me at least.)

Below let’s look at the structure and data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

mysql> DESC workers;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| first_name | varchar(25) | YES | | NULL | |
| pay_rate | decimal(4,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM workers;
+------+------------+----------+
| id | first_name | pay_rate |
+------+------------+----------+
| 2 | Mary | 12.00 |
| 3 | Bob | 8.00 |
| 1 | Jim | 10.00 |
+------+------------+----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

mysql> DESC worker_hours;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| worker_id | int(11) | YES | | NULL | |
| hours_worked | decimal(4,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM worker_hours;
+-----------+--------------+
| worker_id | hours_worked |
+-----------+--------------+
| 1 | 10.00 |
| 1 | 10.00 |
| 1 | 9.50 |
| 2 | 12.00 |
| 2 | 12.00 |
| 2 | 14.00 |
| 2 | 10.00 |
| 3 | 12.00 |
| 3 | 12.00 |
| 3 | 12.00 |
| 3 | 4.00 |
+-----------+--------------+
11 rows in set (0.00 sec)

I total the hours per worker with the SUM() aggregate function, so pretty straight-forward there:

1
2
3
4
5
6
7
8
9
10
11

mysql> SELECT worker_id, SUM(hours_worked)
-> FROM worker_hours
-> GROUP BY worker_id;
+-----------+-------------------+
| worker_id | SUM(hours_worked) |
+-----------+-------------------+
| 1 | 29.50 |
| 2 | 48.00 |
| 3 | 40.00 |
+-----------+-------------------+
3 rows in set (0.00 sec)

Then, use an INNER JOIN on the ‘workers’ table and include the ‘first_name’ of each worker. INNER JOIN‘s are a staple and often, a necessity when querying multiple tables.

Not familiar with them? Then you should have a look at this specific blog post I wrote for information on them.

Basically, I am after a result set like this:

1
2
3
4
5
6
7
8
9
10
11
12
13

mysql> SELECT w.first_name, SUM(wh.hours_worked)
-> FROM workers AS w
-> INNER JOIN worker_hours AS wh
-> ON(w.id = wh.worker_id)
-> GROUP BY w.first_name;
+------------+----------------------+
| first_name | SUM(wh.hours_worked) |
+------------+----------------------+
| Jim | 29.50 |
| Mary | 48.00 |
| Bob | 40.00 |
+------------+----------------------+
3 rows in set (0.03 sec)

That’s wonderful and all, but, the workers must be paid accordingly to their total hours, which is is where the conditional logic comes in. If the total (SUM()) hours worked is more than 40, the ‘pay_rate’ is calculated at time and a half (at least in the US to my knowledge). Pretty simple right?

Let’s try this:

1
2
3
4
5
6
7
8
9
10

SELECT
w.first_name,
SUM(wh.hours_worked) AS total_hours,
IF(SUM(wh.hours_worked) >= 40, w.pay_rate * 40, w.pay_rate * wh.hours_worked) AS regular_pay,
IF((SUM(wh.hours_worked) - 40) > 0, w.pay_rate * 1.5 * (wh.hours_worked - 40), 0) AS overtime_pay
FROM workers AS w
INNER JOIN worker_hours AS wh
ON(w.id = wh.worker_id)
GROUP BY w.first_name;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains non-aggregated column 'practice.w.pay_rate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Oh duh. Silly me. I need to include those IF() columns in the GROUP BY clause:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
w.first_name,
SUM(wh.hours_worked) AS total_hours,
IF(SUM(wh.hours_worked) >= 40, w.pay_rate * 40, w.pay_rate * wh.hours_worked) AS regular_pay,
IF((SUM(wh.hours_worked) - 40) > 0, w.pay_rate * 1.5 * (wh.hours_worked - 40), 0) AS overtime_pay
FROM workers AS w
INNER JOIN worker_hours AS wh
ON(w.id = wh.worker_id)
GROUP BY w.first_name,
IF(SUM(wh.hours_worked) >= 40, w.pay_rate * 40, w.pay_rate * wh.hours_worked),
IF((SUM(wh.hours_worked) - 40) > 0, w.pay_rate * 1.5 * (wh.hours_worked - 40), 0);
ERROR 1111 (HY000): Invalid use of group function

I am a blithering idiot! Of course, I need to place the IF() inside the SUM() function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT
w.first_name,
w.pay_rate,
SUM(wh.hours_worked) AS total_hours,
SUM(IF(wh.hours_worked >= 40, ROUND(w.pay_rate * 40, 2), ROUND(w.pay_rate * wh.hours_worked, 2))) AS regular_pay,
SUM(IF((wh.hours_worked - 40) > 0, w.pay_rate * 1.5 * (wh.hours_worked - 40), 0)) AS overtime_pay
FROM workers AS w
INNER JOIN worker_hours AS wh
ON(w.id = wh.worker_id)
GROUP BY w.first_name, w.pay_rate;
+------------+----------+-------------+-------------+--------------+
| first_name | pay_rate | total_hours | regular_pay | overtime_pay |
+------------+----------+-------------+-------------+--------------+
| Jim | 10.00 | 29.50 | 295.00 | 0.00000 |
| Mary | 12.00 | 48.00 | 576.00 | 0.00000 |
| Bob | 8.00 | 40.00 | 320.00 | 0.00000 |
+------------+----------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

Well now, this is a start. However, still not correct. The ‘regular_pay’ column calculation seems to work fine for hours less than or equal to 40 but not for those greater than 40.

Even slightly changing the conditional logic for the overtime hours still did not produce correct calculations:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT
w.first_name,
w.pay_rate,
SUM(wh.hours_worked) AS total_hours,
SUM(IF(wh.hours_worked >= 40, ROUND(w.pay_rate * 40, 2), ROUND(w.pay_rate * wh.hours_worked, 2))) AS regular_pay,
SUM(IF(wh.hours_worked > 40, w.pay_rate * 1.5 * (wh.hours_worked - 40), 0)) AS overtime_pay
FROM workers AS w
INNER JOIN worker_hours AS wh
ON(w.id = wh.worker_id)
GROUP BY w.first_name, w.pay_rate;
+------------+----------+-------------+-------------+--------------+
| first_name | pay_rate | total_hours | regular_pay | overtime_pay |
+------------+----------+-------------+-------------+--------------+
| Jim | 10.00 | 29.50 | 295.00 | 0.00000 |
| Mary | 12.00 | 48.00 | 576.00 | 0.00000 |
| Bob | 8.00 | 40.00 | 320.00 | 0.00000 |
+------------+----------+-------------+-------------+--------------+
3 rows in set (0.01 sec)

While tackling this problem, something just kept bugging me about how I was wrapping the IF() function with SUM(). It did not seem to be calculating results like it should.

Let’s look at a meaningful, base query real quick and get our mental juices flowing:

1
2
3
4
5
6
7
8
9
10
11

mysql> SELECT worker_id, SUM(hours_worked) AS total_hours
-> FROM worker_hours
-> GROUP BY worker_id;
+-----------+-------------+
| worker_id | total_hours |
+-----------+-------------+
| 1 | 29.50 |
| 2 | 48.00 |
| 3 | 40.00 |
+-----------+-------------+
3 rows in set (0.00 sec)

How could I use this results set to my advantage? Let’s explore a couple of different options I tried with great success.

Up first, using a Derived Table. With this option, the above query will essentially become the table named in the INNER JOIN:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT
w.first_name,
w.pay_rate,
tab_1.total_hours AS total_hours,
IF(tab_1.total_hours >= 40, ROUND(w.pay_rate * 40, 2), ROUND(w.pay_rate * tab_1.total_hours, 2)) AS regular_pay,
IF(tab_1.total_hours > 40, ROUND(w.pay_rate * 1.5 * (tab_1.total_hours - 40), 2), 0) AS overtime_pay
FROM workers AS w
INNER JOIN (SELECT worker_id,
SUM(hours_worked) AS total_hours
FROM worker_hours
GROUP BY worker_id) AS tab_1
ON w.id = tab_1.worker_id
GROUP BY w.first_name, w.pay_rate, tab_1.total_hours;
+------------+----------+-------------+-------------+--------------+
| first_name | pay_rate | total_hours | regular_pay | overtime_pay |
+------------+----------+-------------+-------------+--------------+
| Mary | 12.00 | 48.00 | 480.00 | 144.00 |
| Bob | 8.00 | 40.00 | 320.00 | 0.00 |
| Jim | 10.00 | 29.50 | 295.00 | 0.00 |
+------------+----------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

Finally! Correct results!

Next, I will use something a bit more interesting. A CTE. Mentioning CTE’s, have a look at CTE’s in MySQL – First steps…, where I covered a similar type of functionality as to that we will see below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

WITH tab_1 AS (
SELECT worker_id,
SUM(hours_worked) AS total_hours
FROM worker_hours
GROUP BY worker_id
)

SELECT
w.first_name,
w.pay_rate,
tab_1.total_hours AS total_hours,
IF(tab_1.total_hours >= 40, ROUND(w.pay_rate * 40, 2), ROUND(w.pay_rate * tab_1.total_hours, 2)) AS regular_pay,
IF(tab_1.total_hours > 40, ROUND(w.pay_rate * 1.5 * (tab_1.total_hours - 40), 2), 0) AS overtime_pay
FROM workers AS w
INNER JOIN tab_1
ON w.id = tab_1.worker_id
GROUP BY w.first_name, w.pay_rate, tab_1.total_hours;
+------------+----------+-------------+-------------+--------------+
| first_name | pay_rate | total_hours | regular_pay | overtime_pay |
+------------+----------+-------------+-------------+--------------+
| Mary | 12.00 | 48.00 | 480.00 | 144.00 |
| Bob | 8.00 | 40.00 | 320.00 | 0.00 |
| Jim | 10.00 | 29.50 | 295.00 | 0.00 |
+------------+----------+-------------+-------------+--------------+
3 rows in set (0.01 sec)

To be crystal clear, to my knowledge, CTE’s are not available in MS Access. So I am not saying (or trying) to implement them there. I only included the CTE here as a sort of alternative to the Derived Table.

I suppose the most important question is why did this work? In my opinion, by providing a table column that already contained the aggregated total, summed values – via the ‘tab_1.total_hours’ column alias – enabled the IF() function to evaluate the conditional logic with correct computations. However, I am not 100% sure about this. But, it seems like intermingling the calls to IF() and SUM() together, were ‘wrecking’ the ‘overtime_pay’ calculations. Any feedback (and always) corrections in the comments are a treasure for me so I would love to see them from any readers.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

Explore the official MySQL 8.0 Online Manual for more information.

A Call To Action!

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Digital Owl's Prose

Database Musing.

Pandas dataframe ordering with examples using sort_values().

Like this:

PostgreSQL LEFT() and RIGHT() functions revisitied – String comparison use case.

Like this:

Basic CSV file import and exploration with Pandas – first steps.

Self-Promotion:

A Call To Action!

Like this:

LAG() Window Function – with examples in PostgreSQL.

Like this:

Conditional logic with SUM() and the IF() functions in MySQL…

Self-Promotion:

A Call To Action!

Like this:

Share this:

Like this:

Share this:

Like this:

Self-Promotion:

A Call To Action!

Share this:

Like this:

Share this:

Like this:

Self-Promotion:

A Call To Action!

Share this:

Like this: