Okay, so we'll just we'll just get into it I
don't have the the audio or the the recording enabled through Google meets
but but I'll just go ahead and present my screen and well we'll give David one
second to get back and then then we'll start okay so I'll just go ahead and
just share with you would I have or actually okay all right so
all right so we'll just jump into it so what you can get over here is you can
actually get pretty rich data from the from the LCB so you know if you just do
a Google search for you know washing you know Washington cannabis data actually
you can get the the frequently requested lists then you can make a public records
request and then they'll give you essentially a data dump like this and
so you know this is basically your starting point if you're working with
Washington traceability data right so this is for the month of December and
they dumped out almost 30 gigabytes of data so that's a lot to chew on so you
know how do you go about doing that so a tool I use I'm not sure if you use
Python but if you're looking to get set up with Python you can look at the
anaconda distribution it looks like they are migrating to paid so I'm not a huge
fan of that so if anyone has any alternatives for Python distributions you
know definitely suggest them but exactly so if you're working on windows over
here okay so you open up your bash terminal and you can bypass anaconda all
you would use anaconda for is to set up the libraries for you just makes a more
convenient way but if you use if you go to the bash terminal and just type
jupyter notebook two words it'll open up the jupyter notebook for you so here's just the
anaconda bash and so you say you can just type in jupyter notebook
how do you spell it jupyter
you're using visual studio to okay exactly yeah and so redirect you if it
doesn't just click on that bottom link there and it'll it should open it up in
your browser
that way you bypass anaconda so if they do have to or if they do charge that's
easier way to get into everything that you've got awesome so that everybody is
on a Mac it's just you open up the terminal type in jupyter notebook and it'll
automatically open so just for the sake of this I'm probably just going to be
running a lot of this in spider and it's just sort of tried and true that I use
but I but I just just just to make sure everything runs smoothly will just run
it in spider over here but basically I'll you know provide you with this
script afterwards but I was just going to run through just an example of you
know how to you know read in this data here and then see what we we can do with
it so so what I've done is I you know my company's can litig so I'm primarily
interested in lab results here so I've downloaded this lab results file which
has a lab result CSV in it and so we're just going to read that in you know
right off the bat and so you know what do we need to do that you know we need
pandas and so if you've worked with Python this is a nifty tool so you're
going to import pandas and so I just have the CSV saved locally and then you
know what you can notice is if it's it's going to be a little tricky to work with
the way the date the state formats the data but essentially they have it
formatted as UTF-16 with tab delimited values so you can go ahead and you know
run in your data here and it's a large file here so I'm reading in this file
right here and so this is almost two gigs but we'll just look at lab results
for the past month okay so while this is reading in I'll go ahead and just get
the next block of block of code here all right looks like there's a little okay
so we've read in the data we have almost 2 million observations so this is a
fairly large data set so that's another reason why I wanted to look at the the
lab results here so if any of you are feeling a bit more ambitious there's
probably a lot of really interesting data to be found here and like the sales
data the tax data strain data and then I was going to actually look at inventory
transfers next there may be some interesting things with transfers so
there's a lot of rich data here but enough of that so we'll just go ahead
and jump back in here we've got these lab results and okay so what do they
what do they look like so basically I'm just going to just grab the you know the
first observation here just to okay so what does this look like you've
basically just got a giant dictionary here the key fields are actually going
to be you know your global ID you know then when it was created at and then
there's some other things here like so this is where it was tested at so this
one was tested back in 2016 so you may not want to look at that and then they
dump out all of these values what you'll notice is a lot of them are in it or NAM
so for example at the moment the state doesn't mandate pesticide testing
however or require pesticide testing however I think that is going to change
here in the you know the coming year or so so that could be interesting data to
look out for if if pesticide data starts coming through so it'd be just
interesting to look at pesticide levels okay so you know and then if you want to
see the actual data points here you know you can just list you know you can just
list the so you know it's it's dumping out a lot of data so you know we're
going to need to sort of narrow this down a little bit okay so how can we
narrow narrow this down well you can you know you can figure out okay we know
what type of samples do we you know do we do we have here you say you can look
at all the intermediate types and you can find out you can list all of the
intermediate types that are unique
okay so now you so now we're starting to you know find out a bit more about the
data here we're okay so these are all the product types that you'd have in
Washington okay and so we also didn't want to look at you know every data
point here so I was going to restrict them to to December so let's see
well let's actually just walk through this so basically what I had done is I'd
realized there's just going to be you know a little too many here to work with
a million so I thought okay let's just work with you know ten thousand at a
time just just so it it's it's a bit more manageable so you know just created
a sample here so you know there's the sample and then the full data set is the
two million so this is just for speed of sake but we'll go we'll but we'll we'll
remove the the limit at the end so then just to create a few helper variables
here so right so you have the the tested at time and so you can look at the first
five observations like that you know you could look at the first ten
observations okay and so we've got an ISO formatted time here and so we can
create a nice time variable that's you know time aware so simple enough we can
create a time variable and then given the sample global ID you know you can
also figure out essentially what lab this is coming from so the so that would
be their license number but they realize this isn't actually going to quite do
the trick but anyways you know you can create some helper variables there okay
so now now let's say you know you just want to you know look at one group so
say you just wanted to look at flower so you could just you can say okay let's
just find all of the samples where the intermediate type is a flower lot and so
you can find the flower so about you know roughly 70% of the samples are
flower samples all right and so something I did here was essentially what was
interested to see if there is any correlation between CBD and in THC so
we're going to need a few more packages here so up at the top you can add in the
stats models and this is there's this is a statistics package that I like to use
for simple regressions and things like that so you import your stats models
right and so for a regression you're going to need an in X your dependent
variables or your independent variables so in this case are independent
variables I was wanting to see if CBD a has any effect on Delta 9 THC a so you
can get your X and then your dependent variable Y and then you'll need a
constant because you're you know why you know beta naught plus beta 1 times X right
so we're going to add the constant and we're going to fit the regression here
right and so we've run a regression the first thing I do is look at the R squared
and so this is the total amount of variation that we just explained in this
regression so we just explained you know about 6% of all the variation so this is
not a very explanatory model here so there's not you couldn't put a lot of
stake in this model you know it looks at rough estimate that there may be a
negative relationship between CBD and THC but I will you know you can't make any
conclusions off of this model but it's you know and then what an interesting
rule is always look at the data so let's you know see what you know we just did
here so we basically just we just basically fit this line so we said okay
is there a relationship between CBD and THC and it looks like there may be a
slight you know negative relationship but it's you know there's there's a large
group here so there's it's not not the best trend line in the world but you
know it looks like if you just looked at the the high CBD strains then there may
be you know a better linear relationship there so so that's that's comparing two
analytes and so another interesting thing you can do is let's say we wanted
to look at one of these analytes over time so we just looked at flower so now
let's say okay what happens if we want to look at butane over time right because
some of these concentrates are extracted with butane so you know you may want to
know okay is there you know is there any level of butane in the final product so
remember above we listed all the intermediate types and so what I did was
I basically just went and I grabbed any one that ended in concentrate and put it
into this list so you could do that programmatically I just did that
manually but I just manually created this list you know some of these you may
not expect to have butane in there so you may not expect to have butane in the
non solvent based concentrates however we can exclude those in the future for
now I just wanted to grab everything that was a concentrate okay and so you
know in the laboratory setting the meaningful way that you would look at
these is you would look at them over time so you would say okay over time is
there any trend in this analyte because that's what you would you would be
curious about is this analyte is there a positive trend is there a negative trend
so how do we do that so I am going to group the data by day so you've got
these long lines here I'm going to share the code afterwards and I've also
included these references here for how you can go about you know how you can go
about aggregating the number of occurrences per day so this is a useful
thing to do in pandas so let's go ahead and create these series here okay so
about 20 so about 19% of the samples is or concentrates so then you can
aggregate them by day and so what does the daily concentrates look like well
this is a series over time and once again you can look at all of the
variables so it's I'm creating the mean of each variable by day so that's
numbers so let's look at it so that it's more meaningful so like what I always
say is look at the data so we're going to create a figure for figures we're
going to need matplotlib so the classic way to import matplotlib is import
matplotlib.pyplot as PLT and we're also using the datetime package so we can add
that to the top of the script as well so we're going to create a figure we're
going to plot daily concentrates we're going to plot the butane on the y-axis
we're going to fit a trend line by running the same regression that we ran
well the same regression model that we used earlier except now our X value is
going to be just a time series so this is just going to be the index so you can
fit a trend line and then we'll plot the trend line so putting it all together
you can get the average butane whoops I misspoke so putting it all together you
can get the average butane level in Washington concentrates over time so if
you I put the the limits here so if you look at the limits you'll see that
butane has an action limit of 5,000 so anything less than 5,000 ppm will pass
so as you can see you know these concentrates you know these are passing
samples but you know as you'll see there's a you know a non-zero average
level of butane you know that's 100% up to the consumer and you know it's under
the action limit so so it is what it is I think it's interesting to note that in
this small sample you do see a negative trend and then let's just for fun let's
just go ahead and essentially remove this limit here and just and just see
what see what may happen I haven't actually run this but okay and so now
we can see the data in the full sample and it's similar here where you've got
the negative trend so I think I'll go ahead unless I've been sort of running
through this fairly quickly I should have asked has there been any questions
so far about some of the things that that we've done
awesome can you go over real quick can you go over again where you went and found that data
absolutely so you let me put it in the
here I'll share it with you so there is the data dump and then I believe these
put they put them out periodically but you know if you need historic data then
you know there is just the link to make the request and the request is fairly
simple you just you know they have just a bullet point of you know they just
have these bullet points of things that you get you went to a site to first to
make the request where was that this site or is this the other side you're
referring to
your screen yeah where you came with see LCB records make public requests you
copy that link to the chat yeah sure thing
so you come down in the public make public request
then it gives you a document you got to fill out and email it is I believe you
can email it in so you know so like I said it depends on what you it depends
on what you're looking at like I said a lot of my interest has just been in lab
results and so I know everybody has their own interest and in fact you know
I'm sure most of the interest is probably in sales right because I mean
that's I've heard that's where the actions at rate that's where the consumers
are but I don't know what how about can I just ask the crowd like what sort of
data points what sort of data points are you interested in or what statistics are
you interested in looking at by chance well we would be we'd be actually
extracting for trying to find all of the people that are in the business to be able to
you know we're we're trying to design an add-in for acrobatica's ERP that will
interact with the state or API as it does the growing and does the harvesting
as sort of manufacturing processes okay well why don't just now here on the fly
why don't we see what we can do here so they've got these licensees so we'll
just walk through the you know this is how the sausage gets made so you so you
download this we're gonna do it to the cannabis data science group there it is
and the next question I have for both of you do you know of anybody that is
familiar in dotnet with using the API to call for ID values right I mean that's
the biggest thing of accounting systems trying to work with the state board is
every ID having to be assigned by the API for vendor IDs and you know
you're actually speaking to somebody so that's I I'm actually quite quite
familiar with working with the state API so I've my background is in a testing
laboratory so I so I've integrated with the state and then my company's in the
process of getting our integrator status at the moment so if if if you need any
any help I can we're gonna start we're gonna start small with like a hello
world type of thing just trying to get like a vendor ID assigned by the state
rather than being assigned through the ERP software so we can take that off if
you can you know email me your contact information we can set up another time
to chat and it's not gonna be right away we're finishing up another product first
but I've got a full-time dotnet developer and one of the cool things
about this accounting software is you can customize right within the cloud app
in dotnet so so anything you can do in dotnet you can create screens or
processing screens or change the way that the code works in the system so
everything from purchasing sales you know manufacturing distribution any of
those things where it has to interact with the API we're gonna have to write
code to do that and I wanted you know once we once we kind of learn the API
you know just I think one time if you do one time interacting with it then after
that you know my developer probably be pretty good at it definitely but I'd be
happy to just give you a crash course tell you the nuances show you how to hit
the endpoints but yeah I yeah definitely so I can help you with that and you know
like I said that's what my background's in and like I said you know a lot of
this can be automated but for now let's just grab this licensees file so just
to show you this real quick and then we can talk more about the state
traceability system but I'm just going to do a similar thing here as I did with
the lab results so just substituting the file name and then it's going to be in
this folder so I'm going to add this folder directory here so just open a new
console and let's let it rip and see what we get okay so 28 so you've got
almost 3,000 licensees and same thing so this is how the sausage gets made the
first thing I do is I look at the first observation and maybe let's look at the
last observation so I so yeah so so this first one looks like almost like a like
a test that that looks like actually Washington State but this last
observation here it actually looks like it's it there they may be suspended but
oh yeah but it looks like I thought yeah so it's a dispensary in I don't know I
would like I said I would maybe double check on this I mean it may be worth
giving them a call just to make sure that's accurately coded
yeah so and so actually what you could do here is you could almost say okay
let's just do data suspended okay so okay so now we can do a little on-the-fly
data analytics so let's see how many of these are suspended right so you could
do okay so you could do okay so data dot loc data dot suspended equals true and
then you can just divide that by length of data so it looks like yes so it
looks like like 35% of these licenses are suspended so it could so it could be
that people aren't renewing their licenses or or something like that so
that's where the the data crunching becomes quite interesting um but so you
know so let's say you're looking for you know active licenses well you know
you're gonna be looking for you know people where the suspended's false for
starters so so there's 1871 non suspended but I believe I wonder if the
let's find out all the the fields again oh and then so there's also an is live
and an expired at so let's take a look at is live and then let's see what
expired at looks like
interesting so for your active licenses you know you may want to change this to
a conditional so you want me want to say okay we want everyone that's suspended
false and you know data thought is live is true
some reason it's not crazy about this
there we go so it's a look you know and then you could do a further sanity check
in you know cut off all the ones that aren't expired but you know this is it
you know probably a pretty good list for you here and then you know you could you
know and so what you could do is you know you could actually you know let's
say you want to save this to excel so you can now say okay active licenses
new to excel and you know there's probably some more settings you can
toggle to make it look a little nicer but there you go so you didn't do much
exactly incident so then you know then you could even pass this to your your
marketing your marketing agent and then say okay yeah okay here's a you know you
could clean this up through them a little bit but you can basically say okay
you know here's their phone yeah here here's your yeah here I do think these
phone numbers just for your warning I think maybe not all of them but I think
you know some of these you know because their number is listed publicly they may
get a lot of contacts so a lot of them may have different numbers for their
primary lines but we don't actually market via phone yeah it's not a real
effective method we've got kind of a we've developed a process where we can
identify the people and then they're either using an AI site like really high
percentage probability of getting the good right email address we we do some
email marketing that way and then the other thing we do is we invite them to
connect on LinkedIn and then they kind of see our marketing social media posts
on LinkedIn or business marketing LinkedIn is seems to be the most helpful
yeah well you kind of hit the nail on the head and you may be interested in
joining there I attend this startup group on Tuesdays and one of the topics
they talk about is customer development automation and yes you hit the nail on
the head it's they their strategies it seems to be connect with people on
LinkedIn and I and they may enjoy that better themselves because like I said I
don't think they love getting phone calls all day and in fact like I said I'm
sure some of these numbers are active but the the large cultivators they are
they were you know we're getting so many phone calls that they just they just got
a new number almost but it's still you know it's still just good to know okay
what does the industry look like and you know you can you know you've got the
power of Python here or whatever your programming tool is and so you
Python guy I grew up on the Microsoft side so I've been a sequel guy okay
we because the software we sell is dotnet based we really focus hard to have
stuff on that so but but you know it's the same idea right getting access to
data like this and then in building queries and store procedures to slice
and dice the data or use something like a report builder for an SSRS report or
something of that nature even power BI right gives you a lot of similar type of
capability to slice and dice the data exactly it's just exactly it's just sort
of tools of the trade and then like you said it's just how you want to to rig up
your system and so I really enjoyed this because I've never seen I've never
really seen anybody do what they were doing well well it was kind of it was
cool to kind of see okay that's kind of similar to sequel and I was kind of
seeing what you're doing like oh yeah that's just a different language for
doing essentially the same thing and and this is sort of bare-bones like you can
you know you can use VS code and get better better highlighting and you know
you can run this over here in the terminal but I just sort of learned on
spider and I find it just quite simple because I can write scripts and run
right here in the console but I'm just I'm all about practicality just getting
things done so this was just a quick fast way to so but you know to get what
you wanted so basically we because you know you you doubt you download this
ugly file and you're gonna have to do something with it so you know you have
to parse this one way or another right get the data out of it and then dump it
to to an Excel and then yeah and then this can then go in your database and
you can have at it but but that's sort of round but that I guess that sort of
I you know rounds it out so yeah it was fascinating hearing what you what you do
and I was just wondering you know for you know for next week is there anything
like anything else you'd like to see or I know this is more about analyzing the
data so I wouldn't want to take it really off course too much but but yeah
I'm just as far as the cannabis industry I've been trying to find a way to break
in to that industry with the software that I sell and be able to create and
add in for that software to make it work with the state API and there are some
other competitors out there but you know I think competition is good I don't
think that they have particularly good products yet so we're looking to I've got
experience in growing but it's growing shellfish I wrote for Taylor shellfish
over at Shelton back in 97 when active server pages were the rage we wrote and
a complete growing app for growing from seed to the final pulling out of the
beach clams and oysters and other types of shellfish and it allowed them to to
grow pretty significantly over time over the last 20 years but they they used it
for many many years so that their staff knew what they had in the in the beaches
they they had measurements done on their on their phones with the web apps to
update the size of the shellfish as it was growing you know to take the sales
orders and the guys would go out on the boats with their phones they'll look and
see what the sales orders from the prior day were that need to be fulfilled that
day so there's lots you can do with technology I became kind of a geek after
being a CPA accounting nerd I became a geek with technology and active server
pages really kind of opened my eyes to what you can do with databases and web
pages and it's it's come a long way from there and I've grown a company that does
you know ERP consulting and sales with Acumatica which is if you haven't heard
of it there they're like ten years beyond net suite in terms of platform
and technology they you know that suite was started in 1999 that came out about
2009 but it's all dotnet from the ground up knowledge tml5 from the beginning all
open API so web services both rest or so boarded it is it is amazing software
because it was originally started as a rapid development platform for dotnet
websites and then well we've got a background from our ERP days they were
owners or managers of Solomon this project so they may said oh let's throw
on an ERP platform on top of that and then let people program whatever else
they need in dotnet and they let you view the source code so whenever you're
doing development if you can see the source code if you're developing
enhancements it just makes everything easier right yeah and because of their
development environment you can actually be coding C sharp right within their
application and they have some tools where you can essentially create a table
in the database and then you go in the program you say okay create the class
object that represents that database table and it'll write the code
essentially for you to create that class object and then you can say create me a
screen for that class object and it'll write the code to create the screen for
that right and then you can tweak it after that so it's one of those rapid
development environments for business software applications that makes makes
creating a full solution in a single database with everything from
manufacturing to financials to distribution warehouse management
project accounting recurring billing equipment maintenance everything a
business does or could do in a single application for multiple companies
levels of consolidation of you know three four levels deep parent to child
to child to branch so as you can tell I'm kind of an evangelist for the software
but it was the I found it in 2012 and it's really the most advanced business
software application out there I mean they're really on cutting edge I mean
they've got a they're like one of the first with a business skill for Alexa
right so I literally I can put on my Alexa glasses right and I can I can talk
to the accounting system and say what the inventory stock status is of item X
right you know it's kind of like Star Trek computer type functionality right
so for me imagine a world where we are now interacting with our business
software applications verbally through Alexa and giving commands to the system
imagine being on a warehouse floor and being able to check stock status or to
and I think where they're going I'm actually been making suggestions on
improvements but not only being able to ask for information but what if you
could say create a purchase order for vendor X for item Y right or do a step
in a manufacturing process saying you know complete step a of this particular
manufacturing job all these things are going to be possible for you to
interact in the future with your accounting system your business software
systems verbally hands-free you know they're doing development now with OCR
so like AP documents that get emailed into the system they can monitor the
email queues and convert an AP invoice PDF form into a bill in the accounting
system without anyone having to enter it right expense reports right so if you're
out traveling to a job or say you're an appointment you know service appointment
and you take a picture of the work that you did and attach it to the appointment
or if you take a expense receipt and you take a picture of it and it creates the
expense record in your accounting system for reimbursement all these things are
things they've already developed and I mean so it's as a geek I look at technology
that they're coming up with. I'd like how you think David and so I was thinking just from what
you've mentioned do you think for next week a good topic may be API's? Yeah that's a
great idea. Because that was something I was wanting to hit on and that's
something that is programming language agnostic so you can have any programming
language you want like and we can just we can just go through some endpoints
and just walk through them hit some endpoints get some data and that way
we're all talking the same language. I'll have my developer join us too. He's down in California now. It's amazing with this COVID right?
We operate remotely. I mean we use Acumatica internally as well so he's just working from down in California. He says hey I'm moving to California. I say okay cool. Enjoy yourself. Awesome.
I'm going to go get a license or what I have to get an employer ID from the state of California to get some tax raise but other than that you know gusto. More than marriage.
But definitely so same same time next week and and we can we can talk about API's and we can hit some endpoints.
Send me the information or link information and does the meetup site send out an email or do we have to find one?
I'm going to go ahead and create a new event and I'll get you sent out a new invite so I'm still sort of learning so sorry about the
slow start today. If I can answer any questions for you on anything. My depth is maybe not as deep. My knowledge is not as deep as everyone else.
But I have a lot of breadth of knowledge. I wouldn't say. I can tell you that I have an engineering background nuclear engineering background for my Navy days as a submarine officer.
I'm a family of accountants so when I got out I ended up becoming a CPA. I'm doing payroll and accounting and I'm one of the people that walked away from Amazon before I invested in my stock options back in the 90s.
So if you ever want to feel bad. I can tell you how much money I walked away from pretty much every day. I know how many hundred million.
Don't be hard on yourself, but I've learned a lot from you and this is what this group is about. So I'm here to share what I know and then we can share. Just keep sharing.
Perfect. I love it. Awesome. Well, alright guys. I gotta run. I'm late for my next meeting but this was great and I look forward to next week.
Alright until next time. Bye Charles.