Learning New Data Tools via Parallel Examples

I generally learn new concepts in direct proportion to the number of examples provided. Here we’ll do an exercise in parallel learning. We’ll solve the same set of problems using four different technologies. I’m not trying to teach four different technologies from scratch, but trying to provide a learning bridge for those of you who know one or two of the technologies an easy way to learn another. We’ll be computing four different summaries from a common data file. This will hopefully be similar to the Rosetta Stone, where scholars who knew two of the languages could figure out the third. It’s my hope that this will help you figure out something new.

We’ll summarize the data using:

• Excel
• AWK
• MySQL
• Python and Pandas

Data is from: https://excelbianalytics.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/

It’s a datafile of sales by region and country.

We’ll be using the “5000 sales records” data set. We’ll be doing the following:

• Read in the data and get a total count of the records
• Count the records in a region
• Sum the sales
• Calculate the total sales by region

I made one edit to the 1st line of the file. This line is the header line with the field names. The original file has spaces in some of the field names. Spaces in any identifier: field name, file name, directory, table name, etc. should not contain spaces or special characters, as that makes it difficult to work with on the command line.

Task 1: Read in the file and get the total record count

Excel:

I did this in excel on a Mac, Version 16.65 (22091101)

File->New

File->Import

Import dialog, select “CSV” then “Import”

File dialog: select file 5000_sales_records.csv, click “get data”

Text Import Wizard step 1 of 3 dialog: select “Delimited” start with row: 1, click “Next>”

Text Import Wizard step 2 of 3 dialog: select Delimiters: “comma”, click “Next>”

Text Import Wizard step 3 of 3 dialog: Column data format, select “General”, click “Finish”

Import data dialog box: Where do you want to put the data, leave default =$A$1, click “OK”

Click on the Column A header, note in the bottom right status area, see “Count: 5001” This is 5001 because it’s the 5,000 data rows plus the one row that’s the header.

AWK:

awk -F, ‘END {print NR}’ 5000_sales_records.csv

5001

Awk is a very old-school reporting program found in LINUX. An awk program has an implied loop that reads through all the lines of a file. An Awk script generally has the form of a pattern to match then an action to perform inside of curly brackets. The NR pre-defined variable is just the Record Count. The END means do this when awk hits end-of-file. So this basically says at the END, print the number of records we’ve seen. Like Excel awk is counting the header row.

MySQL

Reading the data into MySQL is a two-step process: creating the table to receive the data and then the actual load. One can create a table structure by hand, by the MySQL workbench or by a command-line tool called csvsql. It can be downloaded from:

See: https://csvkit.readthedocs.io/en/latest/index.html

It’s pretty easy to build. It looks at the data file and generates a suitable table structure as:

csvsql -i mysql 5000_sales_records.csv

We get a warning of: “/usr/local/lib/python3.6/dist-packages/agate/table/from_csv.py:74: RuntimeWarning: Error sniffing CSV dialect: Could not determine delimiter”, then the actual table definition it came up with as:

CREATE TABLE `5000_sales_records` (
`Region` VARCHAR(33) NOT NULL,
`Country` VARCHAR(32) NOT NULL,
`ItemType` VARCHAR(15) NOT NULL,
`SalesChannel` VARCHAR(7) NOT NULL,
`OrderPriority` VARCHAR(1) NOT NULL,
`OrderDate` DATE NOT NULL,
`OrderID` DECIMAL(38, 0) NOT NULL,
`ShipDate` DATE NOT NULL,
`UnitsSold` DECIMAL(38, 0) NOT NULL,
`UnitPrice` DECIMAL(38, 2) NOT NULL,
`UnitCost` DECIMAL(38, 2) NOT NULL,
`TotalRevenue` DECIMAL(38, 2) NOT NULL,
`TotalCost` DECIMAL(38, 2) NOT NULL,
`TotalProfit` DECIMAL(38, 2) NOT NULL
);

Note it doesn’t create any indexes, but that’s OK for our purposes. I then took the output of the command (from CREATE TABLE onward) and fed it into MySQL to generate the table. As:

csvsql -i mysql 5000_sales_records.csv | mysql

The first phase is done, on to the second phase of loading the data into that table.

A couple of things: First, the user running the command has to have FILE privileges. This is generally not set by default. A suitable GRANT command adds this as:

GRANT FILE on *.* to username@host;

Next, MySQL by default has the “secure file priv” flag set, which mean that MySQL will only read in data files from a very specific directory, generally /var/lib/mysql-files. To check to see if this is set, per: https://stackoverflow.com/questions/32737478/how-should-i-resolve-secure-file-priv-in-mysql

SHOW VARIABLES LIKE “secure_file_priv”;

| secure_file_priv | /var/lib/mysql-files/ |

So we have to move the file to the /var/lib/mysql-files directory. I had to sudo to accomplish this. Something like:

sudo cp 5000_sales_records.csv /var/lib/mysql-files

Third, the date format in the file is mm/dd/yyyy, and MySQL expects yyyy-mm-dd, so we have to tweak the load command per: https://stackoverflow.com/questions/9265428/mysql-load-dates-in-mm-dd-yyyy-format

The load command becomes:

LOAD DATA INFILE ‘/var/lib/mysql-files/5000_sales_records.csv’
INTO TABLE 5000_sales_records
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘“‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(Region,Country,ItemType,SalesChannel,OrderPriority,@OrderDate,OrderID,@ShipDate,UnitsSold,UnitPrice,UnitCost,TotalRevenue,TotalCost,TotalProfit)
SET OrderDate = STR_TO_DATE(@OrderDate,’%c/%e/%Y’),
    ShipDate = STR_TO_DATE(@ShipDate,'%c/%e/%Y');

Note on the load we tell it to ignore the first row, as we’ve already incorporated the header information in to the table definition.

total record count

Now getting the total record count is easy:

select count(*) from 5000_sales_records;

| count(*) |

| 5000 |

Python with Pandas

Note: I had to edit the data file, as it had the end-of-line characters typical of Windows-based text files: a carriage return and a newline. I changed it to the Unix/Linux standard of just a newline. I used the vim editor, though tr would work as well.

I ran this in a jupyter notebook on a Mac.

import pandas as pd
pd.options.display.float_format = ‘{:.2f}’.format
df = pd.read_csv(‘/Users/brentl/Downloads/5000_sales_records.csv’)
rows, cols = df.shape
print(f”{rows = }”)
rows = 5000

Task 2: Get the Number of records where Region is “Asia”

Excel:

Go to an unused cell and enter:

=COUNTIF(A2:A5001,”Asia”)

Get Brent Laminack’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Result: 719

AWK

awk -F, '$1 ~ /Asia/ {asia_count++}
END {print asia_count}' 5000_sales_records.csv

719

Here we give awk the pattern to match if the first field ($1), which is the Region column is like (~) the pattern Asia. The -F, command-line argument says to use comma as the field separator.

MySQL

select count(*) from 5000_sales_records where Region = 'Asia';

| count(*) |

| 719 |

Straightforward, the count(*) says to do just that: count the records from that table that match the where count.

Python

asia_rows, asia_cols = df[df[‘Region’] == ‘Asia’].shape
print(f”Asia rows: {asia_rows}”)

Asia rows: 719

Shape gives the size of the resulting dataframe (matrix). It returns the rows and the columns.

Task 3: Sum the TotalProfit column

Excel

Sum the TotalProfit field

In an unused cell, type:

=SUM(N2:N5001)

While highlighted, go to Format->Cells, select “Currency”

Result:: $1,963,223,229.09

Excel is good about formating Currency.

AWK

awk -F, '{grand_total += $14}
END {printf "%.2f\n", grand_total}' 5000_sales_records.csv

1963223229.09

Aws doesn’t have a data dictionary saying which column is named what. The $14 says that we’re doing a running total of the 14th field. Note that in awk, fields are numbered starting with 1, not 0 as in other languages like PHP. Clearly Awk is not big on formatting, but we did get two decimal places out of it using printf.

MySQL

select sum(TotalProfit) from 5000_sales_records;

| sum(TotalProfit) |

+ — — — — — — — — — +

| 1963223229.09 |

Python

df['TotalProfit'].sum()

1963223229.09

Pretty straightforward, we take the TotalProfit column of the dataframe and sum it.

Task 4: Sum of the TotalProfit by Region

Excel

In Excel we have to use the fearsome “pivot table” that’s designed for just this purpose.

Move the Region and TotalProfit columns to be adjacent

Select them both

Choose Insert->Pivot Table

Drag “Region” into the “Rows” panel

Drag “TotalProfit” into the “Values” panel

Choose the “Sum of TotalProfit” column, go to Format->Cells… and choose “Currency”

AWK

awk -F, 'NR > 1 {totals[$1] += $14}
END {
grand_total = 0
for (region in totals){
printf "%s %.2f\n", region, totals[region]
grand_total += totals[region]
}
printf "Total %.2f\n", grand_total
}' 5000_sales_records.csv

Australia and Oceania 174934572.94
Central America and the Caribbean 204611979.72
Asia 278237661.69
North America 41240397.97
Europe 501676048.32
Sub-Saharan Africa 531142267.92
Middle East and North Africa 231380300.53
Total 1963223229.09

We skip the header line by the “NR > 1” pattern. i.e. We only perform the sum on lines whose NR (Record Number, think “line number”) is greater than 1. The idea here is that we have an associative array called “totals” whose key is the region name and value is the running total for that region.

Almost all of the work is done at the end. We walk through the array of Regions and sales sums and print each out. Here the grand total line is produced by the two lines of code that mention “grand_total”

MySQL

select Region, sum(TotalProfit) from 5000_sales_records group by Region with ROLLUP;

| Region | sum(TotalProfit) |

+ — — — — — — — — — — — — — — — — — -+ — — — — — — — — — +

| Asia | 278237661.69 |

| Australia and Oceania | 174934572.94 |

| Central America and the Caribbean | 204611979.72 |

| Europe | 501676048.32 |

| Middle East and North Africa | 231380300.53 |

| North America | 41240397.97 |

| Sub-Saharan Africa | 531142267.92 |

| NULL | 1963223229.09 |

The wokhorse here is the “group by” clause. Note that the last line, the grand total is produced by the “with rollup” clause. There is no name for the region in the rollup total.

Python

df.groupby('Region')['TotalProfit'].sum()

Region

Asia 278237661.69

Australia and Oceania 174934572.94

Central America and the Caribbean 204611979.72

Europe 501676048.32

Middle East and North Africa 231380300.53

North America 41240397.97

Sub-Saharan Africa 531142267.92

The groupby() dataframe method is very analagous to the “group by” in MySQL.

To do a grand total, we’d do something like:

region_summary = df.groupby('Region')['TotalProfit'].sum()
total_line = pd.Series({'Total': df['TotalProfit'].sum()})
pd.concat([region_summary, total_line])

Where we add a series to the end of the summary that holds the sum. Note “series”. Using pandas, one expects most things to be dataframes, but the summary gives a panda-specific data structure called a “series”, which is basically an array with a label.

Summary:

Each tool has its strenghts and weaknesses. Excel is good for point-and-click analysis of small to moderate size data sets. MySQL takes the most work to get the data ingested, but is powerful after that. For a 40+ year old tool, awk proves surprisingly capable. Python with the pandas library has earned its reputation of making powerful data analysis possible just a few lines of code.

Afterward:

Let me know if you liked this, if any code could be improved, if you’d like to see other similar parallel examples.