I am trying to build a bar chart showing the amount of times a link was clicked on. I am using pandas to create a dataframe and then passing the data to Chart js. My data frame currently looks liek this:
| # | Month | Clicks |
|---|---|---|
| 0 | 2022-10 | 3 |
| 1 | 2023-01 | 2 |
| 2 | 2023-02 | 7 |
| 3 | 2023-04 | 4 |
But I want it to be like this:
| Month | Clicks |
|---|---|
| 2022-10 | 3 |
| 2022-11 | 0 |
| 2022-12 | 0 |
| 2023-01 | 2 |
| 2023-02 | 7 |
| 2023-03 | 0 |
| 2023-04 | 4 |
The 'Month' column is a string. I have a database with a table called Click, where each row has a 'date_clicked' attribute (which is a python datetime object). I originally created a dataframe of my database rows, and then added the month column by writing:
df['Month] = df['date_clicked'].dt.strftime("%Y-%m")
then I created a new DataFrame where i counted all the rows for each month:
new_df = df.groupby('Month', as_index=False)['date_clicked].count()
then i renamed date_clicked to Clicks
new_df.rename(columns={'date_clicked:'Clicks'}, inplace=True)
So basically I have a database table called Click and I'm tracking the datetime each click object was created with 'date_clicked'. It seems like it should be a really simple task to just make a chart that has the Months on the X axis and the total # of clicks on the Y axis, while also displaying a y value of 0 if there were 0 clicks in that month. But I clearly don't understand pandas and I think I am going about this the wrong way. Any advice is much appreciated, thank you.
The duplicate question to doesn't show how to organize by month. It is only days. I don't want every single day to show up on my chart.
