How Can I Match The Two Dataframe And Get The Following Result?

I have the following dataframe: payment is the line of payment name with value payment = pd.DataFrame({'Payment':['payment1','payment2','payment3'],'Value':[100,200,300]}) Payment

Solution 1:

I am not sure that I understand the question properly, but if you want to merge both the data frames then you would need to have atleast one column to match i.e., "Value" or "Cost". So if you have the below input

payment = pd.DataFrame({'Payment':["payment1","payment2","payment3"],'Cost':[100,200,300]})
project_cost = pd.DataFrame({'Project':["Project1","Project2","Project3"],'Cost':[200,300,400]})

Then you can merge the two data frames by using merge in pandas

result = pd.merge(payment, project_cost, on= 'Cost')

Let me know if I understood the problem correctly.

Edit:

    Payment Cost    Project
0   payment1    200 Project1
1   payment2    200 Project1
2   payment3    200 Project1

Is this what you are looking for?

Solution 2:

The following seems to work for the sample data. You should check it a little more. The idea is that you compute the accumulation (cumsum) of the costs and the payments. Then you can sort them increasingly and try to propagate the Payment and Project accordingly.

project_cost['accum'] = project_cost['Cost'].cumsum()
payment['accum'] = payment['Value'].cumsum()

(payment.merge(project_cost, on='accum', how='outer')
    .sort_values('accum')
    .bfill()
)

Output:

    Payment  Value  accum   Project   Cost
0  payment1  100.0100  Project1  200.03  payment2  200.0200  Project1  200.01  payment2  200.0300  Project2  300.04  payment3  300.0500  Project2  300.02  payment3  300.0600  Project3  400.05NaNNaN900  Project3  400.0