# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# ### Analysis of Federal Trade Commission complaints related to 'immigration services' 
#
# This notebook runs two analyses of Federal Trade Commission (FTC) data

import pandas as pd
import spacy
import xlrd
# %matplotlib inline

# In this section we open up all Excel sheets and isolate the data to the relevant columns

# +
ftc_complaints_2006_2015 = pd.read_excel(
    '../data/consumer_compls_redacted_2006_2015.xlsx',
    sheet_name = 'CIS Complaint'
)

ftc_complaints_2015_2017 = pd.read_excel('../data/consumer_compls_redacted_2015_2017.xls')

ftc_complaints_2018 = pd.read_excel('../data/consumer_compls_redacted_2018.xlsx')
# -

print(len(ftc_complaints_2006_2015))
print(len(ftc_complaints_2015_2017))
print(len(ftc_complaints_2018))

# The next cell:
# - concatenates all data sets
# - renames columns to remove extraneous spaces
# - parses the `Created Date` column
# - reduces data to relevant columns

# +
complaints_combined = (
    pd.concat([
        ftc_complaints_2006_2015.assign(source = "2006-2015"),
        ftc_complaints_2015_2017.assign(source = "2015-2017"),
        ftc_complaints_2018.assign(source = "2018"),
    ], sort = False)
    .reset_index(drop = True)
    .rename(columns = {
        "  Created Date": "Created Date",
        " Reference Number": "Reference Number"
    })
    .assign(
        date = lambda df: pd.to_datetime(
            df["Created Date"],
            errors = "coerce"
        )
    )    
    [[
        "source",
        "Reference Number",
        "Complaint Info Comments",
        "Created Date",
        "date"
    ]]
)

complaints_combined.head()
# -

complaints_combined["date"].isnull().sum()

# This cell looks for rows where the `Created Date` cell is not a datetime:

(
    complaints_combined
    .loc[lambda df: df["date"].isnull()]
)

# This cell creates dataframe that contains the monthly tally of complaints by time span covered within each Excel file in three different columns. 

# +
monthly_complaints_by_source = (
    complaints_combined
    .loc[lambda df: df["date"].notnull()]
    .groupby([
        pd.Grouper(
            key = "date",
            freq = "MS"
        ),
        "source"
    ])
    .size()
    .unstack()
)

monthly_complaints_by_source.head()
# -

(
    monthly_complaints_by_source
    .plot(figsize = (10, 6))
);

(    
    monthly_complaints_by_source
    .loc[lambda df: df.notnull().sum(axis = 1) > 1]
)

# This cell summarizes counts of complaints annually using the three columns created in the previous dataframe and selecting the value of each row that contains the highest number to avoid double counting complaints that may appear multiple times across spreadsheets. 

# +
annual_complaint_counts = (
    monthly_complaints_by_source
    .assign(
        count = lambda df: df.max(axis = 1)
    )
    .loc["2012":"2017"]
    .reset_index()
    .assign(
        year = lambda df: df["date"].dt.year
    )
    .groupby("year")
    ["count"]
    .sum()
)

annual_complaint_counts
# -

annual_complaint_counts.mean()

annual_complaint_counts.plot(kind = "bar", color = "teal", width = 0.8);

(
    complaints_combined
    .loc[lambda df: df["date"].notnull()]
    ["Reference Number"]
    .value_counts()
    .loc[lambda x: x > 1]
)

# ### Count the words used in the complaints
#
# This part of the notebook creates a list of words and data affiliated with each word (how many comments used it, how often it was used, and what different iterations of it were used).

# This cell checkes whether the `consumer_compls_redacted_2015_2017.xls` file contains any comments that could be useful for a word count analysis.

(
    complaints_combined
    ['Complaint Info Comments']
    .value_counts()
    .head()
)

# The coming cells filter out cells that are either contain no comments or redacted information only. 

nlp = spacy.load('en')

non_redacted_complaints = (
    complaints_combined
    .loc[lambda df: df["Complaint Info Comments"] != "(b)(6)"]
    .loc[lambda df: df["Complaint Info Comments"].notnull()]
)

non_redacted_complaints["Reference Number"].value_counts().head()

(
    non_redacted_complaints
    .loc[lambda df: df["Reference Number"] == "(b)(6)"]
    .head()
)

# The cells below create a data frame, `word_rows`, from every complaint comment. Each row contains the tokenized word, the word's lemmatized version, and a complaint index. 

import re
whitespace_pat = re.compile(r"[\s•]+")

# %%time
word_rows = []
for i, row in non_redacted_complaints.iterrows():
    clean_comment = re.sub(whitespace_pat, " ", row["Complaint Info Comments"])
    for token in nlp(clean_comment):
        word_rows.append({
            "word": token.text.lower(),
            "lemma": token.lemma_,
            "complaint_index": i
        })

words_dataframe = pd.DataFrame(word_rows)
words_dataframe.head()

# Based on the `word_rows` data frame, the next cell finds the total word count, the count for the number of comments using the word, and what iterations of the word that were used:

# +
lemma_counts = (
    words_dataframe
    .groupby("lemma")
    .pipe(lambda grp: pd.DataFrame({
        "total_count": grp.size(),
        "unique_comments": grp["complaint_index"].nunique(),
        "words": grp["word"].apply(lambda x: " | ".join(sorted(set(x))))
    }))
)

lemma_counts.head()
# -

(
    lemma_counts
    .sort_values("unique_comments", ascending = False)
    .head(30)
)

(    
    lemma_counts
    .to_csv(
        "../output/word_counts.csv",
    )
)

# ---
#
# ---
#
# ---

StarcoderdataJupyterScriptsDedupFiltered