Home subtracting the 'INT' column from the 'LMP' column, but only from the indexed row per unique 'ID'

Questions

subtracting the 'INT' column from the 'LMP' column, but only from the indexed row per unique 'ID'

May 25, 2023

I like to create a new column named ‘sub’ in the DataFrame and calculate its values by subtracting the ‘INT’ column from the ‘LMP’ column, but only subtracting from the latest row per unique ‘ID’ where the ‘FM’ column is set to’time0′, I calculated FM as the follows but I dont know how to implement the sub column.

data = {
    'ID': [0, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2],
    'VIS': [0.0, 0.0, 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0],
    'STA': [float('NaN'), 4.0, 7.0, 7.0, 7.0, 7.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0],
    'LMP': [float('NaN'), -35.0, 411.0, 773.0, 1143.0, 1506.0, float('NaN'), float('NaN'), float('NaN'), float('NaN'), float('NaN'), float('NaN')],
    'INT': [0.0, 0.0, 413.0, 777.0, 1171.0, 1509.0, 1967.0, 2310.0, 2627.0, 2970.0, 3357.0, 3768.0],
    'FM': [-1, -1, "time0", -1, -1, "time0", -1, -1, -1, -1, -1,-1]

}

sorted_data = pd.DataFrame(data)

sorted_data['FM'] = np.nan
for id in sorted_data['ID'].unique():
    filter_condition = (sorted_data['ID'] == id) & (~sorted_data['LMP'].isnull())
    if filter_condition.any():
        last_row_index = sorted_data.loc[filter_condition].index[-1]
        sorted_data.loc[last_row_index, 'FM'] = 'time0'

sorted_data['FM'] = sorted_data['FM'].fillna(-1)

Expected output should be calculated as this :

'sub': [float('NaN'), 0-411.0,413-411, 777-1509.0 , 1171.0-1509.0 ,1509-1509, 1967.0-1509, 2310.0-1509,2627.0- 1509, 2970.0-1509, 3357.0-1509,3768.0-1509]

>Solution :

Here is sample code that subtracts the INT column from the LMP column at time0

data = {
    "ID": [0, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2],
    "VIS": [0.0, 0.0, 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0],
    "STA": [float("NaN"), 4.0, 7.0, 7.0, 7.0, 7.0, 2.0, 2.0, 2.0, 2.0, 2.0, 2.0],
    "LMP": [
        float("NaN"),
        -35.0,
        411.0,
        773.0,
        1143.0,
        1506.0,
        float("NaN"),
        float("NaN"),
        float("NaN"),
        float("NaN"),
        float("NaN"),
        float("NaN"),
    ],
    "INT": [
        0.0,
        0.0,
        413.0,
        777.0,
        1171.0,
        1509.0,
        1967.0,
        2310.0,
        2627.0,
        2970.0,
        3357.0,
        3768.0,
    ],
    "FM": [-1, -1, "time0", -1, -1, "time0", -1, -1, -1, -1, -1, -1],
}

sorted_data = pd.DataFrame(data)

lmp_at_time0 = (
    sorted_data.groupby(["ID"])
    .apply(lambda grp: grp[grp["FM"] == "time0"]["LMP"])
    .reset_index()
    .drop(columns=["level_1"])
)
lmp_at_time0.columns = ["ID", "LMP_at_time0"]
sorted_data = sorted_data.merge(lmp_at_time0, on="ID", how="left")
sorted_data["INT"] - sorted_data["LMP_at_time0"]