Using multiple timezones in one dataframe column

user_16347818 0
Viewed 101

I have a large dataframe made up of 8 different datasets, sample of three with different time zones below. I import the data and then rbind the 8 dataframes into one df. I want to be able to run analysis and ggplot the whole dataset or sections of it keeping the Date_time timezone correct to each area.

I recently found that dataframes in R apparently don't support multiple timezones in one column, so all following df added using rbind take on the timezone of the first data (df$Date_time changes from imported value), I've tried different ways to overcome this issue:

  1. Saving the dataframes as a list, and creating a list of the 8 lists keeps the local time zone correct in listx$Date_time but ggplot doesn't allow plotting from a list, and when i convert back to dataframe the timezones are all changed again.

  2. I created df$tz column to have each time zone in the file and I tried this: solution to create a fixed UTC time but this is a character string df$UTC and I'm not sure how to make R and ggplot plot the data with the correct timezone information.

df$Date remains true to the different timezones because time is stripped, and df$time_ser remains correct as the date is stripped.

Do I need to include a function to calculate the actual local time in every analysis I do as it runs - very time consuming, or is there some other way to do this.

Sample Dataframe 'df' with three time zones:

> df
             Date_time Depth       Date time_ser                     UTC               tz
1  2013-10-14 12:30:00 64.45 2013-10-14 12:30:00 2013-10-14 03:30:00 UTC       Asia/Tokyo
2  2013-10-14 12:30:05 65.95 2013-10-14 12:30:05 2013-10-14 03:30:05 UTC       Asia/Tokyo
3  2013-10-14 12:30:10 65.95 2013-10-14 12:30:10 2013-10-14 03:30:10 UTC       Asia/Tokyo
4  2013-10-14 12:30:15 66.45 2013-10-14 12:30:15 2013-10-14 03:30:15 UTC       Asia/Tokyo
5  2013-10-14 12:30:20 67.95 2013-10-14 12:30:20 2013-10-14 03:30:20 UTC       Asia/Tokyo
6  2013-10-14 12:30:25 66.95 2013-10-14 12:30:25 2013-10-14 03:30:25 UTC       Asia/Tokyo
31 2018-05-09 04:11:39  0.00 2018-05-08 14:11:39 2018-05-08 19:11:39 UTC     America/Lima
32 2018-05-09 04:11:42  0.00 2018-05-08 14:11:42 2018-05-08 19:11:42 UTC     America/Lima
33 2018-05-09 04:11:45  0.00 2018-05-08 14:11:45 2018-05-08 19:11:45 UTC     America/Lima
34 2018-05-09 04:11:48  0.00 2018-05-08 14:11:48 2018-05-08 19:11:48 UTC     America/Lima
35 2018-05-09 04:11:51  0.00 2018-05-08 14:11:51 2018-05-08 19:11:51 UTC     America/Lima
36 2018-05-09 04:11:54  0.00 2018-05-08 14:11:54 2018-05-08 19:11:54 UTC     America/Lima
43 2019-03-06 13:52:40  0.50 2019-03-06 17:52:40 2019-03-06 04:52:40 UTC Pacific/Auckland
44 2019-03-06 13:52:50  3.50 2019-03-06 17:52:50 2019-03-06 04:52:50 UTC Pacific/Auckland
45 2019-03-06 13:53:00  6.50 2019-03-06 17:53:00 2019-03-06 04:53:00 UTC Pacific/Auckland
46 2019-03-06 13:53:10  9.00 2019-03-06 17:53:10 2019-03-06 04:53:10 UTC Pacific/Auckland
47 2019-03-06 13:53:20  9.50 2019-03-06 17:53:20 2019-03-06 04:53:20 UTC Pacific/Auckland
48 2019-03-06 13:53:30 12.00 2019-03-06 17:53:30 2019-03-06 04:53:30 UTC Pacific/Auckland
lubridate ggplot2 timezone r datetime
1 Answers

I want to be able to run analysis and ggplot the whole dataset or sections of it keeping the Date_time timezone correct to each area.

I'm interpreting this as saying "I want to plot all data in local time", i.e. I want 11am in Auckland to be equivalent to 11am in Lima/Tokyo.

One way to achieve this is to use the lubridate::force_tz function, which changes the time zone of a POSIXct object without adjusting the "time" component.

> t = as.POSIXct('2013-10-14 12:30:00', tz = 'Asia/Tokyo')

> t
[1] "2013-10-14 12:30:00 JST"

> lubridate::force_tz(t, 'UTC')
[1] "2013-10-14 12:30:00 UTC"

If you do this on all your original dataframes before combining them, then you can pretend all the data is in the same time zone, and that 11am in Tokyo is the same as 11am in Lima.

Of course, you will probably want to keep a record of the real time zones in case you ever want to convert them back!

user_78802510
Related