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").

Find more posts tagged with

python

data

datastream

datastream-api

#technology

Accepted answers

jonathan.legrand

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_data

All comments

jonathan.legrand

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_data

Aleniles

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.

EXPLORE OUR SITES