<jupyter_start><jupyter_text>(Golden Rules for) Exploratory Data Analysis (EDA)What's below is some hard earned wisdom. Please accept this into your heart and go forth such that data never harms you as it has so many before you. ```{figure} https://media.giphy.com/media/Wn74RUT0vjnoU98Hnt/source.gif---height: 300pxname: baby-yodSSSSS---You, soon to be wise``` ````{dropdown} 1. **GOLDEN RULES for EDA**You got new data - fun! But before you go speeding off into analysis, first you must learn some basic facts about the data. You should **always, always, always** open your datasets up to physically (digitally?) look at them[^member] and then also generate summary statistics. Here are _some_ basic outputs you should examine carefully. 1. Print the first and last five rows: `df.head()` and `df.tail()`2. What is the shape ( rows and variables) of the data? `df.shape`1. Print the column names3. How much memory does it take, and what are the variable names/types? `df.info()`4. Summary stats on variables:     - `df.describe()` - count, mean, sd, etc    - `df['var'].value_counts().head(10)`  - the most common values of a variable    - `df['var'].nunique()` - the number of unique values of a variable    ```{tip}Automate your initial EDA by putting something like the below in your codebook. Double tip: [Look in the community codebook :)](https://github.com/LeDataSciFi/ledatascifi-2022/tree/main/community_codebook)You can refine this as you go - you'll see more and more tips how to improve your EDA as we go.``````{warning}Two things:1. Don't just run these and move on! Actually **look** at the output and check for possible issues. (Some possible issues are listed in the next drop down.)1. This isn't a comprehensive list of things I'd do to check datasets, merely a reasonable start!```````[^member]: [Remember this?](../01/07_debugging.htmlseriously-print-your-data-and-objects-often) ````{dropdown} 2. Data cleaning```{admonition} A thought not quite profound enough to be wisdom:class: tipData cleaning, exploration, and analysis exist in a never ending feedback loop. Analysis projects are rarely linear. You'll be doing some analysis and realize there is a data problem, or that you need new data, and you're back at step one above.``` Here are some things you might look for while cleaning your data:| Look for | Comment || :--- | :--- |Do some variables have large outliers? | You might need to winsorize, or drop, those observations.Do some variables have many missing variables? | Maybe there was a problem with the source or how you loaded it.Do some variables have impossible values? | For example, sales can't be negative! Yet, some datasets use "-99" to indicate missing data. Or are there bad dates (June 31st)?Are there duplicate observations? | E.g. two 2001-GM observations because they changed their fiscal year in the middle of calendar year 2001?Are there missing observations or a gap in the time series? | E.g. no observation in 2005 for Enron because the executive team was too "distracted to file its 10-K?Do variables contradict each other? | E.g. If my birthday is 1/5/1999, then I must be 21 as of this writing.```` ````{dropdown} 3. Institutional knowledge is crucial```{tip}Lehigh offers students free subscriptions to WSJ, NYT, and FT!```- For example, in my research on firm investment policies and financing decisions (e.g. leverage), I drop any firms from the finance or utility industries. I know to do this because those industries are subject to several factors that fundamentally change their leverage in ways unrelated to their investment policies. Thus, including them would contaminate any relationships I find.- When we work with 10-K text data, we need to know what data tables are available and how to remove boilerplate. - You only get institutional through reading supporting materials, documentation, related info (research papers and WSJ, etc), and... the documents themselves. For example, I've read in excess of 6,000 proxy statements for a single research project. (If that sounds exciting: Go to grad school and become a prof!)```` ```{dropdown} 4. Explore the data with your question in mind- Compute statistics by subgroups (by age, or industry), or two-way (by gender and age)- Do a big correlation matrix to get a sense of possible relationships between variables in the data on a first pass. (We will do this later.)- This step reinforces institutional knowledge and your understanding of the dataRemember, **data exploration** and **data cleaning** are interrelated and you'll go back and forth from one to the other. ```  Toy EDA I do all of suggested steps here. The output isn't pretty, and I'd clean it up if I was writing a report. But this gets the ball rolling.```{note}This data only has one categorical variable (species). You should do `value_counts` and `nunique` for all categorical variables.```<jupyter_code># import a famous dataset, seaborn nicely contains it out of the box!
import seaborn as sns 
iris = sns.load_dataset('iris') 

print(iris.head(),  '\n---')
print(iris.tail(),  '\n---')
print(iris.columns, '\n---')
print("The shape is: ",iris.shape, '\n---')
print("Info:",iris.info(), '\n---') # memory usage, name, dtype, and # of non-null obs (--> # of missing obs) per variable
print(iris.describe(), '\n---') # summary stats, and you can customize the list!
print(iris['species'].value_counts()[:10], '\n---')
print(iris['species'].nunique(), '\n---')<jupyter_output>sepal_length  sepal_width  petal_length  petal_width species
0           5.1          3.5           1.4          0.2  setosa
1           4.9          3.0           1.4          0.2  setosa
2           4.7          3.2           1.3          0.2  setosa
3           4.6          3.1           1.5          0.2  setosa
4           5.0          3.6           1.4          0.2  setosa 
---
     sepal_length  sepal_width  petal_length  petal_width    species
145           6.7          3.0           5.2          2.3  virginica
146           6.3          2.5           5.0          1.9  virginica
147           6.5          3.0           5.2          2.0  virginica
148           6.2          3.4           5.4          2.3  virginica
149           5.9          3.0           5.1          1.8  virginica 
---
Index(['sepal_length', 'sepal_width', 'petal_length', 'petal_width',
       'species'],
      dtype='object') 
---
The shape is:  (150, 5) 
---
<class 'pandas.core.frame.DataFrame'>
RangeIndex[...]<jupyter_text>EDA of Compustat (Firm accounting variables)Compustat is a professional grade dataset that contains accounting data from SEC filings. I use it a lot in my work. Let's explore it!First, let's download our slice of it. The variables are listed and described in a csv file in the [repo's data folder.](https://github.com/LeDataSciFi/ledatascifi-2022/tree/main/data)<jupyter_code>import pandas as pd
import numpy as np
# these three are used to download the file
from io import BytesIO
from zipfile import ZipFile
from urllib.request import urlopen

url = 'https://github.com/LeDataSciFi/ledatascifi-2022/blob/main/data/CCM_cleaned_for_class.zip?raw=true'

#firms = pd.read_stata(url)   
# <-- that code would work, but GH said it was too big and
# forced me to zip it, so here is the work around to download it:

with urlopen(url) as request:
    data = BytesIO(request.read())

with ZipFile(data) as archive:
    with archive.open(archive.namelist()[0]) as stata:
        ccm = pd.read_stata(stata)<jupyter_output><empty_output><jupyter_text>There are too many variables to print `.describe()` nicely. Here's a trick: transpose the table:<jupyter_code>ccm.describe().T.style.format('{:,.2f}') # by transposing it, I can see more variables<jupyter_output><empty_output><jupyter_text>Observations- There are a lot of missing variables! Worthy of [more investigation...](04e-visualEDA)- Some firms have negative assets, market equity- What does it mean to have negative debt (td) or dividends (dv)?- Tax rates range from 0 to 1!- `Taxlosscarry` variables are booleans (1 or 0) as is `longdebtdum`  Looking for outliersThere are a lot of ways to go about this. - table, but print out percentiles that focus on the tails and look for large jumps near the edges (like from p95 to max)- make a table with skewness and kurtosis variables (look for fat and/or long tails)- boxplots, but you'll need to do a bunch of them- If you "standardize" all variables (subtract the mean and divide by the standard deviation - a common first step in many ML analyses!), you could plot the densities on a handful of charts or a tall ridgeline. Histograms might be better to spot outliers but would require ~40 figures.Some variables I'd be concerned about (not an exhaustive list!):- The leverage variables that start with `td` and `dltt`- Sales growth- Profitability- Market-to-Book<jupyter_code># a table approach to support the list of variables above
ccm.describe(percentiles=[.01,.05,.95,.99]).T.style.format('{:,.2f}')<jupyter_output><empty_output><jupyter_text>Missing valuesBelow is a list of variables that are missing values for more than 10% of the dataset. Future work will need to understand why there are missing values and understand whether and how we should deal with them.<jupyter_code>(
    ( # these lines do the calculation - what % of missing values are there for each var
        ccm.isna()      # ccm.isna() TURNS every obs/variable = 1 when its missing and 0 else
       .sum(axis=0)     # count the number of na for each variable (now data is 1 obs per column = # missing)
        /len(ccm)       # convert # missing to % missing 
        *100            # report as percentage
    ) 
    # you can stop here and report this...
    # but I wanted to format it a bit...
    .sort_values(ascending=False)[:13]
    .to_frame(name='% missing') # the next line only works on a frame, and because pandas sees only 1 variable at this pt
    .style.format("{:.1f}")     # in the code, it calls this a "series" type object, so convert it to dataframe type object
)
#<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0939424031777557, 'fraction_numerical': 0.020754716981132074, 'mean_word_length': 3.4365638766519826, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '34606', 'license': 'MIT', 'n_tokens_mistral': 2966, 'n_tokens_neox': 2782, 'n_words': 1335, 'path': 'content/03/02e_eda_golden.ipynb', 'repo_name': 'Theo-Faucher/ledatascifi-2022', 'size': '10070'}