Date-based indexing and slicing in Data Analysis Python - Mini Project: Build & Apply

Date-based indexing and slicing

📖 Scenario: You work in a small company that tracks daily sales data. You want to analyze sales for specific dates and date ranges to understand trends.

🎯 Goal: Learn how to create a time series data structure and use date-based indexing and slicing to select data for specific dates and ranges.

📋 What You'll Learn

Create a pandas Series with dates as the index

Use a date string to select data for a single day

Use date strings to slice data for a range of days

Print the selected data

💡 Why This Matters

🌍 Real World

Date-based indexing is useful in finance, sales, weather data, and any field where time series data is analyzed.

💼 Career

Data analysts and data scientists often need to filter and analyze data by dates to find trends and patterns.

1

Create a pandas Series with dates as index

Import pandas as pd. Create a pandas Series called sales with these values: [200, 220, 250, 210, 230] and use these dates as the index: ['2024-06-01', '2024-06-02', '2024-06-03', '2024-06-04', '2024-06-05']. Convert the dates to datetime format using pd.to_datetime().

Data Analysis Python

# Your code here

Hint

Use pd.to_datetime() to convert the list of date strings to datetime objects before using them as the index.

2

Create a date string to select a single day

Create a variable called single_date and set it to the string '2024-06-03'.

Data Analysis Python

import pandas as pd

dates = pd.to_datetime(['2024-06-01', '2024-06-02', '2024-06-03', '2024-06-04', '2024-06-05'])
sales = pd.Series([200, 220, 250, 210, 230], index=dates)
# Your code here

Hint

Just assign the string '2024-06-03' to the variable single_date.

3

Select sales data for the single date and a date range

Use single_date to select the sales value for that day from sales and store it in sales_single. Then create two variables start_date and end_date with values '2024-06-02' and '2024-06-04'. Use these to slice sales for the date range and store it in sales_range.

Data Analysis Python

import pandas as pd

dates = pd.to_datetime(['2024-06-01', '2024-06-02', '2024-06-03', '2024-06-04', '2024-06-05'])
sales = pd.Series([200, 220, 250, 210, 230], index=dates)
single_date = '2024-06-03'
# Your code here

Hint

Use sales[single_date] to get one day and sales[start_date:end_date] to get a range.

4

Print the selected sales data

Print sales_single and then print sales_range to show the sales for the single date and the date range.

Data Analysis Python

import pandas as pd

dates = pd.to_datetime(['2024-06-01', '2024-06-02', '2024-06-03', '2024-06-04', '2024-06-05'])
sales = pd.Series([200, 220, 250, 210, 230], index=dates)
single_date = '2024-06-03'
sales_single = sales[single_date]
start_date = '2024-06-02'
end_date = '2024-06-04'
sales_range = sales[start_date:end_date]
# Your code here

Hint

Use two print() statements: one for sales_single and one for sales_range.

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