Datastream Point in Time Data Manipulation

ken03 · April 2023

Hi as the Point in Time data returns a multi-index dataframe, i am having trouble accessing the Dates Column. i intend to set the dates column as an index and to concat on the basis of the dates with another dataframe. Can you let me know how i should go about doing this

Jirapongse · April 2023

@ken03

Thank you for sharing the code.

I ran the code and found that Dates is already an index.

You may refer to this article on Medium regarding How to flatten MultiIndex Columns and Rows in Pandas.

I can run the following code to concatenate two data frames by Dates.

df1 = ds.get_data(tickers='@AAPL, @FB, @GOOGL, @MSFT, @NXPI';, 
            fields=['SOCOO01V','ENPIDP048','SOHRDP012','SOEQ','SOTDDP018','SODODP0012','ENRRDP033','ENERDP052','CGVSDP030'],
            start='-5Y', freq='Y')
df2 = ds.get_data(tickers='U:JPM, U:XOM, U:BAC, U:BABA, U:V', 
            fields=['SOCOO01V','ENPIDP048','SOHRDP012','SOEQ','SOTDDP018','SODODP0012','ENRRDP033','ENERDP052','CGVSDP030'],
            start='-5Y', freq='Y')
pd.concat([df1, df2], axis=1)

The output is:

Jirapongse · April 2023

@ken03

Please share the code that you are using to retrieve the data.

ken03 · April 2023

ds.get_data(tickers='@AAPL, @FB, @GOOGL, @MSFT, @NXPI, U:JPM, U:XOM, U:BAC, U:BABA, U:V', fields=['SOCOO01V','ENPIDP048','SOHRDP012','SOEQ','SOTDDP018','SODODP0012','ENRRDP033','ENERDP052','CGVSDP030'], start='-5Y', freq='Y')

if you print df.dtypes() you will see that "Dates" is not one of the column

ken03 · April 2023

https://community.developers.refinitiv.com/discussion/comment/104381#Comment_104381

@Jirapongse

ds.get_data(tickers='@AAPL, @FB, @GOOGL, @MSFT, @NXPI, U:JPM, U:XOM, U:BAC, U:BABA, U:V', fields=['SOCOO01V','ENPIDP048','SOHRDP012','SOEQ','SOTDDP018','SODODP0012','ENRRDP033','ENERDP052','CGVSDP030'], start='-5Y', freq='Y')

if you print df.dtypes() you will see that "Dates" is not one of the column

ken03 · April 2023

https://community.developers.refinitiv.com/discussion/comment/104382#Comment_104382

@Jirapongse

Hi Jirapongse, try an economic data item from datastream navigator.

df = ds.get_data (tickers='USCONPRCE',

start='2007-05-10', end='2023-04-21', freq='D')

df

when you try the df.types() later u will only get, i need to be able to access the Dates column to make it an index

Instrument  Field  Currency
USCONPRCE          U$          float64
dtype: object 

MultiIndex([('USCONPRCE', '', 'U$')],
           names=['Instrument', 'Field', 'Currency'])

Jirapongse · April 2023

https://community.developers.refinitiv.com/discussion/comment/104384#Comment_104384

How about calling the reset_index() method?

ken03 · April 2023

https://community.developers.refinitiv.com/discussion/comment/104375#Comment_104375

@Jirapongse -- thank you very much it works!

ken03 · April 2023

After doing the above, the dates have been set as the index as such i am able to concat both dataframes. the problem now is that the column with the values does not have a column headers, because the instrument is a multindex format hence it is not directly above the values of the column that it is supposed to relate to. how should i go about solving this issue.

Jirapongse · April 2023

https://community.developers.refinitiv.com/discussion/comment/104401#Comment_104401

@ken03

You may need to flatten MultiIndex Columns, as mentioned in this article.

Datastream Point in Time Data Manipulation

Best Answer

Answers

Categories

EXPLORE OUR SITES

Datastream Point in Time Data Manipulation

Best Answer

Answers

Categories