How to Use Excel's DAY Function in Pandas

Excel's DAY function extracts the day as a number from a time value. It's especially useful when working with large datasets where you need to analyze data at daily granularity.

This page explains how to implement Excel's DAY function in Python using pandas.

Implementing the Day Extraction function in Pandas#

Recreating Excel's DAY function behavior in Python requires a combination of pandas operations. Here are some common implementations:

Extracting Day from Datetime#

In Excel, if you have a datetime value, you might use the DAY function directly to get the day. Similarly, in pandas, you use the `.dt` accessor followed by the `day` attribute.

For example, in Excel you might use =DAY(A2). In pandas:

df['Day'] = df['Datetime_Column'].dt.day

Copy!

Converting string to datetime and then extracting day#

Often, Pandas will infer the data type of your column as string, even if the data to you looks like a date, ie: 1/2/23. In these cases, you need to convert the string to datetime before extracting the day.

To do this in pandas, first use `pd.to_datetime` to convert the column to a datetime column, and then extract the day:

# Convert the string to datetime
df['Datetime_Column'] = pd.to_datetime(df['String_Column'])

# Extract the day from the datetime column
df['Day'] = df['Datetime_Column'].dt.day

Copy!

Extract the day of week as Monday, Tuesday, etc.#

Instead of returning the day of the month as a number, you might want to return the day of the week as Monday, Tuesday, etc.

To do so, use the `day_name` attribute instead of `day`. It will return the day of the week as a word.

# Extract the day of the week as Monday, Tuesday, etc.
df['Day'] = df['Date'].dt.day_name()

Copy!

Day of Week as Number#

To get the day of the week as a number, you can use the `weekday` attribute. It returns the day of the week as an integer, where Monday is 0 and Sunday is 6.

This is similar to Excel's WEEKDAY function, except that Excel's WEEKDAY function returns Sunday as 1 and Saturday as 7.

# Extract the day of the week as a number
df['Day'] = df['Date'].dt.weekday

Copy!

Grouping Data by Day#

There are situations where you want to aggregate data based on day. In Excel, you might use a pivot table after extracting the day. Similarly, in pandas, after extracting the day, you can use the `groupby` method

For example, if you have a column called 'Date' and a column called 'Website Traffic', you might want to group the data by day and sum the traffic for each day.

df['Day'] = df['Date'].dt.day
grouped_data = df.groupby('Day').agg({'Website Traffic': 'sum'}).reset_index()

Copy!

Common mistakes when using DAY in Python#

While implementing the DAY function equivalent in pandas, a few common pitfalls might occur. Here's how to navigate them.

Incorrect datatypes#

The `.dt` accessor is exclusive to pandas Series with datetime64 data types. Using it on non-datetime columns will raise an AttributeError.

For example, if you have a column called 'Date', but it actually has an object data type, you'll need to convert it to datetime before using the `.dt` accessor. You can check the data type of a column using `df.dtypes`.

# Ensure the column is of datetime dtype
df['Datetime_Column'] = pd.to_datetime(df['Datetime_Column'])
df['Day'] = df['Datetime_Column'].dt.day

Copy!

Forgetting to Handle Null Values#

If your dataset has missing or NaT (Not-a-Timestamp) values in the datetime column, trying to extract the day from them will result in NaN (Not a Number) values. Make sure to handle or filter them out as necessary.

# Drop rows with NaT values before extracting day
df.dropna(subset=['Datetime_Column'], inplace=True)
df['Day'] = df['Datetime_Column'].dt.day

Copy!

Understanding the Day Extraction Formula in Excel#

The DAY function in Excel returns the day of a time value, ranging from 1 to 31.

=DAY(serial_number)

DAY Excel Syntax

Examples