CODEWITHSUNDEEP
pythonpandasdataframe
Dan
Dan

Reputation: 131

If two columns satisfy a condition return calculated value from other columns. Pandas / Windows

Refer Image

Refer yellow highlighted cells:

If K = LDE then look for FDE in column J (above LDE's row), in Result column return (D from LDE minus A from FDE) (ie 223-307 = -84)

Refer green highlighted cells: 152-385 = -233 and so on.

How to solve ?

Data:

['03-01-2011', 523, 698, 284, 33, 416, 675, 300, 690, 314, '', '', 'FDM', ''] ['27-01-2011', 353, 1, 50, 547, 514, 957, 804, 490, 108, '', 'LDE', '', ''] ['28-01-2011', 307, 837, 656, 755, 792, 568, 119, 439, 943, 'FDE', '', '', ''] ['31-01-2011', 327, 409, 155, 358, 120, 401, 385, 965, 888, '', '', '', 'LDM'] ['01-02-2011', 686, 313, 714, 12, 140, 112, 589, 908, 605, '', '', 'FDM', ''] ['24-02-2011', 161, 846, 816, 223, 387, 566, 435, 567, 36, '', 'LDE', '', ''] ['25-02-2011', 889, 652, 190, 324, 947, 778, 575, 604, 314, 'FDE', '', '', ''] ['28-02-2011', 704, 33, 232, 630, 344, 796, 331, 409, 597, '', '', '', 'LDM'] ['01-03-2011', 592, 148, 974, 540, 848, 393, 505, 699, 315, '', '', 'FDM', ''] ['31-03-2011', 938, 768, 325, 756, 971, 644, 546, 238, 376, '', 'LDE', '', 'LDM'] ['01-04-2011', 385, 298, 654, 655, 2, 112, 960, 306, 477, 'FDE', '', 'FDM', ''] ['28-04-2011', 704, 516, 785, 152, 355, 348, 106, 611, 426, '', 'LDE', '', ''] ['29-04-2011', 753, 719, 776, 826, 756, 370, 660, 536, 903, 'FDE', '', '', 'LDM'] ['02-05-2011', 222, 28, 102, 363, 952, 860, 48, 976, 478, '', '', 'FDM', ''] ['26-05-2011', 361, 588, 866, 884, 809, 662, 801, 843, 668, '', 'LDE', '', '']

Upvotes: 0

Views: 218

Answers (2)

Rob Raymond
Rob Raymond

Reputation: 31146

  • ugly but works...
  • find rows that contain FDE in column J
  • for each of the rows, find other row you want
  • finally do calc and return it
df = pd.DataFrame([['03-01-2011', 523, 698, 284, 33, 416, 675, 300, 690, 314, '', '', 'FDM', ''], 
['27-01-2011', 353, 1, 50, 547, 514, 957, 804, 490, 108, '', 'LDE', '', ''] ,
['28-01-2011', 307, 837, 656, 755, 792, 568, 119, 439, 943, 'FDE', '', '', ''], 
['31-01-2011', 327, 409, 155, 358, 120, 401, 385, 965, 888, '', '', '', 'LDM'] ,
['01-02-2011', 686, 313, 714, 12, 140, 112, 589, 908, 605, '', '', 'FDM', ''] ,
['24-02-2011', 161, 846, 816, 223, 387, 566, 435, 567, 36, '', 'LDE', '', ''] ,
['25-02-2011', 889, 652, 190, 324, 947, 778, 575, 604, 314, 'FDE', '', '', ''] ,
['28-02-2011', 704, 33, 232, 630, 344, 796, 331, 409, 597, '', '', '', 'LDM'] ,
['01-03-2011', 592, 148, 974, 540, 848, 393, 505, 699, 315, '', '', 'FDM', ''] ,
['31-03-2011', 938, 768, 325, 756, 971, 644, 546, 238, 376, '', 'LDE', '', 'LDM'], 
['01-04-2011', 385, 298, 654, 655, 2, 112, 960, 306, 477, 'FDE', '', 'FDM', ''] ,
['28-04-2011', 704, 516, 785, 152, 355, 348, 106, 611, 426, '', 'LDE', '', ''] ,
['29-04-2011', 753, 719, 776, 826, 756, 370, 660, 536, 903, 'FDE', '', '', 'LDM'], 
['02-05-2011', 222, 28, 102, 363, 952, 860, 48, 976, 478, '', '', 'FDM', ''] ,
['26-05-2011', 361, 588, 866, 884, 809, 662, 801, 843, 668, '', 'LDE', '', '']], columns=["Date"]+list("ABCDEFGHIJKLM"))

def findandcalc(lde):
    # find last row from begining of DF, to place LDE was found that contains "FDE"
    fde = df.iloc[0:lde.name].loc[lambda d: d["J"].eq("FDE")].tail(1)
    # if row was found do calc and return it
    return np.nan if len(fde)==0 else lde["D"] - fde["A"].values[0]

df.loc[df["K"].eq("LDE"), "Result"] = df.loc[df["K"].eq("LDE")].apply(findandcalc, axis=1)

df

vectorised solution

  • much cleaner....
  • filter to rows that have LDE or FDE in required columns
  • test that FDE is in previous row, then perform simple calc
  • join this series back to dataframe for final result
rs = df.loc[df["K"].eq("LDE") | df["J"].eq("FDE")].assign(
    Result=lambda d: np.where(
        d["K"].eq("LDE") & d["J"].shift().eq("FDE"), d["D"] - d["A"].shift(), np.nan
    )
)["Result"]

df.join(rs)
Date A B C D E F G H I J K L M Result
03-01-2011 523 698 284 33 416 675 300 690 314 FDM nan
27-01-2011 353 1 50 547 514 957 804 490 108 LDE nan
28-01-2011 307 837 656 755 792 568 119 439 943 FDE nan
31-01-2011 327 409 155 358 120 401 385 965 888 LDM nan
01-02-2011 686 313 714 12 140 112 589 908 605 FDM nan
24-02-2011 161 846 816 223 387 566 435 567 36 LDE -84
25-02-2011 889 652 190 324 947 778 575 604 314 FDE nan
28-02-2011 704 33 232 630 344 796 331 409 597 LDM nan
01-03-2011 592 148 974 540 848 393 505 699 315 FDM nan
31-03-2011 938 768 325 756 971 644 546 238 376 LDE LDM -133
01-04-2011 385 298 654 655 2 112 960 306 477 FDE FDM nan
28-04-2011 704 516 785 152 355 348 106 611 426 LDE -233
29-04-2011 753 719 776 826 756 370 660 536 903 FDE LDM nan
02-05-2011 222 28 102 363 952 860 48 976 478 FDM nan
26-05-2011 361 588 866 884 809 662 801 843 668 LDE 131

Upvotes: 1

Ric S
Ric S

Reputation: 9247

I found a quite tricky solution that works.

import pandas as pd

# define groups between two LDE
df['Group'] = (df['K'] == 'LDE').cumsum().shift(1, fill_value=0)

# custom function to perform your subtraction
def f(x):
    if x.loc[x['J'] == 'FDE', 'A'].size == 0:
        return None
    else:
        return x.loc[x['K'] == 'LDE', 'D'].iloc[0] - x.loc[x['J'] == 'FDE', 'A'].iloc[0]

# get list of numerical results
results = df.groupby('Group').apply(f).tolist()

# input the list into the specified LDE rows
df.loc[df['K'] == 'LDE', 'Results'] = results

Results

enter image description here

Starting data

df = pd.DataFrame([['03-01-2011', 523, 698, 284, 33, 416, 675, 300, 690, 314, '', '', 'FDM', ''], ['27-01-2011', 353, 1, 50, 547, 514, 957, 804, 490, 108, '', 'LDE', '', ''],
                   ['28-01-2011', 307, 837, 656, 755, 792, 568, 119, 439, 943, 'FDE', '', '', ''], ['31-01-2011', 327, 409, 155, 358, 120, 401, 385, 965, 888, '', '', '', 'LDM'], ['01-02-2011', 686, 313, 714, 12, 140, 112, 589, 908, 605, '', '', 'FDM', ''], ['24-02-2011', 161, 846, 816, 223, 387, 566, 435, 567, 36, '', 'LDE', '', ''], ['25-02-2011', 889, 652, 190, 324, 947, 778, 575, 604, 314, 'FDE', '', '', ''], ['28-02-2011', 704, 33, 232, 630, 344, 796, 331, 409, 597, '', '', '', 'LDM'], ['01-03-2011', 592, 148, 974, 540, 848, 393, 505, 699, 315, '', '', 'FDM', ''], ['31-03-2011', 938, 768, 325, 756, 971, 644, 546, 238, 376, '', 'LDE', '', 'LDM'], ['01-04-2011', 385, 298, 654, 655, 2, 112, 960, 306, 477, 'FDE', '', 'FDM', ''], ['28-04-2011', 704, 516, 785, 152, 355, 348, 106, 611, 426, '', 'LDE', '', ''], ['29-04-2011', 753, 719, 776, 826, 756, 370, 660, 536, 903, 'FDE', '', '', 'LDM'], ['02-05-2011', 222, 28, 102, 363, 952, 860, 48, 976, 478, '', '', 'FDM', ''], ['26-05-2011', 361, 588, 866, 884, 809, 662, 801, 843, 668, '', 'LDE', '', '']],
                  columns=['Date'] + list(map(chr, range(65, 78))))

Upvotes: 1

Related Questions