I have a pandas Dataframe with a Datetime-Index and just one column with a measured value at that time:
| Index | Value |
|---|---|
| 2017-01-01 05:00:00 | 2.8 |
| 2017-01-01 05:15:00 | 3.2 |
I have data for several years now, one value every 15 minutes. I want to reorganize the df to this (I'm preparing the data to train a Neural Network, each line will be one input):
| Index | 0 days 05:00:00 | 0 days 05:00:00 | ... | 1 days 04:45:00 |
|---|---|---|---|---|
| 2017-01-01 | 2.8 | 3.2 | ... | 1.9 |
| 2017-01-02 | ... |
The fastest, most "python" way I could find, was this (with df being the original data, df_result the empty target df):
# prepare df
df_result = pd.DataFrame(index=days_array, columns=times_array)
# fill df
df_result = df_result.apply(order_data_by_days, df=df, log=log, axis=1)
def order_data_by_days(row, df):
for col in row.index:
row[col] = df.loc[row.name + col].values[0]
return row
But this takes >20 seconds für 3.5 years of data! (~120k datapoints). Does anyone have any idea how I could do this a lot faster (I'm aiming at a couple of seconds). If not, I would try to the the transformation with some other language before the import.