从包含每日数据的数据框中绘制每月时间序列(Plot monthly Time series from a data frame with daily data)

我有一个2014年1月1日至2012年12月31日纽约市每天发生的汽车碰撞事件数据集。我想在一个地块中每月绘制受伤骑车人和驾驶者的时间序列。

我的数据如下所示：

Date Time Location Cyclists injured Motorists injured 2014-1-1 12:05 Bronx 0 1 2014-1-1 12:34 Bronx 1 2 2014-1-2 6:05 Bronx 0 0 2014-1-3 8:01 Bronx 1 2 2014-1-3 12:05 Manhattan 0 1 2014-1-3 12:56 Manhattan 0 2

等到2014年12月31日。

现在为此绘制月度时间序列，我知道我首先需要计算每个月的每个总和，然后绘制每月总计。但我不知道我怎么做到这一点。

我使用了这个代码的聚合函数，但它给了我每天而不是月份的总和。请帮忙。

cyclist <- aggregate(NUMBER.OF.CYCLIST.INJURED ~ DATE, data = final_data,sum)

谢谢：）

I have a data set for motor vehicle crashes happening daily in NYC from 1 Jan 2014 to 31 Dec 2012. I want to plot time series of the number of injured cyclists, and motorists, monthly in a single plot.

My data looks like this:

Date Time Location Cyclists injured Motorists injured 2014-1-1 12:05 Bronx 0 1 2014-1-1 12:34 Bronx 1 2 2014-1-2 6:05 Bronx 0 0 2014-1-3 8:01 Bronx 1 2 2014-1-3 12:05 Manhattan 0 1 2014-1-3 12:56 Manhattan 0 2

and so on till 31 Dec 2014.

Now to plot monthly time series for this, I understand I first need to total the each of the sums for each month, and then plot the monthly totals. But I do not know how I can do this.

I used the aggregate function using this code, however it gives me sum for each day and not month. Please help.

cyclist <- aggregate(NUMBER.OF.CYCLIST.INJURED ~ DATE, data = final_data,sum)

Thank you :)

最满意答案

Mannat这里是一个使用data.table包来帮助您聚合的答案。使用install.packages(data.table)首先将它放入你的R.

library(data.table) # For others # I copied your data into a csv file, Mannat you will not need this step, # other helpers look at data in DATA section below final_data <- as.data.table(read.csv(file.path(mypath, "SOaccidents.csv"), header = TRUE, stringsAsFactors = FALSE)) # For Mannat # Mannat you will need to convert your existing data.frame to data.table final_data <- as.data.table(final_data) # check data formats, dates are strings # and field is Date not DATE str(final_data) final_data$Date <- as.Date(final_data$Date, "%m/%d/%Y") # use data table to aggregate on months # First lets add a field plot date with Year and Month YYYYMM 201401 final_data[, PlotDate := as.numeric(format(Date, "%Y%m"))] # key by this plot date setkeyv(final_data, "PlotDate") # second we aggregate with by , and label columns plotdata <- final_data[, .(Cyclists.monthly = sum(Cyclists.injured), Motorists.monthly = sum(Motorists.injured)), by = PlotDate] # PlotDate Cyclists.monthly Motorists.monthly #1: 201401 2 8 # You can then plot this (makes more sense with more data) # for example, for cyclists plot(plotdata$PlotDate, plotdata$Cyclists.monthly)

Mannat如果您不熟悉data.table ，请参阅data.table

数据

对于其他寻求解决此问题的人。这是dput的结果：

final_data <- data.table(Date = c("01/01/2014", "01/01/2014", "01/01/2014", "01/01/2014", "1/19/2014", "1/19/2014"), Time = c("12:05", "12:34","06:05", "08:01", "12:05", "12:56"), Location = c("Bronx", "Bronx","Bronx", "Bronx", "Manhattan", "Manhattan"), Cyclists.injured = c(0L, 1L, 0L, 1L, 0L, 0L), Motorists.injured = c(1L, 2L, 0L, 2L, 1L, 2L))

PLOTS

要么使用ggplot2包

或者对于绘图，请参阅绘制多条线（数据系列），每条线在R中具有唯一的颜色以绘制帮助。

# I do not have your full data so one point line charts not working # I needed another month for testing, so added a fake February testfeb <- data.table(PlotDate = 201402, Cyclists.monthly = 4, Motorists.monthly = 10) plotdata <- rbindlist(list(plotdata, testfeb)) # PlotDate Cyclists.monthly Motorists.monthly #1 201401 2 8 #2 201402 4 10 # Plot code, modify the limits as you see fit plot(1, type = "n", xlim = c(201401,201412), ylim = c(0, max(plotdata$Motorists.monthly)), ylab = 'monthly accidents', xlab = 'months') lines(plotdata$PlotDate, plotdata$Motorists.monthly, col = "blue") lines(plotdata$PlotDate, plotdata$Cyclists.monthly, col = "red") # to add legend legend(x = "topright", legend = c("Motorists","Cyclists"), lty=c(1,1,1), lwd=c(2.5,2.5,2.5), col=c("blue", "red")) # or set legend inset x to another position e.g. "bottom" or "bottomleft"

带图例的事故情节示例

Mannat here is an answer using data.table package to help you aggregate. Use install.packages(data.table) to first get it into your R.

library(data.table) # For others # I copied your data into a csv file, Mannat you will not need this step, # other helpers look at data in DATA section below final_data <- as.data.table(read.csv(file.path(mypath, "SOaccidents.csv"), header = TRUE, stringsAsFactors = FALSE)) # For Mannat # Mannat you will need to convert your existing data.frame to data.table final_data <- as.data.table(final_data) # check data formats, dates are strings # and field is Date not DATE str(final_data) final_data$Date <- as.Date(final_data$Date, "%m/%d/%Y") # use data table to aggregate on months # First lets add a field plot date with Year and Month YYYYMM 201401 final_data[, PlotDate := as.numeric(format(Date, "%Y%m"))] # key by this plot date setkeyv(final_data, "PlotDate") # second we aggregate with by , and label columns plotdata <- final_data[, .(Cyclists.monthly = sum(Cyclists.injured), Motorists.monthly = sum(Motorists.injured)), by = PlotDate] # PlotDate Cyclists.monthly Motorists.monthly #1: 201401 2 8 # You can then plot this (makes more sense with more data) # for example, for cyclists plot(plotdata$PlotDate, plotdata$Cyclists.monthly)

Mannat if you are not familiar with data.table, please see the cheatsheet

DATA

For others looking to work on this. Here is result from dput:

final_data <- data.table(Date = c("01/01/2014", "01/01/2014", "01/01/2014", "01/01/2014", "1/19/2014", "1/19/2014"), Time = c("12:05", "12:34","06:05", "08:01", "12:05", "12:56"), Location = c("Bronx", "Bronx","Bronx", "Bronx", "Manhattan", "Manhattan"), Cyclists.injured = c(0L, 1L, 0L, 1L, 0L, 0L), Motorists.injured = c(1L, 2L, 0L, 2L, 1L, 2L))

PLOTS

Either use ggplot2 package

or for plots please see Plot multiple lines (data series) each with unique color in R for plotting help.

# I do not have your full data so one point line charts not working # I needed another month for testing, so added a fake February testfeb <- data.table(PlotDate = 201402, Cyclists.monthly = 4, Motorists.monthly = 10) plotdata <- rbindlist(list(plotdata, testfeb)) # PlotDate Cyclists.monthly Motorists.monthly #1 201401 2 8 #2 201402 4 10 # Plot code, modify the limits as you see fit plot(1, type = "n", xlim = c(201401,201412), ylim = c(0, max(plotdata$Motorists.monthly)), ylab = 'monthly accidents', xlab = 'months') lines(plotdata$PlotDate, plotdata$Motorists.monthly, col = "blue") lines(plotdata$PlotDate, plotdata$Cyclists.monthly, col = "red") # to add legend legend(x = "topright", legend = c("Motorists","Cyclists"), lty=c(1,1,1), lwd=c(2.5,2.5,2.5), col=c("blue", "red")) # or set legend inset x to another position e.g. "bottom" or "bottomleft"

Accident Plot Example with Legend

更多推荐