Counting Specific Values by Month

Question

I have some data I want to count by month. The column I want count has three different possible values, each representing a different car sold. Here is an example of my dataframe:

Date                        Type_Car_Sold
2015-01-01 00:00:00                2
2015-01-01 00:00:00                1
2015-01-01 00:00:00                1
2015-01-01 00:00:00                3
...                               ...

I want to make it so I have a dataframe that counts each specific car type sold by month separately, so looking like this:

Month       Car_Type_1        Car_Type_2        Car_Type_3         Total_Cars_Sold
1               15                12                17                     44
2                9                18                20                     47
...             ...              ...                ...                    ...

How exactly would I go about doing this? I've tried doing:

cars_sold = car_data['Type_Car_Sold'].groupby(car_data.Date.dt.month).agg('count')

but that just sums up all the cars sold in the month, rather than breaking it down by the total amount of each type sold. Any thoughts?

My first guess would be a pivot table – xjcl Mar 01 '22 at 18:29 — xjcl, Mar 01 '22 at 18:29

xjcl · Accepted Answer · 2022-03-01T18:51:48.207

3

Maybe not the cleanest solution, but this should get you pretty close

import pandas as pd
from datetime import datetime

df = pd.DataFrame({
    "Date": [datetime(2022,1,1), datetime(2022,1,1), datetime(2022,2,1), datetime(2022,2,1)],
    "Type": [1, 2, 1, 1],
})

df['Date'] = df["Date"].dt.to_period('M')
df['Value'] = 1

print(pd.pivot_table(df, values='Value', index=['Date'], columns=['Type'], aggfunc='count'))

Type       1    2
Date             
2022-01  1.0  1.0
2022-02  2.0  NaN

edited Mar 01 '22 at 18:51

answered Mar 01 '22 at 18:46

xjcl

12,848
6
67
89

Aha, that seemed to do the trick! Thanks! – matrix_season Mar 01 '22 at 19:08

score 1 · Answer 2 · answered Mar 02 '22 at 13:44

Alternatively you can also pass multiple columns to groupby:

import pandas as pd
from datetime import datetime

df = pd.DataFrame({
    "Date": [datetime(2022,1,1), datetime(2022,1,1), datetime(2022,2,1), datetime(2022,2,1)],
    "Type": [1, 2, 1, 1],
})

df['Date'] = df["Date"].dt.to_period('M')

df.groupby(['Date', 'Type']).size()

Date     Type
2022-01  1       1
         2       1
2022-02  1       2
dtype: int64

This seems to have the unfortunate side effect of excluding keys with zero value. Also the result is multiindexed rows rather than having the index as rows+columns.

For more information on this approach, check this question.

Counting Specific Values by Month

2 Answers2