Python Pandas crosstab()

Recall that a Pandas dataframe is just like a table or spreadsheet, composed of columns and rows which contain data about some subject matter. Each column denotes typically one attribute (or variable) and each row contains one instance or observation of all variables.

Sometimes we are given “non-dataframe” data, i.e., data that is not in dataframe format and we wish to capture summary statistics from this data in the form of a dataframe. This is what the Pandas crosstab() function is for!

Consider for instance:

Here we are given three lists: products, months, and quantities. Different products are sold in different months in different quantities and this information is recorded in the above lists. Thus, we can see that 5 apples were sold in January, 2 (units of) Shampoo were sold in March, and so on. Note that there are two items sold in March: Shampoo and Chips. We wish to create summaries of sales across months, across products, and across both months and products. That is what pandas crosstab() is for!

Here is the most basic use of crosstab():

The output is:

Note that the crosstab() function above is given only two inputs, namely the products and months (it doesn’t take quantities as input). The output is simply counting the frequencies of each (product, month) pair and outputting these frequencies. As you can see every row has only one “1”. Every column has only one “1” except for March which has two products sold in that month. Also note that the products and months are sorted.

If you flip the order in which you gave these inputs:

The output is:

Thus the rows have become columns and the columns have become rows.

In the above two examples, notice that pandas does not have a good idea of what to name the columns and rows so it has simply called them “col_0” and “row_0”. We can specify names in the following manner:

The output is:

Finally, we can specify that the table must contain not just frequencies but also quantities but you must also specify how these quantities should be aggregated (by default we usually intend for these quantities to be summed up, so the aggregation function is just “sum”):

The output is:

Note that most cells have “NaN” (not a number) because we had no data for those (product, month) combinations. For others we have added up the values to product the final table.

A last feature of pandas crosstab() is that we can extract row marginals, column marginals, and marginals across the whole table. We simply add “margins=True” to our crosstab invocation:

The output is:

Note that there is now an “All” column (denoting all months), an “All” row (denoting all products). Each value here is simply an aggregation along a particular row or column. Further, note that there is an aggregation across both rows and columns, which yields 22.

If you liked this blogpost, checkout our blogpost on Pandas daterange. Also learn how to reorder columns in a Pandas dataframe.

Interested in more things Python? Checkout our post on Python queues. Also see our blogpost on Python's enumerate() capability. Also if you like Python+math content, see our blogpost on Magic Squares. Finally, master the Python print function!

Want to learn Python with us? Sign up for 1:1 or small group classes.