The data collected by the below code are not matched with browser's(data malposition)

y.jia10

SDate = '2021-6-10'
EDate = '2022-6-10'

df,das = ek.get_data('AAPL.O',

                                    ['TR.OfficerTitle()',

                                    'TR.ODOfficerFullName(ODRnk=R1:R100)',
                                    'TR.ODDirectorStartDate(ODRnk=R1:R100)',
                                    'TR.ODOfficerPersonGender(ODRnk=R1:R100)',

                                    'TR.ODOfficerPersonAge(ODRnk=R1:R100)',

                                    'TR.ODOfficerCompSalary(ODRnk=R1:R100,SDate={},Edate={})'.format(SDate,EDate),
                                    'TR.ODOfficerCompBonus(ODRnk=R1:R100,SDate={},Edate={})'.format(SDate,EDate),
                                    'TR.ODOfficerCompTAC(ODRnk=R1:R100,SDate={},Edate={})'.format(SDate,EDate),
                                    'TR.ODOfficerCompRSA(ODRnk=R1:R100,SDate={},Edate={})'.format(SDate,EDate),
                                    'TR.ODOfficerCompAOC(ODRnk=R1:R100,SDate={},Edate={})'.format(SDate,EDate),]



df

python response

first person in browser

raksina.samasiri

Hi @y.jia10 ,

Thank you for your patience. As checked, the data seems to come from different databases because I tried to get the same data with Eikon Excel and found that if the parameter RH=OfficerID is defined, the data will be arranged properly. Unfortunately, the RH parameter cannot be used with Eikon Data APIs (mentioned in the answer of this thread).

Hence I'd recommend to separate the call of each group of fields then then merge them together by OfficerID parameter, the output dataframe compared to the output in Eikon Excel are match.

please see an example code below

import pandas as pd

SDate = '2021-6-10'
EDate = '2022-6-10'
# define parameters to be used on every call
parameters = {'SDate': SDate, 'EDate': EDate, 'ODRnk': 'R1:R100'}
# retrieve 1st group of fields
name_df,name_err = ek.get_data('AAPL.O',['TR.ODOfficerFullName.OfficerID'
                                    ,'TR.ODOfficerFullName'
                                    ,'TR.ODDirectorStartDate'
                                    ,'TR.ODOfficerPersonGender'
                                    ,'TR.ODOfficerPersonAge'
                              ],
                     parameters = parameters
                    )
# retrieve 2nd group of fields
comp_df,comp_err = ek.get_data('AAPL.O',['TR.ODOfficerCompSalary.OfficerID'
                               ,'TR.ODOfficerCompSalary'
                               ,'TR.ODOfficerCompBonus'
                              ],
                     parameters = parameters
                    )
# retrieve 3rd group of fields
comp2_df,comp2_err = ek.get_data('AAPL.O',['TR.ODOfficerCompTAC.OfficerID'
                               ,'TR.ODOfficerCompTAC'
                               ,'TR.ODOfficerCompRSA'
                               ,'TR.ODOfficerCompAOC'
                              ],
                     parameters = parameters
                    )
# merge them together
df = pd.merge(name_df, comp_df,  how='outer', on=['Instrument','Officer PermID'])
df = pd.merge(df, comp2_df,  how='outer', on=['Instrument','Officer PermID'])
df

Below are the dataframes before they're merged together

Hope this helps and please let me know in case you have any further questions.

raksina.samasiri

hi @y.jia10 ,

Could you please attach the result from this code and the browser data that are mismatched for further investigation?

Thanks,
AHS

y.jia10

@raksina.samasiri thanks for your reply, i am confused about how to upload the image along with the comments. it no responds after clicking "Post"

y.jia10

According to the browser's data, the salary and bonus columns of the first person should be a null value. After checking, those values would belong to the second person(data malposition). Thanks in advance for your help.

raksina.samasiri

Note: Eikon Excel formula

=@TR("AAPL.O","TR.OfficerTitle;TR.ODOfficerFullName;TR.ODDirectorStartDate;TR.ODOfficerPersonGender;TR.ODOfficerPersonAge;TR.ODOfficerCompSalary;TR.ODOfficerCompBonus;TR.ODOfficerCompTAC;TR.ODOfficerCompRSA;TR.ODOfficerCompAOC","CH=Fd RH=OfficerID NULL=BLANK ODRnk=R1:R100 SDate=2021-06-10 EDate=2022-06-10")

y.jia10

@raksina.samasiri thanks for your reply. this is good idea.