import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
companies = pd.read_csv('sp500_fundamentals.csv')
companies.shape

companies.columns.tolist()
companies.head(3)

companies.isna().sum().sort_values(ascending=False)
round(companies['Dividend Yield'].isna().mean() * 100, 1)

valuation_cols = ['Symbol', 'Name', 'Sector', 'Price', 'Price/Earnings', 'Market Cap', 'EBITDA', 'Price/Sales', 'Price/Book']
clean = companies.dropna(subset=valuation_cols).copy()
clean.shape

top_market_cap = clean.nlargest(10, 'Market Cap')[['Symbol', 'Name', 'Market Cap']]
top_market_cap

clean['Price/Earnings'].describe().round(2)

plt.figure(figsize=(10, 5))
plt.hist(clean['Price/Earnings'].clip(upper=100), bins=40, color='steelblue', edgecolor='white')
plt.xlabel('Real Price-to-Earnings Ratio')
plt.ylabel('Real Number of Companies')
plt.title('Real P/E Ratio Distribution, S&P 500')
plt.tight_layout()
plt.savefig('pe_distribution.png', dpi=120)
plt.close()

cheapest = clean[clean['Price/Earnings'] > 0].nsmallest(10, 'Price/Earnings')[['Symbol', 'Name', 'Sector', 'Price/Earnings']]
cheapest

sector_pe = clean.groupby('Sector')['Price/Earnings'].mean().sort_values(ascending=False)
sector_pe.head(10).round(2)

sector_counts = clean['Sector'].value_counts()
sector_counts.head(10)

dividend_payers = companies[companies['Dividend Yield'].notna()]
pct_paying = round(len(dividend_payers) / len(companies) * 100, 1)
pct_paying

top_yield = dividend_payers.nlargest(10, 'Dividend Yield')[['Symbol', 'Name', 'Sector', 'Dividend Yield']]
top_yield

eps_clean = companies.dropna(subset=['Earnings/Share'])
negative_eps_count = (eps_clean['Earnings/Share'] < 0).sum()
negative_eps_count

unprofitable = eps_clean[eps_clean['Earnings/Share'] < 0][['Symbol', 'Name', 'Sector', 'Earnings/Share']]
unprofitable.sort_values('Earnings/Share').head(10)

pb_clean = companies.dropna(subset=['Price/Book'])
pb_clean['Price/Book'].describe().round(2)

negative_book = pb_clean[pb_clean['Price/Book'] < 0][['Symbol', 'Name', 'Price/Book']]
len(negative_book)

metric_cols = ['Price/Earnings', 'Price/Sales', 'Price/Book', 'Dividend Yield']
metric_corr = companies[metric_cols].corr()
metric_corr.round(3)

value_screen = clean[(clean['Price/Earnings'] < 15) & (clean['Price/Earnings'] > 0) & (clean['Price/Book'] < 3)]
len(value_screen)

value_screen[['Symbol', 'Name', 'Sector', 'Price/Earnings', 'Price/Book']].sort_values('Price/Earnings').head(10)

cap_bins = [0, 10e9, 50e9, 200e9, np.inf]
cap_labels = ['Small (<$10B)', 'Mid ($10-50B)', 'Large ($50-200B)', 'Mega (>$200B)']
clean['CapTier'] = pd.cut(clean['Market Cap'], bins=cap_bins, labels=cap_labels)
clean['CapTier'].value_counts()

tier_pe = clean.groupby('CapTier', observed=True)['Price/Earnings'].mean()
tier_pe.round(2)

ebitda_clean = clean.dropna(subset=['EBITDA'])
ebitda_to_cap = (ebitda_clean['EBITDA'] / ebitda_clean['Market Cap'])
ebitda_to_cap.describe().round(4)

ebitda_clean = ebitda_clean.assign(EBITDA_to_Cap=ebitda_to_cap)
ebitda_clean.nlargest(10, 'EBITDA_to_Cap')[['Symbol', 'Name', 'EBITDA_to_Cap']]

top_sectors_by_count = clean['Sector'].value_counts().head(6).index
plt.figure(figsize=(11, 6))
plot_data = [clean.loc[clean['Sector'] == s, 'Price/Earnings'].clip(upper=80) for s in top_sectors_by_count]
plt.boxplot(plot_data, tick_labels=top_sectors_by_count)
plt.xticks(rotation=30, ha='right')
plt.ylabel('Real Price-to-Earnings Ratio')
plt.title('Real P/E Spread Across Top Sectors')
plt.tight_layout()
plt.savefig('sector_pe_boxplot.png', dpi=120)
plt.close()

range_clean = companies.dropna(subset=['52 Week Low', '52 Week High', 'Price'])
range_clean = range_clean.assign(PctFromHigh=(range_clean['Price'] - range_clean['52 Week High']) / range_clean['52 Week High'] * 100)
range_clean['PctFromHigh'].describe().round(1)

near_low = range_clean.nsmallest(10, 'PctFromHigh')[['Symbol', 'Name', 'Price', '52 Week High', 'PctFromHigh']]
near_low.round(2)

clean.to_csv('sp500_fundamentals_clean.csv', index=False)
reloaded = pd.read_csv('sp500_fundamentals_clean.csv')
reloaded.shape[0] == clean.shape[0]

(value_screen['Price/Earnings'] < 15).all() and (value_screen['Price/Book'] < 3).all()

value_screen_sectors = value_screen['Sector'].nunique()
value_screen_sectors

print(f'Across {len(companies)} real S&P 500 companies, {pct_paying}% pay a dividend, {negative_eps_count} reported negative earnings, and {len(value_screen)} passed a simple real value screen across {value_screen_sectors} different sectors.')