求筛选出包含每月第一天数据行的实现代码

Hey there! Let's figure out how to filter your data to keep only rows with the first day of each month (from 1999-01-01 all the way to 2010-12-01). I'll cover a few common tools you might be working with, since you didn't specify which one you're using:

1. Python (Pandas) #

If you're using Pandas for data analysis, here are two straightforward methods:

Method 1: Check day of month and date range #

This works if you want to keep any first-of-month date within your target range:

import pandas as pd

# Load your data (replace with your actual file/path)
df = pd.read_csv('your_dataset.csv')

# Convert your date column to datetime (critical for date operations!)
df['date_column'] = pd.to_datetime(df['date_column'])

# Filter rows where day is 1, and date falls between 1999-01-01 and 2010-12-01
filtered_df = df[(df['date_column'].dt.day == 1) &
                 (df['date_column'] >= '1999-01-01') &
                 (df['date_column'] <= '2010-12-01')]

Method 2: Match against a list of target dates #

If you want to explicitly match only the exact first-of-month dates in your range (in case your data has edge cases like leap years or invalid dates), generate the target list first:

import pandas as pd

df = pd.read_csv('your_dataset.csv')
df['date_column'] = pd.to_datetime(df['date_column'])

# Generate all first-of-month dates between your start and end
target_dates = pd.date_range(start='1999-01-01', end='2010-12-01', freq='MS')

# Keep only rows where date is in the target list
filtered_df = df[df['date_column'].isin(target_dates)]

2. SQL #

If your data is in a database, use this query (adjust function names based on your database):

SELECT *
FROM your_table
WHERE 
  -- Check day is 1 (function varies by DB)
  DAY(date_column) = 1 
  -- OR for PostgreSQL: EXTRACT(DAY FROM date_column) = 1
  -- OR for Oracle: EXTRACT(DAY FROM date_column) = 1
  AND date_column >= '1999-01-01'
  AND date_column <= '2010-12-01';

3. Excel #

For Excel users, here's a step-by-step approach:

1. Ensure your date column is formatted as a date: Select the column → Go to Data → Text to Columns → Choose Date and the correct format (e.g., YMD) → Finish.
2. Add an auxiliary column: In a new column (say, B), enter =DAY(A1) (replace A1 with your first date cell) and drag down to fill all rows. This gives you the day of the month for each date.
3. Add a filter column: In another new column (C), enter =AND(A1>=DATE(1999,1,1), A1<=DATE(2010,12,1), B1=1) and drag down. This will return TRUE for rows that meet your criteria.
4. Filter the results: Select column C → Go to Data → Filter → Check only TRUE in the dropdown. The remaining rows are your target data.

Hope one of these solutions fits your workflow! If you're using a specific tool I didn't mention, feel free to share and I can tweak the answer.

内容的提问来源于stack exchange，提问作者bli12blu12