Datetime format changing and averaging to 15 minutes

Question

DateTime 2/16/2024  7:29:00  2/16/2024  7:28:00  2/16/2024  7:27:00  2/16/2024  7:26:00  2/16/2024  7:25:01  2/16/2024  7:24:01  2/16/2024  7:23:01  2/16/2024  7:22:01  2/16/2024  7:21:00  2/16/2024  7:20:00  2/16/2024  7:19:00  2/16/2024  7:18:00  2/16/2024  7:17:00  2/16/2024  7:16:01  2/16/2024

This is my dataframe with datetime in m/dd/yyyy h:m:s format and also the data is in descending order and hence I am not able to resample it into 15 minute average. How do I change the datetime format and also change the order of the date to ascending order?

 df_AERON['DateTime'] = pd.to_datetime(df_AERON.DateTime, format='%m/%d/%Y %H:%M:%S')
  df_AERON['DateTime'] = df_AERON['DateTime'].dt.strftime('%d/%m/%Y %H:%M:%S')
  df_AERON.set_index('DateTime', inplace=True)
  df_AERON.index = pd.to_datetime(df_AERON.index)
  df_AERON_15min = df_AERON.resample('15min').mean()

Used this code but the resampled date isnt from january but from 2024-01-02 00:00:00

Answer 1

Try this:

import pandas as pd

# Assuming df_AERON is your DataFrame
df_AERON = pd.DataFrame({
    'DateTime': [
        '2/16/2024 7:29:00', '2/16/2024 7:28:00', '2/16/2024 7:27:00',
        '2/16/2024 7:26:00', '2/16/2024 7:25:01', '2/16/2024 7:24:01',
        '2/16/2024 7:23:01', '2/16/2024 7:22:01', '2/16/2024 7:21:00',
        '2/16/2024 7:20:00', '2/16/2024 7:19:00', '2/16/2024 7:18:00',
        '2/16/2024 7:17:00', '2/16/2024 7:16:01', '2/16/2024 7:15:00'
    ],
    'Data': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
})

# Convert 'DateTime' column to datetime format
df_AERON['DateTime'] = pd.to_datetime(df_AERON['DateTime'])

# Sort DataFrame by 'DateTime' column in ascending order
df_AERON.sort_values(by='DateTime', ascending=True, inplace=True)

# Resample the DataFrame to get 15-minute averages
df_AERON_15min = df_AERON.set_index('DateTime').resample('15min').mean()

print(df_AERON_15min)

OUTPUT:

                     Data
DateTime                 
2024-02-16 07:15:00   8.0