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.

Preparation

Before any data manipulation can occur, two (2) 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.

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

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

• How to Install Pandas on PyCharm
• How to Install NumPy on PyCharm

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 numpy as np 

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)

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 them 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

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)

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 them 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

DataFrame drop()

The drop() method deletes rows/columns from a DataFrame by entering a label name(s) and an axis or 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')

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

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

After Drop

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)

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)

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

Output

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)

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:

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]]

Further Learning Resources

This is Part 7 of the DataFrame method 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 the serialization and conversion methods from_dict(), to_dict(), from_records(), to_records(), to_json(), and to_pickles().
• Part 22 focuses on the serialization and conversion methods to_clipboard(), to_html(), to_sql(), to_csv(), and to_excel().
• Part 23 focuses on the serialization and conversion methods to_markdown(), to_stata(), to_hdf(), to_latex(), to_xml().
• Part 24 focuses on the serialization and conversion methods to_parquet(), to_feather(), to_string(), Styler.
• Part 25 focuses on the serialization and conversion methods to_bgq() and to_coo().

Also, have a look at the Pandas DataFrame methods cheat sheet!