CODEWITHSUNDEEP
pythonpandasdatetime
Souvik Ray
Souvik Ray

Reputation: 3018

How to group data by datetime in pandas?

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?

Upvotes: 0

Views: 78

Answers (1)

Roshan Santhosh
Roshan Santhosh

Reputation: 687

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]]]

Upvotes: 1

Related Questions