<jupyter_start><jupyter_text>Schema Alignment Example<jupyter_code>import keras
import urllib2

import pandas as pd

from hashlib import md5
from pprint import pprint
from bs4 import BeautifulSoup
from sklearn.cluster import DBSCAN

import sys
sys.path.append('/Users/BenJohnson/projects/what-is-this/wit/')
from wit import *<jupyter_output>Using Theano backend.<jupyter_text>TaskWe attempt to approach the problem of "schema alignment": given two datasets that contain roughly the same kinds of data, merge them into a single dataset such that columns are appropriately aligned.  This is a generic problem that comes up when we're trying to merge data, but in this example we explore it's application to data attained via web scraping. Specifically, we'll try to do this with data scraped from message board forums, which contains data types such as    username    post body    signature    date    ...So the task is to align the username field in the first dataset with the username field in the second dataset, etc.  The usual approach is to write/configure a parser for each schema, which includes a manual mapping of data fields from the HTML page to  DataWe'll use data scraped by <NAME> from     remingtonsociety.com    marauderairrifle.comA post in the first set looks like:```html                    Noel                         Joined: Tue Jun 26, 2007 8:48 pm                  Posts:  112                                                                                             Remington 12A wanted                            Looking for a 12A with condition. Bore must be very good plus, external at 80% or better please.                            ```A post in the second set looks like:```html                                                                    Re: Is this what we need for 22 M-rod accuracy?                                                                                            by                                                             RayK                                                     » Tue May 28, 2013 9:18 pm                                         Thanks for the new photos!  Those are great and clearly show the idea.Mine currently look just like that... Ray                                        25 W-rod | 8 shots @ 70 FPE -2% | 3100 fill25 M-rod                                                        RayK                                    Posts: 5311            Joined: Sat Jun 05, 2010 3:19 pm                                            Top                                ```They have somewhat similar structure, and actually look like maybe they were generated by a different configuration of the same forum building software.  However, they're sufficiently different that they cannot be parsed with the same CSS selectors or regular expressions. ApproachThere are a few stages to this problem:    a) Finding 'atomic data elements' in the raw HTML page.          - i.e. separating the posts on a page that has multiple posts        b) Finding the 'data fields' within an atomic data element        - i.e. finding the locations in the DOM that contain data points we're interested in capturing.        c) Merging 'data fields' across schemaa) and b) are difficult in their own right, and we implement simple, non-optimal solutions.  For a), we totally punt and just do it manually.  For b):    - find all nodes that contain text in the corpus of posts    - find CSS paths of the format            tag1[class1, ...] > tag2[class2, ...] > ... > tag_n[class_m, ...]          to each of these nodes.  From here on, when we say "CSS path", we mean CSS path w/ classes, w/o indices.    - assume data in nodes with the same CSS path are "equivalent".  The last step is an assumption that could be wrong in both directions: data elements with the same CSS path could be different latent variables, and data elements with different CSS paths could be the same latent variables.  These issues could be mitigated by applying more advanced wrapper induction techniques, but they do not turn out to be too damaging to the current approach.  In fact, the process of merging two schemas also merges classes within the same schema (for better or for worse). Set parameters<jupyter_code>num_features = 75  # in characters
max_len      = 350 # in characters
formatter    = KerasFormatter(num_features, max_len)<jupyter_output><empty_output><jupyter_text>Load data from two forums with different schemas.```origin : domain that record comes fromhash   : md5 hash of the path to the elements in the DOM (including classes)id     : record number within origin + md5 hash of origin fieldobj    : text content of DOM nodesrc    : corresponding raw HTML markup```<jupyter_code>df = pd.read_csv('data/simple-forum-dataset.csv')
df.tail()<jupyter_output><empty_output><jupyter_text>Make training set, which will consist of triplets of points    (anchor, positive, negative)        where     - anchor and positive are of the same class    - anchor and negative are of different classes    We'll minimize the loss function    max(0, distance(anchor, positive) - distance(anchor, negative) + margin)In this case, we use    distance(x, y) = 1 - cosine_similarity(x, y)but     distance(x, y) = euclidean_distance(x, y)    also seems to work in general.<jupyter_code>train     = make_triplet_train(df, N = 600)
trn, levs = formatter.format(train, ['obj'], 'hash')<jupyter_output>[92m  + 459fb-1706c[0m
[92m  + 520fa-1706c[0m
[92m  + 9ead9-1706c[0m
[92m  + 34883-1706c[0m
[92m  + 2e98d-1706c[0m
[92m  + 2bb6e-1706c[0m
[92m  + bf07b-1706c[0m
[92m  + 8fbdb-1706c[0m
[92m  + a1c8c-48774[0m
[92m  + e5316-48774[0m
[92m  + 3122c-48774[0m
[92m  + df7d2-48774[0m
[92m  + 5fd24-48774[0m
[92m  + 58faa-48774[0m
[92m  + 0dba3-48774[0m
[92m  + 9acc3-48774[0m
[92m  + 6d664-48774[0m
[92m  + 20a50-48774[0m
[92m  + b9976-48774[0m<jupyter_text>Define and train the model.  This should take several minutes on the current dataset.<jupyter_code>classifier = TripletClassifier(trn, levs)
classifier.fit(batch_size = 250, nb_epoch = 3)<jupyter_output><empty_output><jupyter_text>Create set of all unique records (without duplicates), then project them using the model.  The projection should take tens of seconds.<jupyter_code>unq = df.copy()
del unq['id']
unq = unq.drop_duplicates()

awl, _ = formatter.format(unq, ['obj'], 'hash')
preds  = classifer.predict(awl['x'][0], verbose = True)<jupyter_output>14314/14314 [==============================] - 13s<jupyter_text>Cluster the projections using DBSCAN, then interpret the cluster to be the aligned datatypes.  Though the string similarity was performed on just the HTML text content, we show the classes with HTML markup so we can easily verify the cluster assignments.  Notice that some of the clusters include hashes from both schemas, while others only include hashes from a single schema -- this is desired behavior, as there is not a 1-to-1 mapping between the two schemas.<jupyter_code>db = DBSCAN(eps = .1, min_samples = 50).fit(preds)

# Observation cluster assignments by hash
res         = unq.hash.groupby(db.labels_).apply(lambda x: x.value_counts()).reset_index()
res.columns = ('cluster', 'hash', 'cnt')

# Discard small clusters, or points that were assigned to None cluster
good_res = res[(res.cnt > 100) & (res.cluster > -1)]
eqv = list(good_res.groupby('cluster').hash.apply(lambda x: list(x)))
eqv = map(eval, np.unique(map(str, eqv)))

print '\n-----------------------'
print 'clusters, without markup'
print_eqv(eqv, df, path = 'obj')

print '\n--------------------------------------'
print 'clusters, with markup for verification'
print_eqv(eqv, df, path = 'src')<jupyter_output>height has been deprecated.


-----------------------
clusters, without markup
[93m
 --- 

['3122c-48774', '9acc3-48774', '34883-1706c', '8fbdb-1706c']

[0m
[92m3122c-48774	(1428 rows)[0m
10253    ShawnHu wrote:How about a hammer tapping servi...
14293    I am totally against this, and I think you are...
21204    My bolt broken last night. I wanted to know if...
17490    This line of scopes have alot of potential and...
18197    HI guys, this morning I decided to try and mak...
Name: obj, dtype: object
[92m9acc3-48774	(1209 rows)[0m
13566    The trigger screws do not affect the hammer tr...
17055    thanks for the response man I have been using ...
7474     All my misses are @ close range. So I guess I ...
6539     bstaley wrote:Got a chance to try the heat shr...
15720            Shawnhu,PM just sent with my contact info
Name: obj, dtype: object
[92m34883-1706c	(975 rows)[0m
838     i have one too without markings on barrel . th...
5784    Hello, looks like a nice board to whi[...]
StarcoderdataJupyterStructuredCleanDedup