0

Part of my project is to plot using Python. I have a large amount of data on Excel sheet (55k rows), it's the number of car accidents in LA. I need to plot the number of accidents (Yearly, Monthly, Weekly and Daily) so the X axis represents the date and the Y axis represents the number of collisions.The data looks like this:

COLLISION_DATE
20010101
20010101
20010101
20010101
20010101
20010101

Each row in the sheet represents 1 collision. I used this code to get the daily plot

import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime
df = pd.read_csv('rr.csv') 
df['COLLISION_DATE']=  pd.to_datetime(df['COLLISION_DATE'].astype(str), format='%Y%m%d')
answer = df.groupby('COLLISION_DATE').size().to_frame('Number of Accidents') 

answer.plot.line()
plt.show()

For the monthly and weekly I'm using this code

df = pd.read_csv('rr.csv') 
df['COLLISION_DATE'] = pd.to_datetime(df['COLLISION_DATE'],format='%Y%m%d')
df['week'], df['month'], df['year'],df['day'] = df['COLLISION_DATE'].dt.week, df['COLLISION_DATE'].dt.month, df['COLLISION_DATE'].dt.year,df['COLLISION_DATE'].dt.day
answer = df.groupby('month').size().to_frame('Number of Accidents') 
answer.plot.line()
plt.show()

It is giving me 12 months on the X-axis, what I need is 180 months (2001 to 2015), then I have to save each result on a new Excel sheet.

halfer
  • 19,824
  • 17
  • 99
  • 186
Ara
  • 11
  • 1
  • 1
  • 3
  • As it stands, this is off-topic, because it is reliant on external links. For file lockers we would expect you to want to delete/rename/change those files over time, which will render the question useless for future learners. Would you be so kind as to edit the question to include the necessary information in the question itself? – halfer Oct 21 '17 at 09:09
  • Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – halfer Oct 21 '17 at 09:10
  • 1
    Thanks for your feedback, I edited my question to a proper format – Ara Oct 22 '17 at 01:56

1 Answers1

0

For the weekly and monthly plots you can create a month and week column in your dataframe (based on your date column) and use it as your new x-axis. Like described here this might be achieved the following way (assuming your dataframe is called df):

df['date'] = pd.to_datetime(df['date'])
df['week'], df['month'] = df['date'].dt.week, df['date'].dt.month

For saving to an excel file you can use:

df.to_excel("your_file.xlsx")

Here's a link to the according documentation.

halfer
  • 19,824
  • 17
  • 99
  • 186
SaturnFromTitan
  • 1,334
  • 14
  • 20
  • Thank you for your quick response, I am electrical engineering student this is the first time I use python in my life so I dont know how to create a month and week column so please if you can help me with that – Ara Oct 21 '17 at 03:13
  • I edited my answer. If you need further instructions take a look to the linked post as well. – SaturnFromTitan Oct 21 '17 at 04:13
  • I was able to plot all of them (yearly, monthly, weekly, and daily), so now my X axis for monthly is only 12 months and for weekly is 52 weeks, what I need is 180 months ( for 15 years ). Is that possible to achieve? – Ara Oct 21 '17 at 04:41