Sorting groups by the first value without changing the group order

  • A+
Category:Languages

I am trying to sort a pandas dataframe block-wise without changing the order within blocks.

The dataframe contains forum posts, timestamps, and thread names. I have already sorted the dataframe such that all posts belonging to the same thread are in the right order using df.sort_values(['thread','timestamp'],inplace=True). I now want to sort the data blocks belonging to the same thread according to the timestamp of the first post in each block. The order within the blocks should remain unchanged.

What I currently have:

    post   timestamp         thread 0   this   2009/10/30 16:51  hello    1   be     2009/11/02 17:11  hello 2   some   2008/07/10 15:23  nice 3   text   2007/04/22 14:11  question 4   this   2007/04/24 11:03  question 5   be     2007/05/03 17:55  question 6   some   2004/09/01 09:32  game 7   text   2010/01/01 03:32  wheather 

What I want:

    post   timestamp         thread 6   some   2004/09/01 09:32  game 3   text   2007/04/22 14:11  question 4   this   2007/04/24 11:03  question 5   be     2007/05/03 17:55  question 2   some   2008/07/10 15:23  nice 0   this   2009/10/30 16:51  hello    1   be     2009/11/02 17:11  hello 7   text   2010/01/01 03:32  wheather 

Is there a way to do this?

 


Let's try to first groupby thread then get the first record, sort those records by time, then use the groups attribute of DataFrameGroupBy to get the current order of index in each group. Lastly, use pd.concat and list comprehension to rebuild dataframe in sorted order of first records.

g = df.groupby('thread') s = g.head(1).sort_values('timestamp')['thread'] dg = g.groups  pd.concat([df.reindex(dg[i[1]]) for i in s.iteritems()]) 

Output:

   post           timestamp    thread 6  some 2004-09-01 09:32:00      game 3  text 2007-04-22 14:11:00  question 4  this 2007-04-24 11:03:00  question 5    be 2007-05-03 17:55:00  question 2  some 2008-07-10 15:23:00      nice 0  this 2009-10-30 16:51:00     hello 1    be 2009-11-02 17:11:00     hello 7  text 2010-01-01 03:32:00  wheather 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: