CODEWITHSUNDEEP
pythonpandas
Charles
Charles

Reputation: 1

How to create a new dataframe by using values of another

Assuming I have 2 pandas dataframes (one mapping currencies for symbols over time and the other mapping values for currencies over time) how can I get to value per symbol over time?

sym = pd.DataFrame(data=[["USD","EUR"],["USD","CAD"]],columns=["FB", "SAP"])

and

fx = pd.DataFrame(data=[[1, 2, 3], [4, 5, 6]], columns = ["USD","EUR","CAD"])

to get 

adj = pd.DataFrame(data=[[1, 2], [4, 6]], columns = ["FB", "SAP"])?

Upvotes: 0

Views: 40

Answers (1)

BENY
BENY

Reputation: 323266

stack + lookup

dd=sym.stack().to_frame('value')
dd.loc[:,'value']=fx.lookup(dd.index.get_level_values(0),dd['value'])
dd
Out[973]: 
       value
0 FB       1
  SAP      2
1 FB       4
  SAP      6
dd.unstack()
Out[974]: 
  value    
     FB SAP
0     1   2
1     4   6

Upvotes: 2

Related Questions