def invented_in(): ORDER = [sid('Patent'), sid('Location'), 'type', 'by'] rawinventor = from_sql('rawinventor', True, ['rawlocation_id', 'patent_id', 'inventor_id']) rawlocation = from_sql('rawlocation', True, ['id', 'location_id']) patent_location = rawinventor.merge(rawlocation, left_on=['rawlocation_id'], right_on=['id']).dropna(subset=['patent_id', 'inventor_id', 'location_id']) patent_location.rename(columns={'patent_id':sid('Patent'), 'inventor_id': 'by', 'location_id': sid('Location')}, inplace=True) patent_location['type'] = 'INVENTED_IN' output_tsv(patent_location, rel_file('invented_in'), ORDER)
def inventor_from(): ORDER = [sid('Inventor'), sid('Location'), 'type'] inventor_locs = from_sql('location_inventor').dropna() inventor_locs.rename(columns={'location_id':sid('Location'), 'inventor_id':sid('Inventor')}, inplace=True) inventor_locs['type'] = 'FROM' output_tsv(inventor_locs, rel_file('inventor_from'), ORDER)
def assignee_from(): ORDER = [sid('Assignee'), sid('Location'), 'type'] assignee_locs = from_sql('location_assignee').dropna() assignee_locs.rename(columns={'location_id':sid('Location'), 'assignee_id':sid('Assignee')}, inplace=True) assignee_locs['type'] = 'FROM' output_tsv(assignee_locs, rel_file('assignee_from'), ORDER)
def represented(): ORDER = [sid('Lawyer'), sid('Patent'), 'type'] patent_lawyer = from_sql('patent_lawyer') patent_lawyer.rename(columns={'patent_id':sid('Patent'), 'lawyer_id':sid('Lawyer')}, inplace=True) patent_lawyer['type'] = 'REPRESENTED' output_tsv(patent_lawyer, rel_file('represented'), ORDER)
def assigned_to(): ORDER = [sid('Patent'), sid('Assignee'), 'type'] patent_assignee = from_sql('patent_assignee') patent_assignee.rename(columns={'patent_id':'id:string:Patent', 'assignee_id':'id:string:Assignee'}, inplace=True) patent_assignee['type'] = 'ASSIGNED_TO' output_tsv(patent_assignee, rel_file('assigned_to'), ORDER)
def subclasses(): subclasses = from_sql('uspc')[['mainclass_id', 'subclass_id']].drop_duplicates().dropna() subclasses['id:string:Subclass'] = subclasses.apply(lambdas['concat_class'], axis=1) subclasses = subclasses.drop('subclass_id', 1) subclasses['l:label'] = 'Subclass' subclasses = subclasses.drop_duplicates(cols=['id:string:Subclass']) output_tsv(subclasses, node_file('subclasses'))
def cites(): # Manual query because pulling all fields was too slow... citations = from_sql('uspatentcitation', True, ['patent_id', 'citation_id']).dropna() # Make sure to order them properly... citations.columns = [sid('Patent'), sid('Patent')] citations['type'] = 'CITES' # No order needed because we've whittled it down to only the fields we want output_tsv(citations, rel_file('cites'))
def patents(): patents = from_sql('patent', ['id', 'date', 'abstract', 'title']) patents.rename(columns={'id':'id:string:Patent'}, inplace=True) patents['l:label'] = 'Patent' patents['year:int'] = patents['date'].apply(lambda x: x.split("-")[0]) patents['month:int'] = patents['date'].apply(lambda x: x.split("-")[1]) patents['day:int'] = patents['date'].apply(lambda x: x.split("-")[2]) output_tsv(patents, node_file('patents'))
def classified_as(): ORDER = [sid('Patent'), sid('Subclass'), 'type'] uspc = from_sql('uspc')[['mainclass_id', 'subclass_id', 'patent_id']].drop_duplicates().dropna() uspc['id:string:Subclass'] = uspc.apply(lambdas['concat_class'], axis=1) uspc.rename(columns={'patent_id':sid('Patent')}, inplace=True) uspc = uspc.drop('subclass_id', 1) uspc['type'] = 'CLASSIFIED_AS' output_tsv(uspc, rel_file('classified_as'), ORDER)
def subclasses(): ORDER = [sid('Subclass'), sid('Class'), 'type'] subclasses = from_sql('uspc')[['mainclass_id', 'subclass_id']].drop_duplicates().dropna() subclasses['id:string:Subclass'] = subclasses.apply(lambdas['concat_class'], axis=1) subclasses = subclasses.drop('subclass_id', axis=1) subclasses.rename(columns={'mainclass_id':'id:string:Class'}, inplace=True) subclasses['type'] = 'SUBCLASSES' output_tsv(subclasses, rel_file('subclasses'), ORDER)
def locations(): locs = from_sql('location').dropna(subset=['country']) locs['country'] = locs['country'].apply(lambda x: x.upper()) locs.rename(columns={ 'id':'id:string:Location', 'city':'city:string:Location', 'country':'country:string:Location', 'state':'state:string:Location' }, inplace=True) locs['l:label'] = 'Location' output_tsv(locs, node_file('locations'))
def assignees(): assignees = from_sql('assignee') assignees.rename(columns={'id':'id:string:Assignee'}, inplace=True) assignees['l:label'] = 'Assignee' output_tsv(assignees, node_file('assignees'))
def inventors(): inventors = from_sql('rawinventor').drop_duplicates(cols=['inventor_id']) inventors = inventors.drop(['patent_id', 'rawlocation_id', 'sequence'], 1) inventors.rename(columns={'inventor_id':'id:string:Inventor'}, inplace=True) inventors['l:label'] = 'Inventor' output_tsv(inventors, node_file('inventors'))
def lawyers(): lawyers = from_sql('lawyer') lawyers = lawyers.drop('country', 1) lawyers['l:label'] = 'Lawyer' lawyers.rename(columns={'id':'id:string:Lawyer'}, inplace=True) output_tsv(lawyers, node_file('lawyers'))
def main_classes(): classes = from_sql('mainclass').dropna(subset=['title']) classes = classes.drop('text', 1) # it's empty for all entries right now... classes['l:label'] = 'Class' classes.rename(columns={'id':'id:string:Class'}, inplace=True) output_tsv(classes, node_file('classes'))