Numpy - Map data to a matrix based on index location

Question

First, I am only assuming Numpy is the best way to do this, but I'm open to other better suited packages (pandas?). I am looking to map the following data into a matrix format (to eventually display to users).

Input Data:

1) {StartDate: datetime.date(2023, 6, 30), EndDate: datetime.date(2025, 6, 30), XCord = 3.5, YVal = 65}
2) {StartDate: datetime.date(2022, 6, 30), EndDate: datetime.date(2023, 6, 30), XCord = 0.5, YVal = 50}
3) {StartDate: datetime.date(2022, 6, 30), EndDate: datetime.date(2023, 9, 30), XCord = 5.5, YVal = 100}

With the following requirements

YVal in input data will be mapped as the value for all StartDate <= Y <= EndDate
Linearly interpolate on any values that fall between index values (both on X-Axis and Y-Axis)
For the Y-Axis (date) interpolation, I interpolate based on the difference of the dates. Ie, assume you have some date_low < date_target < date_high. I bucket the data based on how many days there are between date_low - date_high, and date_target to date_low. For example, if date_target fell exactly between two Y-Axis dates, then 50% of the value would be displayed in date_high row.

Matrix X-Axis values:

0 1 2.25 3.5 4 5 6

Matrix Y-Values:

[datetime.date(2022, 9, 30)]
[datetime.date(2022, 12, 30)]
[datetime.date(2023, 6, 30)]
[datetime.date(2023, 12, 30)]
[datetime.date(2024, 6, 30)]
[datetime.date(2024, 12, 30)]
[datetime.date(2025, 6, 30)]

For each of the respective input data examples above they would show:

Input 1:

Date	0	1	2.25	3.5	4	5	6
datetime.date(2022, 9, 30)	0	0	0	0	0	0	0
datetime.date(2022, 12, 30)	0	0	0	0	0	0	0
datetime.date(2023, 6, 30)	0	0	0	65	0	0	0
datetime.date(2023, 12, 30)	0	0	0	65	0	0	0
datetime.date(2024, 6, 30)	0	0	0	65	0	0	0
datetime.date(2024, 12, 30)	0	0	0	65	0	0	0
datetime.date(2025, 6, 30)	0	0	0	65	0	0	0

Input 2:

Date	0	1
datetime.date(2022, 9, 30)	25	25
datetime.date(2022, 12, 30)	25	25
datetime.date(2023, 6, 30)	25	25
datetime.date(2023, 12, 30)	0	0
datetime.date(2024, 6, 30)	0	0
datetime.date(2024, 12, 30)	0	0
datetime.date(2025, 6, 30)	0	0

Input 3:

Date	5	6
datetime.date(2022, 9, 30)	50	50
datetime.date(2022, 12, 30)	50	50
datetime.date(2023, 6, 30)	50	50
datetime.date(2023, 12, 30)	25	25
datetime.date(2024, 6, 30)	0	0
datetime.date(2024, 12, 30)	0	0
datetime.date(2025, 6, 30)	0	0

I'm looking to add these all together in one matrix as the final output.

Thanks so much!

user_15219428 · Answer

You can do this using Xarray.

import datetime
import xarray as xr


input_1 = {"StartDate": datetime.date(2023, 6, 30), "EndDate": datetime.date(2025, 6, 30), "XCord": 3.5, "YVal": 65}
input_2 = {"StartDate": datetime.date(2022, 6, 30), "EndDate": datetime.date(2023, 6, 30), "XCord": 0.5, "YVal": 50}
input_3 = {"StartDate": datetime.date(2022, 6, 30), "EndDate": datetime.date(2023, 9, 30), "XCord": 5.5, "YVal": 100}
input_4 = {"StartDate": datetime.date(2023, 2, 15), "EndDate": datetime.date(2025, 2, 15), "XCord": 4.25, "YVal": 100}

Matrix_X_axis_values = [0, 1, 2.25, 3.5, 4, 5, 6]

Matrix_Y_axis_values = [
    datetime.date(2022, 9, 30),
    datetime.date(2022, 12, 30),
    datetime.date(2023, 6, 30),
    datetime.date(2023, 12, 30),
    datetime.date(2024, 6, 30),
    datetime.date(2024, 12, 30),
    datetime.date(2025, 6, 30),
]


def get_neighbour_values(arr, value):
    left_elements = [element for element in arr if element <= value]
    left_neighbour = left_elements[-1] if left_elements else None

    right_elements = [element for element in arr if element > value]
    right_neighbour = right_elements[0] if right_elements else None
    return left_neighbour, right_neighbour


for input_selected in [input_1, input_2, input_3, input_4]:
    foo = xr.DataArray(0, coords=[Matrix_Y_axis_values, Matrix_X_axis_values], dims=["X", "Y"])

    Startdate = input_selected["StartDate"]
    Enddate = input_selected["EndDate"]
    XCord = input_selected["XCord"]
    YVal = input_selected["YVal"]

    interp_startdate_from, interp_startdate_to = get_neighbour_values(Matrix_Y_axis_values, Startdate)
    interp_enddate_from, interp_enddate_to = get_neighbour_values(Matrix_Y_axis_values, Enddate)

    if XCord in Matrix_X_axis_values:
        foo.loc[interp_startdate_to:interp_enddate_to, XCord] = YVal
    else:
        left_XCord, right_XCord = get_neighbour_values(Matrix_X_axis_values, XCord)

        # linear interpolation
        XCord_range = right_XCord - left_XCord
        left_YVal = YVal * (right_XCord - XCord) / XCord_range
        right_YVal = YVal * (XCord - left_XCord) / XCord_range

        foo.loc[interp_startdate_to:interp_enddate_to, left_XCord] = left_YVal
        foo.loc[interp_startdate_to:interp_enddate_to, right_XCord] = right_YVal

    if interp_startdate_to == interp_enddate_to:
        # startdate and Enddate in same bin
        weight = (Enddate - Startdate).days / (interp_startdate_to - interp_startdate_from).days
        foo.loc[interp_startdate_to, :] = foo.loc[interp_startdate_to, :] * weight
    else:
        # Startdate and Enddate in seperate bins
        if interp_startdate_to and interp_startdate_from:
            # date-interpolation for start
            weight = (interp_startdate_to - Startdate).days / (interp_startdate_to - interp_startdate_from).days
            foo.loc[interp_startdate_to, :] = foo.loc[interp_startdate_to, :] * weight

        if interp_enddate_to and interp_enddate_from:
            # date-interpolation for end
            weight = (Enddate - interp_enddate_from).days / (interp_enddate_to - interp_enddate_from).days
            foo.loc[interp_enddate_to, :] = foo.loc[interp_enddate_to, :] * weight

    print(foo)

The first example yields a different result compared to your examples. But I'd argue, that it is computed the right way here. I did consider the dates in the table to be the upper bound of the bins.

I did add a fourth example-case to showcase the simultaneous interpolation for X and Y.

Numpy - Map data to a matrix based on index location

1 Answers