Last session for today. I hope you're still fit and curious to learn some things this ones. Goldenhar primer for SQL folks. My name is Thomas Hooter's and overloading my name. So you can tell I'm from a German speaking country. I'm unhappy make application.
Um Afya played around with SQL server since version 65. Do software development for the software. Formally called the vision for about 15 years now and I played around with our since version 3.12. That's about 4 years old.
Just one disclaimer, I told you anything of this what I show in production. This is not my daily job. It's sort of a psychic project, but still are like through to give unto you what I learned for myself. Yeah, I'm speaking at, I've been speaking with several events. Some of which you see down here.
Data if you would like to stay in contact right so we have a bit of an agenda. This talk is normally 60 minutes. So I have to write through it just a little bit. I'm going to briefly give you an introduction and how.
Maybe what what are what the ecosystem looks like and why it's there I'm going to show in IDE? Are we gonna use. I have lots of tables there, you will get the slides and all the scripts, so if you want to play around with it. Afterwards so you can just build up on top of what you saw here.
I have something I called a business case demo where will be extracting data from the vision database on the SQL server, and and visualizing some things and my last example. Yeah, I call it data science going wrong, but please. This is an introductory talk so there would be no big data.
Addictions and an old deep dive stuff if you're aware of that. I think will get along fine right So what is RR is a well let's call it programming language for statistical purposes. It's not all the like you have the C Sharp language or with the basic.
Fancy Windows applications, it's there to do data analysis data visualization and the stuff that's the folks called data scientist do at the moment, it's been around. It helps it's 25th birthday last year so it was in.
93 by the 2 Gentlemen Rawson Robert because they were not pleased with the S language that was there before in the 70s. That was proper. Terry that couldn't be extended. It was very clumsy it. It went on special hardware and that sort of things, so they wrote their own so.
In the mean time, it's a new project, so it is open source. It's maintained by Foundation. But there's also the archosaur ciem, which has members like Microsoft and HP and then I don't know.
And they give like a commercial background to to keep the language developing.
Yeah, package is the one of the key success factors for the for our language being so popular is the extensibility through packages. Those are software modules that any our user can create build a package out of that and then give it through the community so others.
Log think function libraries and there's a Central Miracle Cram, which has at the moment more than 30,700 packages. You can download integrate into our system and then use it so if there's any function your thing is missing you can.
That function in our build a package and when you finish. You can give it to the community. So the others can use it as well right.
I gave this talk about.
3 years ago for the first time and then that number. They are set about 8000 and something so within 3 years or almost 3 years that has risen by 5000 packages so you see that that's a really vibrant community and they like to to give to others.
Yeah, commercial support has been around at least since 2007 by a company called Revolution Analytics.
Who will acquired by Microsoft some years ago and all part of the Microsoft Corporation and they provide things as the Microsoft are open, our server, and so on. There's dozens different. I DS you can use to developing are from the naked our app, which I'm almost sure nobody.
Then you got our studio.
From the R Studio Company, which is still the defacto standard IDE to use when you develop a nights available for Windows for Mac for Linux. There has been something called our tools for Visual Studio so that was an add on your could make visual studio look like.
Physically and behave the same way. But I've seen at least one Microsoft documents thing that will be deprecated from Visual Studio 2019 on.
So between brackets that that tells you use our studio.
And yeah of course, you have it in SQL server itself. You can use are you have in power be. I have the possibility to use our scripts in our visualizations. There's Azure machine learning. This data, science VM and so on.
Alright so yes, starting with a smaller than the smallest datatypes. Nothing special here, you have like numeric data types. You have characters. You have logical one in other places is called Boolean you have something called factors? What is compara bulto on?
Type in other languages, you have date and time types that are Posix Compliant. You have something that's gotten a which is we are not available, which is Compara Bulto, the null value for the sequel folks.
That's the basic Briggs to build upon and then you got data structures are the most important is the vector because almost everything in our is a vector if you don't define it otherwise are vector has one dimension and one data type that simple, you can have a matrix, which is 2 dimensional we see.
Sample of that in a moment you can have a list which use a collection of other objects.
It needn't be of the same data type will see that you can have tables with more than 2 dimensions and then you got a data frame, which is probably compara bulto. A sequel table, which is like 2 dimensions and the columns always have one data type, but you can have several then and so on.
Can you see that in the back? Can you read that is OK.
What you see here is the?
Mentioned our studio environment. Let me just briefly walk you through what it's what it contains up here. You got the source code window, which can have several files. Of course down here is the console before interactive usage you can call functions interactively and uh.
It will be displayed around here, we have a terminal. You can call this the environment. So when we generate variables and stuff like that. That will show up here. You have a history function of you over calls you can have database connections right there.
Is a small file Explorer?
Also in this window is the output for any plot functions. There's a list of those packages. I mentioned before those other packages that are installed on my machine. We gotta help. We know and the viewer for things that don't come out in the plot window, so.
So very basic things you can use it just in calculating mode and I'm like 70 + 4 and the result is displayed on the on the bottom left we concede I could actually.
That's better probably OK, you can have like square root and you can do through the power of something blah blah blah then we got the simple data times at the assignment. It works, either this way or that way or with an equal sign and the result is always 4.
The equal sign is is sort of.
Blue the the assignment operator usually what's uses this one that's for historical reasons that comes from S and there, it used to be on one key on the keyboard that gives you this assignment operator.
So it works from left to right from right and it also works with the.
With the equal sign.
OK, you can have characters you can have the billions. There's that datetime arm which can also include the time zone.
To calculate with that, we got the effective thing.
OK. Let me see just walk through this, or not they quickly. You got vectors, which you can construct by combining are single values like here. The numbers from one to 8 or like combining the range from one to 8, whatever and if you call this it gives you?
The numbers from one to 18 in one less OK.
Then we got what have we the matrix, yeah, you know what a matrices 2 dimensional columns and you got rose nothing special you got that list thing, yeah, OK.
That's a bit special here as I said that the list are members don't have to be from the same data type so like here, we have one listen. It contains 12 mega value. It contains one character or logical.
And the last one is a complex number.
You can make even more crazy concert at least got system of lists or data frames within less and so on, it gets complicated.
And.
The most important construction, the data frame that should look familiar. If you see like a simple. SQL table that has rows and columns. There are column names and the column always have one data type.
Yeah, you could.
Oh, you can have the structure of this and you see it has.
The column name Anna column score. One is a factor actually not the character data type and one is numeric.
All right.
Was that yeah packages like I mentioned extend the base our system. They can contain cold? They can contain data? There are packages that consist purely of data sets they should have a documentation if they don't have the documentation. They won't be accepted for upload on the crime mirror.
So flexible user contribute obel. I'm not going to take it too much more detail. There's some functions, which can list installed packages with all the other properties like version and Dependencies and so on, and thus the library command to load a package that you.
For use and maybe OK, one name, you should have heard in that context is Hadley Wickham.
Who is hurtful Hadley Wickham before?
One.
OK, he's 1 of the brightest minds in the our ecosystem. So to speak. He works for our studio and also as a professor at the University. I forgot where it was and he's the creator of many, many packages that are used by data scientists and analyze all over the world.
We are a couple of them that he created and that in the beginning. They used to be called the Headley Worlds, which was something he didn't specifically like so now they called the Tidyverse because they allow manipulating your data in the so-called tidy way.
Which is also philosophy? Yes yes created so that? How can I explain it, you can move the data very easily from one package to another without having to manipulating in between so it's one format that all the packages would accept.
OK, I have a quick demo, but I guess for the sake of time, we skip that and it's not very interesting OK. So yeah, when you want to analyze data are you first have to input something of course, so there are numerous functions that allow you to import data some generic like reads.
There are there are specific versions like re CSV or CSV 244 comma and semicolon delimited subte limited fixed with and so on. There are some additional packages that would allow you to read data from S, the predecessor from SAS, an from starter and even.
Anyone use D base still today.
One.
OK, yeah, you can access databases. We're the Odbc and DVI packages, yeah, OK. The database of these small or bookkeeper axle. Of course, you can handle with those packages and there's the possibility to import XML another table.
So when you
OK, when you got that data.
Most probably you want to analyze it so there's some basic wooden column statistic foundry. A analyzing functions that you could run against your data if it's the first time you get some new datasets want to look at it. What does the what's the content and then how is it?
So we also see also we have already seen the structure function that gives you the structure. For example of your data frame. We have, like, Summary Hatton. Today I will show you all of that.
Those give you back numbers or character values or whatever and then of course, we got the basic graphical analysis of data for example, with the histogram approx. Paul and so on.
OK.
So that's that's a data set that's actually internal to our but I put it out into a CSV file. It contains data of passage of the Titanic. Rightly, the steamship that, saying after hitting the iceberg, you'll know what can we do.
Read CSV and with that, we import the data from the CSV file and you see up here. I've got a variable called Satanic and he says 891 observations off 12 variables, so translator that will mean there are 891 Rose and threw off columns in my date.
OK yeah by clicking on it.
Already called it here to display you can always look at your data within the within the IDE.
The read CS we also works against files that are on the web so I'll start that somewhere on the web. The same file. You can read you can use exactly the same command and give it a URL as a parameter.
And see, those 2 identical so if you want to start the analysis may be the first thing you call would be the structure function. I give you that in a big bill that gives you per column or per variable's escort here. The data type again gives you the number of.
The columns at the top and it gives you some sample data of the of the different columns.
So you see that some integer numbers, there are some factors there are some other numerical values and so on.
If you want to dig in detail of course, their functions like minimum and maximum. Let's take the age column for example, so that that's the age of the passengers you see the minimum is oh .42 years, so they're very young small baby writing on that boat, the all this.
80 years old and on average over like 2829 years.
One other function that gives you an overview is the summary function.
That gives you for each of your column if it's a numerical column. It gives you the minimum maximum values and the median and things like that where have we got there like when it's a factor. It gives you the account so that there's a factor value that has made or female in it, I gives account of each of them.
If you want to look at the small sample of your plane data. You can do hat. That gives you the the top 6 Rose and you can do tail that gives you the bottom 6 rose.
Nothing special about that OK.
Those with them and Eric's arm, yeah, let's start with uh some some visualization or graphical data analysis starting with the histogram. You just call the hist function and tell it yeah. I want to Instagram of my H distribution just like that, it gives you a histogram.
It labels the Axis and so on. That's the basic thing to do. We just want call it gives you a graphic like that? Of course, you can always pretty fire things like this so I said, I want to smaller pins or a greater number of bins.
I wanted to be blue, he want to guess my favorite color. Yeah, I don't give it a title here and a proper label down there.
It's always possible to.
And Hans are graphics.
So, like that, we see the age distribution here with the top around 20 years or 22.
Yes, we have before.
Next maybe you want to do a box plot.
Who knows what a box plot is about a couple of hands OK box but basically gives you a graphic representation of the statistical values. We've seen before. In the summary like we have here the distribution of ages over the.
So first class cabin second class third class and when you got this, this fat line in the middle. That's the medium, meaning that 50% of the cases were below. This age of maybe 38 or whatever it is, and 50% of the cases were older than 38.
The edges of the box gives you the 25% value. That's here. 25% of the folks who are younger than that and that 75% so 75% of the folks were younger than this value.
Then there's some lines and whiskers those are I don't remember I think it one and a half times the box height, so that's arbitrary value. And if there are any points outside that range. They are projects. One by one and those are outliers so-called outlines.
From this you can one spot at once that in the first loss pretend and see the people were all of the new second and in the third class of people were even younger than that.
OK, one more dimension in this graphic.
No.
Oh yeah.
The so called mosaic plots.
Here we go again divided in the classes first are second class third class and we got divided into male and female.
Yeah, I'm a black-and-white, meaning yeah, the whites portion of the passenger passengers survived that crash, and the dark partial means that in survive. So what conclusions can, we draw from this one well, you see relatively easily that.
Passengers the black, one is all was much bigger than for the females.
Why is that?
Correct that German women and children first. That's what they actually practiced that so all the women went into the life saving boats and the man had to stay there. Plus, we see the third class has more of the black.
That was because the third last cabins were really down below the waterline. And they had a hard time getting up and out of the ship so a greater part of those folks didn't make it.
The sad as it is.
OK.
Guess looking good.
Then, OK, we if you want to go on. There are also additional libraries that allow you to use additional functions for data analysis like like tidy and then sort of stuff there is one package made by hand. The weekend that's too deep or 2 that allows so sophisticated plots and so on.
OK, I'll spare you the theory. I go into the next demo.
So I have to call some libraries here.
Um.
Never show me one of my favorite datasets, which comes from this side. It's the Formula One results. This site has all the Formula One result of all the races ever chose the 20161 because the German guys on top.
So I'm I'm interested in in the data. That's here in this table in the middle and you can see, there's there's lots of things distortion new starting here like like menus and buttons and there's lots of advertising.
Nevertheless.
Uhm I through that same URL that I used to the browser at this function.
What it does it just reads the HTML construct it's a memory in the next step. I have told him there's an oh that's called table. That's exactly the table. We circle thing in the data. I want and then extract what's in that table and store that into my variable F one.
So it actually yeah that's my variable of one and it looks almost like that ever we saw online right.
So but
That's not a very pretty format. There are no column names. That's the first thing we have to fix.
Then there are lots of those dashes in there, I would prefer to have zeros in those places. It's something else or folder flicks and make it look like this, I have column names like our want or or no 2024 raised one till race blah blah and I've got all the dashes got rid of them and replaced.
So, in the next step. I'm going to do one step of tidying what I told about before becaus.
That is the so-called wide data format. We have all the all the races spread up in columns ranging to the right and the tidyverse packages prefer the long former so I converted it to the long format that representation contains exactly the same information the same amount of information as before.
But in less columns, they got one column for the driver. If you just omit. The total score. I've got one drawer. One column for the raise an one code and full of points.
OK makes sense and by that is very easy to pass that on to for example, the GG port function.
So we have the races from OneNote 21 OK from one to 21. We got are already filtered down on only the 9 best drivers and the points. They they earn for his raise and yeah well. It's the total picture is a mess right. You can't get anything out of that other then to see whatever here missed.
From there on, he could too easily do second places, because he had already won the season and Hamilton could win the rest of them.
OK, but if you want to really.
Have a useful graphic representation, we can do something like this.
Not going on.
Again, this contains absolutely the same information as the graph before, but I split it up so that every driver got is his own little little graphic OK, so you can see where you see Rosberg like having points like this. I'm going to the end and you can see.
And you can see, there both of them got zero points. Yeah, that was the set raise about both of them crashed into each other. Heating both out of the race and then filter waffles like by Neil Table Zero points for the 3 men air.
And any Dutch folks around.
A theater.
What happen in that race when they got zero points Max for stopping one?
A dentist Oregon Amsterdam was, I was like this zero points and there was a lady in the back, but Max for stopping one and I was like where yeah of course, you see it up there, but that's an outlier that's the one time with.
Dolphins men.
All right.
So what else can we have to clean up every now and then here. What else can we do. We yeah, we can do we can use something else called shapefiles?
Who knows what a shape file is?
Shape file actually is not one file. It's a set of files. The foreman has been defined by the company as we they do graphical information system that huge is a noxious. Maybe all of them and the shape files contained yeah, well shapes, but special show.
Seems like like special shapes polygons that represent countries or borders or whatever. I have got this shapefile. That represents the post code zones in Germany. Yeah, I'll post goes all 5 digits. And all the ones that start with a one are near each other all.
4. In one area so you can easily make a map out of that. That's what I go to here.
Show it.
Like this we gonna come back to that in one of the other demos so that comes out of a shapefile. It just colored them differently. Now let me think that's our.
Yeah, those are the possible so long, I 012.
And they are the lights were not.
We built on top of this in our next demo.
Alright, we also ready at the business case demo so I got a database with me here in a SQL server, which is no vision database, and let's just assume we are the distributor for fall. Joan petrol stations. I have all Joan petrol station in there and we got 2 subsidiaries.
And then some of the business called say we need to analyze the diesel save something may be wrong and I don't know, but yet look at it, so we're interested of course in the distribution, but possible zones.
I got on FB database that shapefiles in another data. I use a publicly available.
Chicken.
Not where am I.
Hit the wrong and we're using the DB I package an order BC to access the data and do some floating around.
Let me see.
Clear this.
OK, I need the DBR library. I need to open a connection to my sickle sore wish and now you see the connection stop come into action. Here are it shows all the databases that are accessible through this connection right now.
And what have we gone. I got a SQL server 27th 2017, running or Lanix says. Yeah, that's not totally true, it's running within a docker container on my Mac so yeah, it's some sort of linear inside.
OK, who knows the dynamics in AV system formally known as the vision.
Sing.
I ask that who assumed Denmark. It was like 90% of the folks OK. It's an ERP system. Originally invented by some Danish folks acquired by Microsoft like in 2001 or 2002 it's multi company capable.
So I got like 3 companies in my database. See Cronus AG is the standard D more company that comes when you install. The thing and the other 2 are the one I brought into the database with my data.
And that means.
If I want to access customer data, yeah, is distributed in 3 different tables there.
Each company has his own custom material, which makes it a bit complicated to consolidate it from outside but nevertheless we can create a function.
That this basically caught our select statement and I can pass the company name as a parameter and the function works. The component me through these select statement OK and then I can call that for all my companies and then I've got some safe figures.
Sales figure this fake data so I don't have all the Saints data off the other petrol station. I have all the addresses, but not the same state worker.
But I used to hear it gives me the subsidiary belong to it gives me the postcode the month and the aggregated seats amount just for an example.
And then I can do something like this, I can. Maybe Aggregated and show the sales. The total sales per month. That's not very interesting of course I can do something like this.
And yeah play that picture you can tell I'm I'm giving you fake data. It would look like that. Otherwise, right, I've got the post code zones. All the post code that start with 01 and so on. And here is the distribution over the years and that should be some states figures.
So maybe you notice if you got your data in shape, then tune analysis doing some some of the visualization. Even is rather easy task. It it does usually not take very much effort of source code be cause. It's usually it's one quarter that you report function like here and you give it some parameters.
The flight as a as also before but for first impression. Usually, it's a one liner or maybe like here. It's too. But then he got already like labels and titles and things on.
OK, I can do that for the yeah that looks that looks awful. That's the the 2 digit passcode thing so on maybe?
I've got another shape for here and R.
Visualize that into
Into a map.
Well.
So there again, you got the map you got the first digit of my posts cold zones. The darker the area is the higher the relative sales is in that area, but that's not enough to to analyze what business answers for so we go down to the 2 day.
And plot that.
Yes.
Do do do do.
OK, I hit the wrong keys.
So.
Thanks.
OK.
I don't know what it takes a second or 32.
To do this now.
And there, you see real white spots in the middle so that's down to the 2 digit zones and yeah. There's some some really nice seats going on there and there are really white spots. We have no seats in that area.
And something we couldn't see for the one digit representation just submitted ago.
And if we
I hope there's no one from touring in here because.
Really slow today.
It does a red spot in them?
So did
It showed there, you go.
So I overlay, the area of touring and over this one and you can see that almost is the white sports. Or maybe the sense web of touring has a problem there. He will be visited.
Yeah, I'll stay within the demo. Next up would be something called shiny you can create web apps within our.
And they are even interactive So what I have here is I have some some radio buttons on the left and some graphics to the right and if I click this one.
It produces the different graphics. We just saw before.
Here you can see opening browser, so I can run this in my browser and still. It's nearly more responsive than the the naked version, so by that. You can you can let your colleagues or anyone else your network?
In your analysis and your reason azatian someone LL anyone else can can call that one and the browser and work with it and to take that one step further.
You can even publish it through some server somewhere in the in the interwebs. I did it before so I can just click on this. It calls a server called shiny apps IO, which is offered also by the R studio folks it some seconds becaus.
Plication goes to sleep after 15 minutes, so that's running on the server. I don't know where is somewhere in the world where I have a small free account to do these things and even from there? It's interactive it's responsive groups criminal.
And you can publish your results to anyone with an Internet connection. When you do a professional paid service from the from the shining web service, you can also do user credentials and not everyone can have everything you can you can manage those things as well?
OK.
Rights.
Yeah.
OK so we saw this one that was the analysis of the remote end date that we saw this one. The shiny app OK and then I'll be at my last example, which is called Anscombe's quartet and I call it data science gone roll? Question mark.
The quote it is. It consists of 4 datasets very small only 11 points. Each totally different. X&Y pairs, but they have identical statistical properties like when you when you analyze it for one by one data set but they just said the mean?
All is 9 for each of the datasets. The mean value of why is 7.5 there's a correlation that's about oh .8 something and if you go for leading our regression model for that. It has those para meters for each of the of the datasets so if you get those.
And our tool to analyze them You would probably be tempted to say they should be about equal compareable or whatever right.
OK let's see.
Oh, I have them in the CSV fossil is just call them in and I show just for.
For one of them that's my data set a it has a column with the letter, A and it's got 1111 Rose X&Y. Nothing too special. The worries are somewhere between zero and 20 whatever and we can see that the.
The mean value of X is 9. The meaning of why is 7.5 OK forget about the fuel diesel places please does a correlation of oh .81?
And the linear model has an intercept of 3 under slope of .5 and if you call that for each of them. The results will be absolutely identical.
So what happens when we look at them.
It looks like this those are 4 candidates so.
Yeah, what do you think? What is happening is that OK?
Those other 4 datasets the blue line is the linear model equation just broad across so let's start here. Those are some scattered points, OK, if you do a linear regression. It probably boils down to this line. There's nothing special about it. That's OK, if you take that one the points are.
Online but the blue line goes somewhere else why is that?
Outlier quite ride, the little one above, there right.
He or she whatever pulls that lineup.
From the lion those other points would make.
That's the only reason OK, yeah, it's all right what's that.
What's the arrow there?
Well, the errors here 'cause I told my sister. Give me a linear regression. There's nothing linear. But those points. That's like the hyperbolic aware you call it yeah, OK. This is that can calculate something in the end. That's the linear regression model. But it it's not a good representation of the.
To them.
And probably those points will cut it if they would continue that would continue down here are not up there right so I was wrong with my assumption, you have linear model will fit something.
And Yeah, the last one down here absolutely crazy. I don't know if you can see him an outlier up there again. Those points are all on the? What is it X is eighth line so that would make absolutely no sense to to interpret something?
This an only because of that one outlier up there. The system give you the same line.
Oh, good, yeah, the round up so I hope I could give you the well first impression of the what is possible with R and it's a lot, that is possible in terms of the analysis and visualization and if you're missing something that's probably a package for that.
The only problem is finding the right package so go through the Cran Mirror and you have to search for it. Yeah, but then, especially when we regard this last example. You should know your data so not every point. But you should have an idea what's coming. In is it sensor data is it? Is it like you stop?
Is it like of function results or what? Can you expect then look at your data not only these statistical values really make a picture maker visualization like we did here.
At think does it make sense what you see consider especially the influence of outliers.
And then you have to decide is a case by case decision what you do with them. If you just disregard them or replace them by an average value or whatever the decision. It's up to you don't blindly rely on our do electric it's your responsibility.
Well, I got some resources some up for you, if you want to get started with our I would maybe recommend spoil you can. That's a package called swirl and if you go to that side you have a 5 or 6 point list of things to do how to install R how to install.
And then you can call it inside a studio and learn interactively how to deal with our OK. If you want to dig deeper. There are moves so all night causes at closer on index something like that.
I have yeah, I still reads books on paper.
Let me think one of them is German. I think that one is in German language, but you finding an English one from that.
OK don't worry you get the slides and everything. Yes, some credits where my data came from then. Yeah, some housekeeping. Please fill out those feedback forms and leave them with a friendly folks at the exit don't forget the Prohibition Party is taking place right in this whole.
Hope to see you there here, I'm afraid not much time for Q&A.
So that will be the address also well the slides will be there in a few days and yeah. If you miss that keep in contact and I will say thank you for your time your interest and enjoy the rest of the show.