Python: Sorting dates after counting them in pandas

Question

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

jezrael · Accepted Answer · 2017-03-03T09:05:40.187

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

Python: Sorting dates after counting them in pandas

1 Answers1