CODEWITHSUNDEEP
pandasdataframedatetimeanalysis
rank
rank

Reputation: 1

How do I count values grouping by month and year (YYYYMM) in a pandas dataframe?

Example:

year_month = ['201801','201801','201801','201801','201801','201802','201802','201802','201802','201802']
Services = ['23','67','23','67','23','23','23','4','4','67']
df = list(zip(year_month, Services)
df = pd.DataFrame(df, columns=['Date', 'Services'])

Help me!

My date column is already in the right format, and I`ve alread have the YYYTMM column from that.

I tried something like: df2 = df.loc[:, ['YYYYMM', 'Services']] df2 = df.groupby(['YYYYMM']).count().reset_index()

EXPECTED OUTPUT Quantity of services per month/year.

year_month 4 23 67

201801 0 3 2

201801 2 2 1

enter image description here

Upvotes: 0

Views: 102

Answers (1)

Panda Kim
Panda Kim

Reputation: 13212

out = df.groupby('Date', as_index=False).count()

out

    Date    Services
0   201801  5
1   201802  5

Update

finally i know desired output.

pd.crosstab(df['Date'], df['Services']).sort_index(axis=1, key=lambda x: x.astype('int'))


Services    4   23  67
Date            
201801      0   3   2
201802      2   2   1

Upvotes: 1

Related Questions