1

I am trying to group all values into months and plot these as a bar chart. Below is what I have tried thus far:

import pandas as pd

d1 = ({
    'Date' : ['1/7/18','1/7/18','1/8/18','1/8/18','1/9/18'],     
    'Value' : ['Foo','Bar','Foo','Bar','Foo'],           
    })

df1 = pd.DataFrame(data = d1)

df1['Date'] = pd.to_datetime(df1['Date'])
df1.set_index('Date', inplace = True)
df1.resample('1M').count()['Value'].plot(kind = 'bar')

But this only produces one bar with a count of 5. I'm hoping the intended output would be 3 separate bars. A count of 2 for July, 2 for August, and 1 for September.

Improve this question

3 Answers 3

Reset to default
3

Problem is with converting to datetimes, need set format or dayfirst=True, because DD/MM/YY:

df1['Date'] = pd.to_datetime(df1['Date'], format='%d/%m/%y')

Or:

df1['Date'] = pd.to_datetime(df1['Date'], dayfirst=True)

And if need plot by month names use:

df1['Date'] = pd.to_datetime(df1['Date'], format='%d/%m/%y').dt.month_name()
#alternative
#df1['Date'] = pd.to_datetime(df1['Date'], format='%d/%m/%y').dt.strftime('%B')
df1.groupby('Date')['Value'].count().plot(kind = 'bar')

g

If need correct ordering of months:

months = ['January','February','March','April','May','June','July','August',
          'September','October','November','December']

df1['Date'] = pd.Categorical(df1['Date'], categories=months, ordered=True)
df1.groupby('Date')['Value'].count().plot(kind = 'bar')

g1

If want filter out 0 values:

df1.groupby('Date')['Value'].count().pipe(lambda x: x[x != 0]).plot(kind = 'bar')

g2

Thanks @asongtoruin for another idea:

df1['Date'] = pd.to_datetime(df1['Date'], format='%d/%m/%y') 
#if necessary sorting datetimes
#df1 = df1.sort_values('Date')

df1['month_name'] = df1['Date'].dt.month_name()

df1.groupby('Date').agg({'Value': 'count', 'month_name': 'first'})
                   .plot(x='month_name', y='Value', kind='bar')
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

An alternative way to get the correct month order - df1['Date'] = pd.to_datetime(df1['Date'], format='%d/%m/%y') then df1['month_name'] = df1['Date'].dt.month_name() then df1.groupby('Date').agg({'Value': 'count', 'month_name': 'first'}).plot(x='month_name', y='Value', kind='bar')
@asongtoruin - Super idea, also added necessary sortied datetimes ;)
0

Your code works just fine, but you've mixed up the day/month format

All you need to do is change

'Date' : ['1/7/18','1/7/18','1/8/18','1/8/18','1/9/18'],

To

'Date' : ['7/1/18','7/1/18','8/1/18','8/1/18','9/1/18'],
Improve this answer

Comments

0

Another solution would be to use a pivot table to group by the dates.

pd.pivot_table(df1, values='Value', index='Date', aggfunc='count').plot(kind='bar')
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.