CODEWITHSUNDEEP
pythonpandas
pablo144
pablo144

Reputation: 117

Joining python tables

I need to join two tables together:

E.g:

Table 1 

index'      (GBP, USD)  (USD, RUB)   etc...
2019-07-01      1.1         62          ...
2019-07-02      1.2         63          ...
2019-07-03      1.3         64          ...

Table 2

date          pair  
2019-07-01  GBP, USD    
2019-07-01  USD, RUB    
2019-07-02  GBP, USD    
2019-07-02  USD, RUB

The aim is to add some sort of join which create a new column in table 2 showing the rate in table 1.

Final

date          pair     Rate
2019-07-01  GBP, USD    1.1
2019-07-01  USD, RUB    62 
2019-07-02  GBP, USD    1.2
2019-07-02  USD, RUB    63

I tried a few variations of pd.merge but could get what I was looking for. Maybe need to do something different?

Upvotes: 1

Views: 78

Answers (1)

piRSquared
piRSquared

Reputation: 294218

Cleanup

This requires some clean up first. tbl1 needs to be in long format and we need to ensure that those columns are tuples rather than strings that look like tuples.

fx = tbl1.stack().rename_axis(['date', 'pair']).reset_index(name='FX')
fx['pair'] = fx['pair'].str[1:-1].str.split(', ').map(tuple)

fx

        date        pair    FX
0 2019-07-01  (GBP, USD)   1.1
1 2019-07-01  (USD, RUB)  62.0
2 2019-07-02  (GBP, USD)   1.2
3 2019-07-02  (USD, RUB)  63.0
4 2019-07-03  (GBP, USD)   1.3
5 2019-07-03  (USD, RUB)  64.0

Or leave it alone and fix columns

tbl1.columns = tbl1.columns.str[1:-1].str.split(', ').map(tuple)

tbl1

            GBP USD
            USD RUB
index              
2019-07-01  1.1  62
2019-07-02  1.2  63
2019-07-03  1.3  64

tbl2 need to ensure that pair column are tuples rather than strings that look like tuples.

tbl2 = tbl2.assign(pair=tbl2.pair.str.split(', ').map(tuple))

tbl2

                  pair
date                  
2019-07-01  (GBP, USD)
2019-07-01  (USD, RUB)
2019-07-02  (GBP, USD)
2019-07-02  (USD, RUB)

lookup

Using the fixed up tbl1

tbl2.assign(FX=tbl1.lookup(tbl2.index, tbl2.pair))

                  pair    FX
date                        
2019-07-01  (GBP, USD)   1.1
2019-07-01  (USD, RUB)  62.0
2019-07-02  (GBP, USD)   1.2
2019-07-02  (USD, RUB)  63.0

pd.merge_asof

Using the long format fx

 pd.merge_asof(tbl2, fx, on='date', by='pair')

         pair       date    FX
0  (GBP, USD) 2019-07-01   1.1
1  (USD, RUB) 2019-07-01  62.0
2  (GBP, USD) 2019-07-02   1.2
3  (USD, RUB) 2019-07-02  63.0

Upvotes: 2

Related Questions