Resampling Pandas Dataframe to only include minute updates

Viewed 37

I have data as follows:

Date Other Data
2000-01-01 10:05:00 ...
2000-01-01 10:05:30 ...
2000-01-01 10:06:00 ...
2000-01-01 10:08:00 ...
2000-01-01 10:08:30 ...
2000-01-01 10:09:00 ...

My intent is to clean up this data such that it start with the first date, and it eliminates all entries where the (date - next_date) is greater than some number representing a minute. Once these entries are eliminated, it should this process with the next available date until the next date is null (some termination logic).

Let's assume the minimum difference in minutes is 1 Minute, then, the table would look like this:

Date Other Data
2000-01-01 10:05:00 ...
2000-01-01 10:06:00 ...
2000-01-01 10:08:00 ...
2000-01-01 10:09:00 ...

The code that currently is able to do this will be posted below, I am just wondering if there is a more efficient way to do this?

Code:

def make_timeframe_faster(file):
    DIST = 60 # Distance in minutes 

    df = pd.read_csv(file, names=["date", "open", "high", "low", "close"])
    df.date = pd.to_datetime(df.date)

    i=0
    while i < len(df):
        print(df.date[i])
        diff = (df.date - df.date[i]).astype('timedelta64[m]')
        df = df.loc[ (diff >= DIST) | (diff <= 0) ]
        df.reset_index(drop=True, inplace=True)
        i = i + 1

    df.to_csv("result.csv")

SOLUTION: For my intents and purposes the following code resamples the data Hourly with 'H', and other possible inputs if you look at Panda's timeseries notation.

Here is the code if anyone needs this in the future:

def make_timeframe_faster4(file):
   df = pd.read_csv(file, names=["date", "open", "high", "low", "close"])
   df.date = pd.to_datetime(df.date)

   df = df.set_index('date')
   df = df.sort_index().resample('H').first()

   df.to_csv("result_" + file, index=False)
1 Answers

You can filter the df where the seconds of Date equal 0: df[df['Date'].dt.second == 0]. Example:

import pandas as pd
import io

data = '''Date  Other Data
2000-01-01 10:05:00 ...
2000-01-01 10:05:30 ...
2000-01-01 10:06:00 ...
2000-01-01 10:08:00 ...
2000-01-01 10:08:30 ...
2000-01-01 10:09:00 ...'''

df = pd.read_csv(io.StringIO(data), sep='\t')
df['Date'] = pd.to_datetime(df['Date'])
df2 = df[df['Date'].dt.second == 0]

Output:

Date Other Data
0 2000-01-01 10:05:00 ...
2 2000-01-01 10:06:00 ...
3 2000-01-01 10:08:00 ...
5 2000-01-01 10:09:00 ...
Related