<jupyter_start><jupyter_text>Before cleaning  number of unique tables: 11k number of unique clusters: 963k  Cluster Cleaning<jupyter_code>df =df_copy.copy()<jupyter_output><empty_output><jupyter_text>step 1:  Group the data by telephoneNorm, and count the number of tables for each group, keep only the groups with records from at least two tables<jupyter_code>tmp = pd.DataFrame(df.groupby('telephoneNorm')['origin'].nunique()).reset_index()
tmp.columns = ['telephoneNorm','Ntables']
df_merge = df.merge(tmp, on='telephoneNorm', how='left')
df1 = df_merge[df_merge['Ntables']>1]
#df1 = df_merge[df_merge['Ntables']>0]
df1
df1['origin'].nunique()
df1["telephoneNorm"].nunique()
# Now check for min_size of 8

df1 = df_merge[df_merge['Ntables']>7]
print(df1['origin'].nunique())
print(df1["telephoneNorm"].nunique())
df1["telephoneNorm"].value_counts().sort_values()<jupyter_output><empty_output><jupyter_text>After step1  number of unique tables: 18.1k (5k last iteration) number of unique clusters: 222.1k (72.6k last iteration)cluster size >= 8: number of unique tables: 5.1k (1.3k last iteration) number of unique clusters: 7.1k (1.3k last iteration)<jupyter_code>df1.drop(columns='Ntables', inplace=True)<jupyter_output>/home/bizer-tp2021/anaconda3/lib/python3.8/site-packages/pandas/core/frame.py:4308: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  return super().drop(<jupyter_text>step 2:  Use more specific address information, e.g., streetaddress, for filtering<jupyter_code>def replace_list(iterator):
    if isinstance(iterator, list):
        return ' '.join(iterator)
    else:
        return iterator
df1['streetaddress']=df1['streetaddress'].apply(replace_list)
df1['addresslocality']=df1['addresslocality'].apply(replace_list)

df1["streetaddress"] = df1["streetaddress"].str.upper()
df1['addresslocality']=df1['addresslocality'].str.upper()
df1
tmp1 = pd.DataFrame(df1.groupby('telephoneNorm')['streetaddress'].nunique()).reset_index()
tmp1.columns = ['telephoneNorm','Nstreetaddress']

tmp2 = pd.DataFrame(df1.groupby('telephoneNorm')['addresslocality'].nunique()).reset_index()
tmp2.columns = ['telephoneNorm','Naddresslocality']
df_merge = df1.merge(tmp1, on='telephoneNorm', how='left').merge(tmp2, on='telephoneNorm', how='left')
df2 = df_merge[(df_merge['Nstreetaddress']<=5) & (df_merge['Naddresslocality']<=5)]
df2.drop(columns=['Nstreetaddress','Naddresslocality'], inplace=True)
tmp_ = pd.DataFrame(df2.groupby('telephoneNorm')['origin'].nunique()).reset_index()
tmp_.columns = ['telephoneNorm','Ntables']
df_merge_ = df2.merge(tmp_, on='telephoneNorm', how='left')
df_ = df_merge_[df_merge_['Ntables']>=8]
print(df_["origin"].nunique())
print(df_["telephoneNorm"].nunique())<jupyter_output>3647
6432<jupyter_text>After step2  number of unique tables: 5.2k (OUTDATED)  number of unique clusters: 72.1k (OUTDATED)cluster size >= 8: number of unique tables: 3.6k (last iteration 1.04k )number of unique clusters: 6.4k (last iteration 1.33k)  step 3:   Additional filtering using geo locationThe function for calculating distances is credited to Marvin<jupyter_code># def calcDifference(pointOne, pointTwo):
#     return geopy.distance.great_circle(pointOne, pointTwo).km

# def calcDifferenceFromRow (row, data):
#     indexValue = row.indexValue
#     indexPosition = (row[lat], row[lon])
#     diffList = []
# #     for value in row['MatchingGeoPoints']:
# #         if not value in tmp.index:
# #             continue
#     for value in row['MatchingNumbers']:
#         currRow = data.loc[value]
#         currIndex = currRow['indexValue']
#         if currIndex == indexValue:
#             diffList.append(-1)
#         else:            
#             currPosition = (currRow[lat], currRow[lon])
#             diffList.append(calcDifference(indexPosition, currPosition))
#     return diffList


# def keepCloseMatches(row, threshold):
#     return np.array(row['MatchingNumbers'])[np.array(row['distance'])<=threshold]
# lat = 'latitude'
# lon = 'longitude'

# check_list = ['latitude', 'longitude', 'indexValue']
# for l in check_list:
#     df2[l]=df2[l].apply(replace_list)
# df2['distance'] = df2.apply(calcDifferenceFromRow, args=(df2, ), axis=1)
# df2_copy = df2.copy()
# df2['MatchingNumbers_New'] = df2.apply(keepCloseMatches, args=(0.05,), axis=1)
# df2.to_json('df2_backup', compression='gzip', orient='records', lines=True)<jupyter_output><empty_output><jupyter_text>Clusters Cluster Statistics<jupyter_code># class EntityCluster():
#     """
#     create culsters and generate summary statistics for each entity tpye
#     """
    
#     def __init__(self, name, df=None, id_col='telephoneNorm'):
#         self.name = name
#         self.df = df
#         self.id_col = id_col
        
#         self._create_clusters()
        
#     @staticmethod
#     def _helperfunc(iterator, cluster, id_col='telephoneNorm', val_col='MatchingNumbers'):
#         key = iterator[id_col]
#         val = iterator[val_col]
#         cluster[key] = cluster[key].union(set(val))
        
#         return cluster
        
        
#     def _create_clusters(self):
#         """
#         create clusters and use the column value as the cluster identifier
#         """
#         df = self.df
#         id_col = self.id_col
        
#         # intialize the cluster
#         if not df is None:
#             cluster_ids = df[id_col].unique()
#             cluster = {key: set([]) for key in cluster_ids}
#             # add elements to the cluster
#             cluster = df.apply(EntityCluster._helperfunc, args=(cluster,), axis=1)[0]
#         else:
#             cluster = None
        
#         self.cluster = cluster
        
    
#     def get_cluster(self):
#         return self.cluster
                                                                            

#     def create_cluster_statistics(self, *args):
#         cluster = self.cluster
#         # create a dataframe for data storage
#         tmp = {}
#         for key, val in cluster.items():
#             tmp[key] = len(val)
            
#         df_clusters = pd.DataFrame().from_dict(tmp, orient='index', columns=['frequency'])
#         df_clusters.sort_values('frequency', ascending=False, inplace=True)
            
#         return df_clusters['frequency'].describe()
      
    
#     def plot_histogram(self, min_=None, max_=None, bins=None, *args): 
    
#         cluster = self.cluster
#         name =self.name
#         # create a dataframe for data storage
#         tmp = {}
#         for key, val in cluster.items():
#             tmp[key] = len(val)
            
#         df_clusters = pd.DataFrame().from_dict(tmp, orient='index', columns=['frequency'])
#         df_clusters.sort_values('frequency', ascending=False, inplace=True)
        
#         # plot the histogram
#         if min_ is None: 
#             min_ = df_clusters.frequency.quantile(0.95) 
#         if max_ is None: 
#             max_ = df_clusters.frequency.max()
#         if bins is None:
#             bins = int((max_ - min_)/10)
            
#         figure, ax= plt.subplots(figsize=(10,3))
#         subset = df_clusters[(df_clusters.frequency> min_)&(df_clusters.frequency< max_)].frequency
#         xint = range(math.floor(min_), math.ceil(max_)+1)
#         plt.xticks(xint)
#         plt.title(name)
#         plt.xlabel('Cluster Size')
#         plt.hist(subset, bins=bins)
# LocalBusiness = EntityCluster('Local Business', df_filtered)
# cluster_statistics_LB = LocalBusiness.create_cluster_statistics()
# cluster_plot_LB = LocalBusiness.plot_histogram(min_=0, max_=30, bins=30)
# cluster_statistics_LB<jupyter_output><empty_output><jupyter_text>Distribution of Matching Entities over Tables<jupyter_code>df_filtered = df2.reset_index(drop=True)
# create a dataframe storing cluster ids
numbers = df_filtered['telephoneNorm'].unique()

dict_id = {}
i = 1
for number in numbers:
    dict_id[number] = i
    i += 1

tmp = pd.DataFrame.from_dict(dict_id, orient='index')
tmp.reset_index(inplace=True)
tmp.columns = ['telephoneNorm','cluster_id']
df_clean = df_filtered.merge(tmp, on='telephoneNorm')#[['cluster id','origin','row_id','page_url']]
#df_clean.columns = ['cluster_id','table_id','row_id','page_url']
#df_clean.to_json('MFile_cleaned', compression='gzip', orient='records', lines=True)
df_clean=df_clean.rename(columns={'origin':'table_id'})
df_clean
tmp = pd.DataFrame(df_clean.groupby(['cluster_id'])['table_id'].nunique()).reset_index()
tmp.columns = ['cluster_id','clusterNtables']
df_final = df_clean.merge(tmp, on='cluster_id', how='left')
tmp = pd.DataFrame(df_final.groupby(['table_id'])['cluster_id'].nunique()).reset_index()
tmp.columns = ['table_id','tableNclusters']
df_final = df_final.merge(tmp, on='table_id', how='left').sort_values(by='tableNclusters', ascending=False)
df_final.sort_values('clusterNtables')
print(df_final["telephoneNorm"].nunique())
df_final["table_id"].nunique()
# convert telephoneNorm to int64 for further processsingb
df_final["telephoneNorm"] = df_final["telephoneNorm"].astype(np.int64)
file = '20-12-21_ClusterFile_withCleaning'
destination_path = r"../../../src/data/LocalBusiness"
df_final.to_json(os.path.join(destination_path, '{}'.format(file)), compression='gzip', orient='records', lines=True)
unique_cluster =df_final.drop_duplicates('cluster_id', keep='first')[df_final['clusterNtables']>=8]
plt.hist(unique_cluster['clusterNtables'], bins=10)
plt.xlabel('number of tables')
plt.ylabel('number of clusters')
unique_table =df_final.drop_duplicates('table_id', keep='first')[df_final['clusterNtables']>=8][['table_id', 'tableNclusters']]

plt.hist(unique_table['tableNclusters'])
plt.xlabel('number of clusters')
plt.ylabel('number of tables')<jupyter_output>/tmp/ipykernel_644278/927170451.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
  unique_table =df_final.drop_duplicates('table_id', keep='first')[df_final['clusterNtables']>=8][['table_id', 'tableNclusters']]
{'fraction_non_alphanumeric': 0.14606299212598425, 'fraction_numerical': 0.016830708661417323, 'mean_word_length': 4.157868020304568, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 36, 'https://': 1, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '474499', 'license': 'MIT', 'n_tokens_mistral': 3417, 'n_tokens_neox': 3300, 'n_words': 627, 'path': 'notebooks/Entity/LocalBusiness/Preprocessing/Entity_LB_Phase1a_Cluster Cleaning.ipynb', 'repo_name': 'JenniferHahn/data_integration_using_deep_learning', 'size': '10160'}