import pyspark.sql.functions as sql
import pyspark.sql.types as types

idb_df_version = "20170130"
idb_df = sqlContext.read.parquet("/guoda/data/idigbio-{0}.parquet".format(idb_df_version))
idb_df.count()

"""
Explanation: Graphing catalog numbers vs rank
Are collection codes typically sequential? Let's graph only numeric codes (can do work to conver alpha-numeric codes to based 10 numeric) vs their rank. If codes are sequential we should have a straight line with slope = 1.
End of explanation
"""


subset = (idb_df
          .select(idb_df.catalognumber)
          .where(idb_df.recordset == "271a9ce9-c6d3-4b63-a722-cb0adc48863f")
          )
subset.cache()
subset.count()

"""
Explanation: To test our code, find one collection that seems to have numeric ids. Go to the search API and find the most common catalog number:
http://search.idigbio.org/v2/summary/top/records?top_fields=catalognumber&count=100
(It is possible that some collections use "good" catalog numbers that are UUIDs or other forms of GUIDs. This work can never apply to them.)
And then look for collection codes that use something popular like "100":
http://search.idigbio.org/v2/summary/top/records?top_fields=recordset&rq={%22catalognumber%22:%22100%22}
Looks like the recordset a6eee223-cf3b-4079-8bb2-b77dad8cae9d has 4 records with this number and 6M records so it sounds interesting but let's start with 271a9ce9-c6d3-4b63-a722-cb0adc48863f since it has 1.8M records and will be a bit easier to work with.
End of explanation
"""


print(subset.where(subset.catalognumber == "0").count())

def to_int(s):
    try:
        return int(s)
    except:
        # 0 is a terrible flag value but it is graphable so we can see
        # how bad things are
        return 0
    
to_int_udf = sql.udf(to_int, types.IntegerType())

catalognumbers = (subset
                  .withColumn("number", to_int_udf(subset.catalognumber))
                  )

print(catalognumbers.where(catalognumbers.number == 0).count())

catalognumbers.where(catalognumbers.number == 0).head(10)

catalognumbers.where("catalognumber='r-70049' OR catalognumber='70049'").head(10)

"""
Explanation: Is there anything that is not numeric?
End of explanation
"""


catalognumbers_pd = (catalognumbers
                     .select(catalognumbers.number)
                     .sort(catalognumbers.number)
                     .toPandas()
                     )

catalognumbers_pd.describe()

catalognumbers_pd[-10:]

%matplotlib inline
import matplotlib.pyplot as plt
plt.plot(catalognumbers_pd.index.values[:-3],
         catalognumbers_pd[:-3]["number"])
plt.axis([0, 2000000, 0, 2000000])
plt.xlabel("Rank of specimen record")
plt.ylabel("Numeric catalog number")
plt.title("Catalog numbers from Museum of Comparative Zoology,\nHarvard University (271a9ce9-c6d3-4b63-a722-cb0adc48863f)")

"""
Explanation: Well there certainly is and there are repeated catalog numbers too. We certainly can't assume that 'r-70049' is the same as '70049' and if we could we certainly can't guess at the catalog number practices across collections.
Graphing
So let's just throw this on a graph.
End of explanation
"""


x_start = 1418000
x_end = 1419000
y_start = 199000
y_end = 201000

plt.plot(catalognumbers_pd.index.values[x_start:x_end],
         catalognumbers_pd[x_start:x_end]["number"])
plt.axis([x_start, x_end, y_start, y_end])

x_start = 1850000
x_end = 1875000
y_start = 600000
y_end = 700000

plt.plot(catalognumbers_pd.index.values[x_start:x_end],
         catalognumbers_pd[x_start:x_end]["number"])
plt.axis([x_start, x_end, y_start, y_end])

"""
Explanation: Let's zoom in on some interesting parts.
End of explanation
"""


