<jupyter_start><jupyter_text>The Zip codes are well ordered by state except for DF and GO, I'll classify the zip codes with two geolocation_states based on the minimum and maximum ranges within the state and there's just one zip_code that I had to google it and classify it<jupyter_code>Geolocation = (
    Geolocation
    .merge(duplicated
           .query("length > 1")
           [["geolocation_zip_code_prefix"]])
    .drop(columns="geolocation_state")
    .assign(F=1)
    .merge(x.assign(F = 1)) 
    .query("geolocation_zip_code_prefix >= min and geolocation_zip_code_prefix <= max")
    .assign(geolocation_state = lambda df: df.apply(lambda row: ("GO" 
                                                                 if row.geolocation_zip_code_prefix == 72915 else 
                                                                 row.geolocation_state)
                                                    , axis=1))
    .drop(columns=["F", "min", "max"])
    .append(Geolocation
            .merge(duplicated
                   .query("length == 1")
                   [["geolocation_zip_code_prefix"]]))
)<jupyter_output><empty_output><jupyter_text>Let's plot Brazil:<jupyter_code>fig, ax = plt.subplots(figsize=(15,10))
ax.scatter(Geolocation.geolocation_lng, Geolocation.geolocation_lat, s=5)
ax.set_title("Brazil", fontsize=25)
plt.show()<jupyter_output><empty_output><jupyter_text>Well, that exactly doesn't seem like Brazil. So I'll have to clean some points. What's the range for brazilian coordinates? [Wiki](https://en.wikipedia.org/wiki/Geography_of_BrazilSize_and_geographical_location) says that north to south 5°16'20" N to -33°44'32" S and east to west -34°47'30" E to -73°59'32" W. So in our decimal range:<jupyter_code>y_max = 5 + 16/60 + 20/3600
y_min = -(33 + 44/60 + 32/3600)
x_max = -(34 + 47/60 + 30/3600)
x_min = -(73 + 59/60 + 32/3600)

fig, ax = plt.subplots(figsize=(15,10))
ax.scatter(Geolocation.geolocation_lng, Geolocation.geolocation_lat, s=5)
ax.plot([x_min, x_max, x_max, x_min, x_min], [y_max, y_max, y_min, y_min, y_max], color="red")
ax.set_title("Brazil", fontsize=25)
plt.show()<jupyter_output><empty_output><jupyter_text>Maybe we can extend a little our maximum x<jupyter_code>x_max += 3
fig, ax = plt.subplots(figsize=(15,10))
ax.scatter(Geolocation.geolocation_lng, Geolocation.geolocation_lat, s=5)
ax.plot([x_min, x_max, x_max, x_min, x_min], [y_max, y_max, y_min, y_min, y_max], color="red")
ax.set_title("Brazil", fontsize=25)
plt.show()<jupyter_output><empty_output><jupyter_text>Let's check those outliers:<jupyter_code>Geolocation = (Geolocation
               .assign(outlier = lambda df: df.apply(lambda row: not (x_min <= row.geolocation_lng <= x_max 
                                                                      and 
                                                                      y_min <= row.geolocation_lat <= y_max)
                                                     , axis=1))
              )

fig, ax = plt.subplots(figsize=(15,10))
Outliers = Geolocation.query("outlier")
ax.scatter(Outliers.geolocation_lng, Outliers.geolocation_lat, s=5)
ax.plot([x_min, x_max, x_max, x_min, x_min], [y_max, y_max, y_min, y_min, y_max], color="red")
ax.set_title("Outliers", fontsize=25)
plt.show()
Outliers.geolocation_zip_code_prefix.nunique()<jupyter_output><empty_output><jupyter_text>There are just 20 outliers, so I will check if the zip code has another valid (long, lat) since it's not an unique column.<jupyter_code>Outliers_Found = (Geolocation
                  .query("not outlier")
                  .merge(Outliers[["geolocation_zip_code_prefix"]].drop_duplicates())
                  [["geolocation_zip_code_prefix"]]
                  .drop_duplicates()
                  .assign(found = 1)
                 )
Outliers_Found<jupyter_output><empty_output><jupyter_text>There are 16 values within the zip code I could assign, so I will asign the outliers between the previous and consecutive zip codes remaining. Also I will calculate the geometric center of the zip codes:<jupyter_code>Geolocation = (Geolocation
               .merge(Outliers_Found, how="left")
               .query("found != found or not outlier")
              )
Centroids = (Geolocation
             .groupby(["geolocation_zip_code_prefix", "geolocation_state", "outlier"])
             .agg({"geolocation_lat": "mean"
                   , "geolocation_lng": "mean"})
             .reset_index()
             .assign(R = lambda df: range(df.shape[0]))
            )
Centroids
Outliers = (Centroids
            .query("outlier")
            .assign(previous = lambda df: df.R-1
                    , consecutive = lambda df: df.R+1)
            [["geolocation_zip_code_prefix", "geolocation_state", "previous", "consecutive"]]
            .merge(Centroids
                   .rename(columns={"geolocation_lat": "lat_0"
                                    , "geolocation_lng": "lng_0"
                                    , "R": "previous"})
                   .drop(columns=["outlier", "geolocation_zip_code_prefix"])
                   , how="left")
            .merge(Centroids
                   .rename(columns={"geolocation_lat": "lat_1"
                                    , "geolocation_lng": "lng_1"
                                    , "R": "consecutive"})
                   .drop(columns=["outlier", "geolocation_zip_code_prefix"])
                   , how="left")
            .assign(geolocation_lat = lambda df: (df.lat_0 + df.lat_1)/2
                    , geolocation_lng = lambda df: (df.lng_0 + df.lng_1)/2)
            [["geolocation_zip_code_prefix", "geolocation_state", "geolocation_lat", "geolocation_lng"]]
           )
Outliers<jupyter_output><empty_output><jupyter_text>Just for visualizing the outliers:<jupyter_code>Centroids = (Centroids
             .query("not outlier")
             .drop(columns=["R", "outlier"])
             .append(Outliers)
            )
fig, ax = plt.subplots(figsize=(15,10))
ax.scatter(Centroids.geolocation_lng, Centroids.geolocation_lat, s=5)
temp = Centroids.merge(Outliers_Found)
ax.scatter(temp.geolocation_lng, temp.geolocation_lat, s=25, color="red")
ax.scatter(Outliers.geolocation_lng, Outliers.geolocation_lat, s=25, color="red")
ax.set_title("Brazil without outliers (former in red)", fontsize=25)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12177875549968573, 'fraction_numerical': 0.021841609050911377, 'mean_word_length': 2.543986636971047, 'pattern_counts': {'":': 8, '<': 38, '<?xml version=': 0, '>': 35, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 20, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '357384', 'license': 'MIT', 'n_tokens_mistral': 2223, 'n_tokens_neox': 2056, 'n_words': 413, 'path': 'Brazil.ipynb', 'repo_name': 'ErickdeMauleon/Brazil', 'size': '6364'}