CODEWITHSUNDEEP
pythonpandasdataframe
PeterBe
PeterBe

Reputation: 830

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

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?

Upvotes: 1

Views: 155

Answers (3)

jezrael
jezrael

Reputation: 862481

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

Upvotes: 2

A M I R
A M I R

Reputation: 46

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

Upvotes: 1

Ynjxsjmh
Ynjxsjmh

Reputation: 29992

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

Upvotes: 1

Related Questions