0

I have dataset of trade records with Type, Currency 1, Currency 2, and Days columns. I would like to filter the records that should be excluded based on the following criteria:

If the Type is equal to ‘FO’, then the below logic should be applied:

  • If the Currency 1 is equal to ‘EUR’, ‘USD’, ‘JPY’, ‘AUD’, or ‘MXN’, the Currency 2 is equal to ‘EUR’, ‘USD’, ‘JPY’, ‘AUD’, or ‘MXN’, and the Days is less than or equal to 2, the trade should be excluded.
  • If the Currency 1 or the Currency 2 is not equal to ‘EUR’, ‘USD’, ‘JPY’, ‘AUD’, or ‘MXN’, and the Day is less than or equal to the settlement period for the currency pair or seven business days (whichever is earlier), then the trade should be excluded.

I also have a dictionary of [SETTLEMENT] value for each currency. The way to obtain the settlement period is the following:

  • Where the Currency 1 or the Currency 2 is equal to ‘USD’, the settlement period should be equal to the minimum value of [SETTLEMENT].
  • Where the Currency 1 or the Currency 2 are both not equal to ‘USD’, the settlement period should be equal to the maximum value of [SETTLEMENT].

The dictionary would look like such: settlement_dict = {'AED':2, 'PHP' = 1, 'AUD' = 2, ...}

The logics are so embedded with one another and I have no clue how to use pandas to apply the logics to filter out the excluded trades. I would really appreciate your help on this. Thank you.

Skipper Lin
  • 149
  • 8
  • You could use numpy.where() to create conditional columns in your dataframe. Here is an example: https://stackoverflow.com/questions/19913659/pandas-conditional-creation-of-a-series-dataframe-column – DSteman May 24 '21 at 20:47
  • @DSteman thank you so much for the direction and conditional column make sense. One quick question: how would we be able to lookup the dictionary and compare the lookup results when writing the conditions for conditional column? – Skipper Lin May 24 '21 at 21:00
  • You can add an extra column where you translate the currency to a number based on that dictionary. Then use that column in your conditions as well. – DSteman May 24 '21 at 21:03

0 Answers0