Python left join with 3 variables and 3 columns-CodePudding

I've two dataframes (points and dates) to left join or do a vlookup, but I've 3 different variables to join in each row.

The dataframe:

idx = [(0,1,2),(0,5,7)]
data = {'pointx': [11, 35],
    'pricey': [1119, 943.7],}
points = pd.DataFrame(data, index = idx)
print(points)

initial_data = {'points': [4, 11, 16, 23, 31, 35, 39, 46],
    'Date': ['16/12/2021', '28/12/2021', '4/1/2022', '13/1/2022', '26/1/2022', '1/2/2022', '7/2/2022', '16/2/2022'],
    'High': [994.97998, 1119, 1208, 1115.599976, 987.690002, 943.700012, 947.77002, 926.429993],}
Dates = pd.DataFrame(initial_data)
print(Dates)

I need to get a result like this:

Where the "points" dataframe creates 3 new columns with dates according to the index numbers (0,1,2) are dates in the Dates dataframe.

I've found several solutions when you have a single column & variable, but having this 3 numbers together is hard for me and also are part of the index and not a column, any insight on how to solve a problem like this?

result = pd.merge(points, Dates, on="idx")

CodePudding user response：

You can try apply on rows

points[['Date1', 'Date2', 'Date3']] = (points.apply(lambda row: Dates.loc[list(row.name), 'Date'].tolist(), axis=1, result_type='expand'))
points = points.reset_index(drop=True)

print(points)

   pointx  pricey       Date1       Date2      Date3
0      11  1119.0  16/12/2021  28/12/2021   4/1/2022
1      35   943.7  16/12/2021    1/2/2022  16/2/2022