Pandas Time-Series DataFrame Methods – Part 18

Rate this post

The Pandas DataFrame/Series has several methods related to time series.

This is Part 18 of the DataFrame methods series:

  • Part 1 focuses on the DataFrame methods abs(), all(), any(), clip(), corr(), and corrwith().
  • Part 2 focuses on the DataFrame methods count(), cov(), cummax(), cummin(), cumprod(), cumsum().
  • Part 3 focuses on the DataFrame methods describe(), diff(), eval(), kurtosis().
  • Part 4 focuses on the DataFrame methods mad(), min(), max(), mean(), median(), and mode().
  • Part 5 focuses on the DataFrame methods pct_change(), quantile(), rank(), round(), prod(), and product().
  • Part 6 focuses on the DataFrame methods add_prefix(), add_suffix(), and align().
  • Part 7 focuses on the DataFrame methods at_time(), between_time(), drop(), drop_duplicates() and duplicated().
  • Part 8 focuses on the DataFrame methods equals(), filter(), first(), last(), head(), and tail()
  • Part 9 focuses on the DataFrame methods equals(), filter(), first(), last(), head(), and tail()
  • Part 10 focuses on the DataFrame methods reset_index(), sample(), set_axis(), set_index(), take(), and truncate()
  • Part 11 focuses on the DataFrame methods backfill(), bfill(), fillna(), dropna(), and interpolate()
  • Part 12 focuses on the DataFrame methods isna(), isnull(), notna(), notnull(), pad() and replace()
  • Part 13 focuses on the DataFrame methods drop_level(), pivot(), pivot_table(), reorder_levels(), sort_values() and sort_index()
  • Part 14 focuses on the DataFrame methods nlargest(), nsmallest(), swap_level(), stack(), unstack() and swap_axes()
  • Part 15 focuses on the DataFrame methods melt(), explode(), squeeze(), to_xarray(), t() and transpose()
  • Part 16 focuses on the DataFrame methods append(), assign(), compare(), join(), merge() and update()
  • Part 17 focuses on the DataFrame methods asfreq(), asof(), shift(), slice_shift(), tshift(), first_valid_index(), and last_valid_index()
  • Part 18 focuses on the DataFrame methods resample(), to_period(), to_timestamp(), tz_localize(), and tz_convert()
  • Part 19 focuses on the visualization aspect of DataFrames and Series via plotting, such as plot(), and plot.area().
  • Part 20 focuses on continuing the visualization aspect of DataFrames and Series via plotting such as hexbin, hist, pie, and scatter plots.
  • Part 21 focuses on converting one data type to/from another data type.
  • Part 22 focuses on converting one data type to/from another data type.
  • Part 23 focuses on converting one data type to/from another data type.
  • Part 24 focuses on converting one data type to/from another data type.
  • Part 25 focuses on converting one data type to/from another data type.

Preparation

Before any data manipulation can occur, one (1) new library will require installation.

  • The Pandas library enables access to/from a DataFrame.

To install this library, navigate to an IDE terminal. At the command prompt ($), execute the code below. For the terminal used in this example, the command prompt is a dollar sign ($). Your terminal prompt may be different.

💡 Note: The pytz comes packaged with pandas and does not require installation. However, this library is needed for the tz_ localize() and tz_convert() methods to work.

$ pip install pandas

Hit the <Enter> key on the keyboard to start the installation process.

If the installation was successful, a message displays in the terminal indicating the same.


Feel free to view the PyCharm installation guide for the required library.


Add the following code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

import pandas as pd
import pytz

DataFrame resample()

The resample() method is useful for manipulating the frequency and time-series data.

This DataFrame/Series must contain a datetime-like index, for example:

The syntax for this method is as follows:

DataFrame.resample(rule, axis=0, closed=None, label=None, convention='start', kind=None, loffset=None, base=None, on=None, level=None, origin='start_day', offset=None)
ParameterDescription
ruleThis parameter is the offset (string/object) representing a target conversion.
axisIf zero (0) or index is selected, apply to each column. Default 0.
If one (1) apply to each row.
closedThis parameter determines which side of the bin interval is closed. Default 'left' for all frequency offsets except:
'M', 'A', 'Q', 'BM', 'BA', 'BQ', and 'W', default 'right'.
labelThis parameter determines which bin edge to label bucket. Default 'left' for all frequency offsets except:
'Q', 'BM', 'BA', 'BQ', and 'W', default 'right'.
conventionThis parameter is the PeriodIndex, and it controls whether to use the start/end of the rule. The available options are: 'start', 'end', 's', or 'e'. Default is 'start'.
kindThis parameter is a timestamp/period and is for the PeriodIndex.
loffsetNot in use since v1.1.0. Add this to df.index after resample() has taken place.
baseNot in use since v1.1.0. Use 'offset' or 'origin' instead.
onIf a DataFrame, the datetime column to use instead of index for resampling.
levelA datetime level in a MultiIndex scenario to use for resampling.
originThe timestamp to adjust the grouping. The origin time-zone must match the index. If a string, one of the following: 'epoch', 'start', 'start_day', 'end', and 'end_day'
offsetThis parameter is the offset timedelta which adds to the origin.

Rivers Clothing is having a 3-hour blow-out sale for a new line they have introduced, scarfs. This example resamples the sales data and adds up the total number of scarf sales per hour.

df = pd.read_csv('rivers.csv', parse_dates=['date'], index_col=['date'])
print(df)

result = df.resample('1H').sum()
print(result)
  • Line [1] reads in a CSV file, parses the date column, and sets this column as the index. The output saves to df.
  • Line [2] outputs the DataFrame to the terminal.
  • Line [3] resamples the data by grouping the total scarf sales by the hour. The output saves to result.
  • Line [4] outputs the result to the terminal.

Output

df

 Itemcolorsold
date   
2022-01-27 08:17:00scarfred3
2022-01-27 08:23:00 scarfblue2
2022-01-27 08:47:00 scarfpink    1
2022-01-27 09:01:00 scarfblack   11
2022-01-27 09:28:00 scarfbrown    6
2022-01-27 09:51:00 scarfburgundy   15
2022-01-27 10:11:00 scarfblack   21
2022-01-27 10:13:00 scarfbrown   10
2022-01-27 10:22:00 scarfblack    9
2022-01-27 10:28:00 scarfnavy   30

result

 sold
date 
2022-01-27 08:00:00    6
2022-01-27 09:00:0032
2022-01-27 10:00:00   70

DataFrame to_period()

The to_period() method converts a DataFrame/Series from a DatetimeIndex format to a PeriodIndex format based on the selected frequency.

The syntax for this method is as follows:

DataFrame.to_period(freq=None, axis=0, copy=True)
ParameterDescription
freqThis parameter is an available frequency of the PeriodIndex method.
axisIf zero (0) or index is selected, apply to each column. Default 0.
If one (1) apply to each row.
copyIf True, the data copies. By default, True.

For these examples, we have a list containing datetimes. These datetimes convert to Monthly & Yearly formats.

Code –Monthly Format

idx = pd.to_datetime(['2022-01-15 08:17:00',
                      '2022-01-15 08:23:00',
                      '2022-01-15 08:47:00',
                      '2022-01-15 09:01:00',
                      '2022-01-15 09:28:00'])
print(idx)

result = idx.to_period('M')
print(result)
  • Line [1] converts a list of strings to a datetime format and saves it to idx.
  • Line [2] outputs the contents of idx to the terminal.
  • Line [3] converts the contents of idx to a PeriodIndex Monthly format. The output saves to result.
  • Line [4] outputs the result to the terminal.

Output

idx
DatetimeIndex(['2022-01-15 08:17:00', '2022-01-15 08:23:00',
               	         '2022-01-15 08:47:00', '2022-01-15 09:01:00',
               	         '2022-01-15 09:28:00'],
              	         dtype='datetime64[ns]', freq=None)

result
PeriodIndex(['2022-01', '2022-01', '2022-01', '2022-01', '2022-01'], dtype='period[M]')

Code –Yearly Example

idx = pd.to_datetime(['2018-01-15 08:17:00',
                      '2019-01-15 08:23:00',
                      '2020-01-15 08:47:00',
                      '2021-01-15 09:01:00',
                      '2022-01-15 09:28:00'])
print(idx)

result = idx.to_period('Y')
print(result)
  • Line [1] converts a list of strings to a datetime format and saves it to idx.
  • Line [2] outputs the contents of idx to the terminal.
  • Line [3] converts the contents of idx to a PeriodIndex Yearly format. The output saves to result.
  • Line [4] outputs the result to the terminal.

Output

idx
DatetimeIndex(['2018-01-15 08:17:00', '2019-01-15 08:23:00',               
	         '2020-01-15 08:47:00', '2021-01-15 09:01:00',
                            '2022-01-15 09:28:00'],
                            dtype='datetime64[ns]', freq=None)

result
PeriodIndex(['2018', '2019', '2020', '2021', '2022'], dtype='period[A-DEC]')

Note: Definition of frequency period [A-DEC]:

  • A: year-end
  • DEC: year ends in December

DataFrame tz_localize()

The tz_localize() method localizes a time zone’s native index of a DataFrame/Series to a targeted time zone.

To view a list of available time zones, click here or navigate to an IDE and run the following code:

$ print(pytz.all_timezones)

The syntax for this method is as follows:

DataFrame.tz_localize(tz, axis=0, level=None, copy=True, ambiguous='raise', nonexistent='raise')
ParameterDescription
tzThe parameter is a string of a valid time zone. Ex: 'America/Phoenix'.
axisIf zero (0) or index is selected, apply to each column. Default 0.
If one (1) apply to each row.
levelA datetime level in a MultiIndex scenario to use for resampling.
copyIf True, this parameter makes a copy.
ambiguousIf the clock moves backward (counterclockwise), an error may occur. The available options are:
'infer': infer DST-transition hours (based on order).
– Boolean-array: True reflects as a DST. False, set as a non-DST time.  
'NaT': returns this value if ambiguous times occur.
– The default is 'raise'.
nonexistentIf the clock moves forward (clockwise), an error may occur. The available options are:
'shift-forward': moves the blank/invalid time forward to the nearest non-empty time.
'shift-backward': moves the blank/empty time backward to the nearest non-empty time.
'NaT': returns this value if ambiguous times occur.
timedelta: shift empty times by the timedelta.
– The default is 'raise'.

For this example, the time localizes to Berlin, Germany.

tz_1 = pd.date_range('2022-12-25 09:00', periods=3)
print(tz_1)

tz_2 = tz_1.tz_localize(tz='Europe/Berlin')
print(tz_2)
  • Line [1] creates a date range based on a start date and a 3-day duration. The output saves to tz_1.
  • Line [2] outputs tz_1 to the terminal.
  • Line [3] localizes the time zone to Berlin. The output saves to tz_2.
  • Line [4] outputs tz_2 to the terminal.

Output

tz_1

DatetimeIndex(['2022-12-25 09:00:00', 
               '2022-12-26 09:00:00',              
               '2022-12-27 09:00:00'],              
               dtype='datetime64[ns]', freq='D')

tz_2

DatetimeIndex(['2022-12-25 09:00:00+01:00', 
               '2022-12-26 09:00:00+01:00',               
               '2022-12-27 09:00:00+01:00'],              
               dtype='datetime64[ns, Europe/Berlin]', freq=None)

💡 Note: The localized time zone displays as Europe/Berlin.


DataFrame tz_convert()

The tz_convert() method converts the time zone of a DataFrame to a different time zone.

To view a list of available time zones, click here or navigate to an IDE and run the following code:

$ print(pytz.all_timezones)

The syntax for this method is as follows:

DataFrame.tz_convert(tz, axis=0, level=None, copy=True)
ParameterDescription
tzThe parameter is a string of a valid time zone. Ex: 'America/Phoenix'.
axisIf zero (0) or index is selected, apply to each column. Default 0.
If one (1) apply to each row.
levelIf MultiIndex, specify the conversion level.
copyIf True, this parameter makes a copy.

A traveler flies from Detroit, Michigan. They are on vacation for a week’s stay in three (3) cities near Berlin.

What is the time difference from their current location to back home in each city visited?

Run this code to find out!

df = pd.DataFrame({'Dest':  ['Berlin', 'Strausberg', 'Bernau'],
                   'Days':  [7, 7, 7]})
  
index_ = pd.date_range('2021-10-09 10:00', periods=3, freq='W', tz='America/Detroit')
df.index = index_
print(df)
print(df.index)

df = df.tz_convert(tz = 'Europe/Berlin')
print(df)
print(df.index)
  • Line [1] creates a DataFrame from a travel itinerary and saves it to df.
  • Line [2] creates an index based on a start date, three (3) weeks, a frequency ('W'), and a time zone.
  • Line [3] sets index_ as the index for the DataFrame.
  • Line [4] outputs the DataFrame to the terminal.
  • Line [5] outputs the index of the DataFrame to the terminal.
  • Line [6] converts the time zone from 'America/Detroit' to 'Europe/Berlin'.
  • Line [7] outputs the updated DataFrame to the terminal.
  • Line [8] outputs the updated index to the terminal.

OutputBefore tz_convert

df

 DestDays
2021-10-10 10:00:00-04:00Berlin    7
2021-10-17 10:00:00-04:00 Strausberg    7
2021-10-24 10:00:00-04:00     Bernau    7

df-index

DatetimeIndex(['2021-10-10 10:00:00-04:00', 
               '2021-10-17 10:00:00-04:00',
               '2021-10-24 10:00:00-04:00'],
               dtype='datetime64[ns, America/Detroit]', freq='W-SUN')

Output – After tz_convert

df

 DestDays
2021-10-10 16:00:00+02:00Berlin    7
2021-10-17 16:00:00+02:00 Strausberg    7
2021-10-24 16:00:00+02:00     Bernau    7

df-index

DatetimeIndex(['2021-10-10 16:00:00+02:00', 
              '2021-10-17 16:00:00+02:00',
              '2021-10-24 16:00:00+02:00'],
              dtype='datetime64[ns, Europe/Berlin]', freq='W-SUN')

Note: Definition of frequency period [W-SUN]:

  • W: weekly
  • SUN: each week starts on a Sunday

DataFrame to_timestamp()

The to_timestamp() method casts (converts) data to a Datetimeindex of timestamps at the start of a selected period.

The syntax for this method is as follows:

DataFrame.to_timestamp(freq=None, how='start', axis=0, copy=True)
ParameterDescription
freqThis parameter is an available frequency of the PeriodIndex method.
howThis parameter is the period conversion to timestamp. The available options are: 'start', 'end', 's', or 'e'.
axisIf zero (0) or index is selected, apply to each column. Default 0.
If one (1) apply to each row.
copyIf True, this parameter makes a copy.

For this example, we have four quarter earnings for Rivers Clothing for 2021. Each row displays a quarter-end date and total earning amount for that time.

earnings = [120545, 230574, 101155, 17598]
the_range = pd.period_range('2021Q1', '2021Q4', freq='Q-DEC')
times = pd.Series(earnings, the_range)
times.index = (the_range.asfreq('M', 'e')).asfreq('H', 's')+8
print(times)
  • Line [1] saves the quarterly earnings for Rivers Clothing in 2021 to a list.
  • Line [2] sets the date range (quarterly) and frequency. This output saves to the_range.
  • Line [3] sets the index and asfreq() month and hour. The start hour for each quarter is 8:00 am.
  • Line [4] outputs the times variable to the terminal.

Output

times

  
2021-03-01 08:00120545
2021-06-01 08:00230574
2021-09-01 08:00101155
2021-12-01 08:00    17598
Freq: H, dtype: int64