Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Skinny Pandas Riding on a Rocket (PyDataGlobal 2020)

3d644406158b4d440111903db1f62622?s=47 ianozsvald
November 11, 2020

Skinny Pandas Riding on a Rocket (PyDataGlobal 2020)

With larger datasets we need to be smarter about how we use Pandas to get results. We’ll look at strategies to shrink our data to get more into RAM, offload computation to tools like Dask or Vaex, store with Parquet or SQLite, make calculations faster and retain debuggability.
For more talks like this see: https://ianozsvald.com/



November 11, 2020


  1. Skinny Pandas Riding on a Rocket @IanOzsvald – ianozsvald.com Ian

    Ozsvald (The inaugural!) PyDataGlobal 2020
  2. •Pandas – get more into RAM & see what’s slow

    •Dask – work on larger datasets and on multi-core •SQLite – slight segue to and oldie and a goodie •Vaex – a Pandas+Dask competitor for certain workflows? •25M row UK Land Registry (house purchase) dataset Today’s goal By [ian]@ianozsvald[.com] Ian Ozsvald
  3.  Interim Chief Data Scientist  19+ years experience 

    Team coaching & public courses –I’m sharing from my Higher Performance Python course Introductions By [ian]@ianozsvald[.com] Ian Ozsvald 2nd Edition!
  4.  Remember – all benchmarks are wrong  Your data/OS/libraries

    will change the results  You need to do your own experiments Benchmarking caveat By [ian]@ianozsvald[.com] Ian Ozsvald
  5.  In-memory only  Operations can be RAM expensive 

    Very wide & rich API  Lots of timeseries tooling  Mixed history with NumPy & Python datatypes Pandas background By [ian]@ianozsvald[.com] Ian Ozsvald
  6. A quick look at the data (25M rows) By [ian]@ianozsvald[.com]

    Ian Ozsvald 25M rows, lots of text columns, circa 10GB in RAM in Pandas before we even start any work (the Pickle file we loaded was 4GB) https://www.gov.uk/government/collections/price-paid-data
  7. The slice we’ll use (25M rows) By [ian]@ianozsvald[.com] Ian Ozsvald

  8. Beware “info” - it underestimates By [ian]@ianozsvald[.com] Ian Ozsvald

  9. Let’s use Category & int32 dtypes By [ian]@ianozsvald[.com] Ian Ozsvald

    10x reduction! 10x speed-up!
  10. So how many sales do we see? By [ian]@ianozsvald[.com] Ian

  11. •Useful package to profile RAM usage in Python •Can track

    whole-program execution with “mprof” •Can track cost of single command with “%memit” memory_profiler By [ian]@ianozsvald[.com] Ian Ozsvald
  12. Note that groupby can be RAM hungry By [ian]@ianozsvald[.com] Ian

    Ozsvald Unfortunately the categorical version doesn’t save much RAM
  13. “ipython memory usage” (“auto- %memit”) By [ian]@ianozsvald[.com] Ian Ozsvald

  14. Install “bottleneck” for faster math By [ian]@ianozsvald[.com] Ian Ozsvald Bottleneck

    (and numexpr) are not installed by default and they offer free speed- ups, so install them!
  15. Drop to NumPy if you know you can By [ian]@ianozsvald[.com]

    Ian Ozsvald Note that NumPy’s mean is not-NaN-aware but Pandas’ is, so the Pandas version is doing more work. This example works if you know you have no NaNs See my PyDataAmsterdam 2020 talk for more details (see my blog)
  16.  Use Categoricals for speed-ups and to save RAM 

    Try Int32 and Float32 – half the size, sometimes faster  Install “bottleneck” and “numexpr” for free speed-ups  We can only take this so far, to scale we need newer tools Tips By [ian]@ianozsvald[.com] Ian Ozsvald
  17.  Rich library wrapping DataFrames, Arrays and arbitrary Python functions

    (very powerful, quite complex)  Popular for scaling Pandas  Wraps Pandas DataFrames Dask for larger datasets By [ian]@ianozsvald[.com] Ian Ozsvald
  18.  Bigger than RAM or “I want to use all

    my cores”  Generally you’ll use Parquet (or CSV or many choices)  The Dashboard gives rich diagnostics  Write Pandas-like code  Lazy – use “.compute()” Dask for larger datasets By [ian]@ianozsvald[.com] Ian Ozsvald
  19. Resampling across 100 partitions By [ian]@ianozsvald[.com] Ian Ozsvald Reasonably fast

    and uses 1GB of RAM and 8 processes 100 parquet files of 20MB each (probably 100MB would be a better size)
  20. memory_profiler’s mprof By [ian]@ianozsvald[.com] Ian Ozsvald 2 processes, 1.2GB total

    RAM footprint groupby similar to the Pandas version plus some preparation (which took 11GB) but takes 45 seconds Caveat – this one loads all columns to amplify memory usage (which we could optimise!) for this demo
  21. memory_profiler’s mprof By [ian]@ianozsvald[.com] Ian Ozsvald With 16 processes, 6GB

    total RAM usage but only 25 seconds You have control over the RAM footprint Note selecting only the subset of needed columns significantly speeds this up
  22. Time series resampling across 100 partitions By [ian]@ianozsvald[.com] Ian Ozsvald

    Dask Dashboard Task-list as a diagnostics
  23.  Keep it simple – many moving parts  Few

    processes, lots of RAM per process  Always call .compute()  ddf.sample(frac=0.01) to subsample makes things faster  Check Array, Bag and Delayed options in Dask too Top Dask Tips By [ian]@ianozsvald[.com] Ian Ozsvald
  24.  20 years old, 2M tests  “Most widely deployed

    db on the planet”  “To be supported through to 2050”  1GB rows, 281TB max db, Android, Java, Python, Firefox, ... SQLite – why use “boring SQL”? By [ian]@ianozsvald[.com] Ian Ozsvald
  25. Query and retrieve a DataFrame By [ian]@ianozsvald[.com] Ian Ozsvald

  26.  Circa 90MB SQLite process in RAM  Happily reads

    large (many GB-TB) database files  “Fast enough” for many applications & multi-user  Sane binary format for data interchange (beats CSV?)  Read/write and could be added to source control SQLite - simple, portable By [ian]@ianozsvald[.com] Ian Ozsvald
  27.  A lesser known Pandas alternative  Designed for billions

    of rows, probably in HDF5 (single big file)  Memory maps to keep RAM usage low  Efficient groupby, faster strings, NumPy focused (also ML, visualisation and more), lazy expressions Vaex – a newer DataFrame system By [ian]@ianozsvald[.com] Ian Ozsvald
  28.  Typically you convert data to HDF5 (with helper functions)

     Once converted opening the file and running queries is very quick  Results look like Pandas DataFrames but aren’t Vaex – a newer DataFrame system By [ian]@ianozsvald[.com] Ian Ozsvald
  29. Completion’s per day By [ian]@ianozsvald[.com] Ian Ozsvald

  30. Vaex RAM usage on a groupby By [ian]@ianozsvald[.com] Ian Ozsvald

    Using the same groupby operations we had with Pandas (which cost up to 1GB) – Vaex is far more efficient with RAM usage than Pandas or Dask and much faster than Dask! Sidenote – string operations (e.g. lower, contains) much faster than Pandas
  31.  Dask – multi-core, -machine & -files, Vaex – multicore,

    single machine and typically single file  Dask allows most of Pandas and supports huge NumPy Arrays, a Bag, Delayed functions and lots more  Both can handle billions of rows and ML. Both have readable code Dask and Vaex? By [ian]@ianozsvald[.com] Ian Ozsvald
  32.  Stick to Dask if you need Pandas functions 

    Think about Vaex common DF operations  See blog for my classes + many past talks  I’d love a postcard if you learned something new! Summary By [ian]@ianozsvald[.com] Ian Ozsvald
  33. File sizes By [ian]@ianozsvald[.com] Ian Ozsvald Format Size on Disk

    (GB) CSV (original) 4.4 Pickle 5 3.6 Parquet uncompressed (faster to load) 2.2 Parquet + Snappy (small speed penalty) 1.5 SQLite 4.6 HDF5 (Vaex) 4.5
  34.  We can export from Pandas with df.to_sql  Works

    via SQLAlchemy  We can execute arbitrary SQL e.g. to “CREATE INDEX” SQLite – why use “boring SQL”? By [ian]@ianozsvald[.com] Ian Ozsvald