Remove Unwanted Columns After Fetching data DSGET
Hello, once we fetch data from DSWS we generally get this ouput:
# Set Start and End Dates with the following format "yyyy-mm-dd"
StartDate = "2023-03-01" #Set start date
EndDate = "2023-05-31" #Set end date
# Set The Frequency Of The Data Equal to D,W or M
Freq = 'M'
# Initializations
sp500_data = pd.DataFrame()
ism_data = pd.DataFrame()
# Fetch daily data and add 1 period return
sp500_data = ds.get_data(tickers='S&PCOMP', fields=['PI'],start=StartDate, end=EndDate, freq=Freq)
The sp500_data dataframe will looks like this:
Instrument S&PCOMP
Field PI
Currency U$
Dates
2023-03-01 3951.39
2023-04-01 4124.51
2023-05-01 4167.87
Now, how to convert that output into a better dataframe similar to the following:
Dates S&PCOMP
3/1/2023 3951.39
4/1/2023 4124.51
5/1/2023 4167.87
I am not able to find a proper solution, looks like it originally fetches data using MultiIndex that always create a mess when you have to manage data into dataframes. BE aware that the ticker is near the "Dates" not ABOVE so a reset_index will not solve here (Dates is my "index").
Best Answer
-
Hi @Aleniles ,
You might be interested in 'Date format and column headings' or try this (I did it with two fields to make it more generic/globally useful):sp500_data = ds.get_data(
tickers='S&PCOMP',
fields=['PI', 'X'],
start="2023-03-01",
end="2023-05-31",
freq='M')
sp500_data.columns = [', '.join(sp500_data.columns[i])
for i in range(len(sp500_data.columns))]
sp500_data0
Answers
-
Hi Jonathan, thanks for getting back to me.
Unfortunately this doesn't solve.As you can see in the attached image, the output still doesn't put the header in one line only (see red blank cells) Annotation 2023-06-05 101601.jpg
You can, of course, achieve this by resetting the index but I would like to have all in one header without resetting (keeping the "dates" as the index).
I mean, the name of the fields and the date must be on the same line (1 row of header) without re-indexing.0 -
Hi @Aleniles,
The Datastream API Owners, here at LSEG Refinitiv, chose to output their Python Library's data using Pandas dataframes because it has become the norm in the Python community. Pandas seperates the data in its dataframes and the label for each row (named 'index'). Pandas also allows you to name its index; here it was named 'Dates'.
0 -
As can be seen with the Python command `sp500_data.columns`, you will see that 'Dates' does not show up (the output is `Index(['S&PCOMP, PI, U$', 'S&PCOMP, X, U$'], dtype='object')`). The Python command `sp500_data.index` shows you the index (`Index(['2023-03-01', '2023-04-01', '2023-05-01'], dtype='object', name='Dates')` in this instance).
If you would like to remove the index name, you can do so with the Python command `sp500_data.index.name = None`. I would not advise it, but it's up to you. This, however, does not "put" the 'Dates' name in the columns' name, since Dates, here, is not in the dataframe, it's the index.
You can have the date in the dataframe if you want, with code like this:
sp500_data = ds.get_data(
tickers='S&PCOMP',
fields=['PI', 'X'],
start="2023-03-01",
end="2023-05-31",
freq='M')
sp500_data.columns = [', '.join(sp500_data.columns[i])
for i in range(len(sp500_data.columns))]
sp500_data = sp500_data.reset_index()0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛