Okay, good, so welcome to the talk thanks for coming, so let's talk about bike sharing
for a moment for those of you that are not familiar with bike sharing if these funny bicycles
that they are around on the cities they have stations, then people are getting the bicycles
and leaving bicycles on stations, right?
Somehow, ten years ago I started building this project that was only supposed to be for
bike sharing Barcelona, I ended up scraping bike shared data for almost a whole world,
this around eight hundred cities around the world that supported.
But the scope of the talk is more of what kind of information the project has.
So the first one is availability data, when you look at availability data it's basically
a point which is a station, has latitude, longitude and then you have information like
the number of bicycles that there are and the time step where this information was
accessed, I call this availability data.
Then, nicer organizations like the city bike system in New York, they have something called
TripData, TripData is basically a point A from point B, that's a trip, it's somebody
that has one trip from one point to the other, then you have something like, at what?
When did it started, when did it ended and so on?
So this is very nice information to work with, but of course not all organizations are
giving this kind of information because it's, I don't know, expensive, interesting, whatever.
So I came up with this I read, I call it time series of availability data, which is basically
the same availability data that I was talking at the start, but over time, so you can see
for one particular station, all the different stats that it has gone through and it's
only showing you whenever it has changed, so it's not time series in the sense of fixed
intervals, but whenever the information has changed.
Okay, so, this life demo, first of this lemmer, I have no idea of what I'm doing in
terms of years, my background is systems engineering, so all of this, it's been mostly learned
as I went or as I go, so the data that you can get from CTVX, it's on this website,
these are different target files that you can get and there is one for every month.
So we're going to be working with the month of December of last year, luckily I already
have it downloaded, we're going to use our favorite tool, which is that TV, it's the
font nice, bigger, yeah, good, so when we read one of these packet files that I have available
on the internet, it's the information that it's contained on it, there is the tag that
that basically it's what it identifies a particular system, then I read the ID of the station,
it's supposed to be unique, I'll call the cross data set, but sometimes it's not, especially
with latitude, longitude that are 0, 0, then there is this new ID, whether there is a lot
of info in there, if we look at it, maybe just get 10 rows of it, wait, so this is what we get
for instance, for the system over here, in Brussels, oops sorry, a lot of that, we get this
information available, which is one station and then we get for instance, there were seven
bikes on 18 spaces, at this particular point in time, then at another point in time, suddenly
it got 8 bikes, then 9 bikes, 8 bikes, and so on, and so on, five minutes left, the good thing
is if I don't finish in time, the end result is also available, so we can look at it, even
though we didn't manage to create, then tired of it, so let's, what table I am at, everything
from full, limit 100, now there is no full, okay, then I have the data already loaded in a table,
so this is what this table looks like, there are lots of rows, but still it will work
because it's that TV, but yeah it has a geometry, which means I should also load special
in here, and we want, so what can we do with this information, if for instance, we try to look,
so what I am trying to do is get one metric that we can represent in a map, and generate a
hit map of the usage of a particular bike sharing network, and to get that, we only, I mean we have
bicycles available, which are kind of okay, like here in New York, sorry,
name, bikes, and free, and then some timestamp, I mean this is good information, but we need to
aggregate it somehow, so something that could be, we could be doing, we could be counting,
and then number of entries, but it's also more interesting, we just try to get
the difference between each set, we can do that with a window function, so let's just jump into
this window function lag here here, yeah it's only never, I'm sorry, this is not going to be able to
be done live, but here, yeah this one, what we are doing, so what then, if we add everything together
and we just put the absolute, we can kind of say this is more or less an activity, right,
or maybe not, you tell me after, okay, so yeah we have this, we can, for instance, we could
order, I mean, limit by 10, then we get the top 10 stations in a particular system,
that's already good information, but then if we create a geosson file with this query,
and I'm just going to skip and just generated for the whole world and not for the video system,
then I go to this website that I created just for this, then we open it, here, oh enter,
go on, work for me, yes and we have a heat map of the whole Brussels, the whole world,
there is no limit, so I don't know if somebody wants to see a particular city, here it is,
we have Paris and the times up and this is the world, thank you, thank you,