How to duplicate values in a Pandas dataframe with a new time resolution

user_7133942 0
Viewed 59

I have the following Pandas dataframe: enter image description here

I have a timestamp and a value. I would now like to duplicate the values of the column "value" 4 times and have 15 minute timestamps. So for the timestamps

01.01.2022 00:00 --> 4.8000

01.01.2022 00:15 --> 4.8000

01.01.2022 00:30 --> 4.8000

01.01.2022 00:45 --> 4.8000

And so on

I tried the following but it did not really work:

df['timestamp'] = pd.to_datetime(df['timestamp'], format='%d.%m.%Y %H:%M')
df1 = df.set_index('timestamp').asfreq('15T')
df1['value'].interpolate()

Any idea how I can do that?

pandas dataframe python
3 Answers

Add parameter method='ffill' to DataFrame.asfreq:

df['timestamp'] = pd.to_datetime(df['timestamp'], dayfirst=True)
df1 = df.set_index('timestamp').asfreq('15T', method='ffill')

print (df1)
                     value
timestamp                 
2022-01-01 00:00:00    4.8
2022-01-01 00:15:00    4.8
2022-01-01 00:30:00    4.8
2022-01-01 00:45:00    4.8
2022-01-01 01:00:00    4.5
2022-01-01 01:15:00    4.5
2022-01-01 01:30:00    4.5
2022-01-01 01:45:00    4.5
2022-01-01 02:00:00    4.3

for original RangeIndex add DataFrame.reset_index:

df['timestamp'] = pd.to_datetime(df['timestamp'], dayfirst=True)
df1 = df.set_index('timestamp').asfreq('15T', method='ffill').reset_index()

print (df1)
            timestamp  value
0 2022-01-01 00:00:00    4.8
1 2022-01-01 00:15:00    4.8
2 2022-01-01 00:30:00    4.8
3 2022-01-01 00:45:00    4.8
4 2022-01-01 01:00:00    4.5
5 2022-01-01 01:15:00    4.5
6 2022-01-01 01:30:00    4.5
7 2022-01-01 01:45:00    4.5
8 2022-01-01 02:00:00    4.3
user_29010020

You can try ffill(forward fill) on value column

df['timestamp'] = pd.to_datetime(df['timestamp'], dayfirst=True)
df1 = df.set_index('timestamp').asfreq('15T')
df1['value'] = df1['value'].ffill()
# or
df1 = df.set_index('timestamp').asfreq('15T').ffill()

print(df1)

                     value
timestamp
2022-01-01 00:00:00    4.8
2022-01-01 00:15:00    4.8
2022-01-01 00:30:00    4.8
2022-01-01 00:45:00    4.8
2022-01-01 01:00:00    4.5
2022-01-01 01:15:00    4.5
2022-01-01 01:30:00    4.5
2022-01-01 01:45:00    4.5
2022-01-01 02:00:00    4.3
user_103151630

I hope you are doing well,

First of all you should transfer your dates to timestamp and then you can do that by adding seconds to the timestamped dates, you can do your task.

times = []
for i in df['timestamp']:
    times.append(time.mktime(datetime.datetime.strptime(i, "%d.%m.%Y %H:%M").timetuple()))

time_col = []
value_col = []

for i in range(len(times)):
    time_col.append(times[i])
    time_col.append(times[i] + 900)
    time_col.append(times[i] + 1800)
    time_col.append(times[i] + 2700)
    value_col.append(df['value'].iloc[i])
    value_col.append(df['value'].iloc[i])
    value_col.append(df['value'].iloc[i])
    value_col.append(df['value'].iloc[i])

new_time = []
from datetime import datetime
for i in time_col:

    new_time.append(datetime.fromtimestamp(i))

d = {'timestamp': new_time, 'values': value_col}
df1 = pd.DataFrame(d)

Code

Result

It worked for me,

I hope it works for you too,

Best of luck

user_170353160
Related