In [188]:
%matplotlib inline 
import matplotlib.pyplot as plt 
import pandas as pd
import numpy as np

import statsmodels.api as sm
from statsmodels.sandbox.regression.predstd import wls_prediction_std

import plotly.plotly as py
import plotly.graph_objs as go
from plotly.tools import FigureFactory as FF
py.sign_in('erikrood','3eqsrype8v')
In [192]:
PE_data = pandas.read_csv('/Users/erikrood/desktop/Sector_Margins_10_15_16.csv') 
In [193]:
PE_data.drop([0,1,2])
Out[193]:
Symbol Company Name Security Type Share price P_E EPS P_E_calc Security Price EBITD Margin (TTM) Market Capitalization Market_cap_clean Sector/Industry Industry Revenue (TTM, Thousands) count Revenue_clean Profit to mkt cap
3 EQIX Equinix Inc CSRT 360.25 403.10 0.89 404.78 351.2500 0.413642 $25.1B $25,100,000 Real Estate Equity Real Estate Investment Trusts (REITs) 3161.7770 1.0 $3,161,777.00 0.000052
4 NFLX Netflix Inc CS 101.47 315.17 0.32 317.09 104.8650 0.584791 $45B $45,000,000 Consumer Discretionary Internet & Direct Marketing Retail 7624.6280 1.0 $7,624,628.00 0.000099
5 ALXN Alexion Pharmaceuticals Inc CS 118.34 299.70 0.39 303.44 125.1300 0.362509 $27.6B $27,600,000 Health Care Biotechnology 2821.6580 1.0 $2,821,658.00 0.000037
6 INCY Incyte Corp CS 87.10 256.82 0.34 256.18 97.9700 0.207029 $17.8B $17,800,000 Health Care Biotechnology 941.2430 1.0 $941,243.00 0.000011
7 CRM salesforce.com Inc CS 74.27 239.30 0.31 239.58 70.9800 0.095010 $48.8B $48,800,000 Information Technology Software 7474.5860 1.0 $7,474,586.00 0.000015
8 NEM Newmont Mining Corp CS 34.23 218.48 0.16 213.94 34.0300 0.368733 $18.1B $18,100,000 Materials Metals & Mining 7919.0000 1.0 $7,919,000.00 0.000161
9 AMZN Amazon.com Inc CS 822.96 205.21 4.01 205.23 839.6500 0.092310 $398.9B $398,900,000 Consumer Discretionary Internet & Direct Marketing Retail 120637.0000 1.0 $120,637,000.00 0.000028
10 IMO Imperial Oil Ltd CS 32.41 121.46 0.27 120.04 32.1700 0.083035 $27.6B $27,600,000 Energy Oil, Gas & Consumable Fuels 17468.7855 1.0 $17,468,785.50 0.000053
11 ZBH Zimmer Biomet Holdings Inc CS 127.15 114.33 1.11 114.55 131.1600 0.393838 $26.1B $26,100,000 Health Care Health Care Equipment & Supplies 7533.8000 1.0 $7,533,800.00 0.000114
12 SE Spectra Energy Corp CS 42.55 99.84 0.43 98.95 41.5600 0.500806 $29.1B $29,100,000 Energy Oil, Gas & Consumable Fuels 4962.0000 1.0 $4,962,000.00 0.000085
13 PTR Petrochina Co Ltd DR 69.51 94.43 0.74 93.93 70.9500 0.146563 $132.4B $132,400,000 Energy Oil, Gas & Consumable Fuels 236014.3840 1.0 $236,014,384.00 0.000261
14 CCI Crown Castle International Corp CSRT 88.30 87.91 1.00 88.30 88.3800 0.545067 $30B $30,000,000 Real Estate Equity Real Estate Investment Trusts (REITs) 3760.7350 1.0 $3,760,735.00 0.000068
15 NWL Newell Brands Inc CS 52.25 80.54 0.65 80.38 51.9999 0.174086 $25.2B $25,200,000 Consumer Discretionary Household Durables 8264.3000 1.0 $8,264,300.00 0.000057
16 EXPE Expedia Inc CS 120.80 79.79 1.51 80.00 117.8300 0.136744 $17.7B $17,700,000 Consumer Discretionary Internet & Direct Marketing Retail 7736.1500 1.0 $7,736,150.00 0.000060
17 PHG Koninklijke Philips NV DR 28.84 70.52 0.41 70.34 29.3500 0.093628 $27.6B $27,600,000 Industrials Industrial Conglomerates 26260.2500 1.0 $26,260,250.00 0.000089
18 AMT American Tower Corp CSRT 108.51 67.20 1.61 67.40 108.5800 0.601310 $46.1B $46,100,000 Real Estate Equity Real Estate Investment Trusts (REITs) 5249.2250 1.0 $5,249,225.00 0.000068
19 IMBBY Imperial Brands PLC DR 47.40 65.61 0.72 65.83 48.0960 NaN $47.1B $47,100,000 Consumer Staples Tobacco 19673.7480 1.0 $19,673,748.00 0.000000
20 KHC Kraft Heinz Co (The) CS 88.53 65.76 1.35 65.58 87.4300 0.303304 $106.5B $106,500,000 Consumer Staples Food Products 26607.0000 1.0 $26,607,000.00 0.000076
21 ILMN Illumina Inc CS 138.00 47.44 2.91 47.42 184.8500 0.307062 $27.1B $27,100,000 Health Care Life Sciences Tools & Services 2313.7060 1.0 $2,313,706.00 0.000026
22 HDB H D F C Bank Ltd DR 72.11 61.91 1.16 62.16 72.3300 0.460053 $60.8B $60,800,000 Financials Banks 11493.6810 1.0 $11,493,681.00 0.000087
23 FB Facebook Inc CS 127.88 61.26 2.09 61.19 128.9950 0.493637 $369.5B $369,500,000 Information Technology Internet Software & Services 22160.0000 1.0 $22,160,000.00 0.000030
24 REGN Regeneron Pharmaceuticals Inc CS 371.68 58.02 6.41 57.98 390.3200 0.287382 $40.9B $40,900,000 Health Care Biotechnology 4648.9770 1.0 $4,648,977.00 0.000033
25 TAP Molson Coors Brewing Co CS 111.25 60.51 1.84 60.46 107.7200 0.166011 $23.1B $23,100,000 Consumer Staples Beverages 3505.2000 1.0 $3,505,200.00 0.000025
26 ADBE Adobe Systems Inc CS 107.84 54.93 1.96 55.02 108.6600 0.296644 $54B $54,000,000 Information Technology Software 5552.4150 1.0 $5,552,415.00 0.000031
27 MU Micron Technology Inc. CS 17.13 53.53 0.32 53.53 17.6350 NaN $18.4B $18,400,000 Information Technology Semiconductors & Semiconductor Equipment 12399.0000 1.0 $12,399,000.00 0.000000
28 BUD Anheuser-Busch InBev SA/NV DR 128.65 54.27 2.37 54.28 127.2500 NaN $204.6B $204,600,000 Consumer Staples Beverages 42305.0000 1.0 $42,305,000.00 0.000000
29 VTR Ventas Inc. CSRT 66.95 52.96 1.26 53.13 67.0150 NaN $22.8B $22,800,000 Real Estate Equity Real Estate Investment Trusts (REITs) 3369.7910 1.0 $3,369,791.00 0.000000
30 FIS Fidelity National Information Services Inc CS 76.50 50.65 1.51 50.66 77.7000 0.289612 $25.6B $25,600,000 Information Technology IT Services 7939.6000 1.0 $7,939,600.00 0.000090
31 SNE Sony Corp DR 32.89 51.81 0.63 52.21 32.5301 0.067437 $41.3B $41,300,000 Consumer Discretionary Household Durables 72991.2060 1.0 $72,991,206.00 0.000119
32 EW Edwards Lifesciences Corp CS 116.00 47.99 2.42 47.93 120.0200 0.287474 $25.5B $25,500,000 Health Care Health Care Equipment & Supplies 2743.2000 1.0 $2,743,200.00 0.000031
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
377 EC Ecopetrol SA DR 9.04 NaN -0.75 NaN 9.0100 0.298349 $18.7B $18,700,000 Energy Oil, Gas & Consumable Fuels 13865.5850 1.0 $13,865,585.00 0.000221
378 BBL BHP Billiton PLC DR 29.55 NaN -2.40 NaN 30.9550 NaN $81.8B $81,800,000 Materials Metals & Mining 31356.0000 1.0 $31,356,000.00 0.000000
379 BHP BHP Billiton Ltd DR 33.99 NaN -2.40 NaN 35.3500 NaN $93.3B $93,300,000 Materials Metals & Mining 31356.0000 1.0 $31,356,000.00 0.000000
380 VOD Vodafone Group PLC DR 27.80 NaN -1.85 NaN 28.2100 NaN $75.7B $75,700,000 Telecommunication Services Wireless Telecommunication Services 58923.2710 1.0 $58,923,271.00 0.000000
381 CXO Concho Resources Inc CS 136.70 NaN -8.50 NaN 139.7600 -0.516660 $18.6B $18,600,000 Energy Oil, Gas & Consumable Fuels 1532.4890 1.0 $1,532,489.00 -0.000043
382 WMB Williams Cos Inc. (The) CS 29.13 NaN -1.63 NaN 30.3900 0.469240 $22.9B $22,900,000 Energy Oil, Gas & Consumable Fuels 7201.0000 1.0 $7,201,000.00 0.000148
383 SU Suncor Energy Inc. CS 28.43 NaN -1.48 NaN 27.7050 0.198331 $46.5B $46,500,000 Energy Oil, Gas & Consumable Fuels 19290.9618 1.0 $19,290,961.80 0.000082
384 BP BP PLC DR 35.84 NaN -1.71 NaN 36.1800 0.074744 $112.5B $112,500,000 Energy Oil, Gas & Consumable Fuels 193006.0000 1.0 $193,006,000.00 0.000128
385 TSLA Tesla Motors Inc CS 196.51 NaN -8.45 NaN 196.7400 -0.073656 $29.8B $29,800,000 Consumer Discretionary Automobiles 4568.2340 1.0 $4,568,234.00 -0.000011
386 JD JD.com Inc DR 26.50 NaN -1.01 NaN 27.0150 -0.001720 $39.5B $39,500,000 Consumer Discretionary Internet & Direct Marketing Retail 32853.3810 1.0 $32,853,381.00 -0.000001
387 BCS Barclays PLC DR 8.28 NaN -0.29 NaN 8.5650 NaN $37.2B $37,200,000 Financials Banks 42784.0440 1.0 $42,784,044.00 0.000000
388 TRP TransCanada Corp CS 46.04 NaN -1.53 NaN 46.3000 0.523026 $32.8B $32,800,000 Energy Oil, Gas & Consumable Fuels 8344.2792 1.0 $8,344,279.20 0.000133
389 CLR Continental Resources Inc CS 51.78 NaN -1.46 NaN 53.4400 0.661164 $20.3B $20,300,000 Energy Oil, Gas & Consumable Fuels 2162.5330 1.0 $2,162,533.00 0.000070
390 TS Tenaris SA, Luxembourg DR 28.99 NaN -0.67 NaN 29.1250 0.173264 $17.2B $17,200,000 Energy Energy Equipment & Services 5357.0470 1.0 $5,357,047.00 0.000054
391 WDAY Workday Inc CS 87.01 NaN -1.79 NaN 91.0197 -0.150826 $18.2B $18,200,000 Information Technology Software 1351.8460 1.0 $1,351,846.00 -0.000011
392 STO Statoil ASA DR 16.70 NaN -0.31 NaN 16.8800 0.273997 $54.2B $54,200,000 Energy Oil, Gas & Consumable Fuels 42411.5300 1.0 $42,411,530.00 0.000214
393 PXD Pioneer Natural Resources Co CS 185.96 NaN -3.16 NaN 186.4100 NaN $31.6B $31,600,000 Energy Oil, Gas & Consumable Fuels 3330.0000 1.0 $3,330,000.00 0.000000
394 ETP Energy Transfer Partners LP UT 35.30 NaN -0.57 NaN 35.8700 0.195801 $18.9B $18,900,000 Energy Oil, Gas & Consumable Fuels 22196.0000 1.0 $22,196,000.00 0.000230
395 SLB Schlumberger Ltd CS 81.32 NaN -1.19 NaN 81.4150 0.242969 $113.3B $113,300,000 Energy Energy Equipment & Services 29901.0000 1.0 $29,901,000.00 0.000064
396 VRTX Vertex Pharmaceuticals Inc CS 79.23 NaN -1.13 NaN 85.7700 -0.043041 $21B $21,000,000 Health Care Biotechnology 1557.4390 1.0 $1,557,439.00 -0.000003
397 BSX Boston Scientific Corp CS 22.95 NaN -0.26 NaN 23.5150 0.267345 $31.9B $31,900,000 Health Care Health Care Equipment & Supplies 7956.0000 1.0 $7,956,000.00 0.000067
398 TEF Telefonica SA DR 9.66 NaN -0.09 NaN 9.6350 0.284554 $47.1B $47,100,000 Telecommunication Services Diversified Telecommunication Services 52879.8770 1.0 $52,879,877.00 0.000319
399 CNQ Canadian Natural Resources Ltd CS 32.13 NaN -0.29 NaN 31.8500 0.344678 $35.5B $35,500,000 Energy Oil, Gas & Consumable Fuels 8039.5961 1.0 $8,039,596.10 0.000078
400 LNKD LinkedIn Corp CS 190.74 NaN -1.66 NaN 191.2100 0.121793 $25.8B $25,800,000 Information Technology Internet Software & Services 3434.8530 1.0 $3,434,853.00 0.000016
401 NOK Nokia Corp DR 4.95 NaN -0.04 NaN 5.4850 0.128740 $32B $32,000,000 Information Technology Communications Equipment 19486.1040 1.0 $19,486,104.00 0.000078
402 AVGO Broadcom Ltd CS 170.09 NaN -1.07 NaN 174.4000 0.382127 $69.1B $69,100,000 Information Technology Semiconductors & Semiconductor Equipment 10944.0000 1.0 $10,944,000.00 0.000061
403 CEO Cnooc Ltd DR 133.46 NaN -0.74 NaN 132.6100 NaN $60.6B $60,600,000 Energy Oil, Gas & Consumable Fuels 22071.5980 1.0 $22,071,598.00 0.000000
404 CVX Chevron Corp CS 101.08 NaN -0.40 NaN 102.3400 0.124601 $192.8B $192,800,000 Energy Oil, Gas & Consumable Fuels 104742.0000 1.0 $104,742,000.00 0.000068
405 AIG American International Group Inc CS 59.86 NaN -0.05 NaN 60.1400 0.122578 $64.9B $64,900,000 Financials Insurance 53574.0000 1.0 $53,574,000.00 0.000101
406 SHLD NaN NaN 10.72 NaN -17.79 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

404 rows × 17 columns

In [194]:
PE_data.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 407 entries, 0 to 406
Data columns (total 17 columns):
Symbol                      407 non-null object
Company Name                406 non-null object
Security Type               406 non-null object
Share price                 407 non-null float64
P_E                         358 non-null float64
EPS                         407 non-null float64
P_E_calc                    358 non-null float64
Security Price              406 non-null float64
EBITD Margin (TTM)          336 non-null float64
Market Capitalization       406 non-null object
Market_cap_clean            406 non-null object
Sector/Industry             406 non-null object
Industry                    406 non-null object
Revenue (TTM, Thousands)    406 non-null float64
count                       406 non-null float64
Revenue_clean               406 non-null object
Profit to mkt cap           406 non-null float64
dtypes: float64(9), object(8)
memory usage: 54.1+ KB

General cleaning

In [195]:
#convert from non-null object to string
PE_data['P_E'] = PE_data['P_E'].astype(str)
In [196]:
#strip/remove/drop where the row is #N/A 
#(this means the company had negative earnings, creating a negative P/E -- it's therefore excluded from calculation)
PE_data = PE_data[PE_data.P_E != '#N/A']
PE_data = PE_data[PE_data.P_E != 'nan']
#converting the clean P_E values over to float (number) now that the N/A is stripped
PE_data['P_E'] = PE_data['P_E'].astype(float)
In [197]:
PE_data.head()
Out[197]:
Symbol Company Name Security Type Share price P_E EPS P_E_calc Security Price EBITD Margin (TTM) Market Capitalization Market_cap_clean Sector/Industry Industry Revenue (TTM, Thousands) count Revenue_clean Profit to mkt cap
0 GSK Glaxosmithkline PLC DR 41.68 3228.51 0.01 4168.00 42.970 NaN $104.3B $104,300,000 Health Care Pharmaceuticals 34570.144 1.0 $34,570,144.00 0.000000
1 RIO Rio Tinto PLC DR 31.77 1246.86 0.03 1059.00 33.150 NaN $59.7B $59,700,000 Materials Metals & Mining 32349.000 1.0 $32,349,000.00 0.000000
2 KMI Kinder Morgan Inc. CS 20.36 552.66 0.04 509.00 21.670 0.492764 $49.2B $49,200,000 Energy Oil, Gas & Consumable Fuels 13682.000 1.0 $13,682,000.00 0.000137
3 EQIX Equinix Inc CSRT 360.25 403.10 0.89 404.78 351.250 0.413642 $25.1B $25,100,000 Real Estate Equity Real Estate Investment Trusts (REITs) 3161.777 1.0 $3,161,777.00 0.000052
4 NFLX Netflix Inc CS 101.47 315.17 0.32 317.09 104.865 0.584791 $45B $45,000,000 Consumer Discretionary Internet & Direct Marketing Retail 7624.628 1.0 $7,624,628.00 0.000099
In [198]:
#used to replace spaces w/ "_" (strip whitespace)
PE_data.columns = [x.strip().replace(' ', '_') for x in PE_data.columns]
PE_data.columns = [x.strip().replace('[', '_') for x in PE_data.columns]
PE_data.columns = [x.strip().replace(']', '_') for x in PE_data.columns]
PE_data.columns = [x.strip().replace('/', '_') for x in PE_data.columns]
PE_data.columns = [x.strip().replace('(', '_') for x in PE_data.columns]
PE_data.columns = [x.strip().replace(')', '_') for x in PE_data.columns]
In [199]:
PE_data.head()
Out[199]:
Symbol Company_Name Security_Type Share_price P_E EPS P_E_calc Security_Price EBITD_Margin__TTM_ Market_Capitalization Market_cap_clean Sector_Industry Industry Revenue__TTM,_Thousands_ count Revenue_clean Profit_to_mkt_cap
0 GSK Glaxosmithkline PLC DR 41.68 3228.51 0.01 4168.00 42.970 NaN $104.3B $104,300,000 Health Care Pharmaceuticals 34570.144 1.0 $34,570,144.00 0.000000
1 RIO Rio Tinto PLC DR 31.77 1246.86 0.03 1059.00 33.150 NaN $59.7B $59,700,000 Materials Metals & Mining 32349.000 1.0 $32,349,000.00 0.000000
2 KMI Kinder Morgan Inc. CS 20.36 552.66 0.04 509.00 21.670 0.492764 $49.2B $49,200,000 Energy Oil, Gas & Consumable Fuels 13682.000 1.0 $13,682,000.00 0.000137
3 EQIX Equinix Inc CSRT 360.25 403.10 0.89 404.78 351.250 0.413642 $25.1B $25,100,000 Real Estate Equity Real Estate Investment Trusts (REITs) 3161.777 1.0 $3,161,777.00 0.000052
4 NFLX Netflix Inc CS 101.47 315.17 0.32 317.09 104.865 0.584791 $45B $45,000,000 Consumer Discretionary Internet & Direct Marketing Retail 7624.628 1.0 $7,624,628.00 0.000099

Setting up PE by sector dataframe

In [200]:
df = PE_data[[11,4]]
df[['P_E']] = df[['P_E']].astype(float)
df.head()
/Users/erikrood/anaconda3/lib/python3.5/site-packages/pandas/core/frame.py:2378: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

Out[200]:
Sector_Industry P_E
0 Health Care 3228.51
1 Materials 1246.86
2 Energy 552.66
3 Real Estate 403.10
4 Consumer Discretionary 315.17
In [201]:
#chart_1
average_PE_sector = df.groupby('Sector_Industry').mean().sort_values(by='P_E',ascending=False).round(decimals=0).reset_index()
average_PE_sector.head()
Out[201]:
Sector_Industry P_E
0 Materials 107.0
1 Health Care 105.0
2 Energy 78.0
3 Real Estate 67.0
4 Consumer Discretionary 36.0
In [202]:
#chart_2
std_PE_sector = df.groupby('Sector_Industry').agg(np.std, ddof=0).sort_values(by='P_E',ascending=False).round(decimals=0).reset_index()
std_PE_sector.columns = ['Sector_Industry', 'PE_Std_dev'] #renaming columns
std_PE_sector.head()
Out[202]:
Sector_Industry PE_Std_dev
0 Health Care 459.0
1 Materials 289.0
2 Energy 136.0
3 Real Estate 95.0
4 Consumer Discretionary 54.0
In [203]:
#Chart_2.5
j1 = average_PE_sector.merge(std_PE_sector, how='left', on="Sector_Industry")
j1.head(10)
Out[203]:
Sector_Industry P_E PE_Std_dev
0 Materials 107.0 289.0
1 Health Care 105.0 459.0
2 Energy 78.0 136.0
3 Real Estate 67.0 95.0
4 Consumer Discretionary 36.0 54.0
5 Information Technology 30.0 33.0
6 Consumer Staples 28.0 14.0
7 Industrials 22.0 11.0
8 Utilities 20.0 8.0
9 Telecommunication Services 20.0 8.0
In [204]:
#chart_3
median_PE_sector = df.groupby('Sector_Industry').median().sort_values(by='P_E',ascending=False).round(decimals=0).reset_index()
median_PE_sector.head()
Out[204]:
Sector_Industry P_E
0 Real Estate 37.0
1 Energy 28.0
2 Health Care 27.0
3 Materials 25.0
4 Information Technology 24.0

PE by company

In [205]:
#chart_4
df1 = PE_data[[1,4]]
#df1.head(100)
top_PE_company = df1.groupby('Company_Name').mean().sort_values(by='P_E',ascending=False).round(decimals=0).reset_index()
top_PE_company.head()
#top_10_PE_company = top_PE_company.head(10)
Out[205]:
Company_Name P_E
0 Glaxosmithkline PLC 3229.0
1 Rio Tinto PLC 1247.0
2 Kinder Morgan Inc. 553.0
3 Equinix Inc 403.0
4 Netflix Inc 315.0
In [206]:
top_10_PE_company = top_PE_company.head(10)
top_10_PE_company['P_E'] = top_10_PE_company['P_E'].astype(int)
bottom_10_PE_company = top_PE_company.tail(10)
bottom_10_PE_company['P_E'] = bottom_10_PE_company['P_E'].astype(int)
/Users/erikrood/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:2: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

/Users/erikrood/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:4: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

EBITD margin by sector

In [207]:
#stripping those that don't contain EBITD values
PE_data['EBITD_Margin__TTM_'] = PE_data['EBITD_Margin__TTM_'].astype(str)
EBITD_data = PE_data[PE_data.EBITD_Margin__TTM_ != 'nan']
PE_data['EBITD_Margin__TTM_'] = PE_data['EBITD_Margin__TTM_'].astype(float)
EBITD_data['EBITD_Margin__TTM_'] = EBITD_data['EBITD_Margin__TTM_'].astype(float)
EBITD_data['EBITD_Margin__TTM_'] = EBITD_data['EBITD_Margin__TTM_']*100
EBITD_data.head()
/Users/erikrood/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:5: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

/Users/erikrood/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:6: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

Out[207]:
Symbol Company_Name Security_Type Share_price P_E EPS P_E_calc Security_Price EBITD_Margin__TTM_ Market_Capitalization Market_cap_clean Sector_Industry Industry Revenue__TTM,_Thousands_ count Revenue_clean Profit_to_mkt_cap
2 KMI Kinder Morgan Inc. CS 20.36 552.66 0.04 509.00 21.670 49.27642 $49.2B $49,200,000 Energy Oil, Gas & Consumable Fuels 13682.000 1.0 $13,682,000.00 0.000137
3 EQIX Equinix Inc CSRT 360.25 403.10 0.89 404.78 351.250 41.36424 $25.1B $25,100,000 Real Estate Equity Real Estate Investment Trusts (REITs) 3161.777 1.0 $3,161,777.00 0.000052
4 NFLX Netflix Inc CS 101.47 315.17 0.32 317.09 104.865 58.47908 $45B $45,000,000 Consumer Discretionary Internet & Direct Marketing Retail 7624.628 1.0 $7,624,628.00 0.000099
5 ALXN Alexion Pharmaceuticals Inc CS 118.34 299.70 0.39 303.44 125.130 36.25092 $27.6B $27,600,000 Health Care Biotechnology 2821.658 1.0 $2,821,658.00 0.000037
6 INCY Incyte Corp CS 87.10 256.82 0.34 256.18 97.970 20.70294 $17.8B $17,800,000 Health Care Biotechnology 941.243 1.0 $941,243.00 0.000011
In [208]:
df2 = EBITD_data[[11,8]]
In [209]:
#chart_5
average_EBITD_sector = df2.groupby('Sector_Industry').mean().sort_values(by='EBITD_Margin__TTM_',ascending=False).reset_index()
average_EBITD_sector.head()
Out[209]:
Sector_Industry EBITD_Margin__TTM_
0 Real Estate 44.749670
1 Financials 40.461852
2 Utilities 35.396935
3 Telecommunication Services 33.203306
4 Information Technology 29.767919

EBITD margin by company

In [210]:
#to include revenue:
##df3 = EBITD_data[[1,8,15]]
##average_EBITD_company = df3.groupby(['Company_Name','Revenue_clean']).mean().sort_values(by='EBITD_Margin__TTM_',ascending=False).reset_index()
df3 = EBITD_data[[1,8]]
average_EBITD_company = df3.groupby(['Company_Name']).mean().sort_values(by='EBITD_Margin__TTM_',ascending=False).reset_index()
average_EBITD_company.head(10)
Out[210]:
Company_Name EBITD_Margin__TTM_
0 Mitsubishi UFJ Financial Group 87.12821
1 Visa Inc 69.91384
2 CME Group Inc 68.81269
3 Gilead Sciences Inc 67.69022
4 Taiwan Semiconductor Manufacturing Co Ltd 64.57556
5 American Tower Corp 60.13103
6 Netflix Inc 58.47908
7 Mastercard Inc 56.59173
8 U.S. Bancorp 55.64148
9 M&T Bank Corp 55.29635
In [211]:
#setting up top/bottom 10 for charts
top_10_EBITD_company = average_EBITD_company.head(10)
bottom_10_EBITD_company = average_EBITD_company.tail(10)
In [212]:
#bar chart
x = average_PE_sector['Sector_Industry']
y = average_PE_sector['P_E']

data = [
    go.Bar(
        x=x,
        y=y,
        
        marker=dict(
            color='rgb(23, 191, 99)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
]
layout = go.Layout(
      title='average P/E ratio by sector',
    xaxis=dict(
        title='sector',
        tickangle = 47,
    ),
    yaxis=dict(
        title='avg. P/E ratio'
    ),
     margin=go.Margin(
        b = 220
        
    )

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='average_PE_sector')
Out[212]:
In [213]:
#bar chart
x = median_PE_sector['Sector_Industry']
y = median_PE_sector['P_E']

data = [
    go.Bar(
        x=x,
        y=y,
        
        marker=dict(
            color='rgb(255, 170, 15)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
]
layout = go.Layout(
      title='median P/E ratio by sector',
    xaxis=dict(
        title='sector',
        tickangle = 47,
    ),
    yaxis=dict(
        title='median P/E ratio'
    ),
     margin=go.Margin(
        b = 220
        
    )

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='median_PE_sector')
Out[213]:
In [214]:
#combo chart - bar with avg PE, line with std dev

x = j1['Sector_Industry']
y1 = j1['P_E']
y2 = j1['PE_Std_dev']


trace0 = go.Bar(
        x=x,
        y=y1,
        name='Avg. P/E ratio',
        marker=dict(
            color='rgb(255, 217, 102)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.6
    )

trace1 = go.Scatter(
        x=x,
        y=y2,
        name='Standard deviation of P/E ratio',
        yaxis = 'y2',
        marker=dict(
            color='rgb(84, 226, 129)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
        
data = [trace0, trace1]
    
layout = go.Layout(
      title='mean P/E by sector, with standard deviation',
    legend=dict(
        x=.5,
        y=1,
        bgcolor='#E2E2E2',
        bordercolor='#FFFFFF',
        borderwidth=2,
        ),
    xaxis=dict(
        title='category',
        tickangle = 47,
    ),
    
    yaxis=dict(
        title='P/E ratio'
    ),
    
    yaxis2=dict(
        title='Standard deviation',
        overlaying='y',
        side='right'
    ),
    
     margin=go.Margin(
        b = 150,
        r=50,
        t = 50
    )

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='mean_PE_plus_std')
Out[214]:
In [215]:
#bar chart
x = top_10_PE_company['Company_Name']
y = top_10_PE_company['P_E']

data = [
    go.Bar(
        x=x,
        y=y,
        
        marker=dict(
            color='rgb(91, 184, 255)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
]
layout = go.Layout(
      title='P/E ratio by company (top 10)',
    xaxis=dict(
        title='company',
        tickangle = 47,
    ),
    yaxis=dict(
        title='P/E ratio'
    ),
     margin=go.Margin(
        b = 220
        
    ),
    
    annotations=[
        dict(x=xi,y=yi,
             text=str(yi),
             xanchor='center',
             yanchor='bottom',
             showarrow=False,
        ) for xi, yi in zip(x, y)]

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='PE_company_top_10')
Out[215]:
In [216]:
#bar chart
x = bottom_10_PE_company['Company_Name']
y = bottom_10_PE_company['P_E']

data = [
    go.Bar(
        x=x,
        y=y,
        
        marker=dict(
            color='rgb(249, 98, 87)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
]
layout = go.Layout(
      title='P/E ratio by company (bottom 10)',
    xaxis=dict(
        title='company',
        tickangle = 47,
    ),
    yaxis=dict(
        title='P/E ratio'
    ),
     margin=go.Margin(
        b = 220
        
    )
    
    ##annotations=[
    #    dict(x=xi,y=yi,
    #         text=str(yi),
    #         xanchor='center',
    #         yanchor='bottom',
    #         showarrow=False,
    #    ) for xi, yi in zip(x, y)]

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='PE_company_bottom_10')
Out[216]:
In [217]:
#bar chart
x = average_EBITD_sector['Sector_Industry']
y = average_EBITD_sector['EBITD_Margin__TTM_']

data = [
    go.Bar(
        x=x,
        y=y,
        
        marker=dict(
            color='rgb(23, 191, 99)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
]
layout = go.Layout(
      title='average EBITD margin by sector',
    xaxis=dict(
        title='sector',
        tickangle = 47,
    ),
    yaxis=dict(
        title='avg. EBITD margin'
    ),
     margin=go.Margin(
        b = 220
        
    )

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='average_ebitd_sector')
Out[217]:
In [218]:
#bar chart
x = top_10_EBITD_company['Company_Name']
y = top_10_EBITD_company['EBITD_Margin__TTM_']

data = [
    go.Bar(
        x=x,
        y=y,
        
        marker=dict(
            color='rgb(91, 184, 255)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
]
layout = go.Layout(
      title='EBITD margin by company (top 10)',
    xaxis=dict(
        title='company',
        tickangle = 47,
    ),
    yaxis=dict(
        title='EBITD margin'
    ),
     margin=go.Margin(
        b = 220
        
    )

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='EBITD_Margin__TTM__company_top_10')
Out[218]:
In [219]:
#bar chart
x = bottom_10_EBITD_company['Company_Name']
y = bottom_10_EBITD_company['EBITD_Margin__TTM_']

data = [
    go.Bar(
        x=x,
        y=y,
        
        marker=dict(
            color='rgb(249, 98, 87)',
            line=dict(
                color='rgb(8,48,107)',
                width=1.5
            ),
        ),
        opacity=0.8
    )
]
layout = go.Layout(
      title='EBITD margin by company (bottom 10)',
    xaxis=dict(
        title='company',
        tickangle = 47,
    ),
    yaxis=dict(
        title='EBITD margin'
    ),
     margin=go.Margin(
        b = 220
        
    )

    
)
fig = go.Figure(data=data, layout=layout)
py.iplot(fig, filename='EBITD_Margin__TTM__company_bottom_10')
Out[219]:
In [ ]:
 
In [ ]: