Duplicate rows in dataset and recalculate non-auto-generated index (Python/Pandas)

user_5400219 0
Viewed 32

I have a (pandas) dataset that consists of a datetime index and several columns for different locations indicating wind speeds.

How can I duplicate the rows two times and always add one hour to the datetime index?

Date AL BA
2000-01-01 00:00:00 2.994117 4.658971
2000-01-01 03:00:00 3.367553 5.964710
2000-01-01 06:00:00 3.431327 6.633670

I have used concat to multiply the rows and could later on also add a new auto_increment column for the hours, but this is not really elegant.

pandas python
2 Answers

Use DataFrame.asfreq with method='ffill':

df = df.set_index('Date').asfreq('1H', method='ffill')
print (df)
                           AL        BA
Date                                   
2000-01-01 00:00:00  2.994117  4.658971
2000-01-01 01:00:00  2.994117  4.658971
2000-01-01 02:00:00  2.994117  4.658971
2000-01-01 03:00:00  3.367553  5.964710
2000-01-01 04:00:00  3.367553  5.964710
2000-01-01 05:00:00  3.367553  5.964710
2000-01-01 06:00:00  3.431327  6.633670

If need append next 2 rows:

df1 = pd.concat([df, 
                   df.iloc[[-1]].assign(Date = lambda x: x['Date'] + pd.Timedelta('2H'))])

df1 = df1.set_index('Date').asfreq('1H', method='ffill')
print (df1)
                           AL        BA
Date                                   
2000-01-01 00:00:00  2.994117  4.658971
2000-01-01 01:00:00  2.994117  4.658971
2000-01-01 02:00:00  2.994117  4.658971
2000-01-01 03:00:00  3.367553  5.964710
2000-01-01 04:00:00  3.367553  5.964710
2000-01-01 05:00:00  3.367553  5.964710
2000-01-01 06:00:00  3.431327  6.633670
2000-01-01 07:00:00  3.431327  6.633670
2000-01-01 08:00:00  3.431327  6.633670
user_29010020

copying can be done simply by indexing new rows like:

df.iloc[3:6,:] = df.iloc[0:2,:]

and changing the datetime index in the new rows can be done via:

df.iloc[3:6].index.hour += 1
user_193548070
Related