question

Upvotes
Accepted
faiza.zafar avatar image
7 ●1 ●2 ●6

I am trying to Convert yy-mm-dd to Q1 , Q2 for each company ID.

Hi community,

I am working with institutional ownership data and I extracting quarterly historical investor data. I want to convert my dates for each company to quarterly format as is shown in the attached picture below.

My current data looks like this:

1688546030373.png

But I want it in the format as shown 1688546056901.png

I have had a look at @Alan Tam123 post https://community.developers.refinitiv.com/questions/60189/date-format-and-column-headings.html but the code seems to be different from how I am doing it.

Any help would be great! @Jirapongse look forward to your expert help.


My code is attached below:

df,e = ek.get_data(instruments=['AAPL.O','AAL.L'], fields=[
    'TR.CommonName',
    'TR.InvestorFullName("TheInvestorType":"113,108,107")',
    'TR.SharesHeld.calcdate',
    'TR.SharesHeld',
    'TR.PctOfSharesOutHeld',
    'SUM(TR.PctOfSharesOutHeld)',
    'TR.InvestorType',
    'TR.InvestorTypeId'],parameters={'SDate':'0','EDate':'-4', 'Period':'FQ0','Frq':'FQ'})
df
#EDate is the number of quaters 
df1= df
df1['id']=df1.groupby(['Instrument']).ngroup()
df1.set_index(['id','Calc Date'])


eikon-data-api#technology
1688546030373.png (37.3 KiB)
1688546056901.png (9.1 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.

raksina.samasiri avatar image   raksina.samasiri

Hi @faiza.zafar ,

Thank you for your participation in the forum. Is the reply below satisfactory in resolving your query?

If so please can you click the 'Accept' text next to the appropriate reply? This will guide all community members who have a similar question.

Thanks,

AHS

Jirapongse avatar image   Jirapongse

@faiza.zafar

Hi,

Please be informed that a reply has been verified as correct in answering the question, and marked as such.

Thanks,

AHS

3 Answers

· Write an Answer
Upvotes
Accepted
Jirapongse avatar image
79.2k ●251 ●52 ●74

@faiza.zafar

I am not a Python Dataframe expert so I used the for loop to check and change the values.

First, I added the Quarter and QuaterTemp columns to the data frame. 

df,e = ek.get_data(instruments=['AAPL.O','AAL.L'], fields=[
    'TR.CommonName',
    'TR.InvestorFullName("TheInvestorType":"113,108,107")',
    'TR.SharesHeld.calcdate',
    'TR.SharesHeld',
    'TR.PctOfSharesOutHeld',
    'SUM(TR.PctOfSharesOutHeld)',
    'TR.InvestorType',
    'TR.InvestorTypeId'],parameters={'SDate':'0','EDate':'-4', 'Period':'FQ0','Frq':'FQ'})
df['Quarter'] = pd.PeriodIndex(df['Calc Date'], freq='Q')
df['QuarterTemp'] = df['Quarter']
df

Then, I used a for loop to change the values in the Quarter column according to the following conditions. Next, I dropped the QuarterTemp column. 

for i in range(1, len(df)):
    if ((df.loc[i-1,'QuarterTemp'] == df.loc[i,'QuarterTemp']) and (df.loc[i-1,'Instrument'] == df.loc[i,'Instrument'])):
        df.loc[i, 'Quarter'] = ""
df = df.drop('QuarterTemp', axis=1)
df

1688610990381.png

Finally, I called the groupby and set_index methods.

df1= df
df1['id']=df1.groupby(['Instrument']).ngroup()
df1 = df1.set_index(['id','Quarter'])
df1

1688611815969.png



1688610990381.png (48.9 KiB)
1688611815969.png (46.7 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.

Upvotes
Jirapongse avatar image
79.2k ●251 ●52 ●74

@faiza.zafar

Thank you for reaching out to us.

Please try this code:

df,e = ek.get_data(instruments=['AAPL.O','AAL.L'], fields=[
    'TR.CommonName',
    'TR.InvestorFullName("TheInvestorType":"113,108,107")',
    'TR.SharesHeld.calcdate',
    'TR.SharesHeld',
    'TR.PctOfSharesOutHeld',
    'SUM(TR.PctOfSharesOutHeld)',
    'TR.InvestorType',
    'TR.InvestorTypeId'],parameters={'SDate':'0','EDate':'-4', 'Period':'FQ0','Frq':'FQ'})
df['Quarter'] = pd.PeriodIndex(df['Calc Date'], freq='Q')
df

1688549348743.png

Then, run this code.

df1= df
df1['id']=df1.groupby(['Instrument']).ngroup()
df1.set_index(['id','Quarter'])

1688549400169.png



1688549348743.png (83.4 KiB)
1688549400169.png (80.3 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.

Upvotes
faiza.zafar avatar image
7 ●1 ●2 ●6

Thank you ! It works fine. I want to know if its is possible that instead of the quarters repeating for each ID , it should just stay once per quarter. Please see attached. @Jirapongse

1688558325507.png


1688558325507.png (86.8 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.

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.