markhneedham/blog huggingface dataset:
Pandas: Exclude columns using regex
Date: 2024-01-05 00:44:37
In this post, we'll learn how to exclude columns in Pandas using a regular expression.

After a few months of using ClickHouse, I've got quite used to using the https://clickhouse.com/docs/en/sql-reference/statements/select#except[`SELECT <expr> EXCEPT`^] modifier, which lets you remove columns based on a regular expression.
I wanted to do something similar when working with some data in Pandas and in this blog we'll explore how to do that.

We're gonna be working with a CSV file of UK energy and gas tariffs for one of the energy providers.
The CSV file looks like this:

.tariffs.csv
[format="csv", options="header"]
|===
include::content/2024/01/05/tariffs.csv[]
|===

We can read it with Pandas like this:

[source, python]
----
import pandas as pd

tariffs = pd.read_csv("tariffs.csv")
print(tariffs.to_string(index=False))
----

.Output
[source, text]
----
 energyType  standingCharge  unitRate  startDate    endDate
electricity          47.560    29.380 2024-01-01 2024-03-30
        gas          29.600     7.450 2024-01-01 2024-03-30
electricity          44.961    26.758 2023-10-01 2023-12-31
        gas          27.726     6.594 2023-10-01 2023-12-31
electricity          44.961    29.428 2023-07-01 2023-09-30
        gas          27.726     7.179 2023-07-01 2023-09-30
electricity          44.961    49.117 2023-04-01 2023-06-30
        gas          27.726    11.990 2023-04-01 2023-06-30
electricity          40.646    65.555 2023-01-01 2023-03-30
        gas          27.128    16.229 2023-01-01 2023-03-30
electricity          40.646    50.630 2022-10-01 2022-12-31
        gas          27.128    14.021 2022-10-01 2022-12-31
----

Now, let's say we only want to return fields that contain the term `Date`.
We can use the `loc` function where we only include columns that contain this term as the column argument:

[source, python]
----
print(
    (tariffs
      .loc[:, tariffs.columns.str.contains("Date")]
      .to_string(index=False)
    )
)
----

.Output
[source, text]
----
 startDate    endDate
2024-01-01 2024-03-30
2024-01-01 2024-03-30
2023-10-01 2023-12-31
2023-10-01 2023-12-31
2023-07-01 2023-09-30
2023-07-01 2023-09-30
2023-04-01 2023-06-30
2023-04-01 2023-06-30
2023-01-01 2023-03-30
2023-01-01 2023-03-30
2022-10-01 2022-12-31
2022-10-01 2022-12-31
----

And what about if we want to get rid of the `Date` fields?
We can add the `~` prefix:

[source, python]
----
print(
    (tariffs
      .loc[:, ~tariffs.columns.str.contains("Date")]
      .to_string(index=False)
    )
)
----

.Output
[source, text]
----
 energyType  standingCharge  unitRate
electricity          47.560    29.380
        gas          29.600     7.450
electricity          44.961    26.758
        gas          27.726     6.594
electricity          44.961    29.428
        gas          27.726     7.179
electricity          44.961    49.117
        gas          27.726    11.990
electricity          40.646    65.555
        gas          27.128    16.229
electricity          40.646    50.630
        gas          27.128    14.021
----
ClickHouse: How does a number have a set number of decimal places?
Date: 2024-01-02 00:44:37
In this post, we'll learn how to format a Decimal number to have a set/fixed number of decimal places.

:icons: font

I've been working with a dataset in ClickHouse where I compute currency values and I really struggled to figure out how to get numbers whose decimal part is divisible by 10 to have a fixed number of decimal places.
If you want to do that too, hopefully, this blog post will help.

Let's start by seeing what happens if we output the number `12.40`

[source, sql]
----
SELECT 12.40 AS number;
----

.Output
[source, sql]
----
┌─number─┐
│   12.4 │
└────────┘
----

The trailing `0` has been stripped. 
But maybe we can fix that by casting it to a Decimal type with 2 decimal places?

[source, sql]
----
SELECT CAST(12.40 AS Decimal(10,2)) AS number;
----

.Output
[source, sql]
----
┌─number─┐
│   12.4 │
└────────┘
----

Hmm, nope.
What about if we coerce the value with the `toDecimal32` function instead?


[source, sql]
----
SELECT toDecimal32(12.40, 2) AS number;
----

.Output
[source, sql]
----
┌─number─┐
│   12.4 │
└────────┘
----

Still nope. 
At this point I was pretty stuck - Google didn't have any suggestions and even ChatGPT didn't know what to do.

Lucky for me, my colleague https://github.com/Avogar[Pavel^] came to the rescue by pointing me to the https://clickhouse.com/docs/en/operations/settings/formats#output_format_decimal_trailing_zeros[`output_format_decimal_trailing_zeros`^] setting.
If we enable that, we'll see our trailing 0 as long as we have a Decimal type.


[source, sql]
----
SELECT 12.40 AS number, 
       toDecimal32(number, 2) AS number2, 
       CAST(number AS Decimal(10,2)) AS number3
SETTINGS output_format_decimal_trailing_zeros=1;
----

.Output
[source, sql]
----
┌─number─┬─number2─┬─number3─┐
│   12.4 │   12.40 │   12.40 │
└────────┴─────────┴─────────┘
----

Thanks Pavel!
DuckDB/SQL: Get decade from date
Date: 2023-04-20 02:44:37
In this post we'll learn how to extract the decade from a date using SQL and DuckDB.

Working with dates in SQL can sometimes be a bit tricky, especially when you need to extract specific information, like the decade a date belongs to. 
In this blog post, we'll explore how to easily obtain the decade from a date using DuckDB, a lightweight and efficient SQL database engine. 

First, https://duckdb.org/docs/installation/[install DuckDB^] and launch it:

[source, bash]
----
./duckdb
----

Next, we're going to create a `movies` table that has columns for `title` and `releaseDate`:

[source, sql]
----
create or replace table movies(title VARCHAR, releaseDate DATE);
----

And now let's add some records of famous movies and their release dates:

[source, sql]
----
INSERT INTO movies 
VALUES ('Avatar', '2009-12-10'), 
       ('Star Wars: The Force Awakens', '2015-12-15'),
       ('Titanic', '1997-11-18'),
       ('The Avengers', '2012-04-25'),
       ('Jurassic World', '2015-06-09'),
       ('Furious 7', '2015-04-01');
----

The `releaseDate` column contains the actual day the movie was released, but I want to know the decade.
We can do this by first extracting the year using the `year` function and then dividing that by 10 and multiplying it by 10.

The following query does this:

[source, sql]
----
select title, releaseDate, year(releaseDate)/10*10 AS decade 
From movies;
----

.Output
[options="header"]
|===
| title                         | releaseDate | decade
| Avatar                        | 2009-12-10  | 2000
| Star Wars: The Force Awakens  | 2015-12-15  | 2010
| Titanic                       | 1997-11-18  | 1990
| The Avengers                  | 2012-04-25  | 2010
| Jurassic World                | 2015-06-09  | 2010
| Furious 7                     | 2015-04-01  | 2010
|===

We could then write a new query that counts the number of movies released per decade:

[source, sql]
----
select year(releaseDate)/10*10 AS decade, count(*)
From movies
GROUP BY ALL
----

.Output
[options="header"]
|===
| decade | count_star()
| 2000   | 1
| 2010   | 4
| 1990   | 1
|===

Tada!
Python 3.10: Pattern matching with match/case
Date: 2023-05-09 02:44:37
In this post we'll learn about Python 3.10's pattern matching with the match/case statement.

:icons: font

I've been reading https://www.oreilly.com/library/view/fluent-python-2nd/9781492056348/[Fluent Python^] and learnt about pattern matching with the match/case statement, introduced in Python 3.10.
You can use it instead of places where you'd otherwise use `if`, `elif`, `else` statements.

I created a small example to understand how it works.
The following function takes in a list where the first argument should be `foo`, followed by a variable number of arguments, which we print to the console:

[source, python]
----
def parse_if(x):
    if x[0] == "foo":
        if len(x[1:]) > 1:
            print(f"Multiple values: {'_'.join([str(n) for n in x[1:]])}")
        elif len(x[1:]) == 1:
            if x[1] == 5:
                print(f"Only one value and it's the magical 5")
            else:
                print(f"Only one value: {x[1]}")
        else:
            print("No values")
    else:
        raise SyntaxError(f"Invalid expression: {x}")
----

Let's run that function with some different arguments:

[source, python]
----
parse_if(["foo", *(range(0,10))])
parse_if(["foo", 1])
parse_if(["foo", 5dada])
parse_if(["foo"])
parse_if(["bar"])
----

.Output
[source, text]
----
Multiple values: 0_1_2_3_4_5_6_7_8_9
Only one value: 1
Only one value and it's the magical 5
No values
Traceback (most recent call last):
  File "/Users/markhneedham/projects/fluent_python/destruct.py", line 40, in <module>
    fn(["bar"])
  File "/Users/markhneedham/projects/fluent_python/destruct.py", line 14, in parse_if
    raise SyntaxError(f"Invalid expression: {x}")
SyntaxError: Invalid expression: ['bar']
----

A version of that function rewritten to use pattern matching would look like this:

[source, python]
----
def parse_match(x):
    match x: <1>
        case ['foo']:
            print("No values")
        case ['foo', 5]: <2>
            print(f"Only one value and it's the magical 5")
        case ['foo', other]: <3>
            print(f"Only one value: {other}")
        case ['foo', *other]: <4>
            print(f"Multiple values: {'_'.join([str(n) for n in other])}")
        case _: <5>
            raise SyntaxError(f"Invalid expression: {x}")
----
<1> `x` is our pattern matching subject
<2> We can match literal values inside a case statement
<3> Or we can assign to a variable instead.
<4> The `*` operator matches multiple values
<5> `_` is a wildcard character that matches everything else

I think this second example is a little bit easier to read than the first and I can see this syntax being super useful for more gnarly combinations of predicates.
Node.js: Minifying JSON documents
Date: 2023-05-30 02:44:37
In this post we'll learn how to minify JSON documents so that they don't take up so much space.

I often need to minimise the schema and table config files that you use to configure Apache Pinot so that they don't take up so much space.
After doing this manually for ages, I came across the https://www.npmjs.com/package/json-stringify-pretty-compact[json-stringify-pretty-compact^] library, which speeds up the process.

We can install it like this:

[source, bash]
----
npm install json-stringify-pretty-compact
----

And then I have the following script:

.minify.mjs
[source, javascript]
----
import pretty from 'json-stringify-pretty-compact';

let inputData = '';

process.stdin.on('data', (chunk) => {
    inputData += chunk;
});

process.stdin.on('end', () => {
    const value = JSON.parse(inputData);
    console.log(pretty(value));
});

process.stdin.resume();
----

Imagine we then have the following file:

.config/schema.json
[source, json]
----
{
    "schemaName": "parkrun",
    "primaryKeyColumns": ["competitorId"],
    "dimensionFieldSpecs": [
      {
        "name": "runId",
        "dataType": "STRING"
      },  
      {
        "name": "eventId",
        "dataType": "STRING"
      },     
      {
        "name": "competitorId",
        "dataType": "LONG"
      },      
      {
        "name": "rawTime",
        "dataType": "INT"
      },

      {
        "name": "lat",
        "dataType": "DOUBLE"
      },
      {
        "name": "lon",
        "dataType": "DOUBLE"
      },
      {
        "name": "location",
        "dataType": "BYTES"
      },
      {
        "name": "course",
        "dataType": "STRING"
      }
    ],
    "metricFieldSpecs": [
      {
        "name": "distance",
        "dataType": "DOUBLE"
      }
    ],
    "dateTimeFieldSpecs": [{
      "name": "timestamp",
      "dataType": "TIMESTAMP",
      "format" : "1:MILLISECONDS:EPOCH",
      "granularity": "1:MILLISECONDS"
    }]
  }
----

The field specs take up so much unnecessary space, so let's get our script to sort that out:

[source, bash]
----
cat config/schema.json | node minify.mjs
----

.Output
[source, json]
----
{
  "schemaName": "parkrun",
  "primaryKeyColumns": ["competitorId"],
  "dimensionFieldSpecs": [
    {"name": "runId", "dataType": "STRING"},
    {"name": "eventId", "dataType": "STRING"},
    {"name": "competitorId", "dataType": "LONG"},
    {"name": "rawTime", "dataType": "INT"},
    {"name": "lat", "dataType": "DOUBLE"},
    {"name": "lon", "dataType": "DOUBLE"},
    {"name": "location", "dataType": "BYTES"},
    {"name": "course", "dataType": "STRING"}
  ],
  "metricFieldSpecs": [{"name": "distance", "dataType": "DOUBLE"}],
  "dateTimeFieldSpecs": [
    {
      "name": "timestamp",
      "dataType": "TIMESTAMP",
      "format": "1:MILLISECONDS:EPOCH",
      "granularity": "1:MILLISECONDS"
    }
  ]
}
----
DuckDB/SQL: Create a list of numbers
Date: 2023-05-24 02:44:37
In this post we'll learn how to create a list of numbers using DuckDB.

:icons: font

While in DuckDB land, I wanted to create a list of numbers, just like you can with Cypher's https://neo4j.com/docs/cypher-manual/current/functions/list/#functions-range[`range`^] function.
After a bit of searching that resulted in very complex solutions, I came across the Postgres https://www.postgresql.org/docs/current/functions-srf.html[`generate_series`^] function, which does the trick.

We can use it in place of a table, like this:

[source, sql]
----
SELECT * 
FROM generate_series(1, 10);
----

[options="header"]
.Output
|===
|generate_series
|1
|2
|3
|4
|5
|6
|7
|8
|9
|10
|===

As you can see from the results, this returns 10 rows with one number per row.

Alternatively, we can use it inside a `SELECT` clause, like this:

[source, sql]
----
SELECT generate_series(1, 10);
----

[options="header"]
.Output
|===
|generate_series(1, 10)
|[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
|===

This time it returns one row with an array containing 10 values.

I later learnt that there is also a https://duckdb.org/docs/sql/functions/nested.html#range-functions[`range`^] function, exactly like the Cypher function that I mentioned at the beginning of the post.

The difference between `range` and `generate_series` is that for `range``, the stop parameter is exclusive, whereas for `generate_series` it is inclusive.

So if we asked `range` to generate numbers between 1 and 11 with a step of 2, it would result in the following output, which excludes 11:

[source, sql]
----
SELECT range(1, 11, 2);
----

[options="header"]
.Output
|===
|range(1, 11, 2)
|[1, 3, 5, 7, 9]
|===
Cropping a video using FFMPEG
Date: 2023-05-15 02:44:37
In this post we'll learn how to use the FFMPEG command line tool to crop part of a video.

:icons: font

I needed to crop a video that I used as part of a video on my YouTube channel, https://www.youtube.com/shorts/7950UzRDZBE[Learn Data With Mark^], and Camtasia kept rendering a black screen. 
So I had to call for FFMPEG!

== Cropping the bottom of a video

My initial video was 2160 x 3840 but I didn't need the bottom 1920 pixels because I'm using that part of the screen for a video of me.
If I was just rendering that video exactly as it is I wouldn't have bothered cropping it, but because I wanted to zoom into different sections I needed to crop out the bottom bit.

I adapted an example from https://ottverse.com/crop-a-video-with-ffmpeg-crop-filter/[this article from OTTVerse^] as my first cut:

[source, bash]
----
ffmpeg -i ConfigSegThresholdDemo.mp4 \
  -filter_complex "[0:v]crop=in_w:in_h-1920:0:0[cropped]" \
  -map "[cropped]" \
  -r output.mp4
----

This command tells FFMPEG to crop a region: 

* whose width is equal to the source video's width,
* whose height is equal to the source video's height minus 1920 pixels,
* and the origin is at [0,0]

This worked, but it lost the quality that I had in the initial video.
I fixed this by also passing the https://stackoverflow.com/questions/25569180/ffmpeg-convert-without-loss-quality[`qscale`^] flag:

[source, bash]
----
ffmpeg -i ConfigSegThresholdDemo.mp4 \
  -filter_complex "[0:v]crop=in_w:in_h-1920:0:0[cropped]" \
  -map "[cropped]" \
  -qscale 0 \
  -r output.mp4
----

I think it worked out pretty well, but https://youtube.com/shorts/7950UzRDZBE?feature=share[watch the video^] and let me know if you agree!

== Cropping the top and bottom of a video

For another video that started off 2160 x 3840, I wanted to take off the top 620 pixels and the bottom 370 pixels. 
I did it by running the following command:

[source, bash]
----
ffmpeg -i PivotDemo.mp4 \
  -filter_complex "[0:v]crop=2160:2850:0:620[cropped]" \
  -map "[cropped]" \
  -qscale 0 \
  PivotDemo-Cropped.mp4
----

Let's break down the crop parameters, `crop=2160:2850:0:620`

* `2160` - That's the width of region that I want to crop (i.e. keep the width the same)
* `2850` - The height of the region that I want to crop (`3840 - 620 (top crop) - 370 (bottom crop)`)
* `0`- The `x` pixel from where the cropping should begin (i.e. 0 pixels from the left)
* `620` - The `y` pixel from where the cropping should begin (i.e. 620 pixels from the top)
Python: Naming slices
Date: 2023-05-13 02:44:37
In this post we'll learn about Python's named slices and show how to use that to extract parts of a vehicle identification number.

:icons: font

Another gem from https://www.oreilly.com/library/view/fluent-python-2nd/9781492056348/[Fluent Python^] is that you can name slices. 
How did I not know that?!

Let's have a look how it works using an example of a https://www.autocheck.com/vehiclehistory/vin-basics[Vehicle Identification Number^], which has 17 characters that act as a unique identifier for a vehicle.
Different parts of that string mean different things. 

So given the following VIN:

[source, python]
----
vin = "2B3HD46R02H210893"
----

We can extract components like this:

[source, python]
----
print(f"""
World manufacturer identifier: {vin[0:3]}
Vehicle Descriptor: {vin[3:9]}
Vehicle Identifier: {vin[9:17]}
""".strip())
----

If we run this code, we'll see the following output:

.Output
[source, text]
----
World manufacturer identifier: 2B3
Vehicle Descriptor: HD46R0
Vehicle Identifiern: 2H210893
----

Let's say we want to reuse those slices elsewhere in our code. 
Maybe when we write the code we can remember what each of the slice indexes mean, but for future us it would help to name them, which we can do using the https://docs.python.org/3.11/library/functions.html#slice[`slice`^] function.

Our code would then look like this:

[source, python]
----
world_manufacturer_id = slice(0,3)
vehicle_descriptor = slice(3,9)
vehicle_identifier = slice(9,17)

print(f"""
World manufacturer identifier: {vin[world_manufacturer_id]}
Vehicle Descriptor: {vin[vehicle_descriptor]}
Vehicle Identifier: {vin[vehicle_identifier]}
""".strip())
----
DuckDB: Ingest a bunch of CSV files from GitHub
Date: 2023-05-25 02:44:37
In this post we'll learn how to ingest a collection of CSV files from a GitHub repository.

:icons: font

Jeff Sackmann's https://github.com/jeffsackmann/tennis_atp/[tennis_atp^] repository is one of the best collections of tennis data and I wanted to ingest the ATP Tour singles matches using the DuckDB CLI.
In this blog post we'll learn how to do that.

Usually when I'm ingesting data into DuckDB I'll specify the files that I want to ingest using the wildcard syntax.
In this case that would mean running a query like this:

[source, sql]
----
CREATE OR REPLACE TABLE matches AS 
SELECT * 
FROM "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_*.csv"
----

But we can't use that technique for files on GitHub because it's not a file system. 
If we run the query above, we'll get the following error message:

[source, sql]
----
Error: Invalid Error: HTTP Error: Unable to connect to URL "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_*.csv": 404 (Not Found)
----

So, instead, we need to construct a list of URLs, which we can do using a combination of the `generate_series` function that I https://www.markhneedham.com/blog/2023/05/24/duckdb-sql-create-list-numbers/[covered in my last blog post^] and the `list_transform` function.

We'll use `generate_series` to create a list of the years, like this:
 
[source, sql]
----
SELECT range(1968, 1970)
----

[options="header"]
.Output
|===
|range(1968, 1970)
|[1968, 1969]
|===

And then we can use `list_transform` to map or project over each value to construct a URL:

[source, sql]
----
SELECT list_transform(
  range(1968, 1970),
  y ->  'https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_' || y || '.csv'
) AS files; 
----

[options="header"]
.Output
|===
|files
|[https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_1968.csv, https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_1969.csv]
|===

And then finally we can bring it all together to ingest the files from 1968 until 2023:

[source, sql]
----
CREATE OR REPLACE TABLE matches AS 
SELECT * FROM read_csv_auto(
    list_transform(
      range(1968, 2023),
      y ->  'https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master/atp_matches_' || y || '.csv'
    ), 
    types={'winner_seed': 'VARCHAR', 'loser_seed': 'VARCHAR'}
);
----

It'll take a few seconds, but once it's done we can do an exploratory query to make sure everything was ingested:


[source, sql]
----
SELECT count(*)
FROM matches;
----

[options="header"]
.Output
|===
|count(*)
|188191
|===