collapse group into one row pandas dataframe

Question

I have a dataframe as below:

         id                     timestamp            name
         1                  2018-01-23 15:49:53     "aaa"
         1                  2018-01-23 15:54:56     "bbb"
         1                  2018-01-23 15:49:57     "bbb"
         1                  2018-01-23 15:49:54     "ccc"

This is one example of group of id from my data. I have several groups of ids. What I am trying to do is to collapse each group into a row but in a chronological order according to timestamp eg like this

         id                       name   
          1                   aaa->ccc->bbb->bbb

The values in name are in chronological order as they appear with timestamp. Any pointers regarding this ?

Answer 1

I too the liberty to add some data to your df:

print(df)

Output:

   id   timestamp   name
0   1   2018-01-23T15:49:53 aaa
1   1   2018-01-23T15:54:56 bbb
2   1   2018-01-23T15:49:57 bbb
3   1   2018-01-23T15:49:54 ccc
4   2   2018-01-23T15:49:54 ccc
5   2   2018-01-23T15:49:57 aaa

Then you need:

df['timestamp'] = pd.to_datetime(df['timestamp'])
df = df.sort_values(['id', 'timestamp'])
grp = df.groupby('id')['name'].aggregate(lambda x: '->'.join(tuple(x))).reset_index()
print(grp)

Output:

    id  name
0   1   aaa->ccc->bbb->bbb
1   2   ccc->aaa