Upgrade from Eikon -> Workspace. Learn about programming differences.

For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
1 1 1 2

Formatting Questions in Codebook

I am trying to do the following with some sample code from the library:

  1. Customize the column headers (at the moment it shows ROUND(TR.EPSMean(Period=FQ1),2), and so on. I searched on Google and believe we use df.rename() but I was unable to incorporate this into the code below.

  2. Display the entire table of 504 rows instead of the current display of 10.
  3. Format revenue column to a) display in thousands, and b) zero decimals (i.e. 21446.61589 = 21,446). When I try wrapping ‘round’ around TR.RevenueMean does not work.
  4. How can I export the contents of my dataframe into an Excel spreadsheet?


It would be great if the below code can be amended with the correct syntax so I can copy it into Codebook and learn how to do this.

Thank you in advance.


import refinitiv.dataplatform.eikon as ek
ek.set_app_key('DEFAULT_CODE_BOOK_APP_KEY')
df, err = ek.get_data(
instruments = ['0#.SPX'],
fields = [
'TR.CommonName',
'TR.GICSSector',
'round(TR.EPSMean(Period=FQ1),2)',
'round(TR.EPSMean(Period=FQ2),2)',
'round(TR.EPSMean(Period=FQ3),2)',
'round(TR.EPSMean(Period=FQ4),2)',
'TR.RevenueMean(Period=FQ1)',
'TR.RevenueMean(Period=FQ2)',
'TR.RevenueMean(Period=FQ3)',
'TR.RevenueMean(Period=FQ4)',
],
parameters = {
                
'Scale': '6',
'Curn': 'USD'
}
)


display(df)
codebook
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hello @tajinder.dhillon

Thank you for your participation in the forum. Is the reply below satisfactory in resolving your query?


If so please can you click the 'Accept' text next to the appropriate reply? This will guide all community members who have a similar question.

Thanks,


AHS

@tajinder.dhillon

Hi,

Please be informed that a reply has been verified as correct in answering the question, and marked as such.

Thanks,

AHS

1 Answer

· Write an Answer
Upvote
Accepted
884 4 2 8

Hi @tajinder.dhillon,

Please find the code below to answer your question:

import refinitiv.dataplatform.eikon as ek
ek.set_app_key('DEFAULT_CODE_BOOK_APP_KEY')

#Part 2 of Question- Display the entire table
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

fields = [
'TR.CommonName',
'TR.GICSSector',
'round(TR.EPSMean(Period=FQ1),2)',
'round(TR.EPSMean(Period=FQ2),2)',
'round(TR.EPSMean(Period=FQ3),2)',
'round(TR.EPSMean(Period=FQ4),2)',
'TR.RevenueMean(Period=FQ1)',
'TR.RevenueMean(Period=FQ2)',
'TR.RevenueMean(Period=FQ3)',
'TR.RevenueMean(Period=FQ4)',
]

df, err = ek.get_data(
instruments = ['0#.SPX'],
fields = fields,
parameters = {
'Scale': '6',
'Curn': 'USD'
}
) 

# Part 1 of Question- Customize the column headers
df.columns = ['Instrument', 'Company Common Name', 'GICS Sector Name', 'TR.EPSMean(Period=FQ1)', 'TR.EPSMean(Period=FQ2)', 'TR.EPSMean(Period=FQ3)', 'TR.EPSMean(Period=FQ4)', 'Revenue - Mean FQ1', 'Revenue - Mean FQ2', 'Revenue - Mean FQ3', 'Revenue - Mean FQ4']

# Part 3 of Question- Format revenue column
df[['Revenue - Mean FQ1', 'Revenue - Mean FQ2', 'Revenue - Mean FQ3', 'Revenue - Mean FQ4']] = df[['Revenue - Mean FQ1', 'Revenue - Mean FQ2', 'Revenue - Mean FQ3', 'Revenue - Mean FQ4']].apply(lambda x: x.map('{:,.0f}'.format))

# Part 4 of Question- Export dataframe to an Excel spreadsheet
df.to_excel("filename.xlsx")


Explanation of the above code-

Part 1 of Question- Customize the column headers: To rename all the columns you can assign a new list of column names. The number of names in that list should be equal to the actual number of columns in dataframe.

There are other alternatives as well like you were trying to use df.rename. Check this link for a description.

Below is the sample code for how to use df.rename:

Option 1:

df.rename(columns=lambda x: {"ROUND(TR.EPSMean(Period=FQ1),2)": "TR.EPSMean(Period=FQ1)", "ROUND(TR.EPSMean(Period=FQ2),2)": "TR.EPSMean(Period=FQ2)", "ROUND(TR.EPSMean(Period=FQ3),2)": "TR.EPSMean(Period=FQ3)", "ROUND(TR.EPSMean(Period=FQ4),2)": "TR.EPSMean(Period=FQ4)"}.get(x, x)) 

Option 2:

df.rename({"ROUND(TR.EPSMean(Period=FQ1),2)": "TR.EPSMean(Period=FQ1)", "ROUND(TR.EPSMean(Period=FQ2),2)": "TR.EPSMean(Period=FQ2)", "ROUND(TR.EPSMean(Period=FQ3),2)": "TR.EPSMean(Period=FQ3)", "ROUND(TR.EPSMean(Period=FQ4),2)": "TR.EPSMean(Period=FQ4)"}, axis=1)

Part 2 of Question- Display the entire table: We need to modify the notebook settings to display all the rows in all datasets. You can set it to max rows by changing display.max_rows and similarly for maximum columns by changing display.max_columns

import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

Part 3 of Question- Format revenue column: We use the python string format syntax '{:,.0f}'.format to add the thousand comma separators to the numbers. Then we use python’s map() function to iterate and apply the formatting to all the rows in the ‘Revenue’ column.

Changing the syntax to '{:,.2f}'.format will give you numbers with two decimal places.

Part 4 of Question- Export dataframe to an Excel spreadsheet: We are using the to_excel() function with the file name to export the content of DataFrame here


Please let us know if this is helpful or if you have any further queries.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.