Simon Willison's Weblog: pandas

Fastest Way to Read Excel in Python

2024-01-03T20:04:00+00:00

Haki Benita produced a meticulously researched and written exploration of the options for reading a large Excel spreadsheet into Python. He explored Pandas, Tablib, Openpyxl, shelling out to LibreOffice, DuckDB and python-calamine (a Python wrapper of a Rust library). Calamine was the winner, taking 3.58s to read 500,00 rows—compared to Pandas in last place at 32.98s.

Via lobste.rs

Tags: excel, pandas, python, rust, duckdb, haki-benita

I’m banned for life from advertising on Meta. Because I teach Python.

2023-10-19T14:56:05+00:00

I’m banned for life from advertising on Meta. Because I teach Python.

If accurate, this describes a nightmare scenario of automated decision making.

Reuven recently found he had a permanent ban from advertising on Facebook. They won’t tell him exactly why, and have marked this as a final decision that can never be reviewed.

His best theory (impossible for him to confirm) is that it’s because he tried advertising a course on Python and Pandas a few years ago which was blocked because a dumb algorithm thought he was trading exotic animals!

The worst part? An appeal is no longer possible because relevant data is only retained for 180 days and so all of the related evidence has now been deleted.

Various comments on Hacker News from people familiar with these systems confirm that this story likely holds up.

Via Hacker News

Tags: ethics, facebook, pandas, python, ai, meta, ai-ethics, ai-misuse

Proof of concept: sqlite_utils magic for Jupyter

2020-10-21T17:26:43+00:00

Proof of concept: sqlite_utils magic for Jupyter

Tony Hirst has been experimenting with building a Jupyter “magic” that adds special syntax for using sqlite-utils to insert data and run queries. Query results come back as a Pandas DataFrame, which Jupyter then displays as a table.

Via @psychemedia

Tags: pandas, sqlite, tony-hirst, jupyter, sqlite-utils

Los Angeles Weedmaps analysis

2019-05-30T04:35:42+00:00

Los Angeles Weedmaps analysis

Ben Welsh at the LA Times published this Jupyter notebook showing the full working behind a story they published about LA’s black market weed dispensaries. I picked up several useful tricks from it—including how to load points into a geopandas GeoDataFrame (in epsg:4326 aka WGS 84) and how to then join that against the LA Times neighborhoods GeoJSON boundaries file.

Via Ben Welsh

Tags: data-journalism, geospatial, latimes, pandas, jupyter, ben-welsh

Pyodide: Bringing the scientific Python stack to the browser

2019-04-17T04:23:33+00:00

Pyodide: Bringing the scientific Python stack to the browser

More fun with WebAssembly: Pyodide attempts (and mostly succeeds) to bring the full Python data stack to the browser: CPython, NumPy, Pandas, Scipy, and Matplotlib. Also includes interesting bridge tools for e.g. driving a canvas element from Python. Really interesting project from the Firefox Data Platform team.

Via Hacker News

Tags: mozilla, pandas, python, scipy, webassembly, numpy, pyodide

How to rewrite your SQL queries in Pandas, and more

2018-04-19T18:34:42+00:00

How to rewrite your SQL queries in Pandas, and more

I still haven’t fully internalized the idioms needed to manipulate DataFrames in pandas. This tutorial helps a great deal—it shows the Pandas equivalents for a host of common SQL queries.

Via Gergely Szerovay

Tags: pandas, python, sql

Analyzing my Twitter followers with Datasette

2018-01-28T06:41:38+00:00

I decided to do some ad-hoc analsis of my social network on Twitter this afternoon… and since everything is more fun if you bundle it up into a SQLite database and publish it to the internet I performed the analysis using Datasette.

The end result

Here’s the Datasette database containing all of my Twitter followers: https://simonw-twitter-followers.now.sh/simonw-twitter-followers-b9bff3a

Much more interesting though are the queries I can now run against it. A few examples:

Search my followers (their name, bio and location), return results ordered by follower count. This is a parameterized query - here are the resuts for django.
Whe are my most influential followers based on their own follower count?
For all of my “verified” followers, who are following the least numbers of people themselves?
Sort my followers by their friend-to-follower ratio
In which years did my followers first join Twitter?
What are the most common locations for my followers?
The most common language setting for my followers - 13,504 en, 403 es, 257 fr, 172 pt etc
What are the most common time zones for followers with different languages? This is another parameterized query - here are the results for en, es, fr.

The thing I find most exciting about this use-case for Datasette is that it allows you to construct entire mini-applications using just a SQL query encoded in a URL. Type queries into the textarea, iterate on them until they do something useful, add some :named parameters (which generate form fields) and bookmark the resulting URL. It’s an incredibly powerful way to build custom interfaces for exploring data.

The rest of this post will describe how I pulled the data from Twitter and turned it into a SQLite database for publication with Datasette.

Fetching my followers

To work with the Twitter API, we first need credentials. Twitter still mostly uses the OAuth 1 model of authentication which is infuriatingly complicated, requiring you to sign parameters using two pairs of keys and secrets. OAuth 2 mostly uses a single access token sent over TLS to avoid the signing pain, but Twitter’s API dates back to the times when API client libraries with robust TLS were not a safe assumption.

Since I have to re-figure out the Twitter API every few years, here’s how I got it working this time. I created a new Twitter app using the form on https://apps.twitter.com/ (which is surprisingly hard to find if you start out on the https://developer.twitter.com/ portal). Having created the app I navigated to the “Keys and Access Tokens” tab, scrolled down and clicked the “Create my access token” button. Then I grabbed the four magic tokens from the following spots on the page:

Now in Python I can make properly signed calls to the Twitter API like so:

from requests_oauthlib import OAuth1Session
twitter = OAuth1Session(
    client_key='...',
    client_secret='...',
    resource_owner_key='...',
    resource_owner_secret='...'
)
print(twitter.get(
    'https://api.twitter.com/1.1/users/show.json?screen_name=simonw'
).json())

The Twitter API has an endpoint for retrieving everyone who follows an account as a paginated JSON list: followers/list. At some point in the past few years Twitter got really stingy with their rate limits - most endpoints, including followers/list only allow 15 requests every 15 minutes! You can request up to 200 followers at a time, but with 15,000 followers that meant the full fetch would take 75 minutes. So I set the following running in a Jupyter notebook and went for a walk with the dog.

from requests_oauthlib import OAuth1Session
import urllib.parse
import time

twitter = OAuth1Session(...)
url = 'https://api.twitter.com/1.1/followers/list.json'

def fetch_followers(cursor=-1):
    r = twitter.get(url + '?'+ urllib.parse.urlencode({
        'count': 200,
        'cursor': cursor
    }))
    return r.headers, r.json()

cursor = -1
users = []
while cursor:
    headers, body = fetch_followers(cursor)
    print(headers)
    users.extend(body['users'])
    print(len(users))
    cursor = body['next_cursor']
    time.sleep(70)

A couple of hours later I had a users list with 15,281 user dictionaries in it. I wrote that to disk for safe keeping:

import json
json.dump(users, open('twitter-followers.json', 'w'), indent=4)

Converting that JSON into a SQLite database

I wrote some notes on How to turn a list of JSON objects into a Datasette using Pandas a few weeks ago. This works really well, but we need to do a bit of cleanup first: Pandas prefers a list of flat dictionaries, but the Twitter API has given us back some nested structures.

I won’t do a line-by-line breakdown of it, but here’s the code I ended up using. The expand_entities() function replaces Twitter’s ugly t.co links with their expanded display_url alternatives - then clean_user() flattens a nested user into a simple dictionary:

def expand_entities(s, entities):
    for key, ents in entities.items():
        for ent in ents:
            if 'url' in ent:
                replacement = ent['expanded_url'] or ent['url']
                s = s.replace(ent['url'], replacement)
    return s

def clean_user(user):
    if user['description'] and 'description' in user['entities']:
        user['description'] = expand_entities(
            user['description'], user['entities']['description']
        )
    if user['url'] and 'url' in user['entities']:
        user['url'] = expand_entities(user['url'], user['entities']['url'])
    if 'entities' in user:
        del user['entities']
    if 'status' in user:
        del user['status']

for user in users:
    clean_user(user):

I now have a nice flat list of users dictionaries - a subset of which is provided here for illustration.

One additional step: SQLite’s built-in functions for handling date and time prefer ISO formatted timestamps, but previewing the DataFrame in Jupyter shows that the data I pulled from Twitter has dates in a different format altogether. I can fix this with a one-liner using the ever-handy dateutil library:

from dateutil.parser import parse
import pandas as pd
df = pd.DataFrame(users)
df['created_at'] = df['created_at'].apply(lambda s: parse(s).isoformat())

Here’s the before and after:

Now that the list contains just simple dictionaries, I can load it into a Pandas DataFrame and convert it to a SQLite table like so:

import sqlite3
conn = sqlite3.connect('/tmp/followers.db')
df.to_sql('followers', conn)
conn.close()

Now I can run datasette /tmp/followers.db to preview what I’ve got so far.

Extracting columns and setting up full-text search

This all works fine, but it’s not quite the finished product I demonstrated above. My desired final state has two additional features: common values in the lang, location, time_zone and translator_type columns have been pulled out into lookup tables, and I’ve enabled SQLite full-text search against a subset of the columns.

Normally I would use the -c and -f arguments to my csvs-to-sqlite tool to do this (see my write-up here), but that tool only works against CSV files on disk. I want to work with an in-memory Pandas DataFrame.

So I reverse-engineered my own code and figured out how to apply the same transformations from an interactive Python prompt instead. It ended up looking like this:

from csvs_to_sqlite import utils

conn = sqlite3.connect('/tmp/simonw-twitter-followers.db')

# Define columns I want to refactor:
foreign_keys = {
    'time_zone': ('time_zone', 'value'),
    'translator_type': ('translator_type', 'value'),
    'location': ('location', 'value'),
    'lang': ('lang', 'value'),
}
new_frames = utils.refactor_dataframes(conn, [df], foreign_keys)

# Save my refactored DataFrame to SQLite
utils.to_sql_with_foreign_keys(
    conn, new_frames[0], 'followers',
    foreign_keys, None, index_fks=True
)

# Create the full-text search index across these columns:
fts = ['screen_name', 'description', 'name', 'location']
utils.generate_and_populate_fts(conn, ['followers'], fts, foreign_keys)

conn.close()

Final step: publishing with Datasette

Having run datasette /tmp/simonw-twitter-followers.db to confirm locally that I got the results I was looking for, the last step was to publish it to the internet. As always, I used Zeit Now via the datasette publish command for this final step:

tmp $ datasette publish now simonw-twitter-followers.db \
    --title="@simonw Twitter followers, 27 Jan 2018"
> Deploying /private/var/.../datasette under simonw
> Ready! https://datasette-cmpznehuku.now.sh (copied to clipboard) [14s]
> Synced 2 files (11.29MB) [0ms] 
> Initializing…
> Building
> ▲ docker build
Sending build context to Docker daemon 11.85 MBkB
> Step 1 : FROM python:3
...
> Deployment complete!

Then I ran new alias to assign a permanent, more memorable URL:

 now alias https://datasette-cmpznehuku.now.sh simonw-twitter-followers.now.sh

Tags: pandas, projects, sqlite, twitter, datasette

How to turn a list of JSON objects into a Datasette

2018-01-20T01:07:09+00:00

How to turn a list of JSON objects into a Datasette

ramadis on GitHub cleaned up data on 184,879 crimes reported in Buenos Aires since 2016 and shared them on GitHub as a JSON file. Here are my notes on how to use Pandas to convert JSON into SQLite and publish it using Datasette.

Tags: json, pandas, sqlite, datasette

Big Data Workflow with Pandas and SQLite

2017-11-28T23:02:50+00:00

Big Data Workflow with Pandas and SQLite

Handy tutorial on dealing with larger data (in this case a 3.9GB CSV file) by incrementally loading it into pandas and writing it out to SQLite.

Via Ben Welsh

Tags: csv, pandas, sqlite

Exploring Line Lengths in Python Packages

2017-11-10T15:34:29+00:00

Exploring Line Lengths in Python Packages

Interesting exploration of the impact if the 79 character length limit rule of thumb on various Python packages—and a thoroughly useful guide to histogram plotting in Jupyter, pandas and matplotlib.

Tags: pandas, python, visualization, jupyter

A Minimalist Guide to SQLite

2017-11-02T01:23:17+00:00

A Minimalist Guide to SQLite

Pretty comprehensive actually—covers the sqlite3 command line app, importing CSVs, integrating with Python, Pandas and Jupyter notebooks, visualization and more.

Tags: pandas, python, sqlite, jupyter

Exploring United States Policing Data Using Python

2017-10-29T16:58:36+00:00

Exploring United States Policing Data Using Python

Outstanding introduction to data analysis with Jupyter and Pandas.

Tags: open-data, pandas, python, jupyter

Streaming Dataframes

2017-10-19T14:25:09+00:00

Streaming Dataframes

This is some deep and brilliant magic: Matthew Rocklin’s Streamz Python library provides some elegant abstractions for consuming infinite streams of data and calculating cumulative averages and rolling reductions... and now he’s added an integration with jupyter that lets you embed bokeh graphs and pandas dataframe tables that continue to update in realtime as the stream continues! Check out the animated screenshots, this really is a phenomenal piece of work.

Tags: pandas, jupyter

PyPy v5.9 Released, Now Supports Pandas, NumPy

2017-10-05T16:58:44+00:00

PyPy v5.9 Released, Now Supports Pandas, NumPy

NumPy and Pandas now work on PyPy2.7. “Many other modules based on C-API extensions work on PyPy as well.”

Tags: pandas, pypy, numpy

Generating interactive HTML charts from Python?

2016-11-25T20:05:00+00:00

My answer to Generating interactive HTML charts from Python? on Ask MetaFilter

D3 is absolutely amazing but the learning curve is a bit steep. Totally worth the effort to learn it in the long run, but it's not so useful if you want to get something done quickly.

I've used NVD3 successfully in the past - it's another high level library on top of D3. Much faster to get results than D3 on its own.

From your description it sounds like you should also check out crossfilter which, for the right use-cases, is phenomenal.

Tags: ask-metafilter, bokeh, pandas, python, visualization, plotly, datavis

Panda Tuesday; The History of the Panda, New APIs, Explore and You

2009-03-04T11:49:21+00:00

Panda Tuesday; The History of the Panda, New APIs, Explore and You

Flickr’s Rainbow Vomiting Panda of Awesomeness now has a family of associated APIs.

Tags: apis, flickr, pandas