I have a dataframe of 13F filings which shows the holdings of each institutional investor. I'm trying to pull a stock like AMC and then it'll show who all holders are, how many shares they have and that compared to what they have in the prior quarter. For example, below is a portion of my data:
indice NAME_OF_ISSUER TITLE_OF_CLASS CUSIP (x$1000) PRN_AMT PRN Period_of_Report date_reported cik_company row_loc
0 00165C1041029092021093085 AMC ENTMT HLDGS INC CL A COM 00165C104 1746681 45892822 SH 20210930 20211112 102909 85
1 00165C1041029092021093086 AMC ENTMT HLDGS INC CL A COM 00165C104 45 1193 SH 20210930 20211112 102909 86
2 00165C1041029092021093087 AMC ENTMT HLDGS INC CL A COM 00165C104 12786 335941 SH 20210930 20211112 102909 87
3 00165C1041029092021093088 AMC ENTMT HLDGS INC CL A COM 00165C104 776 20382 SH 20210930 20211112 102909 88
4 00165C1041029092021093089 AMC ENTMT HLDGS INC CL A COM 00165C104 10292 270417 SH 20210930 20211112 102909 89
5 00165C1041029092021093090 AMC ENTMT HLDGS INC CL A COM 00165C104 23299 612158 SH 20210930 20211112 102909 90
6 00165C1041029092021093091 AMC ENTMT HLDGS INC CL A COM 00165C104 2206 57973 SH 20210930 20211112 102909 91
7 00165C1041029092021063075 AMC ENTMT HLDGS INC CL A COM 00165C104 16681 294310 SH 20210630 20210813 102909 75
8 00165C1041029092021063076 AMC ENTMT HLDGS INC CL A COM 00165C104 5475 96603 SH 20210630 20210813 102909 76
9 00165C1041029092021063077 AMC ENTMT HLDGS INC CL A COM 00165C104 18999 335192 SH 20210630 20210813 102909 77
10 00165C1041029092021063078 AMC ENTMT HLDGS INC CL A COM 00165C104 2477392 43708393 SH 20210630 20210813 102909 78
11 00165C1041364742202109302539 AMC ENTMT HLDGS INC CL A COM 00165C104 27896 732946 SH 20210930 20211109 1364742 2539
12 00165C1041364742202109302540 AMC ENTMT HLDGS INC CL A COM 00165C104 6755 177479 SH 20210930 20211109 1364742 2540
13 00165C1041364742202109302541 AMC ENTMT HLDGS INC CL A COM 00165C104 2010 52799 SH 20210930 20211109 1364742 2541
The institutional holders are identified by the cik_company, the number of shares they ahve is in the PRN_AMT and CUSIP identifies the stock. The Period of Report is what quarter the report was filed.
I want the table to show the cik_numbers as the row values. The prior quarter holdings will be in the first column. The second column would be the most recent quarter holdings. I want to create a new column that shows the share difference between two quarters. I also want a grand total row that totals up the number of shares.
This is what I've come up with so far but I'm stock.
df2 = df.groupby(['cik_company','Period_of_Report'],as_index=False)['PRN_AMT'].sum().pivot('cik_company','Period_of_Report').fillna(0)
PRN_AMT
Period_of_Report 20210630 20210930
cik_company
102909 44434498 47190886
1037389 1812345 2507533
1364742 30482159 40015669
I want it to look like this
20210630 20210930
cik_company Difference
102909 44434498 47190886 +2,756,388
1037389 1812345 2507533 +695,188
1364742 30482159 40015669 +9,533,510
Grand Total 76,729,002 89,714,088 +12,985,086
Thank you for your help.
