Why does pandas grouping-aggregation discard categoricals column?

Viewed 125

Situation

Consider the following two dataframes:

import pandas as pd  # version 0.23.4

df1 = pd.DataFrame({
    'A': [1, 1, 1, 2, 2],
    'B': [100, 100, 200, 100, 100],
    'C': ['apple', 'orange', 'mango', 'mango', 'orange'],
    'D': ['jupiter', 'mercury', 'mars', 'venus', 'venus'],
})

df2 = df1.astype({'D': 'category'})

As you can see in dataframe df2 the column D is of categoricals data type, but otherwise df2 is identical to df1.

Now consider the following groupby-aggregation operations:

result_x_df1 = df1.groupby(by='A').first()
result_x_df2 = df2.groupby(by='A').first()
result_y_df1 = df1.groupby(by=['A', 'B']).first()
result_y_df2 = df2.groupby(by=['A', 'B']).first()

with results looking as follows:

In [1]: result_x_df1
Out[1]:
     B      C        D
A
1  100  apple  jupiter
2  100  mango    venus

In [2]: result_x_df2
Out[2]:
     B      C        D
A
1  100  apple  jupiter
2  100  mango    venus

In [3]: result_y_df1
Out[3]:
           C        D
A B
1 100  apple  jupiter
  200  mango     mars
2 100  mango    venus

In [4]: result_y_df2
Out[4]:
           C
A B
1 100  apple
  200  mango
2 100  mango

Question

result_x_df1, result_x_df2 and result_y_df1 look exactly as I would have expected. What really puzzles me however is that in result_y_df2 the categoricals column D has been completely discarded. This raises the questions:

  • Why is categoricals column D discarded in result_y_df2?
  • How can I prevent categoricals column D from being discarded, i.e. how I can obtain a grouping-aggregation result from df2 that looks similar to result_y_df1?
1 Answers

It would seem that the cause of the issue is a regression bug in pandas (occurring from version 0.23.0 onwards). A work-around is to use head(1) instead of first() (as suggested by Dark).

See this pandas github issue for new developments.

Related