Groupby hours +/- some integer of additional hours

user_1045755 0
Viewed 34

I have a data frame consisting of some columns, where the index is datetime, i.e. it looks something like:

df = 
                            col1        col2      col3        col4        col5
date                    
2021-01-01 00:00:00+01:00   55.646134   1.00      -3890.12    2437.47     4.716594
2021-01-01 01:00:00+01:00   55.646134   2.00      -3786.65    2908.26     4.716594
2021-01-01 02:00:00+01:00   55.646134   3.00      -3694.92    3123.63     4.716594
2021-01-01 03:00:00+01:00   55.646134   4.00      -3619.17    3251.61     4.716594
2021-01-02 00:00:00+01:00   179.775434  -743.27   1352.90     1647.35     81.397542
2021-01-02 01:00:00+01:00   179.775434  -1184.25  752.35      2081.33     81.397542
2021-01-02 02:00:00+01:00   179.775434  -1448.93  -76.22      2306.36     81.397542
2021-01-02 03:00:00+01:00   179.775434  -1465.47  -1016.00    2091.12     81.397542
....

Now if I do something like:

df_grouped = list(df.groupby(df.index.hour))

Then I get a list of data frames where each group has either hour 00:00:00+01:00, 01:00:00+01:00, 02:00:00+01:00 etc. Hence 24 different groups in my case.

However, what I would like is to get them grouped by the hour, but ALSO including some integer of the previous and next hour, i.e. the resulting groups for the df above would instead of:

df_group1 =
                            col1        col2     col3        col4      col5
date                    
2021-01-01 00:00:00+01:00   55.646134   1.00     -3890.12    2437.47   4.716594
2021-01-02 00:00:00+01:00   179.775434  -743.27  1352.90     1647.35   81.397542


df_group2 =
                            col1        col2     col3        col4      col5
date                    
2021-01-01 01:00:00+01:00   55.646134   2.00    -3786.65     2908.26   4.716594
2021-01-02 01:00:00+01:00   179.775434  -1184.25  752.35     2081.33   81.397542


df_group3 =
                            col1        col2     col3        col4      col5
date                    
2021-01-01 02:00:00+01:00   55.646134   3.00     -3694.92    3123.63   4.716594
2021-01-02 02:00:00+01:00   179.775434  -1448.93 -76.22      2306.36   81.397542


df_group4 =
                            col1        col2     col3        col4      col5
date                    
2021-01-01 03:00:00+01:00   55.646134   4.00     -3619.17    3251.61   4.716594
2021-01-02 03:00:00+01:00   179.775434  -1465.47 -1016.00    2091.12   81.397542

Become:

df_group1_true =
                            col1        col2     col3        col4      col5
date
2021-01-01 00:00:00+01:00   55.646134   1.00     -3890.12    2437.47   4.716594
2021-01-01 01:00:00+01:00   55.646134   2.00     -3786.65    2908.26   4.716594
2021-01-02 00:00:00+01:00   179.775434  -743.27  1352.90     1647.35   81.397542
2021-01-02 01:00:00+01:00   179.775434  -1184.25  752.35     2081.33   81.397542

df_group2_true = 
                            col1        col2     col3        col4      col5
date                    
2021-01-01 00:00:00+01:00   55.646134   1.00     -3890.12    2437.47   4.716594
2021-01-01 01:00:00+01:00   55.646134   2.00     -3786.65    2908.26   4.716594
2021-01-01 02:00:00+01:00   55.646134   3.00     -3694.92    3123.63   4.716594
2021-01-02 00:00:00+01:00   179.775434  -743.27  1352.90     1647.35   81.397542
2021-01-02 01:00:00+01:00   179.775434  -1184.25  752.35     2081.33   81.397542
2021-01-02 02:00:00+01:00   179.775434  -1448.93 -76.22      2306.36   81.397542

df_group3_true = 
                            col1        col2     col3        col4      col5
date                    
2021-01-01 01:00:00+01:00   55.646134   2.00     -3786.65    2908.26   4.716594
2021-01-01 02:00:00+01:00   55.646134   3.00     -3694.92    3123.63   4.716594
2021-01-01 03:00:00+01:00   55.646134   4.00     -3619.17    3251.61   4.716594
2021-01-02 01:00:00+01:00   179.775434  -1184.25  752.35     2081.33   81.397542
2021-01-02 02:00:00+01:00   179.775434  -1448.93 -76.22      2306.36   81.397542
2021-01-02 03:00:00+01:00   179.775434  -1465.47 -1016.00    2091.12   81.397542

df_group4_true = 
                            col1        col2     col3        col4      col5
date                    
2021-01-01 02:00:00+01:00   55.646134   3.00     -3694.92    3123.63   4.716594
2021-01-01 03:00:00+01:00   55.646134   4.00     -3619.17    3251.61   4.716594
2021-01-02 02:00:00+01:00   179.775434  -1448.93 -76.22      2306.36   81.397542
2021-01-02 03:00:00+01:00   179.775434  -1465.47 -1016.00    2091.12   81.397542

In this example data set here there are no 23:00:00+01:00, but obviously if you were on 00:00:00+01:00, the previous datetime to include should be 23:00:00+01:00 and so forth.

The way I do it now is actually by doing df_grouped = list(df.groupby(df.index.hour)) which will get me 24 data frames (one for each hour), and then something like:

additional_hours = 1  # How many +/- hours I want to expand each group with.

df_list_final = [
    pd.concat(
        [df[(i + j) % 24][1] for j in range(-additional_hours, additional_hours + 1)]
    ).sort_index()
    for i in range(len(df))
]

And this works. However, I was wondering if there were a more elegant way of doing it with just some kind of groupby instead of having to concatenating a bunch of data frames all the time.

pandas-groupby pandas python
1 Answers

When I try to run your code, It raises an exception, so I can be sure of the output you expect, but here is my take on your question anyway, in hope it might be helpful.

With the following toy dataframe:

import pandas as pd

df = pd.DataFrame(
    [
        ("2021-01-01 00:00:00", 55.646134, 1.00000e00, -3890.12, 2437.47, 4.716594),
        ("2021-01-01 01:00:00", 55.646134, 2.00000e00, -3786.65, 2908.26, 4.716594),
        ("2021-01-01 02:00:00", 55.646134, 3.00000e00, -3694.92, 3123.63, 4.716594),
        ("2021-01-01 03:00:00", 55.646134, 4.00000e00, -3619.17, 3251.61, 4.716594),
        ("2021-01-02 00:00:00", 179.775434, -7.43270e02, 1352.9, 1647.35, 81.397542),
        ("2021-01-02 01:00:00", 179.775434, -1.18425e03, 752.35, 2081.33, 81.397542),
        ("2021-01-02 02:00:00", 179.775434, -1.44893e03, -76.22, 2306.36, 81.397542),
        ("2021-01-02 03:00:00", 179.775434, -1.46547e03, -1016.0, 2091.12, 81.397542),
    ],
    columns=["index", "col1", "col2", "col3", "col4", "col5"],
).set_index("index")
df.index = pd.to_datetime(df.index, format="%Y-%m-%d %H:%M:%S")

print(df)
# Output
                           col1     col2     col3     col4       col5
index
2021-01-01 00:00:00   55.646134     1.00 -3890.12  2437.47   4.716594
2021-01-01 01:00:00   55.646134     2.00 -3786.65  2908.26   4.716594
2021-01-01 02:00:00   55.646134     3.00 -3694.92  3123.63   4.716594
2021-01-01 03:00:00   55.646134     4.00 -3619.17  3251.61   4.716594
2021-01-02 00:00:00  179.775434  -743.27  1352.90  1647.35  81.397542
2021-01-02 01:00:00  179.775434 -1184.25   752.35  2081.33  81.397542
2021-01-02 02:00:00  179.775434 -1448.93   -76.22  2306.36  81.397542
2021-01-02 03:00:00  179.775434 -1465.47 -1016.00  2091.12  81.397542

Here is one way to do it:

additional_hours = 1

# Create new index with additional values
new_index = []
for i in df.index:
    for j in range(additional_hours, 0, -1):
        new_index.append(i - pd.Timedelta(j, unit="H"))
    new_index.append(i)
    for j in range(additional_hours):
        new_index.append(i + pd.Timedelta(j + 1, unit="H"))

# Create new dataframe by merging with original df
new_df = (
    pd.DataFrame(index=new_index)
    .reset_index()
    .reset_index()
    .set_index("index")
    .merge(df, how="left", left_index=True, right_index=True, sort=False)
    .fillna(method="ffill")
    .fillna(method="bfill")
    .sort_values("level_0")
    .drop(columns="level_0")
)

print(new_df)
# Output
                           col1     col2     col3     col4       col5
index
2020-12-31 23:00:00   55.646134     1.00 -3890.12  2437.47   4.716594
2021-01-01 00:00:00   55.646134     1.00 -3890.12  2437.47   4.716594
2021-01-01 01:00:00   55.646134     2.00 -3786.65  2908.26   4.716594
2021-01-01 00:00:00   55.646134     1.00 -3890.12  2437.47   4.716594
2021-01-01 01:00:00   55.646134     2.00 -3786.65  2908.26   4.716594
2021-01-01 02:00:00   55.646134     3.00 -3694.92  3123.63   4.716594
2021-01-01 01:00:00   55.646134     2.00 -3786.65  2908.26   4.716594
2021-01-01 02:00:00   55.646134     3.00 -3694.92  3123.63   4.716594
2021-01-01 03:00:00   55.646134     4.00 -3619.17  3251.61   4.716594
2021-01-01 02:00:00   55.646134     3.00 -3694.92  3123.63   4.716594
2021-01-01 03:00:00   55.646134     4.00 -3619.17  3251.61   4.716594
2021-01-01 04:00:00   55.646134     4.00 -3619.17  3251.61   4.716594
2021-01-01 23:00:00   55.646134     4.00 -3619.17  3251.61   4.716594
2021-01-02 00:00:00  179.775434  -743.27  1352.90  1647.35  81.397542
2021-01-02 01:00:00  179.775434 -1184.25   752.35  2081.33  81.397542
2021-01-02 00:00:00  179.775434  -743.27  1352.90  1647.35  81.397542
2021-01-02 01:00:00  179.775434 -1184.25   752.35  2081.33  81.397542
2021-01-02 02:00:00  179.775434 -1448.93   -76.22  2306.36  81.397542
2021-01-02 01:00:00  179.775434 -1184.25   752.35  2081.33  81.397542
2021-01-02 02:00:00  179.775434 -1448.93   -76.22  2306.36  81.397542
2021-01-02 03:00:00  179.775434 -1465.47 -1016.00  2091.12  81.397542
2021-01-02 02:00:00  179.775434 -1448.93   -76.22  2306.36  81.397542
2021-01-02 03:00:00  179.775434 -1465.47 -1016.00  2091.12  81.397542
2021-01-02 04:00:00  179.775434 -1465.47 -1016.00  2091.12  81.397542
user_112460560
Related