Is there a way to get value from adjacent column of data frame?

Question

I have a list consisting of symbols like ['T.TO', 'VCB.TO']

I have a dataframe like this:

  1 RIC     Expected Return
  2 T.TO    2
  3 A.TO    1.1
  4 VCB.TO  0.004
  5 ASN.TO  3
  6 00G.H   1.1

and so one with different RICS. Not all values in RIC column is in symbols.

What I want to do is iterate over symbols and extract corresponding return from the dataframe.

Something like this:

for i in symbols:
     if i in df.RIC:
         list_returns.append(df.Expected Return.index(i))

I know this is really not the correct syntax to deal with dataframes. I could convert it to a dictionary and then handle it using for loops but is there a way to handle it directly using dataframes?

Expected Output: list_returns = [2, 1.1]

The actual dataframe and symbols list are much larger in size.

You want to add returns of your dataframe to the corresponding RIC in your list, did I understand you correct? — Erfan, Jun 19 '19 at 19:21
A sample of your RICS dataframe, and a sample of your expected output would help — G. Anderson, Jun 19 '19 at 19:23
Convert your symbols list to a dataframe: `symb = pd.DataFrame({'RIC':symbols})` Then do a simple merge: `symb.merge(df, on='RIC')` — Erfan, Jun 19 '19 at 19:26

score 1 · Answer 1 · answered Jun 19 '19 at 19:30

1

Convert your symbols list to a dataframe, the simply merge:

symb = pd.DataFrame({'RIC':symbols})

symb.merge(df, on='RIC')

      RIC  Expected Return
0    T.TO            2.000
1  VCB.TO            0.004

For more info about merging, look here

answered Jun 19 '19 at 19:30

Erfan

31,924
5
41
51

score 1 · Accepted Answer · answered Jun 19 '19 at 19:42

1

Using isin, loc, and tolist would give you the list:

df.loc[df.RIC.isin(symbols), 'Expected_Return'].tolist()

Out[933]: [2.0, 0.004]

answered Jun 19 '19 at 19:42

Andy L.

23,082
3
11
23

Is there a way to get value from adjacent column of data frame?

2 Answers2