How to get an extended dataframe with consecutive datetime rows?

Tags: , ,



I have a pandas dataframe which looks like this:

    Time    
0   2020-11-12 09:00:00+01:00
1   2020-11-12 11:00:00+01:00
2   2020-11-12 13:00:00+01:00
3   2020-11-12 15:00:00+01:00
4   2020-11-12 17:00:00+01:00   

The type of column Time is datetime64[ns, pytz.FixedOffset(60)](1), float64(7), int64(1), frequency of this column is 2H. I now want to extend the dataframe by new dates in order to get a dataframe like this one:

  Time  
0 2020-11-12 09:00:00+01:00
1 2020-11-12 11:00:00+01:00
2 2020-11-12 13:00:00+01:00
3 2020-11-12 15:00:00+01:00
4 2020-11-12 17:00:00+01:00
5 2020-11-12 19:00:00+01:00
6 2020-11-12 21:00:00+01:00

Answer

This function should do the work. As parameters it takes in:

  • df dataframe you want to extend, column name you want to extend
  • col column you want to extend from(in your example ‘Time’)
  • periods how many rows you want to generate
  • frequency frequency of the column(in your example ‘2H’)
def extend_df(df, col, periods, frequency):
  last_date = df.iloc[-1][col]
  
  #adding 1 to periods because pd.date_range includes last_date into its output and we are 
  #dropping that row 
  new_dates = pd.date_range(last_date, periods=periods + 1, freq=frequency)

  new_df = pd.DataFrame(data= {col : new_dates})
  #dropping first row
  new_df = new_df.drop(0)

  extended_df = df.append(new_df, ignore_index=True)

  return extended_df


Source: stackoverflow