如何按数字倍数过滤数据集列？——Strava通勤数据集周末记录剔除方法问询

Hey there! Let's fix this problem properly—your initial idea of filtering multiples of 6 or 7 won't work because the day-of-year number doesn't align consistently with weekends across different years. Instead, we need to convert the year and day values into actual dates, then check the day of the week to exclude Saturdays and Sundays.

Here are step-by-step solutions for two common tools used to handle this kind of dataset:

Using Python with Pandas #

If you're working with a DataFrame in Pandas, follow these steps:

1. Combine year and day into a proper date
   We'll use the %j format code, which represents the day of the year (1-366):

   import pandas as pd
   
   # Load your dataset into a DataFrame (example)
   df = pd.read_csv("your_strava_data.csv")
   
   # Create a date column from year and day-of-year
   df["date"] = pd.to_datetime(df["year"].astype(str) + df["day"].astype(str).str.zfill(3), format="%Y%j")
   

   The str.zfill(3) ensures days 1-99 are padded with leading zeros (e.g., day 5 becomes "005") to match the %j format.

2. Extract the weekday identifier
   Pandas' dt.weekday returns 0 for Monday, 4 for Friday, 5 for Saturday, and 6 for Sunday:

   df["weekday"] = df["date"].dt.weekday
   

3. Filter out weekend records
   Keep only rows where the weekday is not Saturday or Sunday:

   filtered_df = df[df["weekday"] < 5]  # Keeps Monday-Friday
   

4. Clean up (optional)
   Remove the temporary date and weekday columns if you don't need them:

   filtered_df = filtered_df.drop(["date", "weekday"], axis=1)
   

Using SQL #

If you're querying the data directly from a database (example uses PostgreSQL syntax, adjust for your DBMS if needed):

1. Convert year and day to a date
   Use the make_date function to create a date from the year, then add the day-of-year offset:

   SELECT *, 
          make_date(year, 1, 1) + (day - 1) * INTERVAL '1 day' AS date
   FROM your_strava_table
   

2. Filter out weekends
   Use extract(dow from ...) where dow returns 0 for Sunday, 1 for Monday, ..., 6 for Saturday. Exclude 0 and 6:

   SELECT edge_id, year, day, hour, commute_count
   FROM your_strava_table
   WHERE extract(dow from make_date(year, 1, 1) + (day - 1) * INTERVAL '1 day') NOT IN (0, 6);
   

Why Your Initial Approach Won't Work #

The day-of-year number (1-365) doesn't map to fixed weekdays across years. For example:

• In 2024, January 1 is a Monday, so day 7 is a Sunday
• In 2023, January 1 is a Sunday, so day 7 is a Saturday
  There's no consistent multiple of 6 or 7 that corresponds to weekends every year—hence the need to calculate the actual date first.

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