Formatting Questions in Codebook

I am trying to do the following with some sample code from the library:
- 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.
- Display the entire table of 504 rows instead of the current display of 10.
- 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.
- 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)
Best Answer
-
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’smap()
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.
0
Categories
- All Categories
- 3 Polls
- 6 AHS
- 36 Alpha
- 167 App Studio
- 6 Block Chain
- 4 Bot Platform
- 18 Connected Risk APIs
- 47 Data Fusion
- 34 Data Model Discovery
- 688 Datastream
- 1.4K DSS
- 624 Eikon COM
- 5.2K Eikon Data APIs
- 11 Electronic Trading
- 1 Generic FIX
- 7 Local Bank Node API
- 3 Trading API
- 2.9K Elektron
- 1.4K EMA
- 255 ETA
- 557 WebSocket API
- 38 FX Venues
- 14 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 23 Messenger Bot
- 3 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 60 Open Calais
- 276 Open PermID
- 44 Entity Search
- 2 Org ID
- 1 PAM
- PAM - Logging
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 22 RDMS
- 1.9K Refinitiv Data Platform
- 692 Refinitiv Data Platform Libraries
- 4 LSEG Due Diligence
- LSEG Due Diligence Portal API
- 4 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.2K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 12 World-Check Customer Risk Screener
- 1K World-Check One
- 46 World-Check One Zero Footprint
- 45 Side by Side Integration API
- 2 Test Space
- 3 Thomson One Smart
- 10 TR Knowledge Graph
- 151 Transactions
- 143 REDI API
- 1.8K TREP APIs
- 4 CAT
- 27 DACS Station
- 121 Open DACS
- 1.1K RFA
- 105 UPA
- 194 TREP Infrastructure
- 229 TRKD
- 918 TRTH
- 5 Velocity Analytics
- 10 Wealth Management Web Services
- 91 Workspace SDK
- 11 Element Framework
- 5 Grid
- 18 World-Check Data File
- 1 Yield Book Analytics
- 48 中文论坛