Day 3: Transform Your Data Transformations

We apologise for the delayed delivery of today’s advent calendar update. It’s been a long day of wifi-less planes and non-existent trains. Along the way, I discovered Pocket Coffee, a delightful Italian concoction defined best as pd.concat([espresso_shot, praline_chocolate])

Behind today’s door is a remarkably library packed with so much functionality that I’m seriously considering writing a RAG-based LLM to help identify all the places my code could be simplified by switching out my hand-carved functions for their convenience methods. Introducing… PyJanitor!

1. Load the data

Today’s examples build off Mark Pack’s PollBase, a database of UK voting intention opinion polls between 1943 and 2023. Here’s how it looks once read into pandas:

2. Remove empty rows/columns

It needs a lot of cleaning! Let’s import janitor and use PyJanitor’s .remove_empty() method to remove any rows/columns which are entirely empty:

3. Eliminate extraneous columns

Next, let’s remove all columns with a name like Unnamed: 0, these are mostly used to indicate the month-on-month change in polling for each party. This could easily be derived from the raw numbers using pandas’ .diff() method if required. We rename the second column to Date to avoid this being dropped also.

Note: This operation, like most of PyJanitor’s functions, can easily be done using pandas’ native .drop() method, however the .select_columns() method offers more powerful filtering options such as

4. Auto-clean column names

This yields a much simpler DataFrame. Next, let’s use the rather handy .clean_names() method to simplify & lower-case the column names:

5. Filter out rows not pertaining to opinion polls

Now let’s remove the Date rows containing the string ”GE” (these indicate UK General Elections, rather than opinion polls). We could do this using df.query(“date != ‘GE’”) but I’ll use the opportunity to demo the convenient and very powerful method, .filter_column_isin() . This is a method-chain-friendly equivalent to df = df[df["colour"].isin(["red", "green", "blue"])] with optional negation via the complement=True argument.

6. Pivot to long

Pandas has powerful functionality for reshaping data although personally I find the method names unintuitive and tricky to remember. It’s my #1 reason for referencing the (highly recommended) official pandas cheatsheet:

Visually, it appears we can use the pd.melt() function to turn our wide DataFrame into a long dataset, but this doesn’t play nicely with working in a method chain paradigm. PyJanitor introduces two intuitively named methods: .pivot_longer() and .pivot_wider()

7. Encode columns as Categorical

Our DataFrame is beginning to look like model-ready! With the new party column containing a set of strings, it makes sense to convert this to pandas’ Categorical data type. This can be preferably for performance reasons, as well as to signal the data type to other libraries which can utilise the data type metadata to automatically adapt data visualisation or statistical tests.

8. One-hot encoding with category expansion

Another modelling convenience function, similar to pandas pd.get_dummies() or the one-hot encoder in scikit-learn:

9. Splitting data into features & target for machine learning

It’s common to create a features DataFrame X = df.drop(columns=target) and then y = df.target when working with pandas and modelling libraries such as scikit-learn or statsmodels. PyJanitor brings a convenience function for this.

10. The best of the rest

The above gives an idea of the variety of convenience methods built into PyJanitor. It also includes functionality for:

• Coalescing columns (like the SQL COALESCE() function)

• Concatenation (e.g. for creating a unique index) and deconcatenation (great for working with log data)

• Date conversion helpers to convert from Unix epochs, Excel integer timestamps, and Matlab-style timestamps.

• Handy tools like .complete() (the “opposite of dropna”), .conditional_join(), .count_cumulative_unique(), .currency_column_to_numeric(), .groupby_topk(), .round_to_fraction(), .sort_naturally() and .update_where()

• Industry-specific functionality such as working with FASTA bioinformatics files, generating RDKIT molecular descriptors, converting between various engineering units, currency convertors, getting a company’s name from its symbol, an inflation calculator, reading in multiple CSVs into a single dataframe, mathematical functions such as sigmoid() and softmax(), and flagging jumps exceeding a threshold in a time series dataset.

📝 Do you have feedback? 💬

Do you use PyJanitor? Is there a life-changing function that you use regularly that we missed? Let us know, and we’ll feature it (and credit you)!

If you’re enjoying this series, or have feedback or ideas on how we can make it better, please reach out to us via [email protected] or @CoefficientData on Twitter.

See you tomorrow! 🐍

Your Python Advent Calendar Team 😃