Formatting Questions in Codebook

tajinder.dhillon

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)

Shadab Hussain

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.