Pandas DataFrame Time, Drop, and Duplicates

The Pandas DataFrame has several Re-indexing/Selection/Label Manipulations methods. When applied to a DataFrame, these methods evaluate, modify the elements and return the results.

This is Part 7 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()

Getting Started

Remember to add the Required Starter Code to the top of each code snippet. This snippet will allow the code in this article to run error-free.

Required Starter Code

import pandas as pd
import numpy as np 

Before any data manipulation can occur, two new libraries will require installation.

  • The pandas library enables access to/from a DataFrame.
  • The numpy library supports multi-dimensional arrays and matrices in addition to a collection of mathematical functions.

To install these libraries, 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.

$ pip install pandas

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

$ pip install numpy

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

Feel free to check out the correct ways of installing those libraries here:

If the installations were successful, a message displays in the terminal indicating the same.

DataFrame at_time()

The at_time() method selects and retrieves values at a specified time each day. An error occurs if the index is not a DateTimeIndex. To fully understand this method, feel free to watch this short tutorial:

The syntax for this method is as follows:

DataFrame.at_time(time, asof=False, axis=None)
ParameterDescription
timeThis parameter must be a valid datetime.time or string.
asofIf set to True, this parameter uses the start date/time. By default, False.
axisIf zero (0) or index is selected, apply to each column. Default is None. If one (1) is selected, apply to each row.

For this example, the stock prices for Apple return three (3) times per day at eight (8) hour intervals.

todays_date  = date.today()
stock_drange  = pd.date_range(todays_date, periods=3, freq='8H')
stock_df = pd.DataFrame({'AAPL': [10.34, 9.83, 10.39]}, index=stock_drange)
print(stock_df)

result = stock_df.at_time('08:00')
print(result)
  • Line [1] uses today() to retrieve the current date/time and saves it to todays_date.
  • Line [2] uses date_range() to set the date, period(s), frequencies and saves it to stock_drange.
  • Line [3] creates a DataFrame. Sets the index to stock_drange and saves it to stock_df.
  • Line [4] outputs the result to the terminal.
  • Line [5] retrieves the stock price details for the specified time (’08:00’) and saves it to result.
  • Line [6] outputs the result to the terminal.

Output:

AAPL
2022-01-06 00:00:00 10.34
2022-01-06 08:00:00  9.83
2022-01-06 16:00:00 10.39
AAPL
2022-01-06 08:00:00  9.83

DataFrame between_time()

The between_time() method selects and retrieves values occurring between set times. The return value is a DataFrame/Series.

The syntax for this method is as follows:

DataFrame.between_time(start_time, end_time, include_start=True, include_end=True, axis=None)
ParameterDescription
start_timeThis parameter must be a valid datetime.time or string.
end_time This parameter must be a valid datetime.time or string.
include_startBy default, this is True, meaning the results include the start time.
include_end By default, this is True, meaning the results include the end time.
axisIf zero (0) or index is selected, apply to each column. Default is None. If one (1) is selected, apply to each row.

For this example, the stock prices for Apple returns at six (6) hour intervals.

todays_date  = date.today()
stock_drange  = pd.date_range(todays_date, periods=6, freq='6H')
stock_df = pd.DataFrame({'AAPL': [10.34, 9.83, 10.39, 8.54, 9.97, 11.98]}, index=stock_drange)
print(stock_df)

result = stock_df.between_time('06:00', '12:00')
print(result)
  • Line [1] uses today() to retrieve the current date/time and saves it to todays_date.
  • Line [2] uses date_range() to set the date, period(s), frequencies and saves it to stock_drange.
  • Line [3] creates a DataFrame. Sets the index to stock_drange and saves it to stock_df.
  • Line [4] outputs the result to the terminal.
  • Line [5] retrieves the stock prices between the specified start and end times and saves to the result variable.
  • Line [6] outputs the result to the terminal.

Output:

AAPL
2022-01-06 00:00:00 10.34
2022-01-06 06:00:00  9.83
2022-01-06 12:00:00 10.39
2022-01-06 18:00:00  8.54
2022-01-07 00:00:00  9.97
2022-01-07 06:00:00 11.98
AAPL
2022-01-06 06:00:00  9.83
2022-01-06 12:00:00 10.39
2022-01-07 06:00:00 11.98

DataFrame drop()

The drop() method deletes rows/columns from a DataFrame by entering a label name(s) and an axis or by entering the index/column name(s). To fully understand this method, feel free to watch this short tutorial:

The syntax for this method is as follows:

DataFrame.drop(labels=None, axis=0, index=None, columns=None, level=None, inplace=False, errors='raise')
ParameterDescription
labelsA single label or list of label(s) to delete from the DataFrame.
axisIf zero (0) or index is selected, apply to each column. Default is None. If one (1) is selected, apply to each row.
indexRather than entering an index axis: axis=0, you could enter index=labels.
columnsRather than entering a column axis: axis=1, you could enter columns=labels.
levelIf multi-index, enter the appropriate level to delete.
inplaceIf False, create a copy of the DataFrame/Series. If zero (0) or index is selected, apply to each column. Default is None. If True, the original DataFrame/Series updates.
errorsIf set to ignore, the errors suppress.

For this example, Rivers Clothing has decided to drop Sweats from its clothing line. They need this clothing line removed. To perform this task, run the following code:

Code Example 1 (Simple Drop):

df = pd.DataFrame({'Tops':    [10, 12, 13],
                   'Tanks':   [11, 13, 14],
                   'Pants':   [21, 56, 94],
                   'Sweats':  [27, 21, 35]}, index=['Small', 'Medium', 'Large'])

result = df.drop('Sweats', axis=1)
print(result)
  • Line [1] creates a DataFrame and saves it to df.
  • Line [2] drops the Sweats data from the DataFrame and saves it to the result variable.
  • Line [3] outputs the result to the terminal.

Output:

 TopsTanksPants
Small101121
Medium121356
Large131494

For this simple example, we drop a level from a DataFrame with a MultiIndex.

Code Example 2 (Multi-Level Drop):

cols = pd.MultiIndex.from_tuples([("A", "a"), ("C", "c"), ("E", "e")])
df   = pd.DataFrame([[1, 2, 3], [4, 5, 6]], columns=cols)
print(df)

Setting up the MultiIndex & DataFrame

  • Line [1] creates a MultiIndex from tuples and saves it to cols.
  • Line [2] creates a DataFrame with random data and sets the columns to cols.
  • Line [3] outputs the result to the terminal.
df.columns = df.columns.droplevel(0)
print(df)
  • Line [4] uses droplevel() to drop the first column.
  • Line [5] outputs the result to the terminal.

Note: Replacing Line [4] with: df.columns = [col[0] for col in df.columns] accomplishes the same outcome using list comprehension.

Output:

Original:

 ACE
 ace
0123
1456

After Drop:

 ace
0123
1456

DataFrame drop_duplicates()

The drop_duplicates() method returns a DataFrame/Series with duplicate rows removed.

The syntax for this method is as follows:

DataFrame.drop_duplicates(subset=None, keep='first', inplace=False, ignore_index=False)
ParameterDescription
subsetSpecify the column(s) to locate the duplicates. By default, all columns.
keepDetermines what duplicates to keep, first or the last occurrence. By default, first.
inplaceIf False creates a copy of the DataFrame/Series. By default, False. If True, the original DataFrame/Series updates.
ignore_indexIf True, the returning axis will start the numbers from 0 – n value. By default, False.

For this example, Rivers Clothing has found a duplicate clothing line in the DataFrame. They need this duplicate removed. To perform this task, run the following code:

df = pd.DataFrame({'Tops':   [10.12, 12.23, 13.95],
                   'Tanks':   [11.35, 13.45, 14.98],
                   'Sweats':  [27.15, 21.85, 35.75],
                   'Pants':   [21.37, 56.99, 94.87],
                   'Sweats':  [27.15, 21.85, 35.75]})

result = df.drop_duplicates()
print(result)

Output:

 TopsTanksSweatsPants
010.12 11.3527.1521.37
112.2313.4521.85 56.99
213.95 14.98  35.75 94.87

DataFrame duplicated()

The duplicated() method returns a Series of boolean values indicating duplicate row(s).

The syntax for this method is as follows:

DataFrame.drop_duplicates(subset=None, keep='first', inplace=False, ignore_index=False)
ParameterDescription
subsetSpecify the column(s) to locate the duplicates. By default, all columns.
keepDetermines what duplicates to keep, first or the last occurrence. By default, first.
inplaceIf False creates a copy of the DataFrame/Series. By default, False. If True, the original DataFrame/Series updates.
ignore_indexIf True, the returning axis will start the numbers from 0 – n value. By default, False.

For this example, a DataFrame is created that contains four (4) rows.

Notice that GMC has two (2) identical records in the DataFrame.

Code Example 1:

df = pd.DataFrame({'Make':   ['Honda', 'GMC',    'GMC',    'Ford'],
                   'Model':  ['Civic', 'Canyon', 'Canyon', 'Mustang'],
                   'Rating': [4, 3.5, 3.5, 15]})

result = df.drop_duplicates()
print(result)
  • Line [1] creates a DataFrame from a Dictionary of Lists and saves it to df.
  • Line [2] removes the duplicate row(s).
  • Line [3] outputs the result to the terminal.

Output:

 MakeModelRatings
0HondaCivic4.0
1GMCCanyon3.5
3FordMustang15.0

Another way to remove duplicate rows is to use the NumPy library.

Code – Example 2:

df = np.array([[1,8,3,3,4],
               [1,8,9,9,4],
               [1,8,3,3,4]])

new_array   = [tuple(row) for row in df]
result = np.unique(new_array, axis=0)                 
print(result)
  • Line [1] creates a DataFrame from a List of Lists and saves it to df.
  • Line [2] removes the duplicate row(s).
  • Line [3] outputs the result to the terminal.

Output:

[[1 8 3 3 4]
 [1 8 9 9 4]]