Tag: pivot

I’d like to re-format a dataframe such that it shows the counts of combinations of two columns. Here’s an example dataframe:

my_df = pd.DataFrame({'a': ['first', 'second', 'first', 'first', 'third', 'first'],
               'b': ['foo', 'foo', 'bar', 'bar', 'baz', 'baz'],
               'c': ['do', 're', 'mi', 'do', 're', 'mi'],
               'e': ['this', 'this', 'that', 'this', 'those', 'this']})

which looks like this:

        a    b   c      e
0   first  foo  do   this
1  second  foo  re   this
2   first  bar  mi   that
3   first  bar  do   this
4   third  baz  re  those
5   first  baz  mi   this

I want it to make a new dataframe that counts combinations between columns a and c that would look like this:

c        do   mi   re
a                    
first   2.0  2.0  NaN
second  NaN  NaN  1.0
third   NaN  NaN  1.0

I can do this using pivot_table if I set the values argument equal to some other column:

my_pivot_count1 = my_df.pivot_table(values='b', index='a', columns='c', aggfunc='count')

The problem with this is that column ‘b’ could have nan values in it, in which case that combination wouldn’t be counted. For example, if my_df looks like this:

        a    b   c      e
0   first  foo  do   this
1  second  foo  re   this
2   first  bar  mi   that
3   first  bar  do   this
4   third  baz  re  those
5   first  NaN  mi   this

my call to my_df.pivot_table gives this:

first   2.0  1.0  NaN
second  NaN  NaN  1.0
third   NaN  NaN  1.0

I’ve gotten around using b as the values argument for now by setting the values argument equal to a new column I introduce to my_df that is guaranteed to have values using either my_df['count'] = 1 or my_df.reset_index(), but is there a way to get what I want without having to add a column, using only columns a and c?

Solution:

pandas.crosstab has a dropna argument, which by default is set to True, but in your case you can pass False:

pd.crosstab(df['a'], df['c'], dropna=False)
# c       do  mi  re
# a                 
# first    2   2   0
# second   0   0   1
# third    0   0   1