question

Upvotes
Accepted
84 3 4 7

Date format and column headings

Hi, I am try to replicate a formula in DFO/Excel

=DSGRID("CHCNYEZSQ,CHCNBQJHQ,CHCNXFSVQ"," ","-2Y","","Q","RowHeader=true;ColHeader=true;DispSeriesDescription=false;YearlyTSFormat=false;QuarterlyTSFormat=false","")

The dates shown are Q1 XXXX,Q2 XXXX, etc. and there are column headings.

When I use DSWS API, dates returned are yyyy-mm--dd and column headings are just the instruments.

Is there anything to add to the code in order to show the same dates and column headers as in DFO (Excel) ?

pythondatastream-apidsws-api
1589975621561.png (24.8 KiB)
1589975719124.png (28.4 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Upvotes
Accepted
45.2k 103 44 60

@Alan Kar Lun.Tam

From my checking, the DataStreamDSWS library can't retrieve the data type's full name. Therefore, it is unable to show the column headers as in DFO.

However, I found the PyDatastream library can do it. The code is:

from pydatastream import Datastream
import pandas as pd
from dateutil.relativedelta import relativedelta
import datetime

DS = Datastream(username="username", password="password")
itemList = ['CHCNYEZSQ','CHCNBQJHQ','CHCNXFSVQ']
requestList = []
startDate = datetime.datetime.now() - relativedelta(years=2)
for item in itemList:
    r = DS.construct_request(item, [''], date_from=startDate,freq='Q',return_names=True)
    requestList.append(r)
response = DS.request_many(requestList)
dfs = DS.parse_response(response)

dfList = []
for i in  range(len(dfs)):                
    df_temp = dfs[i].droplevel(level=0)
    df_temp.columns = [response['DataResponses'][i]['DataTypeNames'][0]['Value']]
    df_temp['Quarter'] = pd.PeriodIndex(df_temp.index, freq='Q')
    df_temp = df_temp.set_index('Quarter')
    dfList.append(df_temp)
    
result = pd.concat(dfList, axis=1)
result
    

The output is:


1590455889656.png (27.0 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.