技术问询：使用同一列前值填充null值及对应待处理数据集

First, let's restate your dataset clearly for reference:

Looking at the Expected_version column, the pattern is: for each product (Name), fill nulls in Joined_version with the most recent non-null value when sorted by Date. This is called a "forward fill" within groups.

Solution 1: Python (Pandas) #

Pandas makes this straightforward with grouping and the ffill (forward fill) method. Here's step-by-step code:

import pandas as pd

# Load your dataset (replace with your actual data source)
data = [
    ["25/07/2018", "Product 1", "A", "ios", 1, 1],
    ["25/07/2018", "Product 1", "B", "ios", None, 1],
    ["25/07/2018", "Product 1", "C", "ios", None, 1],
    ["25/07/2018", "Product 1", "D", "play", 2, 2],
    ["25/07/2018", "Product 2", "A", "play", 1.1, 1.1],
    ["26/07/2018", "Product 2", "A", "ios", None, 1.1],
    ["26/07/2018", "Product 2", "B", "ios", None, 1.1],
    ["26/07/2018", "Product 2", "C", "ios", None, 1.1],
    ["26/07/2018", "Product 2", "D", "ios", None, 1.1],
    ["26/07/2018", "Product 1", "E", "ios", 3, 3],
    ["26/07/2018", "Product 2", "A", "play", None, 1.1],
    ["27/07/2018", "Product 1", "A", "ios", None, 3],
    ["27/07/2018", "Product 1", "B", "ios", None, 3],
]

df = pd.DataFrame(data, columns=["Date", "Name", "Country", "Channel", "Joined_version", "Expected_version"])

# Step 1: Convert Date column to datetime for proper sorting
df["Date"] = pd.to_datetime(df["Date"], format="%d/%m/%Y")

# Step 2: Group by product name, sort each group by date, then forward fill the nulls
df["Filled_Joined_version"] = df.groupby("Name").apply(
    lambda group: group.sort_values("Date")["Joined_version"].ffill()
).reset_index(drop=True)

# Check the result
print(df[["Date", "Name", "Joined_version", "Filled_Joined_version", "Expected_version"]])

Output Explanation: #

The Filled_Joined_version column will exactly match your Expected_version column. The key steps are:

• Converting Date to datetime ensures we sort rows correctly chronologically.
• Grouping by Name ensures we only fill nulls within the same product.
• ffill() propagates the last valid observation forward to fill nulls.

Solution 2: SQL (PostgreSQL Example) #

If you're working with a database, you can use window functions to achieve the same result. Here's how to do it in PostgreSQL:

SELECT
    Date,
    Name,
    Country,
    Channel,
    Joined_version,
    -- Forward fill using last non-null value in the product group, ordered by date
    LAST_VALUE(Joined_version) OVER (
        PARTITION BY Name
        ORDER BY TO_DATE(Date, 'DD/MM/YYYY')
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Filled_Joined_version,
    Expected_version
FROM your_table_name;

How It Works: #

• PARTITION BY Name: Splits the data into groups for each product.
• ORDER BY TO_DATE(Date, 'DD/MM/YYYY'): Sorts each group chronologically (convert string date to date type first).
• LAST_VALUE(Joined_version) ...: Takes the most recent non-null value up to the current row to fill the null.

Note: If your SQL dialect doesn't support LAST_VALUE with this syntax, you can use MAX(Joined_version) OVER (PARTITION BY Name ORDER BY TO_DATE(Date, 'DD/MM/YYYY') ROWS UNBOUNDED PRECEDING) as an alternative (since we're taking the last non-null, which is the maximum in a sorted group where values don't decrease).

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