Max agg on string series

user_3808826 0
Viewed 595

How can I do max values in string column like this?

        dataframe = pandas.DataFrame.from_dict(
            {
                "DEPARTMENT_ID": ["A", "B", "A", "B"],
                "SLOT_BEGIN_TIME": ["2014-01-01", "2014-01-02", "2014-02-01", "2014-02-02"],
            }
        )
        dataframe["MAX_TIME"] = dataframe.groupby(["DEPARTMENT_ID"])["SLOT_BEGIN_TIME"].max()

I get NaN for dataframe["MAX_TIME"]. If it is not possible to do max on non-numeric series, can I write my own compare function?

pandas python
1 Answers

You should use transform whenever you want to reassign back to your dataframe:

dataframe['MAX_TIME'] = dataframe.groupby('DEPARTMENT_ID')['SLOT_BEGIN_TIME'].transform('max')

Output:

  DEPARTMENT_ID SLOT_BEGIN_TIME    MAX_TIME
0             A      2014-01-01  2014-02-01
1             B      2014-01-02  2014-02-02
2             A      2014-02-01  2014-02-01
3             B      2014-02-02  2014-02-02

Explanation:

dataframe.groupby(["DEPARTMENT_ID"])["SLOT_BEGIN_TIME"].max()

gives you a series indexed by unique DEPARTMENT_ID:

DEPARTMENT_ID
A    2014-02-01
B    2014-02-02
Name: SLOT_BEGIN_TIME, dtype: object

Notice that series' index is different than dataframe's index. On the other hand when you do series assignment:

dataframe['SOME_COL'] = some_series

Pandas will align the two series' indexes, which in this case are non-overlapping. Therefore, you see all NaN values.

user_42384080
Related