How to group data by datetime in pandas?

Question

I have a data which looks like below

data = [(u'Del', datetime.datetime(2019, 11, 1, 0, 0), 59L), (u'Bom', datetime.datetime(2019, 11, 1, 0, 0), 449L), (u'Del', datetime.datetime(2019, 12, 1, 0, 0), 0L), (u'Bom', datetime.datetime(2019, 12, 1, 0, 0), 45L)]

Now I want to sub group the data based on time such that it looks something like this

data = [
         [(u'Del', datetime.datetime(2019, 11, 1, 0, 0), 59L), (u'Bom', datetime.datetime(2019, 11, 1, 0, 0), 449L)] 
        ,[(u'Del', datetime.datetime(2019, 12, 1, 0, 0), 0L), (u'Bom', datetime.datetime(2019, 12, 1, 0, 0), 45L)]
       ]

As you can see, now it is a list of lists where there are two lists inside a list where each list contains similar datetime. For example the first sublist looks like this

[(u'Del', datetime.datetime(2019, 11, 1, 0, 0), 59L), (u'Bom', datetime.datetime(2019, 11, 1, 0, 0), 449L)]

Here the items of the first sublist contains similar date time which is datetime.datetime(2019, 11, 1, 0, 0)

The second sublist looks like this

[(u'Del', datetime.datetime(2019, 12, 1, 0, 0), 0L), (u'Bom', datetime.datetime(2019, 12, 1, 0, 0), 45L)]

Here the items of the first sublist contains similar date time which is datetime.datetime(2019, 12, 1, 0, 0)

I can sort the data based on datetime by doing something like this (though data is already sorted by datetime in this case)

import pandas as pd
import datetime
import psycopg2

df = pd.DataFrame(data)
df['Date'] =pd.to_datetime(df[1])
df = df.sort_values(by='Date')

But I can't group them based on the sorted time. How do I achieve this using pandas?

There are syntax errors in your example data, please edit your post. — eva-vw
– eva-vw, Commented Jan 3, 2020 at 20:00
@eva-vw The data is correct. You have to use imports that I have used to wrangle the data. — Souvik Ray
– Souvik Ray, Commented Jan 3, 2020 at 20:01
Please have a look at How to make good pandas examples and provide a sample of the actually dataframe you're trying to group. A minimal reproducible example should include all of the code necessary to reproduce the issue (and only the necessary code), as well as a sample input and output. — G. Anderson
– G. Anderson, Commented Jan 3, 2020 at 20:10

Souvik Ray · Accepted Answer · 2020-01-03 22:16:48Z

1

You can do the following

df = pd.DataFrame(data)
df.columns = ['place','date','value']


output = [x[1].values for x in df.groupby(date)]

output looks like:

[[[u'Del', Timestamp('2019-11-01 00:00:00'), 59], [u'Bom', Timestamp('2019-11-01 00:00:00'), 449]], [[u'Del', Timestamp('2019-12-01 00:00:00'), 0], [u'Bom', Timestamp('2019-12-01 00:00:00'), 45]]]

edited Jan 3, 2020 at 22:16

Souvik Ray

3,0567 gold badges46 silver badges85 bronze badges

answered Jan 3, 2020 at 20:56

Roshan Santhosh

6874 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Souvik Ray Over a year ago

just exactly what I was looking for. Thanks!

Collectives™ on Stack Overflow

How to group data by datetime in pandas?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related