How to aggregate a column's dates into a list of dates per person with Python Pandas?

user_3635544 0
Viewed 209

I have the following data, with each row per ID and DATE. A person with the same ID can occupy multiple rows, hence multiple dates. I want to aggregate it into one person (or ID) per row, and the dates will be aggregated into a list of date

From this

ID   DATE
1    2012-03-04
1    2013-04-15
1    2019-01-09
2    2013-04-09
2    2016-01-01
2    2018-05-09

To this

ID   DATE
1    [2012-03-04, 2013-04-15, 2019-01-09]
2    [2013-04-09, 2016-01-01, 2018-05-09]

Here is my attempt

df.sort_values(by=['ID', 'DATE'], ascending=True, inplace=True)
df = df[['ID', 'DATE']]
df_pivot = df.groupby('ID').aggregate(lambda tdf: tdf.unique().tolist())
df_pivot = pd.DataFrame(df_pivot.to_records())

The problem is it returns something like this

ID   DATE
1    [1375228800000000000, 1411948800000000000, 1484524800000000000]
2    [1524528000000000000, 1529539200000000000, 1529542200000000000]

What kind of date format is this? I can't seem to find the right function to convert it back to the typical date format.

pandas aggregate python-3.x datetime
3 Answers

Many ways... agg preferred because apply can be very slow

df.groupby('ID')['DATE'].agg(list)

Or

df.groupby('ID')['DATE'].apply(lambda x: x.to_list())
user_89869750

If need unique values in lists use DataFrame.drop_duplicates before aggregate lists:

df = (df.sort_values(by=['ID', 'DATE'], ascending=True)
        .drop_duplicates(['ID', 'DATE'])
        .groupby('ID')['DATE']
        .agg(list))

In your solution should working, but it is slow:

df_pivot = df.groupby('ID')['DATE'].aggregate(lambda tdf: tdf.drop_duplicates().tolist())

What kind of date format is this?

If is native datetimes, called also unix datetime in nanoseconds.

user_29010020

Simply use groupby() and apply() method:

result=df.groupby('ID')['DATE'].apply(list)

OR

result=df.groupby('ID')['DATE'].agg(list)

Now If you print result you will get your desired output:

ID
1    [ 2012-03-04,  2013-04-15,  2019-01-09]
2    [ 2013-04-09,  2016-01-01,  2018-05-09]
Name: DATE, dtype: object

The above code is giving you Series,If you want Dataframe Then use:

result=df.groupby('ID')['DATE'].apply(list).reset_index()
user_142898920
Related