<jupyter_start><jupyter_text>http://www.gbif.org/analytics/global<jupyter_code>df = sqlContext.read.load("/guoda/data/idigbio-20190612T171757.parquet")
df.count()
year_summary = df.groupBy(year("datecollected").cast("integer").alias("yearcollected")).count().orderBy("yearcollected").persist()
year_summary.count()
year_summary.printSchema()
year_summary.describe().show()
year_summary.head(10)
year_summary.orderBy("yearcollected", ascending=False).head(10)
pandas_year_summary = year_summary.filter(year_summary.yearcollected >= 1817).filter(year_summary.yearcollected <= 2017).orderBy("yearcollected").toPandas()
pandas_year_summary.head()
plt.bar(pandas_year_summary["yearcollected"], pandas_year_summary["count"])
yc_sum = (df
          .groupBy(year("datecollected").cast("integer").alias("yearcollected"),
                   "continent")
          .count()
          .orderBy("yearcollected")
          .persist()
          )
yc_sum.head(10)<jupyter_output><empty_output><jupyter_text>We're going to have to fix some data!<jupyter_code>continents = set(["africa", "asia", "australia", "europe", "north america", "south america"])
def fix_continent(c):
    if c in continents:
        return c
    else:
        return "other"
print(fix_continent("europe"))    
print(fix_continent("oceana"))
fix_continent_udf = udf(fix_continent)

yc_sum = (df
          .withColumn("fixed", fix_continent_udf(df.continent))
          .groupBy(year("datecollected").cast("integer").alias("yearcollected"),
                   "fixed")
          .count()
          .orderBy("yearcollected")
          .persist()
          )
yc_sum.head(10)
yc_sum.groupBy(yc_sum.fixed).count().show()
yc_cross = (df
            .select(year("datecollected").cast("integer").alias("yearcollected"),
                    df.continent)
            .withColumn("fixed", fix_continent_udf(df.continent))
            .crosstab("yearcollected", "fixed")
            )
yc_cross.head(5)
pandas_yc_cross = (yc_cross
                   .filter(yc_cross.yearcollected_fixed >= 1817)
                   .filter(yc_cross.yearcollected_fixed <= 2017)
                   .orderBy(yc_cross.yearcollected_fixed)
                   .toPandas()
                   )
pandas_yc_cross.head(200)
p1 = plt.bar(pandas_yc_cross['yearcollected_fixed'], 
                 pandas_yc_cross['asia'], color='#d62728', edgecolor='none')
#p2 = plt.bar(pandas_yc_cross['yearcollected_fixed'], 
#                 pandas_yc_cross['africa'], color='#05ff05', edgecolor='none')
import pandas as pd
import numpy as np
continents_list = sorted(continents)
continents_list.insert(0, "other")
# the 100k subset has no australia stuff so graph loop fails
#continents_list.remove("australia")

# blue -> red
#colors = ["#6d6263", "#00e5c8", "#1bc4ae", "#36a395", "#51827c", "#88414a", "#a32031", "#bf0018"]

# blue -> orange
colors = ["#a75902", "#00e5c8", "#1bcda7", "#37b686", "#539f65", "#51827c", "#bf6603"]

plots = []
bottoms = pd.DataFrame(np.zeros((len(pandas_yc_cross['yearcollected_fixed']), 1)))
for c in continents_list:
    plots.append(
      plt.bar(pandas_yc_cross['yearcollected_fixed'], pandas_yc_cross[c], 
              color=colors[len(plots)], edgecolor='none',
              width=1.0, bottom=bottoms[0])
    )
    bottoms[0] += pandas_yc_cross[c]
    #print(pandas_yc_cross[c])

#print(bottoms)

# Start of WWI
plt.axvline(x=1914)

# Start of WWII
plt.axvline(x=1939)

# "1988 - October 31: President Reagan signs the NSF Authorization Act of 1988, thereby authorizing the doubling of the NSF budget over the next five years."
plt.axvline(x=1988)

plt.legend(plots, continents_list, loc=2)
plt.title("Specimens in iDigBio by Collection Year and Continent")
plt.ylabel("Number of Specimen Records")
plt.xlabel("Year")
axes = plt.gca()
axes.set_xlim([1815, 2020])
axes.set_ylim([0, 1200000])
fig = plt.gcf()
fig.set_size_inches(12, 4)<jupyter_output><empty_output><jupyter_text>GBIF!<jupyter_code>df = sqlContext.read.parquet("/guoda/data/gbif-idigbio.parquet/source=gbif/date=20160825")
df.createOrReplaceTempView("df")
renamed_cols = sqlContext.sql("""
SELECT `http://rs.tdwg.org/dwc/terms/eventDate` as eventDate,
       `http://rs.tdwg.org/dwc/terms/continent` as continent
FROM df
WHERE `http://rs.tdwg.org/dwc/terms/basisOfRecord` LIKE "%SPECIMEN%"
""").persist()
renamed_cols.count()
renamed_cols.groupBy("continent").count().orderBy("count", ascending=False).head(100)
date_group = renamed_cols.groupBy(year("eventDate").cast("integer").alias("year")).count().orderBy("year", ascending=False)
date_group.head(10)
date_group.describe().show()
# need to do some harder-core data cleaning
continents = set(["africa", "asia", "oceania", "europe", "north america", "south america"])
def fix_continent_gbif(c):
    for continent in continents:
        if (c) and (continent in c.lower().replace("_", " ")):
            return continent
    return "other"
print(fix_continent_gbif(None)) 
print(fix_continent_gbif("europe"))    
print(fix_continent_gbif("oceania"))
print(fix_continent_gbif("NORTH_AMERICA"))
print(fix_continent_gbif("North America, Canada, Manitoba, Churchill"))
print(fix_continent_gbif("East Indies, Indonesia: Pulo Pandjang, off Sumatra"))
print(fix_continent_gbif("Asia; Thailand; Pathum Thani"))
from pyspark.sql.functions import udf
fix_continent_gbif_udf = udf(fix_continent_gbif)
yc_cross_gbif = (renamed_cols
            .select(year("eventDate").cast("integer").alias("yearcollected"),
                    renamed_cols.continent)
            .withColumn("fixed", fix_continent_gbif_udf(renamed_cols.continent))
            .crosstab("yearcollected", "fixed")
            .persist()
            )
pandas_yc_cross_gbif = (yc_cross_gbif
                   .filter(yc_cross_gbif.yearcollected_fixed >= 1817)
                   .filter(yc_cross_gbif.yearcollected_fixed <= 2017)
                   .orderBy(yc_cross_gbif.yearcollected_fixed)
                   .toPandas()
                   )
import pandas as pd
import numpy as np
continents_list = sorted(continents)
continents_list.insert(0, "other")
# the 100k subset has no australia stuff so graph loop fails
#continents_list.remove("australia")

# blue -> red
#colors = ["#6d6263", "#00e5c8", "#1bc4ae", "#36a395", "#51827c", "#88414a", "#a32031", "#bf0018"]

# blue -> orange
colors = ["#a75902", "#00e5c8", "#1bcda7", "#37b686", "#539f65", "#51827c", "#bf6603"]


plots = []
bottoms = pd.DataFrame(np.zeros((len(pandas_yc_cross_gbif['yearcollected_fixed']), 1)))
for c in continents_list:
    plots.append(
      plt.bar(pandas_yc_cross_gbif['yearcollected_fixed'], pandas_yc_cross_gbif[c], 
              color=colors[len(plots)], edgecolor='none',
              width=1.0, bottom=bottoms[0])
    )
    bottoms[0] += pandas_yc_cross_gbif[c]
    #print(pandas_yc_cross[c])

#print(bottoms)

# Start of WWI
plt.axvline(x=1914)

# Start of WWII
plt.axvline(x=1939)

# "1988 - October 31: President Reagan signs the NSF Authorization Act of 1988, thereby authorizing the doubling of the NSF budget over the next five years."
plt.axvline(x=1988)

plt.legend(plots, continents_list, loc=2)
plt.title("Specimens in GBIF by Collection Year and Continent")
plt.ylabel("Number of Specimen Records")
plt.xlabel("Year")
axes = plt.gca()
axes.set_xlim([1815, 2020])
axes.set_ylim([0, 1200000])
fig = plt.gcf()
fig.set_size_inches(12, 4)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15098171970209884, 'fraction_numerical': 0.04143534190927556, 'mean_word_length': 4.238297872340426, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '545677', 'license': 'MIT', 'n_tokens_mistral': 2858, 'n_tokens_neox': 2589, 'n_words': 444, 'path': 'ScratchPad-iDigBio_Specimens_Collected_Over_Time.ipynb', 'repo_name': 'bio-guoda/guoda-examples', 'size': '7385'}