Python

How to get average or mean between two pandas pd.Timestamp objects?

In order to compute the mean value between two pd.Timestamp instances, subtract them to obtain a pd.Timedelta and then add said Timedelta object to the first (smaller) timestamp:

t1 = pd.Timestamp('now')
t2 = pd.Timestamp('now')

mean_timestamp = t1 + ((t2 - t1) / 2)

 

Posted by Uli Köhler in pandas, Python

How to display BytesIO containing a PNG image in Jupyter/IPython

In IPython, you can use the Image() function to show an image:

 

from IPython.display import Image
Image(filename="img1.png")

But what if you don’t have the PNG data in a file but in a BytesIO?

Use .getvalue():

from IPython.display import Image

my_bio = ... # Insert your code here
Image(my_bio.getvalue())

 

 

Posted by Uli Köhler in Python

How to export certificates from Traefik certificate store

Traefik stores certificates as base64 encoded X.509 certificates and keys inside the certificate store.

This is a python script to export certificate from Traefik certificate store .json file:

import json
import base64

# Read Traefik ACME JSON
with open("acme.json") as acme_file:
    acme = json.load(acme_file)

# Select certificates from a specific resolver
resolver_name = "my-resolver"
certificates = acme[resolver_name]["Certificates"]

# Find the specific certificate we are looking for
certificate = [certificate for certificate in certificates if "myddomain.com" in certificate["domain"].get("sans", [])][0]

# Extract X.509 certificate data
certificate_data = base64.b64decode(certificate["certificate"])
key_data = base64.b64decode(certificate["key"])

# Export certificate and key to file
with open("certificate.pem", "wb") as certfile:
    certfile.write(certificate_data)

with open("key.pem", "wb") as keyfile:
    keyfile.write(key_data)

Note that depending on what is the primary name for your certificate, you might need to use

if "myddomain.com" == certificate["domain"]["main"]

instead of

if "myddomain.com" in certificate["domain"].get("sans", [])

 

Posted by Uli Köhler in Python, Traefik

How to filter OpenStreetmap ways by tag from .osm.pbf file using Python & Osmium

In our previous post Minimal example how to read .osm.pbf file using Python & osmium we investigated how to read a .osm.pbf file and count all nodes, ways and relations.

Today, we’ll investigate how to filter for specific ways by tag using osmium working on .osm.pbf files. In this example, we’ll filter by highwaytrunk

#!/usr/bin/env python3
import osmium as osm
from collections import namedtuple
import itertools

Way = namedtuple("Way", ["id", "nodes", "tags"])

class FindHighwayTrunks(osm.SimpleHandler):
    """
    Find ways with "highway: trunk" tag
    """

    def __init__(self):
        osm.SimpleHandler.__init__(self)
        self.ways = []

    def way(self, way):
        # If this way is a highway: trunk, ...
        if way.tags.get("highway") == "trunk":
            # ... add it to self.ways
            # NOTE: We may not keep a reference to the way object,
            # so we have to create a new Way object
            nodes = [node.ref for node in way.nodes]
            self.ways.append(Way(way.id, nodes, dict(way.tags)))

# Find ways with the given tag
way_finder = FindHighwayTrunks()
way_finder.apply_file("kenya-latest.osm.pbf")

print(f"Found {len(way_finder.ways)} ways")

This example uses kenya-latest.osm.pbf downloaded from Geofabrik, but you can use any .osm.pbf file. Parsing said file takes about 10 seconds on my Desktop.

Note that we can’t just self.ways.append(way) because way is a temporary/internal Protobuf object and may not be used directly since. Hence, we create our own object that just wraps the extracted contents of way: Its id, its list of nodes (node IDs) and a dictionary containing the tags. Otherwise, you’ll see this error message:

Traceback (most recent call last):
  File "run.py", line 32, in <module>
    trunk_handler.apply_file("kenya-latest.osm.pbf")
RuntimeError: Way callback keeps reference to OSM object. This is not allowed.
Posted by Uli Köhler in Geography, Geoinformatics, OpenStreetMap, Python

How to install cartopy on Ubuntu

First, you need to install libproj (which is a dependency of cartopy) using

sudo apt -y install libproj-dev libgeos-dev

After that, install cartopy using

sudo pip3 install cartopy

 

Posted by Uli Köhler in Cartopy, Geoinformatics, Python

How fix Cartopy pip install error ‘Proj 4.9.0 must be installed.’

Problem:

When trying to install cartopy using e.g. pip3 install cartopy you see this error message:

Collecting cartopy
  Downloading Cartopy-0.19.0.post1.tar.gz (12.1 MB)
     |████████████████████████████████| 12.1 MB 13.2 MB/s 
  Installing build dependencies ... done
  Getting requirements to build wheel ... error
  ERROR: Command errored out with exit status 1:
   command: /usr/bin/python3 /tmp/tmp2q7tvpo8 get_requires_for_build_wheel /tmp/tmpfwd5htse
       cwd: /tmp/pip-install-dg2i313t/cartopy
  Complete output (1 lines):
  Proj 4.9.0 must be installed.
  ----------------------------------------
ERROR: Command errored out with exit status 1: /usr/bin/python3 /tmp/tmp2q7tvpo8 get_requires_for_build_wheel /tmp/tmpfwd5htse Check the logs for full command output.

Solution:

Install proj using e.g.

sudo apt -y install libproj-dev

and then install cartopy again using e.g.

sudo pip3 install cartopy

 

Posted by Uli Köhler in Cartopy, Geoinformatics, Python

How to fix Jupyter Hub Exception: Jupyter command `jupyter-hub` not found.

Problem:

When trying to start jupyter hub, you see this error message:

uli@uli-desktop:~$ jupyter hub
Traceback (most recent call last):
  File "/usr/local/bin/jupyter", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.8/dist-packages/jupyter_core/command.py", line 285, in main
    command = _jupyter_abspath(subcommand)
  File "/usr/local/lib/python3.8/dist-packages/jupyter_core/command.py", line 124, in _jupyter_abspath
    raise Exception(
Exception: Jupyter command `jupyter-hub` not found.

Solution:

Remove the space between jupyter and hub. Instead of

jupyter hub

run

jupyterhub

Example:

uli@uli-desktop $ jupyterhub                           
[I 2021-06-19 02:37:12.766 JupyterHub app:2463] Running JupyterHub version 1.4.1
[I 2021-06-19 02:37:12.766 JupyterHub app:2493] Using Authenticator: jupyterhub.auth.PAMAuthenticator-1.4.1
[I 2021-06-19 02:37:12.766 JupyterHub app:2493] Using Spawner: jupyterhub.spawner.LocalProcessSpawner-1.4.1
[I 2021-06-19 02:37:12.766 JupyterHub app:2493] Using Proxy: jupyterhub.proxy.ConfigurableHTTPProxy-1.4.1
[I 2021-06-19 02:37:12.773 JupyterHub app:1534] Loading cookie_secret from /home/uli/dev/jupyterhub_cookie_secret
[I 2021-06-19 02:37:12.788 JupyterHub proxy:497] Generating new CONFIGPROXY_AUTH_TOKEN
[W 2021-06-19 02:37:12.789 JupyterHub app:1808] No admin users, admin interface will be unavailable.
[W 2021-06-19 02:37:12.789 JupyterHub app:1809] Add any administrative users to `c.Authenticator.admin_users` in config.
[I 2021-06-19 02:37:12.789 JupyterHub app:1838] Not using allowed_users. Any authenticated user will be allowed.
[I 2021-06-19 02:37:12.823 JupyterHub app:2530] Initialized 0 spawners in 0.001 seconds
[W 2021-06-19 02:37:12.825 JupyterHub proxy:699] Running JupyterHub without SSL.  I hope there is SSL termination happening somewhere else...
[I 2021-06-19 02:37:12.825 JupyterHub proxy:703] Starting proxy @ http://:8000
02:37:12.946 [ConfigProxy] info: Proxying http://*:8000 to (no default)
02:37:12.948 [ConfigProxy] info: Proxy API at http://127.0.0.1:8001/api/routes
02:37:12.981 [ConfigProxy] info: 200 GET /api/routes 
[I 2021-06-19 02:37:12.981 JupyterHub app:2778] Hub API listening on http://127.0.0.1:8081/hub/
02:37:12.982 [ConfigProxy] info: 200 GET /api/routes 
[I 2021-06-19 02:37:12.983 JupyterHub proxy:347] Checking routes
[I 2021-06-19 02:37:12.983 JupyterHub proxy:432] Adding route for Hub: / => http://127.0.0.1:8081
02:37:12.984 [ConfigProxy] info: Adding route / -> http://127.0.0.1:8081
02:37:12.985 [ConfigProxy] info: Route added / -> http://127.0.0.1:8081
02:37:12.986 [ConfigProxy] info: 201 POST /api/routes/ 
[I 2021-06-19 02:37:12.986 JupyterHub app:2853] JupyterHub is now running at http://:8000

 

Posted by Uli Köhler in Python

How to run Jupyter Hub (multi-user mode) using systemd

The following script will install Jupyter Hub in single user mode (i.e. only a single Linux user can login to Jupyter Hub using the web interface).

Prerequisites

First install Python & PIP, then NodeJS, then Jupyter Hub, then configurable-http-proxy :

curl -sL https://deb.nodesource.com/setup_16.x | sudo -E bash -
sudo apt-get install -y nodejs python3-pip
sudo pip3 install jupyterhub
sudo npm i -g configurable-http-proxy

Installing the Jupyter Hub systemd service

Run the following script using sudo!

#!/bin/bash
# This script installs and enables/starts the JupyterHub systemd service
export NAME=JupyterHub

# Create service file
cat >/etc/systemd/system/${NAME}.service <<EOF
[Unit]
Description=${NAME}

[Service]
Type=simple
ExecStart=/usr/bin/env jupyterhub --port=11569
User=root
Group=root

Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF

# Enable and start service
systemctl enable --now ${NAME}

This script will install a systemd service named JupyterHub autostart it on boot. It is running on port 11569 by default.

Posted by Uli Köhler in Python, systemd

How to run Jupyter Hub (single user mode) using systemd for autostart

Note: This will only allow a single (preconfigured) user to login to Jupyter lab! See How to run Jupyter Hub (multi-user mode) using systemd on how to deploy Jupyter Hub in multi-user mode using systemd, which allows any Unix user to login!

The following script will install Jupyter Hub in single user mode (i.e. only a single Linux user can login to Jupyter Hub using the web interface).

Prerequisites

First install Python & PIP, then NodeJS, then Jupyter Hub, then configurable-http-proxy :

curl -sL https://deb.nodesource.com/setup_14.x | sudo -E bash -
sudo apt-get install -y nodejs python3-pip
sudo pip3 install jupyterhub
sudo npm i -g configurable-http-proxy

Installing the Jupyter Hub systemd service

Run the following script as the user you want to be able to login! Do not run the script using sudo !

#!/bin/bash
# This script installs and enables/starts a systemd service
export NAME=JupyterHub-$USER
export GROUP=$(id -gn $USER)
# Create service file
sudo tee /etc/systemd/system/${NAME}.service <<EOF
[Unit]
Description=${NAME}

[Service]
Type=simple
ExecStart=/usr/bin/env jupyterhub

WorkingDirectory=$HOME
User=$USER
Group=$GROUP

Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF

# Enable and start service
systemctl enable --now ${NAME}

This script will install a systemd service named JupyterHub-$USER (where $USER is the current user, e.g. uli) and autostart it on boot.

Running multiple services

If you run multiple services, you can run the script for each user and choose a unique port for each service by adding --port=7219 to the /usr/bin/env jupyter hub command, e.g.

ExecStart=/usr/bin/env jupyterhub --port=7192

Alternatively, you can run a single systemwide Jupyter Hub in multi-user mode where multiple users can log in.

Posted by Uli Köhler in Python, systemd

How to fix Jupyter Hub No such file or directory: ‘configurable-http-proxy’

Problem:

When trying to start Jupyter Hub, you see an error message like

[E 2021-06-18 13:07:48.462 JupyterHub proxy:711] Failed to find proxy ['configurable-http-proxy']
    The proxy can be installed with `npm install -g configurable-http-proxy`.To install `npm`, install nodejs which includes `npm`.If you see an `EACCES` error or permissions error, refer to the `npm` documentation on How To Prevent Permissions Errors.
[C 2021-06-18 13:07:48.462 JupyterHub app:2739] Failed to start proxy
    Traceback (most recent call last):
      File "/usr/local/lib/python3.8/dist-packages/jupyterhub/app.py", line 2737, in start
        await self.proxy.start()
      File "/usr/local/lib/python3.8/dist-packages/jupyterhub/proxy.py", line 707, in start
        self.proxy_process = Popen(
      File "/usr/lib/python3.8/subprocess.py", line 854, in __init__
        self._execute_child(args, executable, preexec_fn, close_fds,
      File "/usr/lib/python3.8/subprocess.py", line 1702, in _execute_child
        raise child_exception_type(errno_num, err_msg, err_filename)
    FileNotFoundError: [Errno 2] No such file or directory: 'configurable-http-proxy'

Solution:

As outlined in the Jupyter Hub installation docs, you need to install the configurable-http-proxy npm package in order for Jupyter to work:

sudo npm install -g configurable-http-proxy

In case you haven’t installed npm, see our article on How to install NodeJS 14.x LTS on Ubuntu in 1 minute or find the appropriate distribution for your OS on the NodeJS download page.

Posted by Uli Köhler in Python

How to run Jupyter Lab as systemd service

If you want to run your Jupyter Lab as a network service on any modern Linux distribution, you can install a systemd service that runs Jupyter. First, you need to install jupyter lab using

sudo pip3 install jupyterlab

In case you don’t have pip3, use sudo apt -y install python3-pip or the equivalent on your distribution.

Note that this script will run Jupyter without token authentication and without password and it will listen on any IP (--ip=0.0.0.0) by default. Either change the command line flags or be aware of the security implications !

#!/bin/bash
# This script installs and enables/starts a systemd service
export NAME=Jupyter

# Create service file
cat >/etc/systemd/system/${NAME}.service <<EOF
[Unit]
Description=${NAME}

[Service]
Type=simple
ExecStart=/usr/bin/env jupyter lab --ip=0.0.0.0 --port 17256 --LabApp.token=''

WorkingDirectory=/home/uli/jupyter
User=uli
Group=uli

Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF

# Enable and start service
systemctl enable --now ${NAME}

You need to change the following entries in the script in order to make it work for you:

WorkingDirectory=/home/uli/jupyter
User=uli
Group=uli

Set the WorkingDirectory to whatever directory you want Jupyter to run in. Note that anyone being able to access the webinterface will basically have full access to that directory!

Set User and Group to the user that should run. Note that running Jupyter as root is not allowed. In case you still want to do it, add the --allow-root flag to the command line options.

Now run the script as root to install the service:

sudo ./install-jupyter-service.sh

Now you can access Jupyter at http://[ip of the computer]:17256.

Changing the configuration

In order to change the configuration, I recommend to edit /etc/systemd/systemd/Jupyter.service (or /etc/systemd/systemd/${NAME}.service if you changed export NAME=Jupyter) directly. After that, run

sudo systemctl daemon-reload
sudo systemctl restart Jupyter

You can also change the installation script and re-run it, but you still need to run daemon-reload and restart.

Running multiple Jupyter instances

In order to run  multiple instances, just run multiple copies of the installation script with different names. For example, use

export NAME=Jupyter-DeepLearning

Debugging Jupyter Lab output

If you have problems with Juypter Lab starting up, use

sudo systemctl status Jupyter

in order to view the status and

sudo journalctl -xfu Jupyter

to view all the logs.

Status output example:

● Jupyter.service - Jupyter
     Loaded: loaded (/etc/systemd/system/Jupyter.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2021-06-11 03:44:28 CEST; 4s ago
   Main PID: 48753 (jupyter-lab)
      Tasks: 1 (limit: 14226)
     Memory: 51.7M
     CGroup: /system.slice/Jupyter.service
             └─48753 /usr/bin/python3 /usr/local/bin/jupyter-lab --ip=0.0.0.0 --port 17256 --LabApp.token=

Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.215 ServerApp] nbclassic | extension was successfully loaded.
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.216 LabApp] JupyterLab extension loaded from /usr/local/lib/python3.8/dist-packages/jupyterlab
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.216 LabApp] JupyterLab application directory is /usr/local/share/jupyter/lab
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.219 ServerApp] jupyterlab | extension was successfully loaded.
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.220 ServerApp] Serving notebooks from local directory: /dev/shm
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.220 ServerApp] Jupyter Server 1.8.0 is running at:
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.220 ServerApp] http://uli-desktop:17256/lab
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.220 ServerApp]     http://127.0.0.1:17256/lab
Jun 11 03:44:29 uli-desktop env[48753]: [I 2021-06-11 03:44:29.220 ServerApp] Use Control-C to stop this server and shut down all kernels (twice to skip confirmation).
Jun 11 03:44:29 uli-desktop env[48753]: [W 2021-06-11 03:44:29.224 ServerApp] No web browser found: could not locate runnable browser.

Uninstalling the Jupyter Lab service

In order to just stop and disable autostart (but not uninstall) the jupyter lab service, use

sudo systemctl disable --now Jupyter

After that, you can just remove the service file in order to permanently uninstall the service:

sudo rm /etc/systemd/system/Jupyter.service

You can always reinstall using our installation script.

Note that if you have changed the export NAME=... line, you need to replace Jupyter by the value of Name

Posted by Uli Köhler in Python, systemd

How to disable Jupyter Token Authentication

If you want to run Jupyter in a context where the token authentication should be disabled, use the

--LabApp.token=''

command line flag. This will users allow to access the Jupyter without entering any credentials.

Note that this has security implications and everyone that can access the Jupyter server IP & port will be able to access Jupyter! I recommend to at least add another layer of authentication like HTTP basic authentication if you use Jupyter in a public network.

Posted by Uli Köhler in Python

How to suppress legend in pandas .plot()

In order to suppress the legend when using pandas .plot(), use

legend=False

as an Argument to .plot(), for example:

df.plot(legend=False)

 

Posted by Uli Köhler in pandas, Python

How to generate WireGuard key (private & public) in Python without dependencies

The following code allows you to generate a WireGuard private & public key without having to install any Python library.

import subprocess

def generate_wireguard_keys():
    """
    Generate a WireGuard private & public key
    Requires that the 'wg' command is available on PATH
    Returns (private_key, public_key), both strings
    """
    privkey = subprocess.check_output("wg genkey", shell=True).decode("utf-8").strip()
    pubkey = subprocess.check_output(f"echo '{privkey}' | wg pubkey", shell=True).decode("utf-8").strip()
    return (privkey, pubkey)

Usage example:

print(generate_wireguard_key())

Output:

('KIm+ZlY86I+cInG4FWZpKmhADUnxrqhdtQ5UzaFbuVs=', 'ctX9oUw+CkRe7GfSmUHAB9JjLfQWALOs0gXU9Ikhg1g=')
Posted by Uli Köhler in Networking, Python

How to make your Matplotlib plot nicer in 15 seconds

Use plt.style.ggplot():

from matplotlib import pyplot as plt
plt.style.use("ggplot")

Before:

After:

 

Posted by Uli Köhler in Python

How to fix pandas to_sql() AttributeError: ‘DataFrame’ object has no attribute ‘cursor’

Problem:

You are trying to save your DataFrame in an SQL database using pandas to_sql(), but you see an exception like

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-21-3788db1a4131> in <module>
      7 db = sqlalchemy.create_engine('sqlite:///timeseries.db' class="ansi-blue-fg">)
      8 
----> 9 df.to_sql('timeseries', df)

~/miniconda3/lib/python3.8/site-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2603         from pandas.io import sql
   2604 
-> 2605         sql.to_sql(
   2606             self,
   2607             name,

~/miniconda3/lib/python3.8/site-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    587         )
    588 
--> 589     pandas_sql.to_sql(
    590         frame,
    591         name,

~/miniconda3/lib/python3.8/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1825             dtype=dtype,
   1826         )
-> 1827         table.create()
   1828         table.insert(chunksize, method)
   1829 

~/miniconda3/lib/python3.8/site-packages/pandas/io/sql.py in create(self)
    719 
    720     def create(self):
--> 721         if self.exists():
    722             if self.if_exists == "fail":
    723                 raise ValueError(f"Table '{self.name}' already exists.")

~/miniconda3/lib/python3.8/site-packages/pandas/io/sql.py in exists(self)
    706 
    707     def exists(self):
--> 708         return self.pd_sql.has_table(self.name, self.schema)
    709 
    710     def sql_schema(self):

~/miniconda3/lib/python3.8/site-packages/pandas/io/sql.py in has_table(self, name, schema)
   1836         query = f"SELECT name FROM sqlite_master WHERE type='table' AND name={wld};"
   1837 
-> 1838         return len(self.execute(query, [name]).fetchall()) > 0
   1839 
   1840     def get_table(self, table_name, schema=None):

~/miniconda3/lib/python3.8/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1677             cur = self.con
   1678         else:
-> 1679             cur = self.con.cursor()
   1680         try:
   1681             cur.execute(*args, **kwargs)

~/miniconda3/lib/python3.8/site-packages/pandas/core/generic.py in __getattr__(self, name)
   5137             if self._info_axis._can_hold_identifiers_and_holds_name(name):
   5138                 return self[name]
-> 5139             return object.__getattribute__(self, name)
   5140 
   5141     def __setattr__(self, name: str, value) -> None:

AttributeError: 'DataFrame' object has no attribute 'cursor'

Solution:

You’re calling to_sql() with the wrong arguments! The second argument needs to be the database connection (e.g. an sqlalchemy engine)! You’re probably calling it like this:

df.to_sql('timeseries', df)

but the second argument needs to be db (or whatever your database connection object is named), not df!

Full working example for to_sql()

import pandas as pd
# Load pre-built time series example dataset
df = pd.read_csv("https://datasets.techoverflow.net/timeseries-example.csv", parse_dates=["Timestamp"])
df.set_index("Timestamp", inplace=True)

import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///timeseries.db')

df.to_sql('timeseries', db, if_exists="replace")
Posted by Uli Köhler in pandas, Python

How to export Pandas dataset to SQLite database

In our previous post we showed how to connect to an SQLite database using sqlalchemy.

In this blogpost, we’ll show how you can export a pandas DataFrame – for example, our time series example dataset – to the SQLite database.

First, we’ll load the example data frame:

import pandas as pd
# Load pre-built time series example dataset
df = pd.read_csv("https://datasets.techoverflow.net/timeseries-example.csv", parse_dates=["Timestamp"])
df.set_index("Timestamp", inplace=True)

Now we can open the SQLite database as shown in our previous post

import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///timeseries.db')

and export the DataFrame to the database:

df.to_sql('timeseries', db, if_exists="replace")

I always recommend using if_exists="replace" (i.e. if the table already exists, replace it) for a quicker development process.

The database looks like this when viewed in an SQLite viewer like HeidiSQL:

Complete code example

import pandas as pd
# Load pre-built time series example dataset
df = pd.read_csv("https://datasets.techoverflow.net/timeseries-example.csv", parse_dates=["Timestamp"])
df.set_index("Timestamp", inplace=True)

import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///timeseries.db')

df.to_sql('timeseries', db, if_exists="replace")

 

Posted by Uli Köhler in pandas, Python

How to create SQLite database using SQLAlchemy

The following code will create my-sqlite.db in the current directory using sqlalchemy:

import sqlalchemy
db = sqlalchemy.create_engine('sqlite:///my-sqlite.db')

Note that you need three slashes in sqlite:/// in order to use a relative path for the DB. If you want an absolute path, use four slashes: sqlite:////.

Posted by Uli Köhler in Python

How to make bounding box larger by a percentage in Python

If we have a bounding box (xmin, xmax, ymin, ymax), we can use the following algorithm to resize the bounding box by e.g. 15% on each side:

xmin -= 0.15 * (xmax - xmin)
xmax += 0.15 * (xmax - xmin)
ymin -= 0.15 * (ymax - ymin)
ymax += 0.15 * (ymax - ymin)

Note that this will reisze the bounding box by 15% (=0.15) on each side, i.e. the total width of the resulting bounding box will be 15% * 2 = 30% larger!

Why it works

xmin -= 0.15 * (xmax - xmin)

is the same as

xmin -= 0.15 * [width of the bounding box]

 

 

Posted by Uli Köhler in Python

How to get bounding box of a country using Natural Earth data and Cartopy

In this example, we’ll determine the bounding box of Kenya using the public domain Natural Earth dataset and the Cartopy library.

Rendering just the bounding box of Kenya (with the actual country being highlighted in green) looks like this:

How to get the bounding box

First we use Cartopy’s cartopy.io.shapereader.natural_earth() function that will automatically download Natural Earth data (if it has already been downloaded, the cached data will be used):

shpfilename = shpreader.natural_earth(resolution='10m',
                                      category='cultural',
                                      name='admin_0_countries')
reader = shpreader.Reader(shpfilename)

Now we can filter for Kenya just like we did in our previous post on How to highlight a specific country using Cartopy:

kenya = [country for country in reader.records() if country.attributes["NAME_LONG"] == "Kenya"][0]

and get the bounding box using kenya.bounds:

lon_min, lat_min, lon_max, lat_max = kenya.bounds

Complete example code

This code will render the image shown above:

import cartopy.crs as ccrs
import cartopy.feature as cf
from cartopy.feature import ShapelyFeature
from matplotlib import pyplot as plt

proj = ccrs.PlateCarree()
ax = plt.axes(projection=proj)
# Show only Africa
#ax.set_extent([-23, 55, -35, 40])
ax.stock_img()

ax.add_feature(cf.COASTLINE, lw=2)
# Make figure larger
plt.gcf().set_size_inches(20, 10)

import cartopy.io.shapereader as shpreader
# Read shape file
shpfilename = shpreader.natural_earth(resolution='10m',
                                      category='cultural',
                                      name='admin_0_countries')
reader = shpreader.Reader(shpfilename)
# Filter for a specific country
kenya = [country for country in reader.records() if country.attributes["NAME_LONG"] == "Kenya"][0]
# Determine bounding box
lon_min, lat_min, lon_max, lat_max = kenya.bounds
ax.set_extent([lon_min, lon_max, lat_min, lat_max])

# Display Kenya's shape
shape_feature = ShapelyFeature([kenya.geometry], ccrs.PlateCarree(), facecolor="lime", edgecolor='black', lw=1)
ax.add_feature(shape_feature)

# Save figure as SVG
plt.savefig("Kenya-Bounding-Box-Tight.svg")

 

Posted by Uli Köhler in Cartopy, Geography, Python