<jupyter_start><jupyter_text>Finding and Cleaning r/loseit Challenge Data<jupyter_code>import datetime as dt
import os
import re
import statistics
from pathlib import Path

import matplotlib.pyplot as plt
import numpy as np

import gspread
import lxml
import markdown
import pandas as pd
import praw
import seaborn as sns
from lxml import etree
from oauth2client.service_account import ServiceAccountCredentials

pd.options.mode.chained_assignment = None  # default='warn'<jupyter_output><empty_output><jupyter_text>This is a function used to sort some of the lists in human ordered form.<jupyter_code>def atoi(text):
    return int(text) if text.isdigit() else text


def natural_keys(text):
    """
    alist.sort(key=natural_keys) sorts in human order
    http://nedbatchelder.com/blog/200712/human_sorting.html
    """
    return [atoi(c) for c in re.split("(\d+)", text)]


def month_year(timestamps):
    return str(timestamps.date())[:-3]


def get_date(created):
    return dt.datetime.fromtimestamp(created)
# Create the Data Directories
if not os.path.exists("./data/raw_data/"):
    os.makedirs("./data/raw_data/")
if not os.path.exists("./figures/"):
    os.makedirs("./figures/")
if not os.path.exists("./data/cleaned_data/"):
    os.makedirs("./data/cleaned_data/")
# loseit_data is the name at the top of the praw.ini file
reddit = praw.Reddit("loseit_data")
loseit_sub = reddit.subreddit("loseit")
challenge_posts = loseit_sub.search("loseit challenge tracker", limit=1000)
topics_dict = {
    "title": [],
    "score": [],
    "id": [],
    "url": [],
    "comms_num": [],
    "created": [],
    "body": [],
}
for submission in challenge_posts:
    topics_dict["title"].append(submission.title)
    topics_dict["score"].append(submission.score)
    topics_dict["id"].append(submission.id)
    topics_dict["url"].append(submission.url)
    topics_dict["comms_num"].append(submission.num_comments)
    topics_dict["created"].append(submission.created)
    topics_dict["body"].append(submission.selftext)
topics_data = pd.DataFrame(topics_dict)

_timestamp = topics_data["created"].apply(get_date)
topics_data = topics_data.assign(timestamp=_timestamp)

topics_data.to_csv("../data/raw_data/loseit_search_history.csv")
# Now that we have searched through old loseit posts, we need to find the urls.
links = []
for body in topics_dict["body"]:
    try:
        doc = etree.fromstring(markdown.markdown(re.sub("[\\n]", "", body)))
        for link in doc.xpath("//a"):
            web_url = link.get("href")
            if bool(re.search("spreadsheet", web_url)) and bool(
                re.search("oogle", web_url)
            ):
                links.append(web_url)
    except etree.XMLSyntaxError:
        pass

unique_spreadsheets = list(set(links))
# use creds to create a client to interact with the Google Drive API
names = []
for spreadsheet_link in unique_spreadsheets:
    scope = ["https://spreadsheets.google.com/feeds"]
    creds = ServiceAccountCredentials.from_json_keyfile_name(
        "loseit-sheets-6012c29a1f40.json", scope  # this is the google-app.json file
    )
    gc = gspread.authorize(creds)
    sht = gc.open_by_url(spreadsheet_link)
    if (
        bool(re.search("nter", sht.title)) == False
        and bool(re.search("/r/", sht.title)) == False
        and bool(re.search("Calculator", sht.title)) == False
    ):
        sheet_name = re.sub(
            "_\(responses\)",
            "",
            re.sub(
                ",",
                "",
                re.sub(
                    "\]",
                    "",
                    re.sub(
                        "\[",
                        "",
                        re.sub(
                            " ",
                            "_",
                            re.sub("  ", "_", re.sub("-", "", sht.title.lower())),
                        ),
                    ),
                ),
            ),
        )
        if sheet_name not in names:
            print(f"sheet name: {sheet_name}, link: {spreadsheet_link}")
            names.append(sheet_name)
            try:
                data_sheet = sht.worksheet("Tracker")
                data_vals = data_sheet.get_all_values()
                data_df = pd.DataFrame(data_vals[1:-2], columns=data_vals[0])
                data_df.to_csv("../data/raw_data/" + sheet_name + ".csv")
            except gspread.WorksheetNotFound:
                try:
                    data_sheet = sht.worksheet("Master Spreadsheet")
                    data_vals = data_sheet.get_all_values()
                    data_df = pd.DataFrame(data_vals[1:-2], columns=data_vals[0])
                    data_df.to_csv("../data/raw_data/" + sheet_name + ".csv")
                except gspread.WorksheetNotFound:
                    print("", end="\t")  # sheet_name)
            else:
                print("", end="\t")  # sheet_name)<jupyter_output>sheet name: summer_'14_challenge_tracker, link: https://docs.google.com/spreadsheets/d/1eNKmSi64PvPNw0iA3mOoFh2bkiJ6utyMtP95qmny9qE/edit?pli=1#gid=0
	sheet name: spring_time_to_energize_challenge, link: https://docs.google.com/spreadsheets/d/1R62EK0PZeQJL0Cfd1UyMThaAU8IDZgD_f7R9aqonGVU/edit?usp=sharing
	sheet name: super_mario_brothers_super_loseit_challenge_tracker, link: https://docs.google.com/spreadsheets/d/1cEHfD_pQZw5a48fw8wUHg6QwFEPVzwF8vmgGE5KVj2Y/edit#gid=1034497164
	sheet name: tracker_loseit_spring_semester_challenge, link: https://docs.google.com/spreadsheet/ccc?key=0Ajn9WUix2Lq1dFpHWG14N0hOV1Q2SzUxRXlBNE9JUEE#gid=0
	sheet name: the_summer_challenge_2016, link: https://docs.google.com/spreadsheets/d/1qcOU8dp6Nz-mzkZZlr-9RJgo7hXF_DT0vuFykwkaek4/edit?usp=sharing
sheet name: new_year_new_you_2016_challenge_master_spreadsheet, link: https://docs.google.com/spreadsheets/d/1PQpubmKrJcrA_bEBJZD_YUo87WYkxiu09CFK-ppehlQ/edit?usp=sharing
	sheet name: lord_of_the_rings_loseit_summer_c[...]<jupyter_text>The next step is cleaning up some of the column information, and removing the information that is not useful for analysis.<jupyter_code>weeks_col = {f"W{x}": f"Week {x}" for x in range(0, 11)}
new_names = {
    "W0 (SW)": "Week 0",
    "Sex": "Gender",
    "Male, Female, Other": "Gender",
    "TEAM": "Team",
    "Teams": "Team",
    "Challenge GW": "Challenge Goal Weight",
    "Challenge SW": "Week 0",
    "MyFitnessPal Username/Link": "MFP",
}
df_list = []
p = Path("../data/raw_data/")
for idx, challenge in enumerate(p.rglob("*.csv")):
    # Challenge Names
    challenge_name = re.sub("\d", "", challenge.name[:-4])

    # Read in the csv files and change some of the column names
    test_df = pd.read_csv(challenge, index_col=0)
    test_df.dropna(axis=1, how="all")
    test_df.columns = (
        test_df.columns.str.strip().str.replace("?", "").str.replace(":", "")
    )
    test_df.rename(columns=new_names, inplace=True)

    # timestamp
    if "Timestamp" not in test_df:
        test_df["Timestamp"] = (
            "October 2018"
            if challenge_name == "super_mario_brothers_super_loseit_challenge_tracker"
            else "March 2017"
        )
    test_df.Timestamp = pd.to_datetime(test_df.Timestamp, errors="coerce").apply(
        month_year
    )

    # Age
    test_df["Age"] = test_df[
        test_df.filter(regex=re.compile("Age", re.IGNORECASE)).columns[0]
    ]

    # Gender
    if len(test_df.filter(regex=re.compile("Sex", re.IGNORECASE)).columns):
        test_df["Gender"] = test_df[
            test_df.filter(regex=re.compile("Sex", re.IGNORECASE)).columns[0]
        ]
    if len(test_df.filter(regex=re.compile("Gender", re.IGNORECASE)).columns):
        test_df["Gender"] = test_df[
            test_df.filter(regex=re.compile("Gender", re.IGNORECASE)).columns[0]
        ]
    if "Gender" not in test_df:
        test_df["Gender"] = "Unknown"

    # Ignore KGS
    if len(test_df.filter(regex=re.compile("kgs", re.IGNORECASE)).columns):
        test_df.drop(
            test_df.filter(regex=re.compile("kgs", re.IGNORECASE)).columns[0],
            axis=1,
            inplace=True,
        )

    # Keep Just Starting BMI
    test_df.drop(
        test_df.filter(regex=re.compile("BMI", re.IGNORECASE)).columns[1:],
        axis=1,
        inplace=True,
    )

    # Username
    test_df.columns = test_df.columns.str.replace(
        test_df.filter(like="name").columns[0], "Username"
    )
    test_df.Username = test_df.Username.astype(str).apply(lambda x: x.lower())

    # Weigh-in Data
    test_df.rename(columns=weeks_col, inplace=True)
    if len(test_df.filter(regex=re.compile("week 0", re.IGNORECASE)).columns):
        test_df["Week 0"] = test_df[
            test_df.filter(regex=re.compile("week 0", re.IGNORECASE)).columns[0]
        ]
    elif len(test_df.filter(regex=re.compile("sign-up", re.IGNORECASE)).columns):
        test_df["Week 0"] = test_df[
            test_df.filter(regex=re.compile("sign-up", re.IGNORECASE)).columns[0]
        ]
    elif len(test_df.filter(regex=re.compile("start weight", re.IGNORECASE)).columns):
        test_df["Week 0"] = test_df[
            test_df.filter(regex=re.compile("start weight", re.IGNORECASE)).columns[0]
        ]
    elif len(test_df.filter(regex=re.compile("Signup weight", re.IGNORECASE)).columns):
        test_df["Week 0"] = test_df[
            test_df.filter(regex=re.compile("Signup weight", re.IGNORECASE)).columns[0]
        ]
    elif len(
        test_df.filter(
            regex=re.compile("What is your current weight", re.IGNORECASE)
        ).columns
    ):
        test_df["Week 0"] = test_df[
            test_df.filter(
                regex=re.compile("What is your current weight", re.IGNORECASE)
            ).columns[0]
        ]

    # Height
    test_df["Height"] = test_df[
        test_df.filter(regex=re.compile("Height", re.IGNORECASE)).columns[0]
    ]

    # Highest Weight
    if len(test_df.filter(regex=re.compile("Highest", re.IGNORECASE)).columns):
        test_df["Highest Weight"] = test_df[
            test_df.filter(regex=re.compile("Highest", re.IGNORECASE)).columns[0]
        ]
    else:
        test_df["Highest Weight"] = np.NaN

    # Has NSV
    test_df["Has NSV"] = (
        test_df[test_df.filter(regex=re.compile("NSV", re.IGNORECASE)).columns[0]]
        .notnull()
        .astype("int")
    )
    test_df["NSV Text"] = (
        test_df[test_df.filter(regex=re.compile("NSV", re.IGNORECASE)).columns[0]]
        .astype(str)
        .replace("nan", "")
    )

    # Goal Weight
    test_df["Challenge Goal Weight"] = test_df[
        test_df.filter(regex=re.compile("Goal Weight", re.IGNORECASE)).columns[0]
    ]

    # Has a food tracker
    if len(test_df.filter(regex=re.compile("MyFitnessPal", re.IGNORECASE)).columns):
        test_df["MFP"] = (
            test_df[
                test_df.filter(regex=re.compile("MyFitnessPal", re.IGNORECASE)).columns[
                    0
                ]
            ]
            .notnull()
            .astype("int")
        )
    test_df["Has MFP"] = (
        test_df[test_df.filter(regex=re.compile("MFP", re.IGNORECASE)).columns[0]]
        .notnull()
        .astype("int")
    )
    if len(test_df.filter(regex=re.compile("Loseit", re.IGNORECASE)).columns):
        test_df["Has Loseit"] = (
            test_df[
                test_df.filter(regex=re.compile("Loseit", re.IGNORECASE)).columns[0]
            ]
            .notnull()
            .astype("int")
        )
    else:
        test_df["Has Loseit"] = 0
    test_df["Has Food Tracker"] = test_df["Has MFP"] + test_df["Has Loseit"]
    test_df["Has Food Tracker"] = test_df["Has Food Tracker"].replace(2, 1)

    # fitness tracker
    if len(test_df.filter(regex=re.compile("Fitbit", re.IGNORECASE)).columns):
        test_df["Has Activity Tracker"] = (
            test_df[
                test_df.filter(regex=re.compile("Fitbit", re.IGNORECASE)).columns[0]
            ]
            .notnull()
            .astype("int")
        )
    elif len(
        test_df.filter(regex=re.compile("Fitness tracker", re.IGNORECASE)).columns
    ):
        test_df["Has Activity Tracker"] = (
            test_df[
                test_df.filter(
                    regex=re.compile("Fitness Tracker", re.IGNORECASE)
                ).columns[0]
            ]
            .notnull()
            .astype("int")
        )
    elif len(test_df.filter(regex=re.compile("Garmin", re.IGNORECASE)).columns):
        test_df["Has Activity Tracker"] = (
            test_df[
                test_df.filter(regex=re.compile("Garmin", re.IGNORECASE)).columns[0]
            ]
            .notnull()
            .astype("int")
        )
    elif len(test_df.filter(regex=re.compile("Strava", re.IGNORECASE)).columns):
        test_df["Has Activity Tracker"] = (
            test_df[
                test_df.filter(regex=re.compile("Strava", re.IGNORECASE)).columns[0]
            ]
            .notnull()
            .astype("int")
        )

    # Team and Challenge Names
    test_df["Challenge"] = (
        challenge_name.replace("_", " ")
        .title()
        .replace("'", "")
        .replace("Tracker", "")
        .replace("Master", "")
        .replace("Sign Ups", "")
        .replace("Spreadsheet", "")
        .replace("Loseit", "")
        .replace("Challenge", "")
        .replace("Edition", "")
        .replace("  ", " ")
        .strip()
        + " Challenge"
    )
    test_df["Team"] = test_df["Team"].str.title()
    test_df["Team"] = test_df["Team"].str.replace("2Nd", "2nd")

    # Starting Weight
    test_df["Starting Weight"] = test_df["Week 0"]

    # Create the final Data Frame
    col_weeks = test_df.filter(regex=re.compile("Week", re.IGNORECASE)).columns.tolist()
    col_weeks.sort(key=natural_keys)
    col_names = [
        "Timestamp",
        "Username",
        "Team",
        "Challenge",
        "Age",
        "Gender",
        "Height",
        "Highest Weight",
        "Starting Weight",
        "Challenge Goal Weight",
        "Starting BMI",
        "Has NSV",
        "Has Food Tracker",
        "Has Activity Tracker",
        "NSV Text",
    ]

    data_cols = col_names + list(col_weeks)
    data_df = test_df[data_cols]

    df_list.append((challenge.stem, data_df))<jupyter_output><empty_output><jupyter_text>Now that the data contains only what we are interested in learning, we need to fill in any missing values before we combine all of the challenges together.<jupyter_code>big_df_list = []

for data in df_list:
    df = data[1].copy()

    # Some odties in the data
    if data[0] == "spring_time_to_energize_challenge":
        df.drop([448, 828], inplace=True)
        df.replace({"ERROR": np.NaN}, inplace=True)
    if data[0] == "autumn_animal_challenge":
        df.drop(971, inplace=True)
        df.replace({"#DIV/0!": np.NaN, "old": np.NaN}, inplace=True)
    if data[0] == "rebirth_challenge_2017":
        df.drop(["Week 7", "Week 8"], axis=1, inplace=True)
        df.replace({"20s": 25, "Yes": np.NaN}, inplace=True)

    df.Timestamp = statistics.mode(df.Timestamp)

    df.dropna(subset=["Username", "Challenge Goal Weight"], axis=0, inplace=True)
    df.loc[pd.isnull(df["Gender"]), "Gender"] = "Unknown"
    df.loc[~df["Gender"].isin(["Female", "Male", "Unknown"]), "Gender"] = "Other"
    df.loc[pd.isnull(df["Highest Weight"]), "Highest Weight"] = df["Week 0"]
    df["Timestamp"] = df["Timestamp"].fillna(axis=0, method="ffill", limit=10)

    
    # Now we want to convert the series into the correct types
    numberic = [
        "Age",
        "Height",
        "Highest Weight",
        "Starting Weight",
        "Challenge Goal Weight",
        "Starting BMI",
    ]
    df[numberic] = df[numberic].astype(np.float64)

    """
    Now we need to work on removing those who dropped out of the challenge. 
    First, if only one weigh-in was missed we will fill it with the previous weeks 
    weigh-in. Next, we remove any that are missing the final weigh-in, and lastly, 
    we fill any of the remaining missing values with the previous weeks data.
    """
    weight_cols = df.columns.values[15:].tolist()

    df[weight_cols] = df[weight_cols].fillna(axis=1, method="ffill", limit=1)
    df.dropna(axis=0, subset=[weight_cols[-1]], inplace=True)
    df[weight_cols] = df[weight_cols].fillna(axis=1, method="ffill").astype(np.float64)

    new_cols = [
        "Final Weight",
        "Total Challenge Loss",
        "Challenge Percentage Lost",
        "Percent of Challenge Goal",
    ]

    df["Challenge Goal Loss"] = df["Starting Weight"].astype(np.float64) - df[
        "Challenge Goal Weight"
    ].astype(np.float64)
    df[new_cols[0]] = df[weight_cols[-1]]
    df[new_cols[1]] = df[weight_cols[0]] - df[weight_cols[-1]]
    df[new_cols[2]] = (df[new_cols[1]] / df[weight_cols[0]]) * 100
    df[new_cols[3]] = (
        df[new_cols[1]]
        / (
            df["Starting Weight"].astype(np.float64)
            - df["Challenge Goal Weight"].astype(np.float64)
        )
    ).replace(np.inf, 0).replace(-np.inf, 0) * 100
    df[new_cols] = df[new_cols].astype(np.float64)

    df = df[df.columns.values[:15].tolist() + ["Challenge Goal Loss"] + new_cols]

    # Save the cleaned data and append to the dataframe list
    df.to_csv("../data/cleaned_data/cleaned_" + data[0] + ".csv")

    big_df_list.append(df)
big_df = pd.concat(big_df_list, ignore_index=True).dropna()
big_df.to_csv("../data/processed_data/cleaned_and_combined_loseit_challenge_data.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1465576395242452, 'fraction_numerical': 0.012980329368709973, 'mean_word_length': 2.6164185277088503, 'pattern_counts': {'":': 25, '<': 16, '<?xml version=': 0, '>': 16, 'https://': 7, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '507916', 'license': 'MIT', 'n_tokens_mistral': 6201, 'n_tokens_neox': 5897, 'n_words': 1034, 'path': 'notebooks/01_clean_loseit_challenge_data.ipynb', 'repo_name': 'csmithchicago/Loseit', 'size': '17488'}