How do i map a column in a dataframe using a nested dictionary?

Question

I have created this nested dictionary for certain trading strategies, say:

{'Strategy1' :{'AAPL':'Strategy1_APPLE', 'MSFT':'Strategy1_MICROSOFT'}, 'Strategy2' :{'AAPL': 'Strategy2_APPLE', 'MSFT':'Strategy2_MICROSOFT'}}

I already built a dataframe with a list of trades that belong to either strategy1 or strategy2 and have made a column with that information. What i am then trying to do is map the values in the dict to a new column based on both the top level key (i.e. strategy1 or strategy2) and the nested key(i.e. AAPL or MSFT). So that every single trade is accompanied by its correct strategy and ticker in the same row of the dataframe.

So a row in the dataframe with Strategy1 and AAPL gets a value of Strategy1_APPLE in the new column, but Strategy2 and AAPL gets Strategy2_APPLE.

I have been playing around with both map() and a bunch of lambda functions but i can't get this to work. I think this is possible without doing a bunch of ugly loops and if statements and i could really use some help on how to do that here.

edit: example

So it currently looks like this

  ticker Strategies
1   AAPL  Strategy1
2   MSFT  Strategy1
3   MSFT  Strategy2
4   AAPL  Strategy1
5   MSFT  Strategy2

and the desired result is this

  ticker Strategies          substrategy
1   AAPL  Strategy1      Strategy1_APPLE
2   MSFT  Strategy1  Strategy1_MICROSOFT
3   MSFT  Strategy2  Strategy2_MICROSOFT
4   AAPL  Strategy1      Strategy1_APPLE
5   MSFT  Strategy2  Strategy2_MICROSOFT

Note that concatenating the strings or something like that doesn't work for my actual problem where the substrategy names are a lot more complicated

Answer 1

use a method:

def get_substrategy(t,s):    
    v = mydict[s][t] 
    return v

and than apply a lambda:

df['substrategy'] = df.apply(lambda x: get_substrategy(x['ticker'], x['Strategies']), axis=1)

desired result

   ticker   Strategies  substrategy
1   AAPL    Strategy1   Strategy1_APPLE
2   MSFT    Strategy1   Strategy1_MICROSOFT
3   MSFT    Strategy2   Strategy2_MICROSOFT
4   AAPL    Strategy1   Strategy1_APPLE
5   MSFT    Strategy2   Strategy2_MICROSOFT

Note: make sure to try catch in your function in the event the strategy/ticker doesn't exist

Note2: without the function:

df['substrategy'] = df.apply(lambda x: mydict[x['Strategies']][x['ticker']], axis=1)

but you'll need to consider a scenario where the strategy/ticker doesn't exist.

Answer 2

I will use reindex + MultiIndex, d is your dict here

df['New']=pd.DataFrame(d).stack().reindex(pd.MultiIndex.from_frame(df)).values
df
  ticker Strategies                  New
1   AAPL  Strategy1      Strategy1_APPLE
2   MSFT  Strategy1  Strategy1_MICROSOFT
3   MSFT  Strategy2  Strategy2_MICROSOFT
4   AAPL  Strategy1      Strategy1_APPLE
5   MSFT  Strategy2  Strategy2_MICROSOFT