Date-based indexing and slicing in Data Analysis Python - Step-by-Step Execution

Concept Flow - Date-based indexing and slicing

Create DateTimeIndex

↓

Create DataFrame with DateTimeIndex

↓

Select single date or date range

↓

Return subset of DataFrame

↓

Use result for analysis or display

We create a DataFrame indexed by dates, then select data by specifying dates or date ranges to get subsets.

Execution Sample

Data Analysis Python

import pandas as pd
idx = pd.date_range('2023-01-01', periods=5)
df = pd.DataFrame({'val':[10,20,30,40,50]}, index=idx)
subset = df['2023-01-02':'2023-01-04']
print(subset)

This code creates a DataFrame with dates as index and selects rows from Jan 2 to Jan 4.

Execution Table

Step	Action	Code/Expression	Result/Output
1	Create date range index	pd.date_range('2023-01-01', periods=5)	DatetimeIndex(['2023-01-01', '2023-01-02', '2023-01-03', '2023-01-04', '2023-01-05'], dtype='datetime64[ns]', freq='D')
2	Create DataFrame with date index	pd.DataFrame({'val':[10,20,30,40,50]}, index=idx)	DataFrame with 5 rows indexed by dates from 2023-01-01 to 2023-01-05
3	Select subset by date range	df['2023-01-02':'2023-01-04']	Rows for 2023-01-02, 2023-01-03, 2023-01-04 with values 20, 30, 40
4	Print subset	print(subset)	val 2023-01-02 20 2023-01-03 30 2023-01-04 40

💡 Selection ends after 2023-01-04, slicing includes end date in date-based indexing.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
idx	undefined	DatetimeIndex(['2023-01-01', '2023-01-02', '2023-01-03', '2023-01-04', '2023-01-05'])	same	same	same
df	undefined	undefined	DataFrame with 5 rows indexed by idx	same	same
subset	undefined	undefined	undefined	DataFrame with rows 2023-01-02 to 2023-01-04	same

Key Moments - 2 Insights

Why does the slice df['2023-01-02':'2023-01-04'] include the end date 2023-01-04?

In date-based slicing with pandas, the end date is included unlike normal Python slicing. See execution_table step 3 and 4 where 2023-01-04 row is included.

Can I select a single date like df['2023-01-03']?

Yes, selecting a single date returns the row for that date. This is similar to slicing but returns a single row instead of a DataFrame slice.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of 'val' for the date 2023-01-03 in the subset?

A 20

B 30

C 40

D 50

💡 Hint

Check execution_table row 3 and 4 for the subset values.

At which step is the DataFrame 'df' created with the date index?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at execution_table step 2 where df is created.

If you change the slice to df['2023-01-02':'2023-01-03'], how many rows will the subset have?

A 2

B 1

C 3

D 4

💡 Hint

Date slicing includes both start and end dates, so count rows between 2023-01-02 and 2023-01-03.

Concept Snapshot

Date-based indexing lets you select rows by dates in a DataFrame.
Use a DateTimeIndex for the DataFrame.
Slice with df['start_date':'end_date'] to get all rows in that range, inclusive.
Selecting a single date returns that row.
This is handy for time series data analysis.

Full Transcript

We start by creating a date range index from January 1 to January 5, 2023. Then, we create a DataFrame with this date index and some values. Next, we select a subset of the DataFrame by slicing from January 2 to January 4. The slice includes the end date, so rows for January 2, 3, and 4 are returned. This subset is printed showing the values for those dates. Date-based slicing in pandas includes the end date, unlike normal Python slicing. You can also select a single date to get one row. This method is useful for working with time series data.

Practice

1. What does date-based indexing in pandas allow you to do?

easy

A. Select rows from a DataFrame using dates or date ranges

B. Change the data type of a column to datetime

C. Sort the DataFrame by numerical values

D. Merge two DataFrames based on a key column

Solution

1. Step 1: Understand date-based indexing

   Date-based indexing lets you select rows by specifying dates or date ranges in the index.

2. Step 2: Compare with other options

   Changing data types, sorting, or merging are different operations unrelated to date-based indexing.

3. Final Answer:

   Select rows from a DataFrame using dates or date ranges -> Option A

4. Quick Check:

   Date-based indexing = selecting rows by dates [OK]

Hint: Date-based indexing picks rows by date labels [OK]

Common Mistakes:

• Confusing indexing with data type conversion
• Thinking it sorts data automatically
• Mixing it up with merging DataFrames

2. Which of the following is the correct syntax to select data for January 2023 from a DataFrame df with a datetime index?

easy

A. df['2023-01']

B. df.loc['January 2023']

C. df['01-2023']

D. df.loc[2023, 1]

Solution

1. Step 1: Understand partial string indexing

   Using df['2023-01'] selects all rows in January 2023 by partial date string.

2. Step 2: Check other options

   df.loc['January 2023'] is invalid syntax; df['01-2023'] is wrong format; df.loc[2023, 1] is invalid for datetime index.

3. Final Answer:

   df['2023-01'] -> Option A

4. Quick Check:

   Partial date string 'YYYY-MM' selects month [OK]

Hint: Use 'YYYY-MM' string to slice by month [OK]

Common Mistakes:

• Using month-year in wrong order
• Trying to use month name string directly
• Using tuple indexing for dates

3. Given the DataFrame df with datetime index from '2023-01-01' to '2023-01-05' and a column 'value' as [10, 20, 30, 40, 50], what is the output of df['2023-01-03':'2023-01-05']['value'].tolist()?

medium

A. [10, 20, 30]

B. [20, 30, 40]

C. [30, 40, 50]

D. [40, 50]

Solution

1. Step 1: Understand slicing by date range

   The slice '2023-01-03':'2023-01-05' selects rows from Jan 3 to Jan 5 inclusive.

2. Step 2: Extract 'value' column and convert to list

   The values on these dates are 30, 40, and 50, so the list is [30, 40, 50].

3. Final Answer:

   [30, 40, 50] -> Option C

4. Quick Check:

   Date slice includes start and end dates [OK]

Hint: Date slices include both start and end dates [OK]

Common Mistakes:

• Excluding the end date in slice
• Confusing index positions with dates
• Selecting wrong column

4. What is wrong with this code snippet to select data for February 2023 from a DataFrame df with datetime index?

df.loc['2023-02']

medium

A. It will raise a KeyError if the index is not sorted

B. It will raise a TypeError because string slicing is not allowed

C. It will return data for all of 2023 instead of February

D. Nothing is wrong; this is correct syntax

Solution

1. Step 1: Check syntax for date-based slicing

   Using df.loc['2023-02'] is valid to select all rows in February 2023 if the index is datetime.

2. Step 2: Confirm no errors occur

   This syntax works correctly and returns February data; no error or wrong output happens.

3. Final Answer:

   Nothing is wrong; this is correct syntax -> Option D

4. Quick Check:

   Partial date string with .loc works for datetime index [OK]

Hint: Use .loc with 'YYYY-MM' to slice by month [OK]

Common Mistakes:

• Thinking string slicing is invalid with .loc
• Assuming index must be sorted for this to work
• Confusing .loc with .iloc for date strings

5. You have a DataFrame df indexed by datetime from '2023-01-01' to '2023-03-31'. How would you select all rows for the first quarter of 2023 using date-based slicing?

hard

A. df.loc['2023-01':'2023-03-01']

B. df['2023-01-01':'2023-03-31']

C. df.loc['2023-Q1']

D. df['2023-01-01':'2023-03']

Solution

1. Step 1: Understand date range for Q1 2023

   Q1 covers January 1 to March 31, so the full date range is '2023-01-01' to '2023-03-31'.

2. Step 2: Use slicing with full dates

   Using df['2023-01-01':'2023-03-31'] selects all rows in this range correctly.

3. Step 3: Check other options

   df.loc['2023-Q1'] is not valid; df.loc['2023-01':'2023-03-01'] selects only up to March 1st, excluding the rest of March; df['2023-01-01':'2023-03'] ends on incomplete date.

4. Final Answer:

   df['2023-01-01':'2023-03-31'] -> Option B

5. Quick Check:

   Use full date range strings for quarters [OK]

Hint: Use full start and end dates for quarter slicing [OK]

Common Mistakes:

• Using quarter notation like '2023-Q1' which is invalid
• Using partial month strings that exclude days
• Ending slice with incomplete date string