<jupyter_start><jupyter_text>Drop Useless Columns<jupyter_code>df.drop(df.columns[-8:], axis = 1, inplace = True)
df.drop(columns = ["Owner Name","Mailing Address","Email","EMAIL","phone, 2","secondary broker"], inplace = True)
df.drop(columns = ["Number of Units"], inplace = True)
df.drop(columns = ["Total area"], inplace = True)<jupyter_output><empty_output><jupyter_text>Clean Price Column And Convert to Integer<jupyter_code>df = df[df["Price"].str.contains("-")==False]
df["Price"] = df["Price"].apply(lambda x: x[1:])
df["Price"] = df["Price"].apply(lambda x: x.replace(",", ""))
df["Price"] = ["DROP" if "." in x else x for x in df["Price"]]
df.drop(df[df["Price"] == "DROP"].index, inplace = True)
df.drop(df[df["Price"] == "ot Type"].index, inplace = True)
df["Price"] = ["DROP" if " " in x else x for x in df["Price"]]
df.drop(df[df["Price"] == ""].index, inplace = True)
df.drop(df[df["Price"] == "DROP"].index, inplace = True)
df["Price"] = df["Price"].apply(lambda x : int(x))<jupyter_output><empty_output><jupyter_text>Create City, State, Zip Columns<jupyter_code>df["split"] = [x.split(",") for x in df["Address"]]
df["city"] = [x[0] for x in df["split"]]

df.drop(df[df["split"].apply(len) == 1].index, inplace = True)

df["state"] = [x[1].strip().split()[0] for x in df["split"]]
df["zipcode"] = [x[1].strip().split()[1] for x in df["split"]]<jupyter_output><empty_output><jupyter_text>Drop Number of Stories Column<jupyter_code>df.drop(columns = ["Number Of Stories"], inplace = True)<jupyter_output><empty_output><jupyter_text>Clean Lot Size<jupyter_code>df.drop(df[(df["Lot Size"] == "") | (df["Lot Size"] == " ")].index, inplace = True)
df = df[df["Lot Size"].str.contains("-")==False]
df["Lot Size"] = df["Lot Size"].apply(lambda x: x.strip())
df["Lot Size"] = df["Lot Size"].apply(lambda x: x.split())
df["Lot Size"] = [x if len(x) == 2 else "DROP" for x in df["Lot Size"]]
df.drop(df[df["Lot Size"] == "DROP"].index, inplace = True)
df["Lot Size"] = df["Lot Size"].apply(lambda x: " ".join(x))
df["Lot Size"] = df["Lot Size"].apply(lambda x: x.replace(",", ""))<jupyter_output><empty_output><jupyter_text>Convert Acres to Square Foot for Consistency<jupyter_code>df["Lot Size"] = [float(x.split()[0]) if x.split()[1] == "SF" 
                  else (float(x.split()[0]) * 43560)
                  for x in df["Lot Size"]]<jupyter_output><empty_output><jupyter_text>Save Cleaned Data<jupyter_code>df.drop(columns = ["split","phone","broker"], inplace = True)
df.drop(columns = ["Year Built"], inplace = True)
df.drop(columns = ["Address"], inplace = True)
df.rename(columns = {"City":"Address", "Lot Size":"Lot Size (sqft)", "url":"Url","city":"City","state":"State","zipcode":"Zipcode"}, 
          inplace = True)
df.to_excel("credata_cleaned.xlsx")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.23342836778332146, 'fraction_numerical': 0.0067712045616536, 'mean_word_length': 6.234536082474227, 'pattern_counts': {'":': 6, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '419931', 'license': 'MIT', 'n_tokens_mistral': 1067, 'n_tokens_neox': 1056, 'n_words': 221, 'path': 'cre data explore.ipynb', 'repo_name': 'Johnsteer/Real-Estate-Tableau-Dashboard', 'size': '2806'}