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)