Python Self-Learning Manual
In data processing scenarios,Excel is a commonly used data storage format, and filtering by date fields (such as filtering data after a certain date or within a specific time period) is a frequent requirement.ThePandas library provides a concise and efficient API that allows for quick implementation of Excel reading and date filtering, while also supporting the handling of irregular time formats and time zone issues. This article covers the core operations of this scenario from basic to advanced.
1. Environment Setup: Core Library Installation
Pandas reading Excel relies on third-party engines, and you need to install the corresponding libraries based on the Excel format (.xlsx/.xls). It is recommended to install a compatible combination of dependencies for all formats:
|
bash# Core library: pandas (data processing)pip install pandas# Read .xlsx format (Excel 2007+): openpyxlpip install openpyxl# Read .xls format (Excel 97-2003): xlrd (Note: xlrd 2.0+ does not support .xls, specify version)pip install xlrd==1.2.0 |
|
If only processing.xlsx files, you only need to install pandas + openpyxl; if dealing with older .xls files, you must install xlrd 1.2.0 version. |
2. Basic Implementation: Reading Excel and Filtering Time Data
The core process: read Excel file → convert the time field to Pandas time type (datetime64) → perform time filtering. The following uses a common “sales data.xlsx” as an example (which includes the “order time” field).
2.1 Step 1: Read Excel Data and Process Time Fields
The read_excel() method of Pandas supports automatic recognition of time fields, and you can also manually specify the conversion, it is recommended to manually specify to ensure accuracy.
|
pythonimport pandas as pd# 1. Read Excel file (specify time field column, automatically convert to datetime type)# Method 1: Automatically recognize time fields (suitable for standard format time)df = pd.read_excel( “sales_data.xlsx”, # Excel file path (absolute/relative path) engine=”openpyxl”, # Read .xlsx with openpyxl, .xls with xlrd parse_dates=[“order_time”] # Specify “order_time” column as time type)# Method 2: Manually convert time fields (suitable for irregular time formats, such as “2024-05-20 14:30”, “2024/05/20” mixed)# df = pd.read_excel(“sales_data.xlsx”, engine=”openpyxl”)# df[“order_time”] = pd.to_datetime(df[“order_time”], errors=”coerce”) # errors=”coerce” converts invalid times to NaT# View data structure and time field typeprint(“Data shape:”, df.shape)print(“Field types:”)print(df.dtypes) # Confirm “order_time” type is datetime64[ns]print(“First 5 rows:”)print(df.head()) |
2.2 Step 2: Common Time Filtering Scenarios
Pandas time filtering supports various conditions, the core is to utilize the comparability of datetime types, combined with boolean indexing. The following are 6 common scenarios:
Scenario 1: Filter data for a specific date
Note:Pandas time fields include hours, minutes, and seconds, so you need to use dt.date to extract the date part for matching.
|
pythonimport datetime# Target date: May 20, 2024target_date = datetime.date(2024, 5, 20)# Filter data where “order_time” is 2024-05-20filtered_df = df[df[“order_time”].dt.date == target_date]print(f”Number of orders on 2024-05-20: {len(filtered_df)}”)print(filtered_df[[“order_id”, “order_time”, “amount”]].head()) |
Scenario 2: Filter data after (including) a specific date
Use comparison operators (>=), supports directly passing in string format dates (Pandas automatically parses).
|
python# Filter orders from June 1, 2024, and laterfiltered_df = df[df[“order_time”] >= “2024-06-01”]# Equivalent writing (using datetime object)# filtered_df = df[df[“order_time”] >= pd.to_datetime(“2024-06-01”)]print(f”Number of orders from 2024-06-01: {len(filtered_df)}”)print(“Latest 5 orders:”)print(filtered_df[[“order_id”, “order_time”, “amount”]].tail()) |
Scenario 3: Filter data within a specific time range (date range)
Combine two comparison conditions (>= start time and <= end time), use & to connect (note the parentheses).
|
python# Filter orders from May 1, 2024, to May 15, 2024start_date = “2024-05-01”end_date = “2024-05-15”filtered_df = df[(df[“order_time”] >= start_date) & (df[“order_time”] <= end_date)]print(f”Number of orders from 2024-05-01 to 2024-05-15: {len(filtered_df)}”)print(f”Total amount of orders in this period: {filtered_df[‘amount’].sum():.2f} yuan”) |
Scenario 4: Filter data for a specific time period (including hours and minutes)
Applicable for filtering down to the hour/minute, such as “2024-05-20 10:00 to 14:30 orders.
|
python# Filter orders from 2024-05-20 10:00:00 to 2024-05-20 14:30:00start_time = pd.to_datetime(“2024-05-20 10:00:00”)end_time = pd.to_datetime(“2024-05-20 14:30:00”)filtered_df = df[(df[“order_time”] >= start_time) & (df[“order_time”] <= end_time)]print(f”Number of orders from 2024-05-20 10:00-14:30: {len(filtered_df)}”)print(filtered_df[[“order_id”, “order_time”, “amount”]]) |
Scenario 5: Filter data for specific weeks/months
Utilize dt.weekday (week, 0= Monday, 6= Sunday) or dt.month (month) to extract time attributes for filtering.
|
python# Scenario 5.1: Filter all orders on Sundays (weekday=6)filtered_sunday = df[df[“order_time”].dt.weekday == 6]print(f”Number of Sunday orders: {len(filtered_sunday)}”)# Scenario 5.2: Filter all orders in May 2024filtered_may = df[(df[“order_time”].dt.year == 2024) & (df[“order_time”].dt.month == 5)]print(f”Number of orders in May 2024: {len(filtered_may)}”)# Scenario 5.3: Filter orders from the 1st to the 5th of each monthfiltered_month_start = df[df[“order_time”].dt.day.isin([1,2,3,4,5])]print(f”Number of orders from the 1st to the 5th of each month: {len(filtered_month_start)}”) |
Scenario 6: Filter data from the last N days (dynamic time range)
Combine pd.Timestamp.now() to get the current time, calculate the time point N days ago to achieve dynamic filtering.
|
python# Filter orders from the last 7 days (168 hours)days = 7current_time = pd.Timestamp.now() # Current timepast_time = current_time – pd.Timedelta(days=days) # Time 7 days agofiltered_recent = df[df[“order_time”] >= past_time]print(f”Number of orders in the last {days} days: {len(filtered_recent)}”)print(f”Total amount of orders in the last {days} days: {filtered_recent[‘amount’].sum():.2f} yuan”) |
3. Advanced Techniques: Handling Complex Time Scenarios
3.1 Handling Irregular Time Format Data
If Excel contains time fields with mixed formats (such as “2024-05-20”, “2024/05/20”, “5-20-2024” mixed), you need to use pd.to_datetime() to force conversion and handle exceptions.
|
pythonimport pandas as pd# Read data (do not automatically convert time)df = pd.read_excel(“sales_data_mixed_format.xlsx”, engine=”openpyxl”)# Manually convert time fields, handle exceptionsdf[“order_time”] = pd.to_datetime( df[“order_time”], errors=”coerce”, # Invalid times converted to NaT (Not a Time) format=None # Automatically recognize multiple formats, if a fixed format is known, it can be specified (e.g., format=”%Y-%m-%d %H:%M:%S”))# Filter valid time data (exclude NaT)df_valid = df.dropna(subset=[“order_time”])print(f”Original data row count: {len(df)}, valid time data row count: {len(df_valid)}”)print(f”Invalid time data row count: {len(df) – len(df_valid)}”)# Subsequent filtering operations (e.g., filter 2024 data)filtered_2024 = df_valid[df_valid[“order_time”].dt.year == 2024] |
3.2 Sort by Time Field and Remove Duplicates
In practical scenarios, there may be duplicate order data, which needs to be sorted by “order_time” and keep the latest/earliest records.
|
pythonimport pandas as pddf = pd.read_excel(“sales_data.xlsx”, engine=”openpyxl”, parse_dates=[“order_time”])# Group by “order_id”, sort by “order_time” in descending order, keep the first of each group (latest record)df_deduplicated = df.sort_values(“order_time”, ascending=False).drop_duplicates(subset=[“order_id”], keep=”first”)print(f”Row count before deduplication: {len(df)}, row count after deduplication: {len(df_deduplicated)}”)# Filter deduplicated June 2024 datafiltered_june = df_deduplicated[(df_deduplicated[“order_time”].dt.year == 2024) & (df_deduplicated[“order_time”].dt.month == 6)] |
3.3 Save Filtered Results as New Excel File
After filtering, you can use to_excel() to save the results as a new Excel, facilitating subsequent use.
|
pythonimport pandas as pd# (Continuing from the filtering results of Scenario 3)start_date = “2024-05-01”end_date = “2024-05-15”filtered_df = df[(df[“order_time”] >= start_date) & (df[“order_time”] <= end_date)]# Save filtered results as new Exceloutput_path = “sales_data_from_May_1_to_15_2024.xlsx”filtered_df.to_excel( output_path, index=False, # Do not save index column engine=”openpyxl”, # Save as .xlsx format sheet_name=”Filtered Results” # Specify sheet name)print(f”Filtered results saved to: {output_path}”)print(f”Total {len(filtered_df)} records saved”) |
4. Common Issues and Solutions
|
Common Issue |
Solution |
|
Time field recognized as string when reading Excel file |
1. Use parse_dates parameter to specify time column; 2. Manually convert using pd.to_datetime(), set errors=”coerce” |
|
Filtered data is empty, but Excel contains corresponding time data |
1. Check if the time field type is datetime64; 2. Check the date format of the filtering conditions (e.g., “2024/05/20” and “2024-05-20” are consistent); 3. Exclude NaT invalid times |
|
Results do not match expectations when filtering by week |
Note the encoding rules of dt.weekday: 0= Monday, 1= Tuesday…6= Sunday, not 1= Monday |
|
Time format displays abnormally (e.g., displayed as numbers) when saving Excel file |
No additional processing is required, Excel will automatically recognize datetime types as time format; if a custom format is needed, you can use openpyxl library to set cell formats |
5. Conclusion
The core of Pandas implementing Excel time filtering is “ first ensure that the time field is datetime type, and then achieve conditional filtering through boolean indexing“. In practical applications, you need to choose the appropriate reading engine based on Excel format, and handle exceptions for irregular time formats. The 6 filtering scenarios covered in this article basically meet daily needs, and combined with techniques such as sorting, deduplication, and result saving, a complete data processing loop can be formed. If you need to handle large-scale Excel data (such as 1 million+ rows), you can first read in chunks using the chunksize parameter, then filter and merge in chunks to improve processing efficiency.