CODEWITHSUNDEEP
pythonpandasdataframetime-series
MC2020
MC2020

Reputation: 87

Count rows based on year-month and sort it from oldest to newest

I have a df like this:

data = {'date':['2019-01-01', '2019-01-02', '2020-01-01', '2020-02-02'],
        'tweets':["aaa", "bbb", "ccc", "ddd"]}

df = pandas.DataFrame(data)

df['daate'] = pandas.to_datetime(df['date'], infer_datetime_format=True)

So I have an object type date and a datetime64[ns] type date. I want to know the counts of rows in each year-month, like two rows in 2019-01, one row in 2020-01 and 2020-02. I would also like to sort the data based on date, from the oldest to the newest. Thanks, folks!

Upvotes: 0

Views: 158

Answers (1)

nay
nay

Reputation: 1775

you can use groupby to count the rows

df['year-month'] = df['daate'].dt.strftime('%Y-%m')
df.groupby('year-month').count()

            date    tweets  daate
year-month          
2019-01     2       2       2
2020-01     1       1       1
2020-02     1       1       1

here is how to sort_values,the ascending=True means from lowest to highest,when False means from highest to lowest

df.sort_values(by='daate',ascending=True)

Upvotes: 1

Related Questions