Most pythonic way to preform sub-section analysis on a data frame multiple times?

user_13955091 0
Viewed 38

I have a large time-series dataframe at the second interval, and I need to preform some analysis on every grouping of 0-59 seconds. This is then used to be a feature in a different time-series dataframe by using the floor of the sub-section

I feel like I'm missing something basic, but unsure of the wording to get it right.

ex:

timestamp,close
2021-06-01 00:00:00,37282.0
2021-06-01 00:00:01,37282.0
2021-06-01 00:00:02,37285.0
2021-06-01 00:00:03,37283.0
2021-06-01 00:00:04,37281.0
2021-06-01 00:00:05,37278.0
2021-06-01 00:00:06,37275.0
2021-06-01 00:00:07,37263.0
2021-06-01 00:00:08,37264.0
2021-06-01 00:00:09,37259.0
...
2021-06-01 00:00:59,37260.0
2021-06-02 00:01:00,37261.0 --> new analysis starts here
2021-06-02 00:01:01,37262.0
# and repeat

My current implementation works, but I have a feeling is a really bad way of doing it.

df['last_update_hxro'] = df.apply(lambda x: 1 if x.timestamp.second == 59 else 0, axis=1)
df['hxro_close'] = df[df['last_update_hxro']==1].close
df['next_hxro_close'] = df['hxro_close'].shift(-60)
df['hxro_result'] = df[df['last_update_hxro']==1].apply(lambda x: 1 if x.next_hxro_close > x.hxro_close else 0, axis=1)
df['trade_number'] = df.last_update_hxro.cumsum() - df.last_update_hxro
unique_trades = df.trade_number.unique()

for x in unique_trades:
    temp_df = btc_df[btc_df['trade_number']==x]
    new_df = generate_sub_min_features(temp_df)
    feature_df = feature_df.append(new_df)

def generate_sub_min_features(full_df):
    # do stuff here and return a series of length 1, with the minute floor of the subsection as the key
time-series dataframe python
2 Answers

First set timestamp as index:

df['timestamp'] = pd.to_datetime(df['timestamp'], format="%Y-%m-%d %H:%M:S")
df.set_index('timestamp')

Then group by hour and save each dataframe in a list:

groups = [g for n, g in df.groupby(pd.Grouper(freq='H'))]
user_146478680

IMO, any pandas solution which includes a loop could be done better.

I feel I may be a little lost on your ask without a basic example, but it sounds like you're looking for a simple resample or groupby?

Example

Here's a sample using a df loaded with the distinct seconds from 1/1/21 - 1/2/21, paired with random integers from 0 - 10. We'll take the average of every minute.

import pandas as pd
import numpy as np

df_time = pd.DataFrame(
    {'val': np.random.randint(0,10, size=(60 * 60 * 24) + 1)},
    index=pd.date_range('1/1/2021', '1/2/2021', freq='S')
)

df_mean = df_time.resample('T').apply(lambda x: x.mean())

print(df_mean)

Returns...

                          val
2021-01-01 00:00:00  4.566667
2021-01-01 00:01:00  5.000000
2021-01-01 00:02:00  4.316667
2021-01-01 00:03:00  4.800000
2021-01-01 00:04:00  4.533333
...                       ...
2021-01-01 23:56:00  4.916667
2021-01-01 23:57:00  4.450000
2021-01-01 23:58:00  4.883333
2021-01-01 23:59:00  4.316667
2021-01-02 00:00:00  2.000000

Notes

Note the use of T here to define the "minute" portion of the datetime flags in the index. Read more about Offset Aliases. Also the use of the resample() method since our timeseries also acts as the index. groupby() would have also been valid here with a slightly different approach in case our datetime information was not our index.

Customization

In application, one would replace the contents of lambda() with whatever function you'd like to apply to each distinct group of line items which share a truncated datetime-minute.

user_103588100
Related