如何从井水位数据中提取1997-2017年连续数据的井？

Hey there! Let's figure out how to filter those wells that have water level data every single year from 1997 to 2017. Below are practical implementations using common tools—pick the one that fits your workflow:

If you're working with a CSV/Excel file and prefer code, pandas makes this straightforward:

1. First, load your data and narrow it down to the 1997-2017 time range
2. Group by each well and count how many unique years it has records for
3. Keep only wells with exactly 21 unique years (since 2017 - 1997 + 1 = 21)

Here's the code snippet:

import pandas as pd

# Load your dataset (adjust the file path as needed)
well_df = pd.read_csv("well_level_data.csv")

# Filter rows to only include the target year range
target_years = well_df[(well_df["year"] >= 1997) & (well_df["year"] <= 2017)]

# Count unique years per well
year_counts_per_well = target_years.groupby("well")["year"].nunique()

# Get the list of wells that have data for all 21 years
valid_wells = year_counts_per_well[year_counts_per_well == 21].index.tolist()

# Optional: Extract the full dataset for these valid wells
valid_well_data = well_df[well_df["well"].isin(valid_wells)]

If your data is stored in a database, you can use a simple GROUP BY query to get the result directly:

SELECT well
FROM well_data
WHERE year BETWEEN 1997 AND 2017
GROUP BY well
HAVING COUNT(DISTINCT year) = 21;

The COUNT(DISTINCT year) ensures we're counting unique years per well, and the HAVING clause filters for wells that have all 21 required years.

If you prefer a GUI tool, Excel can handle this too:

Method 1: Pivot Table #

1. Select your entire dataset, go to the Insert tab, and create a Pivot Table
2. Drag the well field to the Rows area, and the year field to the Values area
3. Click the dropdown on the year value field, select Value Field Settings, then choose Distinct Count
4. Filter the pivot table to show only rows where the distinct count equals 21—these are your valid wells

Method 2: Helper Column #

1. Add a new column (e.g., Unique Year Count)
2. Use this formula (adjust column letters to match your data; assuming well is column A, year is column B):

   =SUMPRODUCT(($A:$A=A2)*(($B:$B>=1997)*($B:$B<=2017)/COUNTIFS($A:$A,$A:$A,$B:$B,$B:$B)))
   

3. Filter the helper column to show only values equal to 21, then extract the corresponding well IDs

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