0

I have 3 tables whereby I need to have an end result of a combination of the currency values for different portfolios. Dataframe 1: A Reference table containing only the portfolios we care about and the thresholds

import pandas as pd
df1 = pd.DataFrame({'Portfolio': ['Port1', 'Port2', 'Port3'], 
                   'PosThresh': [0.0055, 0.0055, 0.0055],
                   'NegThresh': [-0.0055, -0.0055, -0.0055]})
Portfolio PosThresh NegThresh
Port1 0.0055 -0.0055
Port2 0.0055 -0.0055
Port3 0.0055 -0.0055

Dataframe 2: The currency values contained within the Groups (There will be more than 1 currency for each portfolio)

import pandas as pd
df1 = pd.DataFrame({'Portfolio': ['Port1', 'Port2', 'Port2', 'Port2', 
                   'Port3', 'Port3', 'Port4', 'Port4'], 
                   'Value': [0.1, 0.003, 0.2, 0.004, 0.2, 0.65, 0.23, 
                    0.004],
                   'Currency': ['EUR', 'CHF', 'CNY', 'USD', 'USD', 'CHF', 
                   'CNY', 'EUR']})
Portfolio Value Currency
Port1 0.1 EUR
Port1 0.05 CHF
Port1 0.1 CNY
Port2 -0.05 USD
Port2 -0.1 EUR
Port2 0.05 CHF
Port3 0.5 CNY
Port3 0.2 CHF
Port4 0.5 EUR
Port4 0.005 CNY
Port4 0.003 CHF

Dataframe 3: The main portfolio data where we need to report on any currencies outside of the threshold stored within the reference table.

import pandas as pd
df1 = pd.DataFrame({'Portfolio': ['Port1', 'Port2', 'Port3'], 
                   'Value': [0.05, -0.003, 0.004]})
Portfolio Value
Port1 0.05
Port2 -0.003
Port3 0.004

I need to complete the following:

  1. Filter df2 against df1 to find only the portfolios we care about
  2. Combine the CHF & CNY values for each portfolio to give a total value (All other currencies are left as they are)
  3. Flag currencies in df2 which fall outside of the Portfolio PosThresh & NegThresh columns from df1
  4. Flag df3 Values which fall outside the thresholds
  5. Report on each portfolio we checked to give an output

Expected Outcome:

Portfolio Value Outside Threshold? Currencies Outside Thresh
Port1 0.1 Yes EUR (0.1), CHF/CNY (0.2)
Port2 -0.05 Yes USD (-0.05)

I hope that all makes sense...

PandasBeginner
  • 1
  • 3
  • You should provide code to [reproduce all the DataFrames easily](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – mozway Jul 25 '23 at 10:16
  • @mozwayI have updated with code to recreate the tables. Thanks – PandasBeginner Jul 25 '23 at 11:55
  • Thanks, your expected output is however unclear, is it complete? Also how is portfolio 2 outside of threshold? You want a lot of steps but many of them are unclear. Please provide your own attempt – mozway Jul 25 '23 at 11:59

0 Answers0