Grouping data by company ID, investor id and quarterly date.

faiza.zafar

Hi Community,

I am trying to calculate institutional investor volatility. I have already downloaded quarterly data of each firm and their quarterly holding as well.

I am stuck in trying to :

Point1. Group data by company ID, investor id and quarterly date and then download that specific Dataframe.

Once I have them grouped as shown in the picture

Point2. I want to calculate standard deviation of the quarterly holdings of each institutional investor of a firm over the 6 quarters.

Any help would be greatly appreciated for either points ! Really stuck on this for a while now. I have the code written but happy to have it tweaked.

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':'-6', 'Period':'FQ0','Frq':'FQ'})
df
#EDate is the number of quaters 
#Assume there are two companies

df['Quarter'] = pd.PeriodIndex(df['Calc Date'], freq='Q')
df

gkk = df.groupby(['Investor Full Name', 'Quarter'])
gkk.first()

@Jirapongse Look forward to your help.

Jirapongse

@faiza.zafar

Please the format and actual results that you would like to get.