<jupyter_start><jupyter_text>**Import and meta**<jupyter_code>from google.cloud import bigquery
from open_patstat.plots import bar_chart, stacked_bar_chart
from open_patstat.documentation.shortdoc import ShortDoc, print_markdown, print_signature

import plotly.plotly as py
import plotly.graph_objs as go
import plotly.io as pio
import plotly.figure_factory as ff
import pandas as pd
import os
import pycountry
import colorlover as cl
data_path = '../data/'
plots_path = '../plots/'
views_path = '../views/'<jupyter_output><empty_output><jupyter_text>[Credentials]:https://cloud.google.com/bigquery/docs/quickstarts/quickstart-client-librariesclient-libraries-install-pythonTo instantiate your Client, you need to set the environment variable `GOOGLE_APPLICATION_CREDENTIALS` to the file path of the JSON file that contains your service account key. Follow the steps described [here][credentials]. Readers only interested in existing output can comment the `client` instantiation and execute the rest of the notebook using the aggregate data provided in `/views`.<jupyter_code>documentation = ShortDoc()
client = bigquery.Client()<jupyter_output><empty_output><jupyter_text>---- **What is this notebook for?** The Application table (`TLS201_APPLN`) is PATSTAT's central table. It contains the key bibliographical data elements relevant to identify the patent application. Most of the elements in this table can be found on the first page of a printed patent document. It also links to many other database tables.This notebook documents the 101 of the Patstat "Application" table (aka `TLS201_APPLN`, `PATSTAT2016a`).More precisely:- we report variable descriptions and encoding details from the `Data Catalog for Patstat`(5.07)- we provide summary statistics and visualizations (when relevant) **Data**<jupyter_code>print_markdown(documentation.version())<jupyter_output><empty_output><jupyter_text>**Output** [db-logo]: https://aem.dropbox.com/cms/content/dam/dropbox/www/en-us/branding/app-dropbox-windows@2x.png.transform/half-res/img.png[db]:https://www.dropbox.com/sh/b1gs90gtoduu02v/AABICBNYH2kysjX-4JTqee0Wa?dl=0`plots/` and `views/` are not available on our GitHub repository. However, interested readers can access those files on our dedicated dropbox. [![alt text][db-logo]][db] ---- **TLS201_APPLN Schema**<jupyter_code>print_markdown(documentation.desc_table('TLS201_APPLN'))
table_ref = client.dataset('raw').table('tls201_cp')
table = client.get_table(table_ref)
schema = table.schema
schema = [[schema[i].name, schema[i].field_type, schema[i].mode] for i in range(len(schema))]
pd.DataFrame(schema, columns=['Name', 'Type', 'Mode']).set_index('Name')<jupyter_output><empty_output><jupyter_text>----  Technical identifier  APPLN_ID<jupyter_code>print_markdown(documentation.desc_variable('APPLN_ID'))<jupyter_output><empty_output><jupyter_text>Note: 1. Range 1: 1 to 900 000 000. Filed applications which have a related publication in DOCDB. UNique but not sequential.2. Range 2: from 900 000 001 to 930 000 000. Artificial applications which are created in PATSTAT for prior applications, claimed as *priorities*, which do not have an application-reference in DOCDB.3. Range 3: 930 000 001 to 960 000 000. Artificial filing applications with kind code `D2` which are created in PATSTAT for those artificial publications which are also created in PATSTAT because these *publications* are cited, but do not have a publication-reference in DOCDB.3. Range 4: 960 000 001 to 999 999 999. Artificial filing applications with kind code `D3` which are created in PATSTAT because these *applications* are cited.  ```pythonquery = """SELECT  year,  appln_auth,  COUNT(*) AS nb_range,  CASE    WHEN appln_id BETWEEN 1 AND 900000000 THEN "range_1"    WHEN appln_id BETWEEN 900000001 AND 930000000 THEN "range_2"    WHEN appln_id BETWEEN 930000001 AND 960000000 THEN "range_3"    WHEN appln_id BETWEEN 960000001 AND 999000000 THEN "range_4"  END AS rangeFROM  raw.tls201_cpGROUP BY  year,  appln_auth,  nb_rangeORDER BY  year,  appln_auth;"""client.query(query=query).to_dataframe().to_csv(views_path + '201_ApplnId_byRange.csv')```<jupyter_code>df = pd.read_csv(views_path + '201_ApplnId_byRange.csv', index_col=0)
# Overall range distribution
tmp = df.groupby(['range']).sum()['nb_range']

fig = bar_chart(tmp, title='ApplicationId by Range')
pio.write_image(fig, plots_path + '/201_ApplnId_byRange.png')
#py.iplot(fig, filename='201_ApplnId_byRange')
# Range distribution by year
tmp = df.groupby(['year', 'range']).sum()['nb_range'].to_frame().reset_index('range')
fig = stacked_bar_chart(tmp, 'range', 'nb_range', title='ApplnId by range and year')
pio.write_image(fig, plots_path + '/201_ApplnId_byRangeYear.png', width=1200, height=800)
#py.iplot(fig, filename='201_ApplnId_byRange')<jupyter_output><empty_output><jupyter_text>Business identifiers  APPLN_AUTH<jupyter_code>print_markdown(documentation.desc_variable('APPLN_AUTH'))<jupyter_output><empty_output><jupyter_text>```pythonquery = """SELECT     appln_auth,    year,    count(appln_auth) as nb_patentsFROM    raw.tls201_cpGROUP BY     appln_auth,    yearORDER BY    appln_auth,    year;"""client.query(query=query).to_dataframe().to_csv(views_path + '201_ApplnAuth_byYear.csv')```<jupyter_code>tmp_path = '/201_ApplnAuth_byYearAuth'
df_org = pd.read_csv(data_path + 'tls801_part01.txt')
df = pd.read_csv(views_path + '/201_ApplnAuth_byYear.csv', index_col=0)
# Overall applications by auth since the 1970s
threshold = 1970
tmp = df.query('year>=@threshold').groupby('appln_auth').sum().merge(df_org, how='left',
                                                                     left_index=True, right_on=['ctry_code']).set_index(
    'st3_name')
fig = bar_chart(tmp['nb_patents'].sort_values(ascending=False).iloc[:15],
                title='Number of Applications by ApplnAuth since {}'.format(threshold))
pio.write_image(fig, plots_path + '/201_ApplnAuth_byAuth70s.png', width=1200, height=800)
#  py.iplot(fig)

# Application by auth over time
for cnt_code in df.appln_auth.unique():
    try:  # some organizations don't have names ...
        cnt_name = df_org.query('ctry_code == @cnt_code')["st3_name"].values[0]
    except:
        pass
    tmp = df.dropna().set_index('year').query('appln_auth==@cnt_code')['nb_patents']
    # be careful; there is a large number of year=nan for some countries (ex: DE, CA)
    if sum(tmp) > 1e6:
        fig = bar_chart(tmp, title='Applications by year in {}'.format(cnt_name))
        pio.write_image(fig,
                        plots_path + tmp_path + '/201_ApplnAuth_{}_byYear.png'.format(cnt_code),
                        width=1200, height=800)<jupyter_output><empty_output><jupyter_text>APPLN_NR<jupyter_code>print_markdown(documentation.desc_variable('APPLN_NR'))<jupyter_output><empty_output><jupyter_text>Note: Ends with A, D, K, T or X to create "dummy" application numbers that are present because the number is mandatory but the actual number is not known.- A: data errors- D: dummy application; the publication number is put in front of the D- K: special type of older Brazilian application (number format 11nnnnnK )- T: dummy technical priority- X: dummy pre-1970 derived priority ```pythonquery = """SELECT    year,  COUNT(*) AS nb_dummy,  CASE    WHEN SUBSTR(appln_nr, -1)="A" THEN "data errors"    WHEN SUBSTR(appln_nr, -1)="D" THEN "dummy application"    WHEN SUBSTR(appln_nr, -1)="T" THEN "technical priority"    WHEN SUBSTR(appln_nr, -1)="X" THEN "pre-1970 derived priority"    ELSE "no dummy"  END AS dummyFROM  raw.tls201_cpGROUP BY    year,    appln_auth,  dummy;  ORDER BY    year;    appln_auth;""" feel-free to uncomment to get year and appln_auth detailsclient.query(query=query).to_dataframe().to_csv(views_path + '201_ApplnNR_byDummy.csv')```<jupyter_code>df = pd.read_csv(views_path + '201_ApplnNR_byDummy.csv', index_col=0)
tmp = df.set_index('dummy')['nb_dummy'].sort_values(ascending=False)
fig = bar_chart(tmp, title='ApplnNr by Dummy')
pio.write_image(fig, plots_path + '201_ApplnNR_byDummy.png')<jupyter_output><empty_output><jupyter_text>APPLN_KIND<jupyter_code>print_markdown(documentation.desc_variable('APPLN_KIND'))<jupyter_output><empty_output><jupyter_text>Note:- A: patent- U: utility model- F: design patent- P: provisional application- W: PCT application (in the international phase)- T: used by some offices (e. g. AT, DE, DK, ES, GR, HR, PL, PT, SI, SM, TR) for applications which are "translations" of granted PCT or EP applications- D2, D3: artificial applications- D,K,L,M,N: dummy for de-duplicating- Other values are used temporarily to resolve minor problems that would otherwise have prevented the application to be recorded in DOCDB ```pythonquery = """SELECT  year,  appln_auth,  count(*) AS nb_kind,  CASE    WHEN appln_kind="A " THEN "Patent"    WHEN appln_kind="U " THEN "Utility model"    WHEN appln_kind="F " THEN "Design patent"    WHEN appln_kind="P " THEN "Provisional application"    WHEN appln_kind="W " THEN "PCT application"    WHEN appln_kind="T " THEN "Translation"    WHEN appln_kind="D2" OR appln_kind="D3" THEN "Translation"    WHEN appln_kind="D "  OR appln_kind="K "  OR appln_kind="L "  OR appln_kind="M "  OR appln_kind="N " THEN "Dummy"    ELSE "Temp"  END AS kindFROM  raw.tls201_cpGROUP BY  year,  appln_auth,  kindORDER BY  year,  appln_auth,  kind;"""client.query(query).to_dataframe().to_csv(views_path+'201_ApplnKind_byYearAuth.csv')```<jupyter_code>df = pd.read_csv(views_path + '201_ApplnKind_byYearAuth.csv',
                 index_col=0)

# Overall ApplnKind
tmp = df.groupby('kind').sum()['nb_kind'].sort_values(ascending=False)
fig = bar_chart(tmp, title='Appln kind')
pio.write_image(fig, plots_path + '201_ApplnKind.png')

# ApplnKind by Year
tmp = df.dropna().set_index('year')
fig = stacked_bar_chart(tmp, 'kind', 'nb_kind', title='Appln kind by year')
pio.write_image(fig, plots_path + '201_ApplnKind_byYear.png',
                width=1200, height=800)<jupyter_output><empty_output><jupyter_text>PC: Could look by cnt as well.  APPLN_FILING_DATE<jupyter_code>print_markdown(documentation.desc_variable('APPLN_FILING_DATE'))<jupyter_output><empty_output><jupyter_text>APPLN_FILING_YEAR<jupyter_code>print_markdown(documentation.desc_variable('APPLN_FILING_YEAR'))<jupyter_output><empty_output><jupyter_text>PC: Partitioning is derived from the `APPLN_FILING_YEAR`. Many prior queries can help us retrieve the distribution of patents per year at 0 cost.<jupyter_code>df = pd.read_csv(views_path + '/201_ApplnAuth_byYear.csv', index_col=0)
tmp = df.groupby('year').sum()['nb_patents']
fig = bar_chart(tmp, title='Appln filing by Year')
pio.write_image(fig, plots_path + '201_ApplnFilingYear_byYear.png',
               width=1200, height=800)<jupyter_output><empty_output><jupyter_text>APPLN_NR_EPODOC<jupyter_code>print_markdown(documentation.desc_variable('APPLN_NR_EPODOC'))<jupyter_output><empty_output><jupyter_text>PC: can be used to retrieve other variables (see p 96)  APPLN_NR_ORIGINAL<jupyter_code>print_markdown(documentation.desc_variable('APPLN_NR_ORIGINAL'))<jupyter_output><empty_output><jupyter_text>Note: Typically these numbers do not contain the country code. In about 10% of the applications no original application number is known.Comment: This attribute is useful to combine application data of PATSTAT with other databases which also contains the original application number.  IPR_TYPE<jupyter_code>print_markdown(documentation.desc_variable('IPR_TYPE'))<jupyter_output><empty_output><jupyter_text>Note: Derived from `APPLN_KIND` (see p 169)    - PI: Patent of innovation - UM: Utility Model- DP: Design Patent *TODO*: - Query by year, auth and ipr_type only.- Clean code<jupyter_code>df_ipr = pd.read_csv(views_path + '/ipr_yr_auth.csv', index_col=0)
df_ipr.query('appln_auth =="FR"').head()
def plot_ipr_type(df: pd.DataFrame, cnt: str, path: str = None):
    tmp = df_ipr.query('appln_auth == @cnt').dropna()
    try:
        cnt_name = pycountry.countries.get(alpha_2=cnt).name
    except:
        cnt_name = cnt_a2_name[cnt]
    data = []
    i = 0
    for ipr in tmp.groupby('ipr_type').sum()["nb_ipr"].sort_values(
            ascending=False).index.unique():
        data += [
            go.Bar(
                x=tmp.query('ipr_type == @ipr')["date"].values,
                y=tmp.query('ipr_type == @ipr')["nb_ipr"].values,
                name=ipr,
                marker=dict(color=cl.flipper()['seq']['3']['Reds'][i], ))
        ]
        i += 1
    layout = go.Layout(
        barmode='stack',
        title='Composition of patent applications in {}'.format(cnt_name))
    fig = go.Figure(data=data, layout=layout)
    pio.write_image(fig, path + '/{}_ts_patents.png'.format(cnt))
    #py.iplot(fig, filename='stacked-bar', )<jupyter_output><empty_output><jupyter_text>no_match = []for cnt in df_ipr.appln_auth.unique():    if df_pat_yrcnt.query(            'appln_auth == @cnt')['nb_patents'].dropna().sum() > 1e5:        try:            plot_ipr_type(df_ipr, cnt, plots_path + 'ipr_type/')        except:            no_match += [cnt]    else:        pass<jupyter_code>## <font color=grey>INTERNAT_APPLN_ID</font><jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('INTERNAT_APPLN_ID'))<jupyter_code># Route of the application
<font color=orange>*TODO*</font>: 

- Summarize : http://www.wipo.int/pct/en/guide/ip03.html#_chapt3 and p 155 (5.07)
- Funnel chart ? From national to international even if the route goes the other way round. Still don't know what to do with that info.

```python
query="""SELECT
  year,
  appln_auth,
  int_phase,
  reg_phase,
  nat_phase,
  COUNT(*) AS nb_phase
FROM
  raw.tls201_cp
GROUP BY
  year,
  appln_auth,
  int_phase,
  reg_phase,
  nat_phase
ORDER BY
  year,
  appln_auth;"""

client.query(query).to_dataframe().to_csv(views_path + '201_Phases.csv')
```<jupyter_output><empty_output><jupyter_text>df = pd.read_csv(views_path + '201_Phases.csv', index_col=0) df.groupby(['int_phase', 'reg_phase', 'nat_phase']).sum()['nb_phase'].to_frame() df.groupby(['int_phase']).sum()['nb_phase'].to_frame() df.groupby(['reg_phase']).sum()[['int_phase', 'nb_phase']] df.groupby(['nat_phase']).sum()[['int_phase', 'nb_phase']]<jupyter_code>### INT_PHASE<jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('INT_PHASE'))<jupyter_code>Note: These indicators provide a somewhat *simplistic* approach to identify the route an application has taken. This is the result of interpretations and assumptions for which no responsibility whatsoever can be accepted.

WARNING: These indicators only help to understand applications which actually exist in PATSTAT. It does not help to answer questions like “How many EP applications are valid in country x”, because not every office publishes patents which are validated / granted in their country. Consequently, there is no publication or application in PATSTAT for every granted patent. The same will apply for the Unitary Patents, if there is no publication for that (see p 154).
### REG_PHASE<jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('REG_PHASE'))<jupyter_code>### NAT_PHASE<jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('NAT_PHASE'))<jupyter_code># Data from priorities
## <font color=grey>EARLIEST_FILING_DATE</font><jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('EARLIEST_FILING_DATE'))<jupyter_code><font color=grey>Note: Only directly related applications are considered; this is unlike the INPADOC family, where applications might also be indirectly related.</font>
## <font color='grey'>EARLIEST_FILING_YEAR</font><jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('EARLIEST_FILING_YEAR'))<jupyter_code>## <font color=grey>EARLIEST_FILING_ID</font><jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('EARLIEST_FILING_ID'))<jupyter_code><font color=grey>Note: If multiple applications have been filed on the earliest filing date, then conceptually any of these applications can be regarded as the earliest application. Nevertheless, the logic to determine the application which has been filed first is like this:

1. If there is a PCT application which was filed on the earliest application date, then the APPLN_ID of this PCT application is taken as the EARLIEST_FILING_ID.
2. Else: If there are 1 or more Paris convention priorities which were filed on the earliest application date, then the Paris convention priority with the smallest APPLN_ID is taken as the EARLIEST_FILING_ID.
3. Else: the application which was filed on the earliest application date with the smallest APPLN_ID will be taken.
</font>
# Data from publications
<font color=orange>TODO</font>: 

- count by YEAR and AUTH
- avg(year) between EARLIEST_FILING_DATE and EARLIEST_PUBN_DATE
## <font color='grey'>EARLIEST_PUBLN_DATE</font><jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('EARLIEST_PUBLN_DATE'))<jupyter_code>## <font color='grey'>EARLIEST_PUBLN_YEAR</font><jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('EARLIEST_PUBLN_YEAR'))<jupyter_code>## <font color='grey'>EARLIEST_PAT_PUBLN_ID</font><jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('EARLIEST_PAT_PUBLN_ID'))<jupyter_code># Data derived from publications
<font color=orange>TODO</font>: 

- clean code
## GRANTED<jupyter_output><empty_output><jupyter_text>print_markdown(documentation.desc_variable('GRANTED'))<jupyter_code>Note: This variable is the result of interpretations and assumptions for which no responsibility whatsoever can be accepted.
```python
query = """
SELECT
  granted,
  year,
  appln_auth,
  COUNT(*) AS nb_grant
FROM
  raw.tls201_cp
#WHERE
#  date>'1900-01-01'
GROUP BY
  granted,
  appln_auth,
  year
ORDER BY
  granted,
  year,
  appln_auth
  ;"""
df_granted = client.query(query).to_dataframe()
df_granted.to_csv(views_path + '/grant_yr_auth.csv')
```<jupyter_output><empty_output><jupyter_text>df_granted = pd.read_csv(views_path + '/grant_yr_auth.csv', index_col=0) def plot_grant(df: pd.DataFrame, cnt: str, path: str = None):    tmp = df.query('appln_auth == @cnt').dropna()    try:        cnt_name = pycountry.countries.get(alpha_2=cnt).name    except:        cnt_name = cnt_a2_name[cnt]    data = []    for boo in [True, False]:        i = 0 if boo == False else -1        data += [            go.Bar(                x=tmp.query('granted == @boo')["year"].values,                y=tmp.query('granted == @boo')["nb_grant"].values,                name=boo,                marker=dict(color=cl.scales['3']['div']['RdBu'][i], ))        ]    layout = go.Layout(        barmode='stack', title='Patent grants in {}'.format(cnt_name))    fig = go.Figure(data=data, layout=layout)    pio.write_image(fig, path + '/{}_ts_grants.png'.format(cnt))<jupyter_code>no_match = []
for cnt in df_granted.appln_auth.unique():
    if df_pat_yrcnt.query(
            'appln_auth == @cnt')['nb_patents'].dropna().sum() > 1e5:
        try:
            plot_grant(df_granted, cnt, plots_path + 'pat_grant/')
        except:
            no_match += [cnt]
    else:
        pass<jupyter_output><empty_output><jupyter_text>Family data  DOCDB_FAMILY_ID<jupyter_code>print_markdown(documentation.desc_variable('DOCDB_FAMILY_ID'))<jupyter_output><empty_output><jupyter_text>Note: 0 indicates that the application does not belong to any DOCDB family. This is only the case for the dummy application (APPLN_ID = 0) and for artificial applications (APPLN_ID ≥ 900 000 000). See more p 132.  INPADOC_FAMILY_ID<jupyter_code>print_markdown(documentation.desc_variable('INPADOC_FAMILY_ID'))<jupyter_output><empty_output><jupyter_text>Note: Much patent research is affected by the “family” concepts. There are various definitions of how to link different patents into “families”. This INPADOC extended priority family was developed by the INPADOC organisation before it was integrated into the EPO.  DOCDB_FAMILY_SIZE<jupyter_code>print_markdown(documentation.desc_variable('DOCDB_FAMILY_SIZE'))<jupyter_output><empty_output><jupyter_text>TODO: - distribution (requires external info to trace the earliest filing appn such as YEAR and AUTH)  NB_CITING_DOCDB_FAM TODO: - distribution (requires external info to trace de earliest filing appn such as YEAR and AUTH)- interactions between family size and nb_citing (dist plot) https://plot.ly/ipython-notebooks/2d-kernel-density-distributions/<jupyter_code>print_markdown(documentation.desc_variable('NB_CITING_DOCDB_FAM'))<jupyter_output><empty_output><jupyter_text>Aggregated data  NB_APPLICANTS TODO: distribution + Think about longitudinal dimension (YEAR, AUTH)WARNING: How to avoid duplicates ? -> Group by earliest_pat_publn_date, DOCDB family.<jupyter_code>print_markdown(documentation.desc_variable('NB_APPLICANTS'))<jupyter_output><empty_output><jupyter_text>NB_INVENTORS TODO: distribution + Think about longitudinal dimension (YEAR, AUTH)WARNING: How to avoid duplicates ? -> Group by earliest_pat_publn_date, DOCD family.<jupyter_code>print_markdown(documentation.desc_variable('NB_INVENTORS'))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11517710532488858, 'fraction_numerical': 0.019000703729767768, 'mean_word_length': 5.636363636363637, 'pattern_counts': {'":': 0, '<': 181, '<?xml version=': 0, '>': 188, 'https://': 4, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 4, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '39', 'dir': 'jupyter-structured-clean-dedup', 'id': '295344', 'license': 'Apache-2.0', 'n_tokens_mistral': 7559, 'n_tokens_neox': 7005, 'n_words': 1712, 'path': 'EDA/.ipynb_checkpoints/tls201-checkpoint.ipynb', 'repo_name': 'rezaho/usptobias', 'size': '21315'}