question

Upvotes
Accepted
19 2 1 6

Datastream Point in Time Data Manipulation

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

#technologydatastream-api
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.

@ken03

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

@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


Upvotes
Accepted
81.1k 264 53 76

@ken03

Thank you for sharing the code.

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

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:

1682069285912.png




1682069016795.png (17.6 KiB)
1682069285912.png (60.2 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.

@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']) 

How about calling the reset_index() method?

1682070773805.png


1682070773805.png (8.3 KiB)
@Jirapongse -- thank you very much it works!
Upvotes
19 2 1 6

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


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.

Upvotes
19 2 1 6

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.

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.

@ken03

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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