I extracted dates and times from a string and converted them to the Pandas DatFrame, by wrintig:
df = pd.to_datetime(news_date, format='%m/%d/%Y')
and the output is like:
['1997-10-31 18:00:00', '1997-10-31 18:00:00',
'1997-10-31 18:00:00', '1997-10-31 18:00:00',
'1997-10-31 18:00:00', '1997-10-31 18:00:00',
'1997-10-31 18:00:00', '1997-10-31 18:00:00',
'1997-10-31 18:00:00', '1997-10-31 18:00:00',
...
'2016-12-07 03:14:00', '2016-12-09 16:31:00',
'2016-12-10 19:02:00', '2016-12-11 09:41:00',
'2016-12-12 05:01:00', '2016-12-12 05:39:00',
'2016-12-12 06:44:00', '2016-12-12 08:11:00',
'2016-12-12 09:36:00', '2016-12-12 10:19:00']
Then I wanted to keep only month and year and sort the date, I wrote:
month_year = df.to_series().apply(lambda x: dt.datetime.strftime(x, '%m-%Y')).tolist() # remove time and day
new = sorted(month_year, key=lambda x: datetime.datetime.strptime(x, '%m-%Y')) # sort date
so far, I have a list of dates. The problem occurs when I try to count the frequency of them (I have to plot time-distribution later on). My code is :
print(pd.DataFrame(new).groupby(month_year).count())
and the output is:
01-1998 60
01-1999 18
01-2000 49
01-2001 50
01-2002 87
01-2003 129
01-2004 125
01-2005 225
01-2006 154
01-2007 302
01-2008 161
01-2009 161
01-2010 167
01-2011 181
01-2012 227
... ...
12-2014 82
12-2015 89
12-2016 13
Nevertheless, I want to have a sorted date in one column, and its frequency in the other column(e.g., Pandas DataFrame) that can be plotted easily, like:
01-1998 60
02-1998 32
03-1998 22
... ...
11-2016 20
12-2016 13
I think you need month period by converting to_period and then value_counts, for sorting use sort_index:
news_date = ['1997-10-31 18:00:00', '1997-10-31 18:00:00',
'1997-10-30 18:00:00', '1997-10-30 18:00:00',
'1997-10-30 18:00:00', '1997-10-30 18:00:00',
'1997-11-30 18:00:00', '1997-11-30 18:00:00',
'1997-12-30 18:00:00', '1997-12-30 18:00:00',
'2016-12-07 03:14:00', '2016-01-09 16:31:00',
'2016-12-10 19:02:00', '2016-01-11 09:41:00',
'2016-12-12 05:01:00', '2016-02-12 05:39:00',
'2016-12-12 06:44:00', '2016-12-12 08:11:00',
'2016-12-12 09:36:00', '2016-12-12 10:19:00']
idx = pd.to_datetime(news_date)
new = pd.Series(idx.to_period('m'))
print (new)
0 1997-10
1 1997-10
2 1997-10
3 1997-10
4 1997-10
5 1997-10
6 1997-11
7 1997-11
8 1997-12
9 1997-12
10 2016-12
11 2016-01
12 2016-12
13 2016-01
14 2016-12
15 2016-02
16 2016-12
17 2016-12
18 2016-12
19 2016-12
dtype: object
df = new.value_counts().sort_index().reset_index()
df.columns = ['Date','Count']
df.Date = df.Date.dt.strftime('%Y-%m')
print (df)
Date Count
0 1997-10 6
1 1997-11 2
2 1997-12 2
3 2016-01 2
4 2016-02 1
5 2016-12 7
Another possible solution is convert to strings first by strftime:
new = pd.Series(idx.strftime('%Y-%m'))
df = new.value_counts().sort_index().reset_index()
df.columns = ['Date','Count']
print (df)
Date Count
0 1997-10 6
1 1997-11 2
2 1997-12 2
3 2016-01 2
4 2016-02 1
5 2016-12 7