CODEWITHSUNDEEP
pythonpandas
jf328
jf328

Reputation: 7331

pandas DataFrame, fetching value according to another DF

I have two DF which share the same indices

DF1 contains the column names in DF2

             id1   id2
2010-01-01    a     b
2010-01-02    a     b
2010-01-03    b     c
2010-01-04    c     d

DF2 contains the actual values

              a     b    c    d
2010-01-01    1    10   100   -1
2010-01-02    2    20   200   -2
2010-01-03    3    30   300   -3
2010-01-04    4    40   400   -4

The output I want has the same schema as DF1 but with values fetched from DF2 on the matching column name and index

DFo

             id1     id2
2010-01-01    1       10
2010-01-02    2       20
2010-01-03    30      300
2010-01-04    400     -4

Is there a quick way to do without loops to build DFo one value a time?

Thanks

Upvotes: 0

Views: 93

Answers (1)

chrisb
chrisb

Reputation: 52236

Try df.lookup() which takes a list of row/column indices and an array of the corresponding values.

In [53]: df1
Out[53]: 
           id1 id2
2010-01-01   a   b
2010-01-02   a   b
2010-01-03   b   c
2010-01-04   c   d

In [54]: df2
Out[54]: 
            a   b    c  d
2010-01-01  1  10  100 -1
2010-01-02  2  20  200 -2
2010-01-03  3  30  300 -3
2010-01-04  4  40  400 -4

In [55]: for c in df1.columns:
    ...:     df1[c] = df2.lookup(df1.index, df1[c])
    ...:     

In [56]: df1
Out[56]: 
            id1  id2
2010-01-01    1   10
2010-01-02    2   20
2010-01-03   30  300
2010-01-04  400   -4

Upvotes: 1

Related Questions