I have 8 years of daily data. I want to graph all values per day of the week, per weeks in a year and per month in a year. How do I do that?

Question

I want to be able to visualize my data points per days of the week, per weeks in a year and per months. I was able to visualize my data per year. But when I adjust the code for Monthly and weekly, the x-axis remains as per year.

I have 8 years of hospital records. My data is organized into 2 columns. Column number 1 is my "dates" column starting from 2010-03-10 and ending at 2017-12-31. Column number 2 is my value column. This column lists if I've had a patient come in for treatment or not. The values in column 2 are 0 or x numbers. For example 0 meaning I've had no patients x meaning I've had x number of patients. When I try to graphically represent this data, it only counts the largest x number I've had per week.

df=pd.read_csv('Data 3.csv', parse_dates=["Dates"], index_col="Dates")
# create the plot space upon which to plot the data
fig, ax = plt.subplots(figsize = (10,10))

# add the x-axis and the y-axis to the plot
ax.plot(df.resample('Y').sum()['Total # Events'],color = 'blue')

# rotate tick labels
plt.setp(ax.get_xticklabels(), rotation=45)

# set title and labels for axes
ax.set(xlabel="Years",
ylabel="Total # of Events",
title="Yearly Treatment Events from 2010-2017");

Graph result

So I get the correct graphical figure. But when I change the (df.resample('Y').sum() to ('M') from ('Y') for monthly I get a graph that displays a yearly X-axis and values. How can I change this to get monthly X-axis and Weekly X-axis?

Answer 1

So, as I had assumed, this was easy given the dates were available.

So first create new columns denoting which week, year and month a date belong to. For that you need to set Dates as index and parse_dates while reading the csv:

data=pd.read_csv('Data 3.csv',index_col='Dates',parse_dates=True)

Now you can create the three columns:

data['Week']=data.index.week
data['Month']=data.index.month
data['Year']=data.index.year

This will give you data like:

            Total # Events  Week  Year  Month
Dates                                        
2010-03-10               0    10  2010      3
2010-03-11               4    10  2010      3
2010-03-12               0    10  2010      3
2010-03-13               0    10  2010      3
2010-03-14               0    10  2010      3
2010-03-15               0    11  2010      3
2010-03-16               0    11  2010      3
2010-03-17               0    11  2010      3
2010-03-18               2    11  2010      3
2010-03-19               0    11  2010      3

Rather than sharing whole code for you, I'll just show the Year output and how it works, rest I hope you can do by yourself, or you won't learn anything that way.

So the next step is:

Yearwise=data.groupby(by=('Year')).sum()['Total # Events']

And that's it, plot it:

plt.figure(figsize=(14,10))
Yearwise.plot()

And the graph as you have shared too, is:

Remember, for Month wise, you'll need Year and Month together for grouping or it will end up grouping all the same numbered months together. What I mean is something like this-

by=('Year','Month'))

Rest I am sure you can figure it out on your own. Still if you're getting error, let me know.