<jupyter_start><jupyter_text>'The 80/20 Pandas Tutorial: 5 Methods to Handle Most Data Transformation Needs'> An opinionated pandas tutorial on my preferred methods to accomplish the most essential data transformation tasks in a way that will make veteran R and tidyverse users smile.- toc: false- badges: true- comments: true- categories: [pandas, tidyverse]- hide: false Oh, pandas. In addition to being everyone's favorite vegetarian bear from south central China, it's also _the _python library for working with tabular data, a.k.a. dataframes.When you dive into pandas, you'll quickly find out that there is a lot going on; indeed there are [hundreds](https://pandas.pydata.org/docs/reference/frame.html) of methods for operating on dataframes. But luckily for us, as with many areas of life, there is a [Pareto Principle](https://en.wikipedia.org/wiki/Pareto_principle), or 80/20 rule, that will help us focus on the small set of methods that collectively solve the majority of our data transformation needs.If you're like me, then pandas is not your first data-handling tool; maybe you've been using SQL or R with `data.table` or `dplyr`. If so, that's great because you already have a sense for the key operations we need when working with tabular data. In their book, [R for Data Science](https://r4ds.had.co.nz/), <NAME> and <NAME> describe five essential operations for manipulating dataframes. I've found that these cover the majority of my data transformation tasks to prepare data for analysis, visualization, and modeling.1. filtering rows based on data values2. sorting rows based on data values3. selecting columns by name4. adding new columns based on the existing columns5. creating grouped summaries of the datasetI would add that we also need a way to build up more complex transformations by chaining these fundamental operations together sequentially. Before we dive in, here's the TLDR on the pandas methods that I prefer for accomplishing these tasks, along with their equivalents from SQL and `dplyr` in R. | description                                   | dplyr                   | pandas             ||-----------------------------------------------|-------------------------|--------------------|| filter rows based on data values              | `filter()`                | `query()`            || sort rows based on data values                | `arrange()`               | `sort_values()`      || select columns by name                        | `select()`                | `filter()`          || add new columns based on the existing columns | `mutate()`                | `assign()`           || create grouped summaries of the dataset       | `group_by()`  `summarise()` | `groupby()`  `apply()` || chain operations together                     | `%>%`                     | `.`                  |  Imports and Data<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>We'll use the [nycflights13](https://github.com/hadley/nycflights13) dataset which contains data on the 336,776 flights that departed from New York City in 2013.<jupyter_code># pull some data into a pandas dataframe
flights = pd.read_csv('https://www.openintro.org/book/statdata/nycflights.csv')

flights.head()<jupyter_output><empty_output><jupyter_text>Select  rows based on their values with `query()` `query()` lets you retain a subset of rows based on the values of the data; it's like `dplyr::filter()` in R or `WHERE` in SQL.Its argument is a string specifying the condition to be met for rows to be included in the result.You specify the condition as an expression involving the column names and comparison operators like ``, `=`, `==` (equal), and `~=` (not equal). You can specify compound expressions using `and` and `or`,and you can even check if the column value matches any items in a list.<jupyter_code>#hide_output
# compare one column to a value
flights.query('month == 6')

# compare two column values
flights.query('arr_delay > dep_delay')

# using arithmetic
flights.query('arr_delay > 0.5 * air_time')

# using "and"
flights.query('month == 6 and day == 1')

# using "or"
flights.query('origin == "JFK" or dest == "JFK"')

# column value matching any item in a list
flights.query('carrier in ["AA", "UA"]')<jupyter_output><empty_output><jupyter_text>You may have noticed that it seems to be much more popular to filter pandas data frames using boolean indexing.Indeed when I ask my favorite search engine how to filter a pandas dataframe on its values, I find[this tutorial](https://cmdlinetips.com/2018/02/how-to-subset-pandas-dataframe-based-on-values-of-a-column/),[this blog post](https://medium.com/swlh/3-ways-to-filter-pandas-dataframe-by-column-values-dfb6609b31de),[various](https://stackoverflow.com/questions/17071871/how-to-select-rows-from-a-dataframe-based-on-column-values)[questions](https://stackoverflow.com/questions/11869910/pandas-filter-rows-of-dataframe-with-operator-chaining)on Stack Overflow,and even [the pandas documentation](https://pandas.pydata.org/pandas-docs/stable/getting_started/intro_tutorials/03_subset_data.html),all espousing boolean indexing.Here's what it looks like.<jupyter_code>#hide_output
# canonical boolean indexing
flights[(flights['carrier'] == "AA") & (flights['origin'] == "JFK")]

# the equivalent use of query()
flights.query('carrier == "AA" and origin == "JFK"')<jupyter_output><empty_output><jupyter_text>There are a few reasons I prefer `query()` over boolean indexing.1. `query()` does not require me to type the dataframe name again, whereas boolean indexing requires me to type it every time I wish to refer to a column.1. `query()` makes the code easier to read and understand, especially when expressions get complex.1. `query()` is [more computationally efficient](https://jakevdp.github.io/PythonDataScienceHandbook/03.12-performance-eval-and-query.html) than boolean indexing.1. `query()` can safely be used in dot chains, which we'll see very soon.  Select columns by name with `filter()` `filter()` lets you pick out a specific set of columns by name; it's analogous to `dplyr::select()` in R or `SELECT` in SQL.You can either provide exactly the column names you want, or you can grab all columns whose names contain a given substring or which match a given regular expression. This isn't a big deal when your dataframe has only a few  columns, but is particularly useful when you have a dataframe with tens or hundreds of columns.<jupyter_code>#hide_output
# select a list of columns
flights.filter(['origin', 'dest'])

# select columns containing a particular substring
flights.filter(like='time')

# select columns matching a regular expression
flights.filter(regex='e$')<jupyter_output><empty_output><jupyter_text>Sort rows with `sort_values()` `sort_values()` changes the order of the rows based on the data values; it's like`dplyr::arrange()` in R or  `ORDER BY` in SQL.You can specify one or more columns on which to sort, where their order denotes the sorting priority. You can also specify whether to sort in ascending or descending order.<jupyter_code>#hide_output
# sort by a single column
flights.sort_values('air_time')

# sort by a single column in descending order
flights.sort_values('air_time', ascending=False)

# sort by carrier, then within carrier, sort by descending distance
flights.sort_values(['carrier', 'distance'], ascending=[True, False])<jupyter_output><empty_output><jupyter_text>Add new columns with `assign()` `assign()` adds new columns which can be functions of the existing columns; it's like `dplyr::mutate()` from R.<jupyter_code>#hide_output
# add a new column based on other columns
flights.assign(speed = lambda x: x.distance / x.air_time)

# another new column based on existing columns
flights.assign(gain = lambda x: x.dep_delay - x.arr_delay)<jupyter_output><empty_output><jupyter_text>If you're like me, this way of using `assign()` might seem a little strange at first.Let's break it down.In the call to `assign()` the keyword argument `speed` tells pandas the name of our new column.The business to the right of the `=` is a inline lambda function that takes the dataframe we passed to `assign()` and returns the column we want to add.I like using `x` as the lambda argument because its easy to type and it evokes tabular data (think [design matrix](https://en.wikipedia.org/wiki/Design_matrix)), which reminds me that it refers to the entire dataframe.We can then access the other columns in our dataframe using the dot like `x.other_column`. It's true that you can skip the whole lambda business and refer to the dataframe to which you are assigning directly inside the assign. That might look like this. ```flights.assign(speed = flights.distance / flights.air_time)``` I prefer using a lambda for the following reasons.1. If you gave your dataframe a good name, using the lambda will save you from typing the name every time you want to refer to a column.1. The lambda makes your code more portable. Since you refer to the dataframe as a generic `x`, you can reuse this same assignment code on a dataframe with a different name.1. Most importantly, the lambda will allow you to harness the power of dot chaining.   Chain transformations together with the dot chain One of the awesome things about pandas is that the `object.method()` paradigm lets us easily build up complex dataframe transformations from a sequence of method calls.In R, this is effectively accomplished by the pipe `%>%` operator.For example, suppose we want to look at high-speed flights from JFK to Honolulu, which would require us to query for JFK to Honolulu flights, assign a speed column, and maybe sort on that new speed column.We can say:<jupyter_code>#hide_output
# neatly chain method calls together
(
    flights
    .query('origin == "JFK"')
    .query('dest == "HNL"')
    .assign(speed = lambda x: x.distance / x.air_time)
    .sort_values(by='speed', ascending=False)
    .query('speed > 8.0')
)<jupyter_output><empty_output><jupyter_text>We compose the dot chain by wrapping the entire expression in parentheses and indenting each line within.The first line is the name of the dataframe on which we are operating.Each subsequent line has a single method call.There are a few great things about writing the code this way:1. Readability. It's easy to scan down the left margin of the code to see what's happening. The first line gives us our noun (the dataframe) and each subsequent line starts with a verb. You could read this as "take `flights` then query the rows where origin is JFK, then query for rows where destination is HNL, then assign a new column called speed, then sort the dataframe by speed, then query only for the rows where speed is greater than 8.0.1. Flexibility - It's easy to comment out individual lines and re-run the cell. It's also easy to reorder operations, since only one thing happens on each line.1. Neatness - We have not polluted our workspace with any intermediate variables, nor have we wasted any mental energy thinking of names for any temporary variables. By default, dot chains do not modify the original dataframe; they just output a temporary result that we can inspect directly in the output.If you want to store the result, or pass it along to another function (e.g. for plotting), you can simply assign the entire dot chain to a variable.<jupyter_code>#hide_output
# sotre the output of the dot chain in a new dataframe
flights_high_speed = (
    flights
    .assign(speed = lambda x: x.distance / x.air_time)
    .query('speed > 8.0')
)<jupyter_output><empty_output><jupyter_text>Collapsing rows into grouped summaries with `groupby()` `groupby()` combined with `apply()` gives us flexibility and control over our grouped summaries; it's like `dplyr::group_by()` and `dplyr::summarise()` in R.This is the primary pattern I use for SQL-style groupby operations in pandas. Specifically it unlocks the following essential functionality you're used to having in SQL.1. specify the names of the aggregation columns we create1. specify which aggregation function to use on which columns1. compose more complex aggregations such as the proportion of rows meeting some condition1. aggregate over arbitrary functions of multiple columnsLet's check out the departure delay stats for each carrier.<jupyter_code># grouped summary with groupby and apply
(
    flights
    .groupby(['carrier'])
    .apply(lambda d: pd.Series({
        'n_flights': len(d),
        'med_delay': d.dep_delay.median(),
        'avg_delay': d.dep_delay.mean(),
    }))
    .head()
)<jupyter_output><empty_output><jupyter_text>While you might be used to `apply()` acting over the rows or columns of a dataframe, here we're calling apply on a grouped dataframe object, so it's acting over the _groups_.According to the [pandas documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.groupby.html):> The function passed to apply must take a dataframe as its first argument and return a dataframe, a series or a scalar. apply will then take care of combining the results back together into a single dataframe or series. apply is therefore a highly flexible grouping method.We need to supply `apply()` with a function that takes each chunk of the grouped dataframe and returns (in our case) a series object with one element for each new aggregation column.Notice that I use a lambda to specify the function we pass to `apply())`, and that I name its argument `d`, which reminds me that it's a dataframe.My lambda returns a pandas series whose index entries specify the new aggregation column names, and whose values constitute the results of the aggregations for each group.Pandas will then stitch everything back together into a lovely dataframe.Notice how nice the code looks when we use this pattern. Each aggregation is specified on its own line, which makes it easy to see what aggregation columns we're creating and allows us to comment, uncomment, and reorder the aggregations without breaking anything. Here are some more complex aggregations to illustrate some useful patterns.<jupyter_code># more complex grouped summary
(
    flights
    .groupby(['carrier'])
    .apply(lambda d: pd.Series({
        'avg_gain': np.mean(d.dep_delay - d.arr_delay), 
        'pct_delay_gt_30': np.mean(d.dep_delay > 30), 
        'pct_late_dep_early_arr': np.mean((d.dep_delay > 0) & (d.arr_delay < 0)), 
        'avg_arr_given_dep_delay_gt_0': d.query('dep_delay > 0').arr_delay.mean(),
        'cor_arr_delay_dep_delay': np.corrcoef(d.dep_delay, d.arr_delay)[0,1],
    }))
    .head()
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08836604883386909, 'fraction_numerical': 0.0067710826892825385, 'mean_word_length': 4.257820927723841, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 58, 'https://': 13, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '59681', 'license': 'Apache-2.0', 'n_tokens_mistral': 4071, 'n_tokens_neox': 3807, 'n_words': 1841, 'path': 'drafts/.ipynb_checkpoints/2020-11-25-8020-pandas-checkpoint.ipynb', 'repo_name': 'mcb00/blog_bak', 'size': '14640'}