1

I am trying to find a way to do the equivalent re-sampling action as the pandas manipulation below:

example original dataframe df:

                      FT
Time                     
2017-03-18 23:30:00  73.9
2017-03-18 23:31:00  73.5
2017-03-18 23:32:00  71.6
2017-03-18 23:33:00  71.3
2017-03-18 23:34:00  72.3
2017-03-18 23:35:00  72.1
2017-03-18 23:36:00  70.1
2017-03-18 23:37:00  67.9
2017-03-18 23:38:00  65.4
2017-03-18 23:39:00  63.4
2017-03-18 23:40:00  61.3
2017-03-18 23:41:00  59.9
2017-03-18 23:42:00  58.4
2017-03-18 23:43:00  58.4
2017-03-18 23:44:00  55.6
2017-03-18 23:45:00  54.3
2017-03-18 23:46:00  54.3
2017-03-18 23:47:00  53.0
2017-03-18 23:48:00  51.9
2017-03-18 23:49:00  50.8
2017-03-18 23:50:00  49.8
2017-03-18 23:51:00  48.9
2017-03-18 23:52:00  47.6
2017-03-18 23:53:00  44.5
2017-03-18 23:54:00  57.2
2017-03-18 23:55:00  61.6
2017-03-18 23:56:00  59.8
2017-03-18 23:57:00  58.0
2017-03-18 23:58:00  56.2
2017-03-18 23:59:00  56.2

resampling:

date_format= '%d-%b-%Y %H:%M:%S'
df.index=pd.to_datetime(df.index,format=date_format)
df=df.resample('5Min').mean()

Output:

                  FT
Time                      
2017-03-18 23:30:00  72.52
2017-03-18 23:35:00  67.78
2017-03-18 23:40:00  58.72
2017-03-18 23:45:00  52.86
2017-03-18 23:50:00  49.60
2017-03-18 23:55:00  58.36

I would like to know the simplest way to resample a dataframe using a given aggregate function (e.g. mean, sum etc.) and a given sampling time. in Pandas, I understand interpolation is not used and the resample function performs a 'group by' manipulation.

I am guessing the conversion to a datetime could be done this way:

df$Time=strptime(df$Time,"%d-%b-%Y %H:%M:%S")

but I am not sure which R library I should use for the resample action itself.

Thank you

edit:

using readr read_csv I obtain

# A tibble: 43,981 × 6
                   Time Power   Tin    FT    RT  Flow
*                 <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1  16-Feb-2017 11:00:00  0.09 18.87  57.9  53.3    17
2  16-Feb-2017 11:01:00  0.09 18.87  57.9  53.3    17
3  16-Feb-2017 11:02:00  0.09 18.87  57.9  53.3    17
4  16-Feb-2017 11:03:00  0.09 18.87  57.9  53.3    17
5  16-Feb-2017 11:04:00  0.09 18.87  57.9  53.3    17
6  16-Feb-2017 11:05:00  0.09 18.87  57.9  53.3    17
7  16-Feb-2017 11:06:00  0.09 18.87  57.9  53.3    17
8  16-Feb-2017 11:07:00  0.09 18.87  57.9  53.3    17
9  16-Feb-2017 11:08:00  0.09 18.87  57.9  53.3    17
10 16-Feb-2017 11:09:00  0.09 18.87  57.9  53.3    17
# ... with 43,971 more rows

but

df %>% thicken("5 min") %>% group_by(Time_5_min) %>% summarise(mean(FT))

gives the following error:

"Error: x does not contain a variable of class Date, POSIXct, or POSIXlt.
Traceback:"

update:

the solution given by @Edwin works well

I used the following conversion to datetime.

df$Time=as.POSIXct(df$Time, format="%d-%b-%Y %H:%M:%S")
user7188934
  • 1,021
  • 3
  • 11
  • 17
  • Could you clarify what is Time_5_min in the `df %>% thicken("5 min") %>% group_by(Time_5_min) %>% summarise(mean(FT))` line? @user7188934 – Guy May 31 '17 at 22:23
  • I figured it out - a `time_5_min` column is automatically added to the dataframe from the `thicken` function when running `df %>% thicken("5 min") ` – Guy May 31 '17 at 22:35

1 Answers1

2

Using dplyr and padr. (This is assumes that Time is a datetime variable, which it will be if you use a function from readr.)

library(dplyr); library(padr)
dt$Time <- anytime::anytime(dt$Time)
dt %>% thicken("5 min") %>% group_by(Time_5_min) %>% summarise(mean(FT))
Edwin
  • 3,184
  • 1
  • 23
  • 25