From image classification to chatbot therapyContinue reading on Towards Data Science »

Towards Data Science - Medium

Sign upSign inSign upSign inTheo LebrykFollowTowards Data Science--ListenShareIf you’ve played around with recent models on HuggingFace, chances are you encountered a causal language model. When you pull up the documentation for a model family, you’ll get a page with “tasks” like LlamaForCausalLM or LlamaForSequenceClassification.If you’re like me, going from that documentation to actually finetuning a model can be a bit confusing. We’re going to focus on CausalLM, starting by explaining what CausalLM is in this post followed by a practical example of how to finetune a CausalLM model in a subsequent post.Many of the best models today such as LLAMA-2, GPT-2, or Falcon are “decoder-only” models. A decoder-only model:This is contrasted with models with “encoder-only” or hybrid “encoder-decoder” architectures which will input the entire sequence, not just previous tokens. This difference disposes the two architectures towards different tasks. Decoder models are designed for the generative task of writing new text. Encoder models are designed for tasks which require looking at a full sequence such as translation or sequence classification. Things get murky because you can repurpose a decoder-only model to do translation or use an encoder-only model to generate new text. Sebastian Raschka has a nice guide if you want to dig more into encoders vs decoders. There’s a also a medium article which goes more in-depth into the differeneces between masked langauge modeling and causal langauge modeling.For our purposes, all you need to know is that:With decoder-only language models, we can think of the next token prediction process as “causal language modeling” because the previous tokens “cause” each additional token.In HuggingFace world, CausalLM (LM stands for language modeling) is a class of models which take a prompt and predict new tokens. In reality, we’re predicting one token at a time, but the class abstracts away the tediousness of having to loop through sequences one token at a time. During inference, CausalLMs will iteratively predict individual tokens until some stopping condition at which point the model returns the final concatenated tokens.During training, something similar happens where we give the model a sequence of tokens we want to learn. We start by predicting the second token given the first one, then the third token given the first two tokens and so on.Thus, if you want to learn how to predict the sentence “the dog likes food,” assuming each word is a token, you’re making 3 predictions:During training, you can think about each of the three snapshots of the sentence as three observations in your training dataset. Manually splitting long sequences into individual rows for each token in a sequence would be tedious, so HuggingFace handles it for you.As long as you give it a sequence of tokens, it will break out that sequence into individual single token predictions behind the scenes.You can create this ‘sequence of tokens’ by running a regular string through the model’s tokenizer. The tokenizer will output a dictionary-like object with input_ids and an attention_mask as keys, like with any ordinary HuggingFace model.With CausalLM models, there’s one additional step where the model expects a labels key. During training, we use the “previous” input_ids to predict the “current” labels token. However, you do not want to think about labels like a question answering model where the first index of labels corresponds with the answer to the input_ids (i.e. that the labels should be concatenated to the end of the input_ids). Rather, you want labels and input_ids to mirror each other with identical shapes. In algebraic notation, to predict labels token at index k, we use all the input_ids through the k-1 index.If this is confusing, practically, you can usually just make labels an identical copy of input_ids and call it a day. If you do want to understand what’s going on, we’ll walk through an example.Let’s go back to “the dog likes food.” For simplicity, let’s leave the words as words rather than assigning them to token numbers, but in practice these would be numbers which you can map back to their true string representation using the tokenizer.Our input for a single element batch would look like this:The double brackets denote that technically the shape for the arrays for each key is batch_size x sequence_size. To keep things simple, we can ignore batching and just treat them like one dimensional vectors.Under the hood, if the model is predicting the kth token in a sequence, it will do so kind of like so:Note this is pseudocode.We can ignore the attention mask for our purposes. For CausalLM models, we usually want the attention mask to be all 1s because we want to attend to all previous tokens. Also note that [:k] really means we use the 0th index through the k-1 index because the ending index in slicing is exclusive.With that in mind, we have:The loss would be taken by comparing the true value of labels[k] with pred_token_k.In reality, both get represented as 1xv vectors where v is the size of the vocabulary. Each element represents the probability of that token. For the predictions (pred_token_k), these are real probabilities the model predicts. For the true label (labels[k]), we can artificially make it the correct shape by making a vector with 1 for the actual true token and 0 for all other tokens in the vocabulary.Let’s say we’re predicting the second word of our sample sentence, meaning k=1 (we’re zero indexing k). The first bullet item is the context we use to generate a prediction and the second bullet item is the true label token we’re aiming to predict.k=1:k=2:k =3:Let’s say k=3 and we feed the model “[the, dog, likes]”. The model outputs:In other words, the model thinks there’s a 10% chance the next token is “dog,” 60% chance the next token is “food” and 30% chance the next token is “the.”The true label could be represented as:In real training, we’d use a loss function like cross-entropy. To keep it as intuitive as possible, let’s just use absolute difference to get an approximate feel for loss. By absolute difference, I mean the absolute value of the difference between the predicted probability and our “true” probability: e.g. absolute_diff_dog = |0.10–0.00| = 0.10.Even with this crude loss function, you can see that to minimize the loss we want to predict a high probability for the actual label (e.g. food) and low probabilities for all other tokens in the vocabulary.For instance, let’s say after training, when we ask our model to predict the next token given [the, dog, likes], our outputs look like the following:Now our loss is smaller now that we’ve learned to predict “food” with high probability given those inputs.Training would just be repeating this process of trying to align the predicted probabilities with the true next token for all the tokens in your training sequences.Hopefully you’re getting an intuition about what’s happening under the hood to train a CausalLM model using HuggingFace. You might have some questions like “why do we need labels as a separate array when we could just use the kth index of input_ids directly at each step? Is there any case when labels would be different than input_ids?”I’m going to leave you to think about those questions and stop there for now. We’ll pick back up with answers and real code in the next post!----Towards Data ScienceA master’s student who is still not quite sure what data science is…HelpStatusAboutCareersBlogPrivacyTermsText to speechTeams

Training CausalLM Models Part 1: What Actually Is CausalLM?. For simplicity, let’s leave the words as words rather than assigning them to token numbers, but in practice these would be numbers which you can map back to their true string representation using the tokenizer

Training CausalLM Models Part 1: What Actually Is CausalLM?

Sign upSign inSign upSign inIndranilFollowSystem Weakness--1ListenShareLet’s take down the YONO SBI Phishing website mentioned in the previous post.As we’ve seen, this Yono SBI phishing site attempts to collect victims’ credentials by tempting them to convert their reward points to cash and clicking on the provided link. This could spell disaster for unsuspecting individuals who are unaware of the site’s illegitimacy, risking the loss of sensitive credentials in mere seconds.But let’s find out how long this site has been active on the internet. To gather more information about the website, we will use a Whois Lookup.So, the website has been active for the past four years and counting. (These individuals might have already conducted numerous scams.) Here are the details listed below about the website.Let’s now run it through VirusTotal once again to check how many security vendor’s have flagged this URL as malicious.So, only 6 out of 92 security vendors have flagged this URL as malicious. But this isn’t sufficient. Let’s take action and report the website to all the security vendors, then check the results.Navigate to McAfee’s reporting page and locate the Customer URL Ticketing System. Choose McAfee Real-Time Database and insert the malicious URL into the provided blank space. Finally, click on “Check URL” to proceed.Next, under category suggestions, choose “phishing” and describe the page (optional). Then, submit the URL for review.You will receive feedback indicating that the issue should be addressed within 3–5 business days.2. The second big one is Google SafeBrowsing.Open the Google SafeBrowsing page and fill in the details in the same manner, then click on submit.You will receive similar feedback once again.3. The third big one you want to report to is Microsoft.Open the Microsoft Security Intelligence page using the given link and click on Continue as a guest.Enter the details of the site, complete the security task and click on submit.The feedback :)4. The next one is Fortiguard.Open the Fortiguard page and paste the URL. Then, press enter to view the URL rating.Even though it’s already categorized as phishing, let’s still request a review. To do so, click on Request a Review. Provide all the necessary details, including a screenshot if possible, and then submit the request.You should receive a feedback after the submission is successful.5. The next one is BrightCloud.Open the BrightCloud webpage , paste the malicious URL and click on Look Up.The results indicate that the URL is uncategorized, which is concerning. Let’s request a category change. To do this, enter the details of the website and other necessary information in the text boxes located on the left side of the website.Click on I would like to suggest a category for this URL under the URL (first text box), and select Phishing and Other Frauds. Then, click Done.Ensure all details are filled out correctly, then click on submit. You should receive feedback confirming the submission.6. The next one is CRDF.Open the CDRF Labs page and enter the malicious URL in the List of Web Addresses to Report box, as shown. Then, click on Submit your request.Once the submission is successful, you should receive this feedback.7. Next we have Netcraft.Open the netcraft reporting page, paste the URL and type your email to get updates and then click Report Malicious URL.Upon successful reporting, you should receive this feedback.8. The next one is Palo Alto Networks.Open the Palo Alto Networks page, then paste the URL and hit Search.Since the results indicate that this site has already been categorized under phishing, we can proceed to the next one.9. The next one is ESET.Open the ESET webpage, then fill in all the details and click Submit.The feedback..10. Next we have Trend Micro.Open the Trend Micro webpage and paste the URL on the search bar and click Check Now.Again since the results indicate that this site has already been categorized under phishing, we can proceed to the next one.11. The next one is BitDefender.Open the BitDefender’s webpage and scroll down to the bottom of the page to submit the URL. Fill in all the required details as shown and click Submit.As confirmation, you should receive the following feedback.12. The next big one is Forcepoint.Open the Forcepoint website, then paste the URL and hit the Analyze button.As the results show the threat severity is undetermined, so lets suggest a different classification. To do that click on Suggest a different classification.Add the details and click on submit. This should save the classification.13. The next one is Symantec.Open the Symantec website, paste the URL and click Check Category.Once more, since the results indicate that this site has already been categorized under phishing, we can proceed to the next one.14. Next we have Spam404.Navigate to Spam404’s webpage, then paste the URL along with a description explaining how the website is malicious. Finally, click Submit.As a confirmation, you should get the following feedback.15. The next big one is Kaspersky.Open Kaspersky’s webpage/dashboard and navigate to the Web Address Analysis section. Paste the URL and click Enter.Once again, since the results indicate that this site has already been categorized under phishing, we can move on to the next one.16. Next up, we have the Cisco Talos.Visit the Cisco Talos webpage, then paste the URL into the search bar and click the search button.Once more, since the results show that this site has already been categorized under phishing, we can proceed to the next one.17. The final one is the URL haus.Open the URLhaus webpage and navigate to submit the URL. Add the tags, if applicable, and finally click on submit.Upon successfully submitting the URL, the feedback should resemble thisNote: You must have an abuse.ch account to report any url on URL hause.The final step to block this site is to report it to the registrar, which can be found in the WHOIS lookup. The registrar is Cloudflare, and the email is: registrar-abuse@cloudflare.com.Now that we know the registrar is Cloudflare, let’s visit its website and report it. To do so, navigate to Cloudflare’s abuse reporting page and click on Submit an abuse report. Here is the link to the website: https://www.cloudflare.com/en-gb/trust-hub/reporting-abuse/Select the abuse type as Phishing & Malware. Fill in the necessary details. In place of evidence and logs, write down the steps to reproduce. Finally, click submit to complete the form.You should receive confirmation similar to this.And now we wait for the security vendors to take action.2. Firefox has currently blocked the site, even though it was accessible via Firefox before reporting it to Google Safe Browsing.3. Brave browser has also blocked access to the page.4. Previously, only a few security vendors marked this as malicious, but now the number has increased. That’s good news for us.Hopefully, this will help protect individuals from falling victim to this scam or phishing attack. Thank you for reading. Stay safe and secure online.----1System WeaknessI write about security and stuffs i loveHelpStatusAboutCareersBlogPrivacyTermsText to speechTeams

How To Take Down Any Phishing Website In Minutes. Once more, since the results indicate that this site has already been categorized under phishing, we can proceed to the next one.14.

System Weakness - Medium

How To Take Down Any Phishing Website In Minutes

Sign upSign inSign upSign inMember-only storyBo HellgrenFollowLevel Up Coding--ShareYou want to build an app for real-time classification of, for example, dogs. With real-time I mean that as soon as a dog enters the camera view, its breed should be displayed — without need to tap a button or anything. Which software and tools would you use?There are several possibilities. Here I present the results of a proof-of-concept I made usingFlutter is a superb tool for building the user interface. For camera APIs on Android, CameraX has been around for a while and is the obvious choice. The brand new MediaPipe is in “preview mode” at the time of writing, but has the potential of becoming a great tool for on-device AI.I made a simple prototype app using these tools. The code is available on GitHub.The reader is assumed to have some experience with Flutter and Kotlin.“Flutter is an open-source framework by Google for building beautiful, natively compiled, multi-platform applications from a single codebase.” I use Flutter for all my apps and love it. If you are new to Flutter, go to flutter.dev.CameraX is the latest Android camera API, with lots of improvements over Camera2. It introduces a new programming model: use cases. My app, called Dog Camera 2, implements three use cases:Both MediaPipe and MLKit provide APIs for object detection (“Is there a dog, and if there is, where is it?”) and image classification (“What kind of dog is it?”). Which should I use?I could not find any information from Google guiding me on this choice. I asked ChatGPT4 (through Bing) about it, and it said that…----Level Up CodingHelpStatusAboutCareersBlogPrivacyTermsText to speechTeams

Flutter, CameraX and MediaPipe. I use Flutter for all my apps and love it.

Level Up Coding - Medium

Flutter, CameraX and MediaPipe

Sign upSign inSign upSign inNajib SharifiFollowTowards Data Science--ListenShareInterconnected graphical data is all around us, ranging from molecular structures to social networks and design structures of cities. Graph Neural Networks (GNNs) are emerging as a powerful method of modelling and learning the spatial and graphical structure of such data. It has been applied to protein structures and other molecular applications such as drug discovery as well as modelling systems such as social networks. Recently the standard GNN has been combined with ideas from other ML models to develop exciting innovative applications. One such development is the integration of GNN with sequential models — Spatio-Temporal GNN that is able to capture both the temporal and spatial (hence the name) dependences of data, this alone could be applied to a number of challenges/problems in industry/research.Despite the exciting developments in GNN, there are very few resources on the topic which makes it inaccessible to many. In this short article, I want to provide a brief introduction to GNN covering both the mathematical description as well as a regression problem using the pytorch library. By unraveling the principles behind GNNs, we unlock a deeper comprehension of their capabilities and applications.A graph G can be defined as G = (V, E), where V is the set of nodes, and E are the edges between them. A graph is often represented by an adjacency matrix, A, which represents the presence or absence of edges between nodes i.e. aij takes values of 1 to indicate an edge (connection) between nodes i and j or 0 otherwise. If a graph has n nodes, A has a dimension of (n × n). The adjacency matrix is demonstrated in Figure 1.Each node (and edges! But we’ll come back to this later for simplicity) will have a set of features (e.g. if the node is a person, the features will be age, gender, height, job etc). If each node has f features, then the feature matrix X is (n × f). In some problems, each node may also have a target label which maybe a set of categorical labels or numerical values (shown in Figure 2).Single Node CalculationsTo learn the interdependence between any node and its neighbours, we need to consider the features of its neighbours. This is what enables GNNs to learn the structural representation of the data through a graph. Consider a node j with Nj neighbours, GNNs transform the features from each neighbour, aggregate them and then update node i’s feature space. Each of these steps are described as follows.Neighbour feature transformation could be done a number of ways such as passing through an MLP network or by linear transformation such aswhere w and b represent the weights and bias of the transformation. Information aggregation, the information from each neighboring nodes are then aggregated:The nature of the aggregation step could be a number of different methods such as summation, averaging, min/max pooling and concatenation:Following the aggregation step, the final step is to update node j:This updated could be done using MLP with the concatenated node features and neighbour information aggregation (mj) or we could use linear transformation i.e.Where U is a learnable weights matrix that combines the original node features (xj) with aggregated neighbour features (mj) through a non-linear activation function (ReLU in this case). This is it for the process of updating a single node in a single layer, the same process is applied to all other nodes in the graph, mathematically, this can be presented using the Adjacency matrix.Graph Level CalculationFor a graph with n nodes and each node has f features, we can concatenate all the features in a single matrix:The neighbour feature transformation and aggregation steps can therefore be written as:Where I is the identity matrix, this helps to include the each nodes own features too, otherwise, we are only considering the transformed features from the node j’s neighbours and not it’s own features. One final step is to normalise each node based on the number of connections i.e. for node j with Nj connections, the feature transformation can be done as:The equation above can be adjusted as:Where D is the degree matrix, a diagonal matrix of number of connections for each node. However, more commonly, this normalisation step is done asThis is the graph convolution network (GCN) method that enables GNN to learn the structure and relationship between nodes. However, an issue with GCN is that the weight vector for neighbour feature transformation is shared across all neighbours i.e. all neighbours are considered equal, but this is usually not the case so not a good representative of real systems. To address is, graph attention network (GATs) can be used to compute the importance of a neighbour’s feature to the target node, allowing the different neighbours to contribute differently to the feature update of the target node based on their relevance. The attention coefficients are determined using a learnable matrix as follows:Where W is the shared learnable feature linear transformation, Wa is a learnable weight vector and eij is the raw attention score indicating importance of node i’s features to node j. The attention score is normalised using the SoftMax function:Now the feature aggregation step can be calculated using the attention coefficients:This is it for a single layer, we can build multiple layers to increase the complexity of the model, this is demonstrated in Figure 3. Increasing the number of layers will allow the model to learn more global features and also capture more complex relationships, however, it is also likely to overfit so regularisation techniques should always be used to prevent this.Finally, once final feature vectors for all nodes are obtained from the network, a feature matrix, H can be formed:This feature matrix can be used to do a number of tasks e.g. node or graph classification. This brings us to the end of introduction into the mathematical description of GCN/GATs.Let’s implement a regression example where the aim is to train a network to predict the value of a node given the value of all other nodes i.e. each node has a single feature (which is a scalar value). The aim of this example is to leverage the inherent relational information encoded in the graph to accurately predict numerical values for each node. The key thing to note is that we input the numerical value for all nodes except the target node (we mask the target node value with 0) then predict the target node’s value. For each data point, we repeat the process for all nodes. Perhaps this might come across as a bizarre task but lets see if we can predict the expected value of any node given the values of the other nodes. The data used is the corresponding simulation data to a series of sensors from industry and the graph structure I have chosen in the example below is based on the actual process structure. I have provided comments in the code to make it easy to follow. You can find a copy of the dataset here (Note: this is my own data, generated from simulations).This code and training procedure is far from being optimised but it’s aim is to illustrate the implementation of GNNs and get an intuition for how they work. An issue with the currently way I have done that should definitely not be done this way beyond learning purposes is the masking of node feature value and predicting it from the neighbours feature. Currently you’d have to loop over each node (not very efficient), a much better way to do is the stop the model from include it’s own features in the aggregation step and hence you wouldn’t need to do one node at a time but I thought it is easier to build intuition for the model with the current method:)Preprocessing DataImporting the necessary libraries and Sensor data from CSV file. Normalise all data in the range of 0 to 1.Defining the connectivity (edge index) between nodes in the graph using a PyTorch tensor — i.e. this provides the system’s graphical topology.The Data imported from csv has a tabular structure but to use this in GNNs, it must be transformed to a graphical structure. Each row of data (one observation) is represented as one graph. Iterate through Each Row to Create Graphical representation of the dataA mask is created for each node/sensor to indicate the presence (1) or absence (0) of data, allowing for flexibility in handling missing data. In most systems, there may be items with no data available hence the need for flexibility in handling missing data. Split the data into training and testing setsGraph VisualisationThe graph structure created above using the edge indices can be visualised using networkx.Model DefinitionLet’s define the model. The model incorporates 2 GAT convolutional layers. The first layer transforms node features to an 8 dimensional space, and the second GAT layer further reduces this to an 8-dimensional representation.GNNs are highly susceptible to overfitting, regularation (dropout) is applied after each GAT layer with a user defined probability to prevent over fitting. The dropout layer essentially randomly zeros some of the elements of the input tensor during training.The GAT convolution layer output results are passed through a fully connected (linear) layer to map the 8-dimensional output to the final node feature which in this case is a scalar value per node.Masking the value of the target Node; as mentioned earlier, the aim of this of task is to regress the value of the target node based on the value of it’s neighbours. This is the reason behind masking/replacing the target node’s value with zero.Training the modelInitialising the model and defining the optimiser, loss function and the hyper parameters including learning rate, weight decay (for regularisation), batch_size and number of epochs.The training process is fairly standard, each graph (one data point) of data is passed through the forward pass of the model (iterating over each node and predicting the target node. The loss from the prediction is accumulated over the defined batch size before updating the GNN through backpropagation.Testing the trained modelUsing the test dataset, pass each graph through the forward pass of the trained model and predict each node’s value based on it’s neighbours value.Visualising the test resultsUsing iplot we can visualise the predicted values of nodes against the ground truth values.Despite a lack of fine tuning the model architecture or hyperparameters, it has done a decent job actually, we could tune the model further to get improved accuracy.This brings us to the end of this article. GNNs are relatively newer than other branches of machine learning, it will be very exciting to see the developments of this field but also it’s application to different problems. Finally, thank you for taking the time to read this article, I hope you found it useful in your understanding of GNNs or their mathematical background.Unless otherwise noted, all images are by the author----Towards Data SciencePhD in Molecular Chemistry. Enthusiastic about novel approaches, merging ideas from various sciences for cutting-edge solutions to complex challenges.HelpStatusAboutCareersBlogPrivacyTermsText to speechTeams

Structure and Relationships: Graph Neural Networks and a Pytorch Implementation. Information aggregation, the information from each neighboring nodes are then aggregated:The nature of the aggregation step could be a number of different methods such as summation, averaging, min/max pooling and concatenation:Following the aggregation step, the final step is to update node j:This updated could be done using MLP with the concatenated node features and neighbour information aggregation (mj) or we could use linear transformation i.e.Where U is a learnable weights matrix that combines the original node features (xj) with aggregated neighbour features (mj) through a non-linear activation function (ReLU in this case).

Structure and Relationships: Graph Neural Networks and a Pytorch Implementation

Sign upSign inSign upSign in🐼 panDataFollowLevel Up Coding--ListenShareHave you ever encountered a sanitary inspection program for cruise ships?We are embarking on an educational journey to apply SQL to real-world data, publicly available through ANVISA (the Brazilian National Health Surveillance Agency).This unique context will serve as our introductory framework to SQL language learning. Our approach is to immerse learners in a realistic scenario, enhancing understanding and engagement.Rather than merely presenting SQL commands, we aim to provide a meaningful learning experience. You’re here to gain genuine knowledge, and to ensure this, we will place our challenges within a specific context, seeking solutions while simultaneously delving into SQL language.We will utilize publicly accessible data hosted on the Brazilian open data portal, a treasure trove of information similar to other repositories such as Data.gov (the U.S. government’s open data site), Google Dataset Search, Data.world, Amazon Public Datasets, Microsoft Research Open Data, and many others. With an abundance of data at our disposal, our focus will always remain on publicly available datasets.I encourage you to adopt this practice when developing projects for your portfolio or during your studies. I will endeavor to introduce a variety of data sources to enhance your everyday learning experience.Regarding the dataset, there is a noteworthy consideration. Originating from a Latin background, the dataset contains numerous accents in its names, which could complicate processing in MySQL. To streamline our focus on SQL language, I have simplified the process. I downloaded the original CSV file from dados.gov.br, and translated the variable names to circumvent potential obstacles, allowing us to dive straight into the essence of our learning.The updated CSV file is made available on a GitHub link. While I point out the original data source, I recommend utilizing the modified file provided through my GitHub for a seamless experience. The original file is under a Creative Commons Attribution license, permitting free modification as long as the source is acknowledged — a practice I have diligently followed.This dataset pertains to the National Health Surveillance Program for Cruise Ships, which conducts sanitary inspections aboard cruise vessels to ensure food safety and water quality, in addition to assessing compliance with cleaning and disinfection procedures, solid waste management, air conditioning systems, and the health conditions within onboard hospitals.The primary aim of these inspections is to prevent outbreaks of diseases among passengers and crew. Following the inspections, ships are assigned a rating from categories A to D based on the identified discrepancies. Vessels in category A are in excellent sanitary conditions, whereas those in category D exhibit unsatisfactory conditions requiring immediate rectification.The Anvisa’s guidelines for sanitary safety on cruise ships are comprehensively outlined in the Sanitary Guide for Cruise Ships, a document likely containing the regulations and standards that ships are expected to adhere to. This dataset enables stakeholders to monitor and assess the sanitary compliance of cruise ships.This data dictionary originates from the dataset associated with the sanitary inspection of cruise ships. The included variables are:1. Ship Name: Identifies the cruise ship.2. Month-Year of Inspection: Indicates the month and year the inspection was conducted.3.Risk Classification: The risk classification assigned to the vessel, with four possible levels (A, B, C, D), where A represents excellent sanitary conditions with a risk score up to 150, and D indicates unsatisfactory sanitary conditions with a risk score above 450.4. Risk Score: A score reflecting the associated risk based on the sanitary inspection. Higher values indicate greater risk.5. Compliance Index: Represents the percentage of inspection items that were complied with by the vessel.6. Season: Refers to the season of the National Health Surveillance Program for Cruise Ships, which typically starts in October and continues until April of the following year.It is also noted that there is a rule for downgrading classification: if a critical control is not implemented during an inspection, the ship is downgraded to the next risk standard. The Risk Score (PSR) is calculated by summing the risk values of each item from the inspection checklist. The absence of controls or failures in controls increases the risk score, indicating lower compliance and higher sanitary risk.We now face a business problem. We will analyze ANVISA’s program for sanitary inspections on cruise ships, having the data and the data dictionary at our disposal. Next, we will prepare the database, subsequently load it, and begin our analysis.Let’s proceed to MySQL Workbench. We will delve directly into a concept of paramount importance: Schemas. Database Management Systems (DBMS) are increasingly transitioning from the traditional concept of Databases to that of Schemas.Depending on the DBMS, this can be essentially the same idea, though it’s possible to have multiple schemas within the same database. For our purposes, the key point to understand is as follows.The ”sys” schema is inherent to the SQL DBMS and is not recommended for user-defined operations. Tampering with this schema could compromise the integrity of your DBMS installation. We shall initiate a new schema instead.Simply right-click in the empty space of the schemas tab and select Create Schema. Our new schema will be named cap02, in alignment with the second chapter of our series SQL from Scratch to Data Science.We will create a schema for each chapter as needed. We can leave the Character Set and Collation options set to Default. These settings essentially dictate the format of characters stored in the database, but the Default Charset and Default Collation settings are sufficient for our requirements.Notice that it presents us with an exact SQL statement, a DDL (Data Definition Language) instruction, designed to create an object in the database. In this instance, CREATE SCHEMA, followed by clicking on Apply, will successfully create the schema.Observe that the schema ”cap02" we created now appears, and also note the presence of a sort of log at the bottom detailing all actions performed. It is always beneficial to monitor the Action Output to check for any error messages.Next, we select ”cap02" and notice that there are no tables present. Let’s proceed to create our first table by right-clicking on Tables and selecting Create Table…I will name this table TB_SHIPS, which is fitting considering we will be analyzing data from sanitary inspections on cruise ships.Next, we need to define the columns. What will be the columns in this table? I will create one column for each column in my data file available on GitHub.Let’s begin. Click on Column, then click on ”click to edit”, double-click, and enter SHIP_NAME as the column name. You will be prompted to specify the data type, which you should set to VARCHAR.VARCHAR allows for a variable length of characters, but in this case, we will specify a length of 50 positions. This approach ensures that each ship’s name is adequately accommodated within our database, allowing for a wide range of names without exceeding the allocated space.Moving on to the second row, with a double-click, although INSPECTION_MONTH_YEAR represents a date, as you can observe in our data file, the presence of a “/” could pose issues when loading the data.Thus, I will also designate it as VARCHAR, but this time with a length of 10 positions to accommodate the data stored in this column.This decision ensures compatibility and avoids potential complications during data import, while still preserving the clarity and integrity of the inspection date information.Next, we’ll address the variable RISK_CLASSIFICATION, which I will also set as VARCHAR with a length of 15. Following that is COMPLIANCE_INDEX; although it represents a numeric value, due to the presence of a comma, I will assign it as VARCHAR with a length of 15 as well, to circumvent any issues during data loading.Then we have RISK_SCORE, a numeric value, for which I will configure the type as INT. Lastly, for SEASON, I will use VARCHAR with a length of 200 to accommodate its extensive character and word count.With these settings, we have successfully established our table columns, ensuring that each variable’s format is tailored to prevent data import issues while preserving the integrity and specificity of our dataset.This defines the structure of our table. Now, when you click on Apply, the SQL command that will be executed will appear as follows:This section outlines a DDL (Data Definition Language)instruction for creating an object within the database using the `CREATE TABLE` statement.This script, which you will find on GitHub, is crucial for establishing the structure of our database. It’s a good practice to save this script; do so in a text editor to ensure you have a backup. Once you click Apply, the TB_SHIPS table will be configured and ready for use in our database.This step is fundamental in database management, as it transitions our schema from concept to reality, laying down the foundation upon which our data analysis will be built. Saving the script not only provides a reference but also ensures that we can recreate the table with ease, should the need arise.We’re now set to populate the table with data. To do this, right-click on the TB_SHIPS table and select the ”Table Data Import Wizard” option. This wizard facilitates the process of importing data from a CSV file into our table.Utilizing the Table Data Import Wizard streamlines the task of loading data, ensuring that the information contained within our CSV file is accurately transferred to the TB_SHIPS table within our database. This step is crucial for preparing our dataset for analysis, as it enables us to work with real, contextually relevant data in our SQL queries and operations.The wizard will prompt you to locate the file. Navigate to the folder where you saved the adjusted file after downloading it from GitHub, and then proceed to load the file.This step is vital as it ensures that the specific, modified version of the data file — tailored for compatibility and ease of use within our SQL environment — is correctly identified and selected for import.By choosing the adjusted file, we guarantee a smoother data import process, minimizing potential issues related to format discrepancies or data integrity.In my case, I retrieved the file from my Desktop, ensuring that I had the complete path to the CSV file. Simply click on ”browse”to search your computer, select the file, and then click ”next”.In the subsequent screen, you will be asked whether you wish to load the file into an existing table. Since we already have the TB_SHIPS table in place, there’s no need to create a new one for the cap02 schema. Additionally, you will see the option to ”Truncate table before import”.This step emphasizes the importance of accurately locating and selecting the correct file for import, ensuring it aligns with our predefined table structure.Opting to use the existing TB_SHIPS table streamlines the process, directly importing the data into the structure we’ve meticulously prepared. The truncate option offers a clean slate for the import, removing any existing data in the table to prevent duplicate records and maintain data integrity.The ”Truncate” command is indeed an SQL instruction designed to clear the contents of a table. Imagine you’ve made modifications to the table and wish to refresh it by reloading the data; this command facilitates that process.By selecting the ”Truncate” option, the wizard will clear all existing records in the table before importing the new data. This feature is particularly useful in a learning environment, allowing for the iterative testing and retesting of data importation and table manipulation without concern for clutter or redundancy.However, it’s crucial to exercise caution when considering the use of this command in a real-world production environment. Executing a ”Truncate” command will permanently delete all data within the table, which could have significant consequences if performed on essential datasets.Therefore, for study purposes, maintaining the ”using existing table” option and proceeding to the next step is advisable. Always ensure to evaluate the implications of data deletion carefully, especially in production settings, to avoid unintended loss of critical information.Following this, notice that the wizard automatically recognizes the columns, identifying the semicolon (“;”) as the delimiter for columns in our CSV file.Furthermore, it displays the file’s encoding as utf-8, a crucial detail for ensuring the correct interpretation of character data. The wizard provides a clear mapping between the Source — the names of each column in the CSV file — and the Dest Column (Destination Column), which corresponds to the names of each column in our table.At the bottom, you’ll find a preview showcasing sample records from each column, allowing you to verify that the data is correctly aligned and formatted before proceeding with the import.This step is instrumental in ensuring that the data import process is precise and tailored to the structure of our destination table, facilitating a seamless integration of the CSV data into the SQL database environment.Upon clicking ”next” again, the wizard indicates it is ready to commence the data loading process, beginning the preparation phase. This step is pivotal as it transitions from setting up the import parameters to the actual execution of data transfer, ensuring that the information is accurately mapped and ready to be integrated into your database.This phase signifies the readiness of the system to ingest the CSV data into the specified table, marking a crucial step towards actualizing your data analysis objectives.When we click ”next” once more, the wizard will display a summary of the actions to be taken.This file contains 463 records, and this exact number should be imported. Should the import yield a different count, it indicates an issue within the environment. An initial investigation should be conducted to identify and rectify the problem. If assistance is required, feel free to reach out to @panData for support.Once completed, and the table has been successfully loaded, we will proceed to execute a brief SQL query.Navigate to our TB_SHIPS table, right-click, and select Select Rows — Limit 1000 to observe the results. This process not only confirms the successful import of our data but also provides an opportunity to visually inspect the records and ensure everything appears as expected, serving as a preliminary check of the data’s integrity and readiness for analysis.Having executed our initial SQL query and successfully retrieved data, we’ve reached a pivotal milestone in our journey. This achievement marks the beginning of our exploration into SQL and data analysis. Our work here lays the foundation for deeper inquiry and more complex operations in future sessions.I invite you to join me in the next lesson, where we will build upon this groundwork, delving further into the intricacies of SQL and data manipulation. Until then, I encourage you to experiment with the queries and familiarize yourself with the dataset we’ve introduced today.Stay curious, and I look forward to our continued exploration in the next session. See you there! 🐼❤️----Level Up Coding🐼 Derived from the Latin word "pan," which means "all" or "every," a personal repository of Data I have studied (and somewhat self-taught).HelpStatusAboutCareersBlogPrivacyTermsText to speechTeams

SQL Series: Preparing a Database Step by Step. This step is instrumental in ensuring that the data import process is precise and tailored to the structure of our destination table, facilitating a seamless integration of the CSV data into the SQL database environment

Ten Years of AI in Review