Remove Unwanted Columns After Fetching data DSGET

Aleniles

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

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