I am working on a data set that has multiple traffic speed measurements per day. My data is from the city of chicago, and it is taken every minute for about six months. I wanted to consolidate this data into days only, so this is what I did:

traffic <- read.csv("path.csv",header=TRUE)
traffic2 <- aggregate(SPEED~DATE, data=traffic, FUN=MEAN)

this was perfect because it took all of my data and averaged it by date. For example, my original data looked something like this:

DATE        SPEED  
12/31/2012   22
12/31/2012   25
12/31/2012   23

and the final looked like this:

DATE        SPEED 
10/1/2012    22
10/2/2012    23
10/3/2012    22

The only problem, is my data is supposed to start at 9/1/2012. I plotted this data, and it turns out the data goes from 10/1/2012-12/31/2012 and then 9/1/2012-9/30/2012.

What in the world is going on here?

  • 4
    $\begingroup$ What class is the Date column? It looks like it might be sorting by character (1 comes before 9) rather than date value. $\endgroup$ – user1683454 Aug 1 '14 at 20:13
  • $\begingroup$ Base R read.csv() converts strings to factor. I agree with @user1683454; you will find that your dates are in alphabetical order. $\endgroup$ – Seth Aug 2 '14 at 4:59
  • 1
    $\begingroup$ You should probably ask this on stackoverflow, its a basic R programming question, not really data science. $\endgroup$ – Spacedman Aug 3 '14 at 22:15
  • $\begingroup$ @Spacedman good point. I will put it there next time. $\endgroup$ – BigDataDude Aug 4 '14 at 14:23

I am going to agree with @user1683454's comment. After importing, your DATE column is of either character, or factor class (depending on your settings for stringsAsFactors). Therefore, I think that you can solve this issue in at least several ways, as follows:

1) Convert data to correct type during import. To do this, just use the following options of read.csv(): stringsAsFactors (or as.is) and colClasses. By default, you can specify conversion to Date or POSIXct classes. If you need a non-standard format, you have two options. First, if you have a single Date column, you can use as.Date.character() to pass the desired format to colClasses. Second, if you have multiple Date columns, you can write a function for that and pass it to colClasses via setAs(). Both options are discussed here: https://stackoverflow.com/questions/13022299/specify-date-format-for-colclasses-argument-in-read-table-read-csv.

2) Convert data to correct format after import. Thus, after calling read.csv(), you would have to execute the following code: dateColumn <- as.Date(dateColumn, "%m/%d/%Y") or dateColumn <- strptime(dateColumn, "%m/%d/%Y") (adjust the format to whatever Date format you need).

  • 1
    $\begingroup$ As an addendum, I'd recommend taking a look at the lubridate package, which can make certain date manipulation tasks simpler. $\endgroup$ – shadowtalker Aug 4 '14 at 23:30
  • $\begingroup$ @ssdecontrol: Agree, thanks for the comment. Upvoted. $\endgroup$ – Aleksandr Blekh Aug 5 '14 at 0:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.