CODEWITHSUNDEEP
pythonpandasdataframe
User1
User1

Reputation: 107

How to partition pandas data frame according to time stamp?

I have a pandas data frame columns Time Stamp,ID,Latitude,Longitude. This data frame about one month period. How do I plot number of distinct visited location VS time(per day or week)? 

Time Stamp              Id  Latitude    Longitude
08/10/2016 15:22:51:700 1      23        50
08/10/2016 16:28:08:026 1      23        50
08/10/2016 16:28:09:026 1      12        45
08/10/2016 19:00:08:026 2      23        50
08/10/2016 20:28:08:026 1      23        50
08/10/2016 19:00:08:000 2      23        50
09/10/2016 01:02:33:123 2      23        50
09/10/2016 06:15:08:500 1      23        50
09/10/2016 10:01:07:022 3      28        88

Upvotes: 0

Views: 1349

Answers (1)

jezrael
jezrael

Reputation: 863156

I believe you need:

First create datetime Series by to_datetime - times was removes, so get datetimes with no times. (thanks cᴏʟᴅsᴘᴇᴇᴅ for idea)

Then groupby and get nunique, last plot:

days = pd.to_datetime(df['Time Stamp'].str.split().str[0])

s1 = df['Id'].groupby(days).nunique()
print (s1)
Time Stamp
2016-08-10    2
2016-09-10    3
Name: Id, dtype: int64


s1.plot()

For weeks convert to weeks:

weeks = days.dt.week

s2 = df['Id'].groupby(weeks).nunique()
print (s2)
Time Stamp
32    2
36    3
Name: Id, dtype: int64

s2.plot()

Another approach for all dates is resample:

df['Days'] = pd.to_datetime(df['Time Stamp'].str.split().str[0])

s2 = df.resample('D', on='Days')['Id'].nunique()

print (s2)
Days
2016-08-10    2
2016-08-11    0
2016-08-12    0
2016-08-13    0
2016-08-14    0
2016-08-15    0
2016-08-16    0
2016-08-17    0
2016-08-18    0
2016-08-19    0
2016-08-20    0
2016-08-21    0
2016-08-22    0
2016-08-23    0
2016-08-24    0
2016-08-25    0
2016-08-26    0
2016-08-27    0
2016-08-28    0
2016-08-29    0
2016-08-30    0
2016-08-31    0
2016-09-01    0
2016-09-02    0
2016-09-03    0
2016-09-04    0
2016-09-05    0
2016-09-06    0
2016-09-07    0
2016-09-08    0
2016-09-09    0
2016-09-10    3
Freq: D, Name: Id, dtype: int64

s2.plot()

For weeks:

s2 = df.resample('W', on='Days')['Id'].nunique()
print (s2)
Days
2016-08-14    2
2016-08-21    0
2016-08-28    0
2016-09-04    0
2016-09-11    3
Freq: W-SUN, Name: Id, dtype: int64


s2.plot()

Upvotes: 1

Related Questions