Adinda Wrangling



In [1]:
import pandas as pd
from pandas import DataFrame as df
import matplotlib as mpl
import numpy as np
from math import log

In [2]:
path = 'Adinda.csv'
data = pd.read_csv(path)
data.head()


Out[2]:
Firm Year CEI BRIB_CORR BUSS_ETH FAIR_COMP POL_CONTR INDIG_PPL IND_EC_IMP 0TH_ENG ... BRD_EFFC BRD_INDP BRD_MEET BRD_SIZE BRD_COMPT DIVIDEND LOSS TOT_ASSETS SLACK ROE
0 1 2011 0.57143 0 0.50 0.50 0.25 0.25 1 1.0000 ... 2.50000 2.00000 2.50000 3 2.500 30.35000 0 5.144545e+13 5.081927e+12 0.2200
1 1 2012 0.54762 0 0.25 0.25 0.50 1.00 1 0.8333 ... 2.35294 1.66667 2.83333 3 2.500 55.82524 0 6.497336e+13 4.857942e+12 0.1410
2 1 2013 0.52381 0 0.50 0.25 0.25 1.00 1 0.6667 ... 2.23529 1.83333 2.33333 3 2.500 15.36585 0 8.212195e+13 8.308451e+12 0.0740
3 1 2014 0.52381 0 0.50 0.25 0.25 1.00 1 0.6667 ... 2.17647 1.50000 2.50000 3 2.500 29.37500 0 8.017500e+13 9.315600e+12 0.0560
4 2 2011 0.48810 0 0.50 0.75 0.00 1.00 1 0.1667 ... 2.26458 1.83333 2.00000 3 2.225 0.00000 0 2.301384e+12 3.591630e+11 0.0569

5 rows × 21 columns


In [3]:
data['BRD_INDP_DIV'] = data['BRD_INDP'] * data['DIVIDEND']
data['BRD_MEET_DIV'] = data['BRD_MEET'] * data['DIVIDEND']
data['BRD_SIZE_DIV'] = data['BRD_SIZE'] * data['DIVIDEND']
data['BRD_COMPT_DIV'] = data['BRD_COMPT'] * data['DIVIDEND']
data['BRD_EFFC_DIV'] = data['BRD_EFFC'] * data['DIVIDEND']

In [4]:
data.head()


Out[4]:
Firm Year CEI BRIB_CORR BUSS_ETH FAIR_COMP POL_CONTR INDIG_PPL IND_EC_IMP 0TH_ENG ... DIVIDEND LOSS TOT_ASSETS SLACK ROE BRD_INDP_DIV BRD_MEET_DIV BRD_SIZE_DIV BRD_COMPT_DIV BRD_EFFC_DIV
0 1 2011 0.57143 0 0.50 0.50 0.25 0.25 1 1.0000 ... 30.35000 0 5.144545e+13 5.081927e+12 0.2200 60.700000 75.875000 91.05000 75.875000 75.875000
1 1 2012 0.54762 0 0.25 0.25 0.50 1.00 1 0.8333 ... 55.82524 0 6.497336e+13 4.857942e+12 0.1410 93.042253 158.171327 167.47572 139.563100 131.353440
2 1 2013 0.52381 0 0.50 0.25 0.25 1.00 1 0.6667 ... 15.36585 0 8.212195e+13 8.308451e+12 0.0740 28.170674 35.853599 46.09755 38.414625 34.347131
3 1 2014 0.52381 0 0.50 0.25 0.25 1.00 1 0.6667 ... 29.37500 0 8.017500e+13 9.315600e+12 0.0560 44.062500 73.437500 88.12500 73.437500 63.933806
4 2 2011 0.48810 0 0.50 0.75 0.00 1.00 1 0.1667 ... 0.00000 0 2.301384e+12 3.591630e+11 0.0569 0.000000 0.000000 0.00000 0.000000 0.000000

5 rows × 26 columns


In [5]:
data.to_csv('adinda.clean.csv', sep=',', encoding='utf-8')

In [6]:
pd.read_csv('adinda.clean.csv')


Out[6]:
Unnamed: 0 Firm Year CEI BRIB_CORR BUSS_ETH FAIR_COMP POL_CONTR INDIG_PPL IND_EC_IMP ... DIVIDEND LOSS TOT_ASSETS SLACK ROE BRD_INDP_DIV BRD_MEET_DIV BRD_SIZE_DIV BRD_COMPT_DIV BRD_EFFC_DIV
0 0 1 2011 0.57143 0.0 0.50 0.50 0.25 0.2500 1.0 ... 30.35000 0 5.144545e+13 5.081927e+12 0.22000 60.700000 75.875000 91.05000 75.875000 75.875000
1 1 1 2012 0.54762 0.0 0.25 0.25 0.50 1.0000 1.0 ... 55.82524 0 6.497336e+13 4.857942e+12 0.14100 93.042253 158.171327 167.47572 139.563100 131.353440
2 2 1 2013 0.52381 0.0 0.50 0.25 0.25 1.0000 1.0 ... 15.36585 0 8.212195e+13 8.308451e+12 0.07400 28.170674 35.853599 46.09755 38.414625 34.347131
3 3 1 2014 0.52381 0.0 0.50 0.25 0.25 1.0000 1.0 ... 29.37500 0 8.017500e+13 9.315600e+12 0.05600 44.062500 73.437500 88.12500 73.437500 63.933806
4 4 2 2011 0.48810 0.0 0.50 0.75 0.00 1.0000 1.0 ... 0.00000 0 2.301384e+12 3.591630e+11 0.05690 0.000000 0.000000 0.00000 0.000000 0.000000
5 5 2 2012 0.73810 0.0 1.00 1.00 1.00 0.6670 1.0 ... 0.00000 1 2.903932e+12 1.526311e+11 -0.07730 0.000000 0.000000 0.00000 0.000000 0.000000
6 6 2 2013 0.45238 0.0 1.00 0.00 0.00 0.6667 1.0 ... 0.00000 1 3.855817e+12 2.247561e+10 -0.07990 0.000000 0.000000 0.00000 0.000000 0.000000
7 7 2 2014 0.78571 0.0 1.00 1.00 1.00 1.0000 1.0 ... 0.00000 1 4.239362e+12 5.275000e+10 -0.22930 0.000000 0.000000 0.00000 0.000000 0.000000
8 8 3 2011 0.00000 0.0 0.00 0.00 0.00 0.0000 0.0 ... 0.00000 1 1.116601e+11 3.469378e+09 -0.64310 0.000000 0.000000 0.00000 0.000000 0.000000
9 9 3 2012 0.19444 0.0 0.00 0.00 0.00 1.0000 0.5 ... 0.00000 1 1.508296e+08 2.807016e+06 -0.38200 0.000000 0.000000 0.00000 0.000000 0.000000
10 10 3 2013 0.19048 0.0 0.00 0.00 0.00 0.6667 0.5 ... 0.00000 0 1.489340e+12 3.015988e+07 0.01200 0.000000 0.000000 0.00000 0.000000 0.000000
11 11 3 2014 0.48810 0.0 0.25 0.00 0.50 1.0000 1.0 ... 0.00000 1 1.508296e+11 3.185917e+09 -0.36100 0.000000 0.000000 0.00000 0.000000 0.000000
12 12 4 2011 0.40476 0.0 0.00 0.00 0.00 1.0000 1.0 ... 3.55000 0 1.871818e+13 4.729745e+12 0.30730 9.466679 7.691679 3.55000 8.875000 7.395821
13 13 4 2012 0.48611 0.0 1.00 0.25 0.00 1.0000 1.0 ... 7.76699 1 2.085437e+13 3.534437e+12 -0.74090 18.122951 15.533980 23.30097 23.300970 18.732116
14 14 4 2013 0.72143 0.8 1.00 0.25 0.00 1.0000 1.0 ... 0.00000 1 2.440271e+13 4.977427e+12 -2.03000 0.000000 0.000000 0.00000 0.000000 0.000000
15 15 4 2014 0.80476 0.8 1.00 1.00 0.00 1.0000 1.0 ... 0.00000 1 2.216250e+13 4.243275e+12 -2.02000 0.000000 0.000000 0.00000 0.000000 0.000000
16 16 5 2011 0.77619 0.6 1.00 0.00 1.00 1.0000 1.0 ... 0.00000 0 1.740441e+13 1.124181e+12 0.04726 0.000000 0.000000 0.00000 0.000000 0.000000
17 17 5 2012 0.67500 0.8 0.50 0.25 1.00 1.0000 1.0 ... 0.00000 1 1.929230e+13 1.131750e+12 -0.04000 0.000000 0.000000 0.00000 0.000000 0.000000
18 18 5 2013 0.78810 0.6 0.75 0.75 0.75 1.0000 1.0 ... 0.00000 1 2.423301e+13 1.421650e+12 0.04000 0.000000 0.000000 0.00000 0.000000 0.000000
19 19 5 2014 0.68095 0.6 0.75 0.00 0.75 1.0000 1.0 ... 0.00000 1 2.329414e+13 7.845501e+10 -0.12500 0.000000 0.000000 0.00000 0.000000 0.000000
20 20 6 2011 0.64286 0.0 1.00 1.00 0.00 1.0000 1.0 ... 80.00000 0 1.438620e+13 1.527995e+12 0.28500 160.000000 186.666400 240.00000 200.000000 196.666400
21 21 6 2012 0.64286 0.0 1.00 1.00 0.00 1.0000 1.0 ... 200.00000 0 3.611168e+26 4.023288e+26 0.07800 400.000000 300.000000 600.00000 500.000000 400.000000
22 22 6 2013 0.58095 0.4 0.75 0.00 0.75 0.6667 1.0 ... 200.00000 0 2.328177e+13 1.863041e+12 0.07800 366.666000 300.000000 600.00000 500.000000 388.236000
23 23 6 2014 0.52381 0.0 0.50 0.50 0.00 1.0000 1.0 ... 0.00000 1 1.452070e+13 1.000979e+12 -0.74000 0.000000 0.000000 0.00000 0.000000 0.000000
24 24 7 2011 0.54048 0.2 0.25 0.25 0.25 1.0000 1.0 ... 0.00000 1 3.692054e+12 3.377904e+11 -0.08000 0.000000 0.000000 0.00000 0.000000 0.000000
25 25 7 2012 0.48889 0.6 0.50 0.50 0.00 0.6670 1.0 ... 0.00000 1 4.266755e+12 2.292203e+11 -0.14000 0.000000 0.000000 0.00000 0.000000 0.000000
26 26 7 2013 0.56190 0.6 0.50 0.50 0.00 0.6667 1.0 ... 0.00000 1 5.359461e+12 2.879231e+11 0.14000 0.000000 0.000000 0.00000 0.000000 0.000000
27 27 7 2014 0.87619 0.8 1.00 1.00 0.50 1.0000 1.0 ... 0.00000 0 4.448238e+12 1.699568e+11 0.00000 0.000000 0.000000 0.00000 0.000000 0.000000
28 28 8 2011 0.45238 0.0 0.25 0.25 0.00 1.0000 1.0 ... 0.00000 1 1.082000e+13 1.932000e+12 -0.16021 0.000000 0.000000 0.00000 0.000000 0.000000
29 29 8 2012 0.45238 0.0 0.25 0.25 0.00 1.0000 1.0 ... 0.00000 1 1.126214e+13 5.533981e+11 0.16000 0.000000 0.000000 0.00000 0.000000 0.000000
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
114 114 29 2013 0.70952 0.8 0.50 0.00 1.00 1.0000 1.0 ... 56.29000 0 7.883294e+12 6.136980e+11 0.13000 140.725000 140.725000 168.87000 140.725000 142.380489
115 115 29 2014 0.70952 0.8 0.50 0.00 1.00 1.0000 1.0 ... 0.00000 0 9.752477e+12 3.644960e+11 0.13000 0.000000 0.000000 0.00000 0.000000 0.000000
116 116 30 2011 0.28571 0.0 0.50 0.00 0.00 0.3333 1.0 ... 0.00000 0 2.182515e+11 1.042113e+10 0.01000 0.000000 0.000000 0.00000 0.000000 0.000000
117 117 30 2012 0.36111 0.0 0.25 0.25 0.50 1.0000 1.0 ... 0.00000 0 1.760010e+11 1.419955e+10 0.03500 0.000000 0.000000 0.00000 0.000000 0.000000
118 118 30 2013 0.50000 0.0 0.75 0.00 0.75 0.6667 1.0 ... 0.00000 0 3.269600e+11 1.304858e+10 0.00610 0.000000 0.000000 0.00000 0.000000 0.000000
119 119 30 2014 0.46429 0.0 0.50 0.00 0.75 0.6667 1.0 ... 0.00000 0 3.660530e+11 1.031394e+10 0.01150 0.000000 0.000000 0.00000 0.000000 0.000000
120 120 31 2011 0.42857 0.0 0.00 0.50 0.50 1.0000 1.0 ... 0.00000 0 1.485407e+11 3.327728e+10 0.43750 0.000000 0.000000 0.00000 0.000000 0.000000
121 121 31 2012 0.19444 0.0 0.00 0.00 0.00 1.0000 1.0 ... 0.00000 0 1.485410e+11 3.327728e+10 0.23300 0.000000 0.000000 0.00000 0.000000 0.000000
122 122 31 2013 0.21429 0.0 0.00 0.00 0.00 0.3333 1.0 ... 2.00000 0 1.569930e+11 5.000691e+10 0.19720 5.000000 5.000000 6.00000 5.000000 5.058820
123 123 31 2014 0.40476 0.0 0.50 0.00 0.00 1.0000 1.0 ... 0.00000 0 3.626790e+11 4.758827e+10 0.02780 0.000000 0.000000 0.00000 0.000000 0.000000
124 124 32 2011 0.21429 0.0 0.00 0.00 0.00 0.3333 1.0 ... 0.00000 0 1.710689e+12 2.985594e+10 0.11000 0.000000 0.000000 0.00000 0.000000 0.000000
125 125 32 2012 0.08333 0.0 0.00 0.00 0.00 0.3333 1.0 ... 0.00000 0 1.503500e+12 2.722860e+10 0.09000 0.000000 0.000000 0.00000 0.000000 0.000000
126 126 32 2013 0.21429 0.0 0.00 0.00 0.00 0.3333 1.0 ... 0.00000 0 5.516122e+12 1.529416e+09 0.02600 0.000000 0.000000 0.00000 0.000000 0.000000
127 127 32 2014 0.21429 0.0 0.00 0.00 0.00 0.3333 1.0 ... 0.00000 1 5.512453e+12 2.253194e+11 -0.03100 0.000000 0.000000 0.00000 0.000000 0.000000
128 128 33 2011 0.65476 0.0 0.75 0.75 0.75 0.6667 1.0 ... 190.90909 0 2.238391e+12 5.512677e+11 0.34000 286.363635 381.818180 572.72727 477.272725 429.545453
129 129 33 2012 0.43056 0.0 0.50 0.50 0.25 0.6667 1.0 ... 436.89320 0 2.606194e+12 2.766204e+11 0.24000 728.156790 946.603390 1310.67960 1092.233000 925.186885
130 130 33 2013 0.69762 0.8 0.25 0.00 1.00 1.0000 1.0 ... 457.31707 0 3.343305e+12 5.081530e+11 0.25000 685.975605 1067.071639 1371.95121 1143.292675 968.437493
131 131 33 2014 0.69762 0.8 0.25 0.00 1.00 1.0000 1.0 ... 468.50000 0 3.248688e+12 4.962125e+11 0.17000 702.750000 1093.165105 1405.50000 1171.250000 992.119025
132 132 34 2011 0.74048 0.6 0.75 0.75 0.75 1.0000 1.0 ... 74.00000 0 1.825382e+13 3.433645e+12 0.14000 123.333580 172.666420 222.00000 185.000000 175.750000
133 133 34 2012 0.62222 0.4 0.50 0.50 0.50 1.0000 1.0 ... 60.00000 0 2.278897e+13 3.401705e+12 0.03000 109.999800 139.999800 180.00000 150.000000 134.117400
134 134 34 2013 0.67619 0.4 0.50 0.50 0.50 1.0000 1.0 ... 44.47561 1 2.824785e+13 3.982530e+12 -0.00070 81.538470 111.189025 133.42683 111.189025 102.032386
135 135 34 2014 0.67619 0.4 0.50 0.50 0.50 1.0000 1.0 ... 0.00000 1 2.862940e+13 4.158715e+12 -0.03150 0.000000 0.000000 0.00000 0.000000 0.000000
136 136 35 2011 0.78095 0.8 1.00 1.00 1.00 0.0000 1.0 ... 10.00000 1 9.212775e+11 8.507606e+10 -0.04340 18.333300 21.666700 30.00000 25.000000 23.750000
137 137 35 2012 0.74444 0.8 1.00 1.00 1.00 0.0000 1.0 ... 0.00000 1 1.073942e+12 7.732039e+10 -0.15160 0.000000 0.000000 0.00000 0.000000 0.000000
138 138 35 2013 0.90000 0.8 1.00 1.00 1.00 1.0000 1.0 ... 0.00000 0 1.517317e+12 1.033780e+11 0.00650 0.000000 0.000000 0.00000 0.000000 0.000000
139 139 35 2014 0.90000 0.8 1.00 1.00 1.00 1.0000 1.0 ... 0.00000 1 1.552375e+11 6.432500e+10 -0.20020 0.000000 0.000000 0.00000 0.000000 0.000000
140 140 36 2011 0.00000 0.0 0.00 0.00 0.00 0.0000 0.0 ... 0.00000 0 5.442820e+11 2.375282e+10 0.24850 0.000000 0.000000 0.00000 0.000000 0.000000
141 141 36 2012 0.00000 0.0 0.00 0.00 0.00 0.0000 0.0 ... 0.00000 0 3.890070e+11 5.578254e+10 0.19980 0.000000 0.000000 0.00000 0.000000 0.000000
142 142 36 2013 0.09524 0.0 0.00 0.00 0.00 0.6667 0.0 ... 50.00000 0 7.658814e+11 7.229814e+10 0.03970 90.000000 91.666500 50.00000 112.500000 93.750000
143 143 36 2014 0.09524 0.0 0.00 0.00 0.00 0.6667 0.0 ... 8.00000 0 8.972817e+11 4.202218e+10 0.17920 14.400000 14.666640 8.00000 18.000000 15.000000

144 rows × 27 columns


In [ ]: