Pandas: groupby neighboring identical elements

Question

I need to groupby dataframe

df = pd.DataFrame({'id': [111, 111, 111, 111, 111, 222, 222], 'domain': ['vk.com', 'facebook.com', 'facebook.com', 'twitter.com', 'vk.com', 'facebook.com', 'twitter.com'], 'time': ['2017-01-12', '2017-01-12', '2017-01-12', '2017-01-13', '2017-01-12', '2017-01-14', '2017-01-14'], 'duration': [10, 20, 5, 12, 34, 12, 4]})

I use

df.groupby([df.id, df.domain]).agg({'duration':'sum', 'time': 'first'}).reset_index().reindex(columns=df.columns)

And get

         domain  duration   id        time
0  facebook.com        25  111  2017-01-12
1   twitter.com        12  111  2017-01-13
2        vk.com        44  111  2017-01-12
3  facebook.com        12  222  2017-01-14
4   twitter.com         4  222  2017-01-14

But desire output is:

         domain  duration   id        time
         vk.com        10  111  2017-01-12
   facebook.com        25  111  2017-01-12
         vk.com        34  111  2017-01-12
    twitter.com        12  111  2017-01-13
   facebook.com        12  222  2017-01-14
    twitter.com         4  222  2017-01-14

How can I fix that?

Your desired output is what I`m getting when I run your code. What do you want from us — Bharath M Shetty, Dec 25 '17 at 07:27
@Dark I change place of desire and really output( sorry. I change the question — Petr Petrov, Dec 25 '17 at 07:31

score 4 · Accepted Answer · answered Dec 25 '17 at 08:57

Here's an alternative without an extra column -

i = df.domain.ne(df.domain.shift()).cumsum()
m = dict(zip(i, df.domain))

df = df.groupby(['id', i], sort=False)\
       .agg({'duration':'sum', 'time': 'first'})\
       .reset_index()
df.domain = df.domain.map(m)

df

    id        domain        time  duration
0  111        vk.com  2017-01-12        10
1  111  facebook.com  2017-01-12        25
2  111   twitter.com  2017-01-13        12
3  111        vk.com  2017-01-12        34
4  222  facebook.com  2017-01-14        12
5  222   twitter.com  2017-01-14         4

Bharath M Shetty · Answer 2 · 2017-12-25T07:49:03.680

We can make use of an extra column which denotes next domain is equal to current domain

df['new'] = (df.domain == df.domain.shift(-1)).cumsum()

ndf = df.groupby([df.domain,df.id,df.new]).agg({'duration':'sum', 'time': 'first'}).reset_index()\

          .sort_values('id').reindex(columns=df.columns).drop(['new'],1)

         domain  duration   id        time
0  facebook.com        25  111  2017-01-12
2   twitter.com        12  111  2017-01-13
4        vk.com        10  111  2017-01-12
5        vk.com        34  111  2017-01-12
1  facebook.com        12  222  2017-01-14
3   twitter.com         4  222  2017-01-14

Pandas: groupby neighboring identical elements

2 Answers2

Linked