<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Nicola Simboli on Medium]]></title>
        <description><![CDATA[Stories by Nicola Simboli on Medium]]></description>
        <link>https://medium.com/@simboli?source=rss-cd0fe0e8ffb4------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*ThbI7NoSHzjtBuIFHq56-w.jpeg</url>
            <title>Stories by Nicola Simboli on Medium</title>
            <link>https://medium.com/@simboli?source=rss-cd0fe0e8ffb4------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 15:21:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@simboli/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Which Tableau Cloud licences is nobody using? A SQL answer in 10 minutes]]></title>
            <link>https://simboli.medium.com/which-tableau-cloud-licences-is-nobody-using-a-sql-answer-in-10-minutes-b78b83104c5c?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/b78b83104c5c</guid>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[finops]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[duckdb]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Wed, 11 Nov 2026 16:21:17 GMT</pubDate>
            <atom:updated>2026-11-11T16:21:17.202Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Renewal season is the one time of year when “who actually uses this?” has a price tag. Here is how to answer it with one query and a file on your laptop.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mKLipIkwLZFnjEUK9nNLew.png" /><figcaption><em>What 92 idle seats cost on an illustrative 400-seat site, at Standard list prices.</em></figcaption></figure><p>A Tableau Cloud Creator seat lists at $75 per user per month, billed annually. That is $900 a year. An Explorer is $504, a Viewer $180. On the Enterprise edition the same three seats cost $1,380, $840 and $420.</p><p>So ten Creators who left the company, changed team, or never got past the welcome e-mail are a $9,000 line on your renewal quote. Nobody decided to spend it. It is just there, because finding those ten people is tedious enough that it rarely gets done before the contract is signed.</p><p>It does not have to be tedious. This article walks through the whole job: collect the site, list the idle seats, put a price on them, and avoid the two mistakes that make admins reclaim the wrong licence.</p><h3>Why not just use Admin Insights?</h3><p>You can. The TS Users data source has a “Days Since Last Login” field, and a quick workbook on top of it gives you a list.</p><p>It gets awkward after that. You cannot easily combine it with token usage or your own price list. You cannot hand it to a colleague or a consultant without handing over every name and e-mail address on the site. And the event data behind it is kept for 90 days, so next year’s “is this getting better or worse?” has nothing to compare against.</p><p>SQL on a local file solves all three. The tool I use for this is <a href="https://github.com/simboli/tableau-cloud-audit">tableau-cloud-audit</a>, an open-source collector I maintain. It reads a site through the official APIs and writes everything into a single DuckDB file, with user identities replaced by pseudonyms (U-0042) before anything touches disk.</p><h3>Step 1: collect the site (about five minutes)</h3><p>You need Python 3.11 or later and a Personal Access Token from a site administrator.</p><pre>pip install tableau-cloud-audit</pre><pre>tca init                              # wizard: site, pod, token name<br>export TCA_PAT_SECRET=&#39;&lt;PAT secret&gt;&#39;  # never written to a file<br>export TCA_DB_KEY=&#39;&lt;passphrase&gt;&#39;      # optional: encrypts the file at rest<br>tca verify                            # checks the token and Admin Insights access<br>tca collect                           # resumable</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Du9vHqY8FGZeAwxaPdHixA.png" /><figcaption><em>Verify, collect, browse: the whole setup runs from the terminal, with your own token.</em></figcaption></figure><p>A full collection, permissions and lineage included, took 4 minutes 9 seconds on the first external site that ran it (28 users, 485 content items). Larger sites take longer, and an interrupted run continues with tca collect --resume.</p><p>Then open the file with any DuckDB client:</p><pre>ATTACH &#39;your-site.duckdb&#39; AS audit (ENCRYPTION_KEY &#39;passphrase&#39;, READ_ONLY);<br>USE audit;</pre><p>From here on there is no API, no rate limit and no waiting.</p><h3>Step 2: list the idle seats</h3><p>One query, straight from the project’s <a href="https://simboli.github.io/tableau-cloud-audit/cookbook/inactive-users/">cookbook</a>:</p><pre>SELECT u.user_pseudo, u.site_role, u.last_login_at,<br>       date_diff(&#39;day&#39;, u.last_login_at, current_timestamp::TIMESTAMP) AS days_inactive<br>FROM state.v_users_current u<br>WHERE u.last_login_at IS NULL<br>   OR u.last_login_at &lt; current_timestamp::TIMESTAMP - INTERVAL 90 DAY<br>ORDER BY days_inactive DESC NULLS FIRST;</pre><pre>┌─────────────┬───────────┬─────────────────────┬───────────────┐<br>│ user_pseudo │ site_role │    last_login_at    │ days_inactive │<br>├─────────────┼───────────┼─────────────────────┼───────────────┤<br>│ U-0117      │ Creator   │ NULL                │          NULL │<br>│ U-0042      │ Creator   │ 2025-09-14 08:21:03 │           423 │<br>│ U-0288      │ Explorer  │ 2026-01-27 16:44:51 │           288 │<br>│ U-0031      │ Creator   │ 2026-03-02 09:05:12 │           254 │<br>│ ...         │ ...       │ ...                 │           ... │<br>└─────────────┴───────────┴─────────────────────┴───────────────┘</pre><p>The rows fall into two groups, and they tell different stories. A NULL last login is usually an onboarding problem: someone was added in bulk and never came. An old date is usually a lifecycle problem: someone used Tableau, then stopped.</p><h3>Step 3: put a price on it</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NIqrI9lVRxklW8OMETexQg.png" /><figcaption><em>Explorers, not Creators, are the biggest line: cheaper seats, but many more of them.</em></figcaption></figure><p>A list of pseudonyms does not move a budget conversation. A number does, and it only takes a count per role and your price list.</p><p>Take an illustrative 400-seat site, not a real customer, where the query returns 92 rows. That is 23% of the site, split like this at Standard list prices:</p><p>At Enterprise list prices the same 92 seats come to $60,300. Use what your contract actually says; list prices are from November 2026 and almost nobody pays exactly list.</p><p>One thing the table makes obvious: the largest waste is not where you would look first. Creators are the expensive seat, but Explorers are the expensive <em>group</em>, because there are more of them and they are easier to hand out.</p><h3>Step 4: do not reclaim the wrong seat</h3><p>This is the step that separates a useful list from an incident. “Has not signed in” has two blind spots.</p><p><strong>Service accounts.</strong> The last-login date records interactive sign-ins. An account that only ever talks to Tableau through a Personal Access Token, publishing extracts or running a nightly script, looks dead and is not. The collector also lands the Admin Insights Tokens source, with the date each token was last used: check every idle user against it before touching anything. The account that has not signed in since January and used a token at two o’clock this morning is your ETL job. Unlicense it and tomorrow’s dashboards are empty.</p><p><strong>Subscription-only readers.</strong> Some people never open Tableau because a scheduled e-mail brings the dashboard to them every Monday. They do not sign in, but they would notice. Ask before you remove them, or check the subscription list first.</p><h3>Step 5: turn pseudonyms into names</h3><p>Everything so far ran on pseudonyms, which means you could have shared the result above without exposing a single person. When it is time to act, swap state.v_users_current for clear.users in the same query and it comes back with real names and e-mail addresses.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IFHk0jgT6pO5R7qH-Ssb6Q.png" /><figcaption><em>Real identities live in one local table; everything else in the file sees only pseudonyms.</em></figcaption></figure><p>The clear.* views join against an identity table that exists only in your local file. tca export produces a copy without it, so that join is impossible on anything you send to someone else.</p><h3>What to do with the list</h3><ol><li><strong>Unlicense, do not delete.</strong> Setting a user to Unlicensed frees the seat and keeps their content and ownership intact. It is also easy to reverse.</li><li><strong>Fix the tap, not just the puddle.</strong> If most of your idle seats have never signed in at all, look at Grant License on Sign In: users get a seat the first time they show up, not the day someone uploads a CSV of names.</li><li><strong>Run it again next month.</strong> The collector accumulates events across runs, so the file keeps history that Admin Insights has already dropped. Next renewal you will have a twelve-month trend to bring to the negotiation.</li></ol><h3>Try it, and tell me what breaks</h3><p>The project is a working alpha. The first run on someone else’s site surfaced three bugs that my sandbox never could, and all three were fixed the same day. If you try it, tca diagnostics --format markdown prints a report with no names, e-mails or identifiers in it, ready to paste into a <a href="https://github.com/simboli/tableau-cloud-audit/issues">GitHub issue</a>. Scale reports from large sites are especially welcome.</p><ul><li>Repository: <a href="https://github.com/simboli/tableau-cloud-audit">https://github.com/simboli/tableau-cloud-audit</a></li><li>Documentation and more recipes: <a href="https://simboli.github.io/tableau-cloud-audit/cookbook/">https://simboli.github.io/tableau-cloud-audit/cookbook/</a></li></ul><p><em>tableau-cloud-audit is an independent open-source project, not affiliated with or endorsed by Salesforce. Tableau and Tableau Cloud are trademarks of Salesforce, Inc.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b78b83104c5c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cutting hiring time in half: the power of AWS Bedrock for Talent Acquisition]]></title>
            <link>https://simboli.medium.com/cutting-hiring-time-in-half-the-power-of-aws-bedrock-for-talent-acquisition-147d4b613a00?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/147d4b613a00</guid>
            <category><![CDATA[talent-acquisition]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[llama-3]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[aws-bedrock]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Mon, 13 Jan 2025 07:51:58 GMT</pubDate>
            <atom:updated>2025-01-13T07:51:58.827Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="Image credit: Amazon Web Services, Inc. The AWS Bedrock logo is a registered trademark of Amazon." src="https://cdn-images-1.medium.com/max/904/1*qxuQN3gQSv9IHgw7Mn4z7w.png" /></figure><p>Recruitment has always been a complex and time-consuming process. Traditional resume screening methods often rely on keyword matching, which can miss context, overlook talented candidates, and favor those who optimize for keywords rather than fundamental skills. Additionally, these methods are prone to unconscious biases, which can inadvertently impact diversity and fairness in hiring. AWS Bedrock offers a transformative approach to recruitment by using generative AI. Unlike traditional systems, Bedrock models can analyze resumes holistically, extracting contextual insights and aligning them with job requirements. This enables organizations to make faster, fairer, and more data-driven hiring decisions.</p><h3>What is AWS Bedrock?</h3><p>AWS Bedrock provides access to foundational AI models (FMs) from providers like Claude, Titan, and Llama. These models can be easily integrated into applications without requiring in-depth machine learning expertise. For recruitment workflows, AWS Bedrock simplifies the integration of advanced AI capabilities. Pre-trained models eliminate the need for extensive training data while enabling rapid deployment.</p><h3>How AI Evaluates Fit</h3><p>Generative AI models on AWS Bedrock process resumes in their entirety, identifying skills, experiences, and achievements that align with job requirements. These models understand:</p><ul><li><strong>Context:</strong> Recognizing the depth and relevance of experiences.</li><li><strong>Phrasing:</strong> Interpreting nuanced descriptions of roles and responsibilities.</li><li><strong>Intent:</strong> Capturing transferable skills and potential growth areas.</li></ul><p>The experiment utilized two input files to store the necessary data:</p><p><strong>Job Description File (</strong><a href="https://www.simboli.eu/resources.php#search=Cutting%20hiring%20time%20in%20half"><strong><em>job_description.json</em></strong></a><strong>)</strong>:</p><p>Contains details of the job requirements and expectations.</p><pre>{<br>    &quot;job_description&quot;: &quot;We are looking for a Python Developer with experience in web development and cloud technologies. Requirements: - Proficiency in Python and Django. - Experience with AWS services like S3, Lambda, and DynamoDB. - Strong problem-solving skills. - Good communication skills.&quot;<br>}</pre><p><strong>Candidate Resumes File (</strong><a href="https://www.simboli.eu/resources.php#search=Cutting%20hiring%20time%20in%20half"><strong>candidate_resumes.json</strong></a><strong>)</strong>:</p><p>Stores an array of resumes with details like skills, experience, and education.</p><pre>[<br>    {<br>        &quot;name&quot;: &quot;Alice Smith&quot;,<br>        &quot;skills&quot;: [&quot;Python&quot;, &quot;Django&quot;, &quot;AWS Lambda&quot;, &quot;S3&quot;, &quot;Docker&quot;, &quot;REST APIs&quot;],<br>        &quot;experience&quot;: [<br>            {<br>                &quot;role&quot;: &quot;Python Developer&quot;,<br>                &quot;duration&quot;: &quot;3 years&quot;,<br>                &quot;details&quot;: &quot;Developed web applications using Django and deployed on AWS.&quot;<br>            }<br>        ],<br>        &quot;education&quot;: &quot;BSc Computer Science&quot;<br>    },<br>    {<br>        &quot;name&quot;: &quot;Bob Johnson&quot;,<br>        &quot;skills&quot;: [&quot;Java&quot;, &quot;Spring&quot;, &quot;PostgreSQL&quot;, &quot;Kubernetes&quot;],<br>        &quot;experience&quot;: [<br>            {<br>                &quot;role&quot;: &quot;Software Engineer&quot;,<br>                &quot;duration&quot;: &quot;5 years&quot;,<br>                &quot;details&quot;: &quot;Built scalable backend systems using Java Spring Framework.&quot;<br>            }<br>        ],<br>        &quot;education&quot;: &quot;MSc Software Engineering&quot;<br>    },<br>    {<br>        &quot;name&quot;: &quot;Charlie Lee&quot;,<br>        &quot;skills&quot;: [&quot;Python&quot;, &quot;Flask&quot;, &quot;AWS DynamoDB&quot;, &quot;React&quot;],<br>        &quot;experience&quot;: [<br>            {<br>                &quot;role&quot;: &quot;Web Developer&quot;,<br>                &quot;duration&quot;: &quot;2 years&quot;,<br>                &quot;details&quot;: &quot;Created dynamic websites using Flask and React, integrated with AWS DynamoDB.&quot;<br>            }<br>        ],<br>        &quot;education&quot;: &quot;BSc Information Technology&quot;<br>    },<br>    {<br>        &quot;name&quot;: &quot;Diana Morales&quot;,<br>        &quot;skills&quot;: [&quot;Python&quot;, &quot;FastAPI&quot;, &quot;Azure&quot;, &quot;Docker&quot;, &quot;CI/CD Pipelines&quot;],<br>        &quot;experience&quot;: [<br>            {<br>                &quot;role&quot;: &quot;Cloud Developer&quot;,<br>                &quot;duration&quot;: &quot;2 years&quot;,<br>                &quot;details&quot;: &quot;Developed microservices using FastAPI and deployed to Azure Kubernetes Service.&quot;<br>            },<br>            {<br>                &quot;role&quot;: &quot;Data Analyst&quot;,<br>                &quot;duration&quot;: &quot;3 years&quot;,<br>                &quot;details&quot;: &quot;Analyzed large datasets and built automated ETL pipelines using Python.&quot;<br>            }<br>        ],<br>        &quot;education&quot;: &quot;BSc Data Science&quot;<br>    },<br>    {<br>        &quot;name&quot;: &quot;Edward Turner&quot;,<br>        &quot;skills&quot;: [&quot;Python&quot;, &quot;Django&quot;, &quot;AWS EC2&quot;, &quot;S3&quot;, &quot;Terraform&quot;],<br>        &quot;experience&quot;: [<br>            {<br>                &quot;role&quot;: &quot;Backend Developer&quot;,<br>                &quot;duration&quot;: &quot;4 years&quot;,<br>                &quot;details&quot;: &quot;Designed and implemented RESTful APIs using Django and deployed on AWS.&quot;<br>            },<br>            {<br>                &quot;role&quot;: &quot;DevOps Engineer&quot;,<br>                &quot;duration&quot;: &quot;2 years&quot;,<br>                &quot;details&quot;: &quot;Automated infrastructure provisioning using Terraform and AWS services.&quot;<br>            }<br>        ],<br>        &quot;education&quot;: &quot;MSc Computer Science&quot;<br>    }<br>]This experiment leveraged three foundational models available in AWS Bedrock to evaluate candidate fit:</pre><p><strong>Claude (</strong>Anthropic)</p><ul><li><strong>Specialty:</strong> Contextual understanding and nuanced language interpretation.</li><li><strong>Use Case:</strong> Ideal for evaluating resumes with complex phrasing and extracting deep insights from descriptions.</li></ul><p><strong>Titan (</strong>Amazon)</p><ul><li><strong>Specialty:</strong> Fast and scalable performance for structured tasks.</li><li><strong>Use Case:</strong> Suitable for general-purpose ranking and scoring of candidates.</li></ul><p><strong>Llama </strong>(Meta)</p><ul><li><strong>Specialty:</strong> Versatile capabilities with a focus on intent recognition and text generation.</li><li><strong>Use Case:</strong> Excels in capturing transferable skills and assessing potential fit for multi-disciplinary roles.</li></ul><h3>Notebook Workflow</h3><h4>Data Preparation</h4><p>The dataset included a JSON file with the job description and another with candidate resumes:</p><pre>import json<br><br># Load candidate resumes<br>with open(&quot;candidate_resumes.json&quot;, &quot;r&quot;) as candidates_file:<br>    candidates = json.load(candidates_file)<br><br># Load job description<br>with open(&quot;job_description.json&quot;, &quot;r&quot;) as job_desc_file:<br>    job_description = json.load(job_desc_file)</pre><h4>Model Selection</h4><p>Three different models were evaluated: Claude, Titan, and Llama. Below is an example of invoking the Claude model:</p><pre>import boto3<br>import yaml<br><br># Load credentials from YAML file<br>with open(&quot;credentials.yml&quot;, &quot;r&quot;) as keyholder:<br>    credentials = yaml.safe_load(keyholder)<br><br>AWS_SERVER_PUBLIC_KEY = credentials[&#39;bedrock&#39;][&#39;AWS_SERVER_PUBLIC_KEY&#39;]<br>AWS_SERVER_SECRET_KEY = credentials[&#39;bedrock&#39;][&#39;AWS_SERVER_SECRET_KEY&#39;]<br>REGION_NAME = credentials[&#39;bedrock&#39;][&#39;REGION_NAME&#39;]<br><br># Initialize the Bedrock client<br>client = boto3.client(<br>    &quot;bedrock-runtime&quot;,<br>    region_name=REGION_NAME,<br>    aws_access_key_id=AWS_SERVER_PUBLIC_KEY,<br>    aws_secret_access_key=AWS_SERVER_SECRET_KEY,<br>)<br><br>def rank_candidates_with_claude(candidates, job_description):<br>    prompt_template = (<br>        &quot;Human: You are an AI model skilled in evaluating resumes for job positions. &quot;<br>        &quot;The job description is as follows:\n{job_description}\n\n&quot;<br>        &quot;Here are the candidate resumes:\n&quot;<br>        &quot;{candidate_resumes}\n\n&quot;<br>        &quot;Please rank the candidates based on how well they match the job description, &quot;<br>        &quot;providing a brief explanation for each ranking. Please explicitly give a number from 0 to 100 on how the candidate fits the position.\n\n&quot;<br>        &quot;Assistant:&quot;<br>    )<br><br>    job_desc_text = job_description[&quot;job_description&quot;]<br>    candidate_texts = [<br>        f&quot;Name: {c[&#39;name&#39;]}\nSkills: {c[&#39;skills&#39;]}\nExperience: {c[&#39;experience&#39;]}\nEducation: {c[&#39;education&#39;]}&quot;<br>        for c in candidates<br>    ]<br>    candidates_combined = &quot;\n\n&quot;.join(candidate_texts)<br><br>    prompt = prompt_template.format(<br>        job_description=job_desc_text,<br>        candidate_resumes=candidates_combined<br>    )<br><br>    response = client.invoke_model(<br>        body=json.dumps({&quot;prompt&quot;: prompt, &quot;max_tokens_to_sample&quot;: 1000}),<br>        modelId=&quot;anthropic.claude-v2&quot;,<br>        accept=&quot;application/json&quot;,<br>        contentType=&quot;application/json&quot;<br>    )<br><br>    return response[&quot;body&quot;].read().decode(&quot;utf-8&quot;)<br><br>candidates, job_description = candidates, job_description<br>result = rank_candidates_with_claude(candidates, job_description)<br>print(result)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zsz7J6uMz1M8nCA-YcuqPQ.png" /></figure><h4>Outcome</h4><p>The model identified Alice Smith and Edward Turner as the top candidates, scoring high on Python, Django, and AWS experience. Interestingly, it also highlighted Charlie Lee’s potential due to transferable skills despite missing direct Django experience.</p><p>While the full outputs of the models are available in the accompanying notebooks, the summarized results for each candidate and model are presented below:</p><p><strong>Claude (</strong>Anthropic)</p><blockquote><strong>1. Edward Turner (90/100)</strong> <br>Has very relevant skills in Python, Django, AWS services like S3, and infrastructure provisioning with Terraform. 4+ years of backend development experience and has worked on designing REST APIs. Fits the job requirements very closely.</blockquote><blockquote><strong>2. Alice Smith (80/100)</strong> <br>Strong skills in Python, Django, and AWS Lambda, S3. 3 years of relevant development experience including building web applications with Django. Lacks explicit mention of REST APIs or communication skills. Overall a good fit.</blockquote><blockquote><strong>3. Diana Morales (70/100)</strong> <br>Has Python experience and some AWS/cloud knowledge. Background seems more tailored for data science and analytics roles though. The FastAPI and Azure experience is less relevant for this position focused on Django and AWS. Still moderately aligned.</blockquote><blockquote><strong>4. Charlie Lee (60/100)</strong> <br>Has some relevant Python and DynamoDB experience, but is missing skills in Django and broader AWS services. Experience is focused on front-end web development rather than back-end systems. Partial fit but lacks key requirements.</blockquote><blockquote><strong>5. Bob Johnson (40/100)</strong> <br>Does not have any Python or AWS experience specified. The Java and Spring background is less relevant for this Python/Django focused role. Weak alignment with the job description overall.</blockquote><p><strong>Titan (</strong>Amazon)</p><blockquote><strong><em>1. Alice Smith (90/100)</em></strong><em> <br>Alice’s resume matches the job description perfectly. She has 3 years of experience as a Python Developer, with a focus on web development and AWS services. She has developed web applications using Django and deployed them on AWS, which is exactly what the job requires. Additionally, her education in Computer Science provides a strong foundation for Python development.</em></blockquote><blockquote><strong><em>2. Bob Johnson (80/100)</em></strong><em> <br>Bob’s resume also aligns well with the job description. While his experience is in Java Spring Framework, he has worked on scalable backend systems, which is relevant to the job. His education in Software Engineering further strengthens his qualifications.</em></blockquote><blockquote><strong><em>3. Charlie Lee (75/100)</em></strong><em> <br>Charlie’s resume demonstrates experience in web development with Python and React, as well as integration with AWS DynamoDB. While his experience is not as deep as Alice’s or Bob’s, it still shows proficiency in the required technologies.</em></blockquote><blockquote><strong><em>4. Diana Morales (70/100)</em></strong><em> <br>Diana’s resume showcases experience in cloud development with FastAPI and Azure Kubernetes Service, as well as data analysis with Python. While her experience is diverse, it may not be as directly relevant to the job as the other candidates.</em></blockquote><blockquote><strong><em>5. Edward Turner (65/100)</em></strong><em> <br>Edward’s resume includes experience in backend development with Django and AWS EC2, as well as DevOps engineering with Terraform and AWS services. While his experience is strong, it may not be as focused on web development as the other candidates.</em></blockquote><blockquote>Alice’s resume stands out for its direct alignment with the job description. Her experience in developing web applications using Django and deploying them on AWS is precisely what the job requires, making her the strongest candidate.<br>Bob also ranks highly due to his work on scalable backend systems, despite his focus on Java Spring Framework. His education further solidifies his fit.<br>The remaining candidates demonstrate relevant skills but lack the same depth or alignment as Alice and Bob.</blockquote><p><strong>Llama </strong>(Meta)</p><blockquote><strong><em>1. Edward Turner (90)</em></strong></blockquote><blockquote>- Edward has the most relevant experience in Python and Django, which are core requirements for the position.</blockquote><blockquote>- He also has experience with AWS services like S3 and EC2, which is a significant plus.</blockquote><blockquote>- His background as a DevOps Engineer adds value, demonstrating hands-on expertise in infrastructure provisioning and automation.</blockquote><blockquote><strong><em>2. Alice Smith (85)</em></strong></blockquote><blockquote>- Alice has solid experience with Python and Django, meeting the key requirements for the position.</blockquote><blockquote>- She also brings experience with AWS services like Lambda and S3, which are advantageous.</blockquote><blockquote>- Her proficiency with Docker highlights her familiarity with containerization technologies.</blockquote><blockquote><strong><em>3. Charlie Lee (75)</em></strong></blockquote><blockquote>- Charlie has experience with Python and Flask, which is a positive aspect.</blockquote><blockquote>- His familiarity with AWS DynamoDB aligns with the job’s requirements.</blockquote><blockquote>- However, his limited 2 years of experience and lack of exposure to Django, a critical requirement, lower his ranking.</blockquote><blockquote><strong><em>4. Diana Morales (70)</em></strong></blockquote><blockquote>- Diana has experience with Python and FastAPI, adding value to her profile.</blockquote><blockquote>- She also has cloud experience with Azure, which, while not required, showcases her versatility with cloud technologies.</blockquote><blockquote>- Despite this, her 2 years of experience and the absence of Django expertise impact her fit for the role.</blockquote><blockquote><strong><em>5. Bob Johnson (0)</em></strong></blockquote><blockquote>- Bob lacks relevant experience with Python or Django, the primary requirements for the position.</blockquote><blockquote>- His background in Java and Spring, while technically strong, does not align with the job’s expectations.</blockquote><blockquote>- The absence of cloud technology experience is a significant disadvantage.</blockquote><blockquote>Edward and Alice emerge as the strongest candidates, with Edward slightly ahead due to his extensive and relevant experience in both Django and AWS.<br>Charlie and Diana, while skilled, fall short in meeting some critical job requirements.<br>Bob’s profile is unfortunately not aligned with the role, lacking both Python/Django expertise and cloud experience.</blockquote><p>We encourage readers to explore the notebooks for more detailed results and explanations of how the models arrived at these scores.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/814/1*6gqXYhrvE4PymSHthX2YUw.png" /><figcaption>Recap of tests score (image by the author)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xbjdJX0T8rBmdUbK34fyEQ.png" /></figure><h4>Future Possibilities</h4><p>As AI evolves, Bedrock could further streamline recruitment by integrating video interview analysis, cultural fit assessments, and predictive career path modeling.</p><p>AWS Bedrock and its generative AI capabilities offer a powerful way to revolutionize hiring. By evaluating candidates holistically and aligning them with job requirements, Bedrock saves time, reduces bias, and improves hiring outcomes.</p><p>While AWS Bedrock provides valuable insights, the final selection of candidates still requires the expertise of talent acquisition professionals. This AI solution enables hiring teams to save significant time by quickly identifying potential candidates who align with the job requirements. Instead of spending countless hours on initial screenings, recruiters can focus their efforts on a smaller, more relevant pool of candidates, allowing for deeper evaluations and better hiring decisions.</p><p>Ready to transform your hiring process? <a href="https://www.simboli.eu/resources.php#search=Cutting%20hiring%20time%20in%20half">Download the notebooks</a> to explore how AWS Bedrock can streamline your recruitment workflow. Start your journey towards smarter hiring today!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=147d4b613a00" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Enhancing Quality in Agile: Poka-Yoke Tips for Product Managers]]></title>
            <link>https://simboli.medium.com/enhancing-quality-in-agile-poka-yoke-tips-for-product-managers-df8d8b7dc6c0?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/df8d8b7dc6c0</guid>
            <category><![CDATA[toyota-production-system]]></category>
            <category><![CDATA[agile-development]]></category>
            <category><![CDATA[agile-methodology]]></category>
            <category><![CDATA[lean-startup]]></category>
            <category><![CDATA[poka-yoke]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Mon, 08 Jan 2024 13:16:57 GMT</pubDate>
            <atom:updated>2024-01-08T13:16:57.737Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="DALL-E generated picture" src="https://cdn-images-1.medium.com/max/1024/1*V6RKfWUtHLb-IKUuOymnfQ.png" /></figure><p>Poka-Yoke, a term originating from Japanese manufacturing practices, is instrumental in pursuing quality assurance and error prevention. Translating to ‘mistake-proofing,’ this methodology was developed in the 1960s by Shigeo Shingo, a prominent engineer in the Toyota Production System. Its core principle lies in designing processes that inherently prevent errors or make them immediately detectable.</p><p>In Agile software development, Poka-Yoke transcends its manufacturing roots, offering a strategic approach to enhancing quality and efficiency. Agile, characterized by its iterative nature and emphasis on adaptability, aligns well with Poka-Yoke’s continuous improvement and error minimization ethos. The methodology’s focus on preventing errors before they occur or identifying them early complements Agile’s rapid, iterative cycles, enabling teams to maintain high-quality standards without compromising speed or flexibility.</p><p>Poka-Yoke’s integration into Agile methodologies is more than just about incorporating new tools or practices. It necessitates a shift in mindset — from one that reacts to errors post-occurrence to one that proactively anticipates and prevents them. This paradigm shift is essential in an environment where speed and adaptability are paramount and where the cost of errors can escalate rapidly if not addressed promptly.</p><p>In pursuing excellence within Agile software development, integrating Poka-Yoke principles offers a pathway to enhance process efficiency and product quality. This article delves into three fundamental principles of Poka-Yoke that Agile teams can adopt to fortify their development processes against errors.</p><p><strong>Principle 1: Prevention Over Detection</strong></p><p>The quintessence of Poka-Yoke lies in its emphasis on error prevention rather than detection. In an Agile setting, this translates to identifying potential sources of errors early in the development cycle and implementing measures to prevent them before they occur. This proactive approach is more cost-effective and less time-consuming than rectifying mistakes post-development.</p><p>In practical terms, prevention might involve establishing coding standards to avoid common programming errors or incorporating automated linting tools that check code for potential issues as it is written. By embedding these preventive measures into daily workflows, Agile teams can significantly reduce the incidence of defects, leading to a smoother, more reliable development process.</p><p><strong>Principle 2: Simplification and Standardization</strong></p><p>Simplification and standardization are crucial in minimizing errors. Complex processes with numerous variables are prone to mistakes, whereas simple, standardized procedures are more accessible to follow and less likely to result in errors. In Agile software development, this principle can be applied in various ways, such as simplifying user stories and acceptance criteria to ensure clarity or standardizing the design patterns and architectural practices across projects.</p><p>Moreover, standardizing tools and environments can also play a significant role. For instance, using a uniform development environment across the team can eliminate inconsistencies that might lead to errors. Simplification and standardization reduce errors, facilitate quicker onboarding of new team members, and enhance overall team efficiency.</p><p><strong>Principle 3: Immediate Feedback Mechanisms</strong></p><p>The third principle of Poka-Yoke involves the use of immediate feedback mechanisms. In Agile development, rapid feedback is vital for early detection and correcting issues. This principle aligns perfectly with Agile’s iterative approach, where continuous feedback loops are intrinsic to the process.</p><p>Practical applications of this principle include:</p><ul><li>Conducting regular code reviews.</li><li>Utilizing pair programming practices.</li><li>Implementing continuous integration systems that provide immediate feedback on integrating and testing new code.</li></ul><p>These practices enable immediate identification and resolution of issues, preventing minor errors from escalating into more significant problems.</p><p>Adopting these three Poka-Yoke principles can profoundly impact the efficacy and quality of Agile software development. By focusing on prevention over detection, embracing simplification and standardization, and utilizing immediate feedback mechanisms, Agile teams can significantly reduce the frequency and impact of errors. This proactive approach to error management not only enhances product quality but also contributes to a more efficient and streamlined development process, aligning perfectly with the Agile ethos of continuous improvement and adaptability.</p><p>The subsequent branches will further explore how these principles can be practically implemented through specific Poka-Yoke techniques in Agile software development, offering product managers and development teams actionable strategies to incorporate into their Agile practices.</p><p><strong>Technique 1: Using Automated Testing Tools</strong></p><p>Automation is a cornerstone in the application of Poka-Yoke in software development. Automated testing tools are essential for preventing errors from entering the final product. These tools perform various checks without human intervention, ensuring that new code additions or changes do not break existing functionalities.</p><p>Incorporating automated unit, integration, and acceptance tests into the continuous integration pipeline enables teams to detect issues early and frequently. Tools like Selenium for web applications, JUnit for Java applications, or PyTest for Python are examples of automated testing tools that can be integrated into the development workflow. These tools provide immediate feedback on code health, significantly reducing the manual effort required in testing and the likelihood of defects slipping through to production.</p><p><strong>Technique 2: Implementing Code Review Checklists</strong></p><p>Code reviews are fundamental to Agile software development, offering a platform for collaborative error detection and knowledge sharing. To enhance their effectiveness, implementing code review checklists can serve as a Poka-Yoke technique. These checklists standardize the review process, ensuring that standard errors are systematically checked, and best practices are consistently applied.</p><p>A code review checklist might include items such as compliance with coding standards, proper implementation of business logic, effective handling of edge cases, and adequate test coverage. By adhering to a structured checklist, teams can simplify the review process and significantly reduce the probability of human error, thereby improving code quality and maintainability.</p><p><strong>Technique 3: Continuous Integration and Deployment Practices</strong></p><p>Continuous integration (CI) and continuous deployment (CD) practices are pivotal in the Agile methodology, aligning closely with the principle of immediate feedback. CI/CD pipelines automate the process of integrating, testing, and deploying code, providing immediate feedback to developers on the impact of their changes.</p><p>In the context of Poka-Yoke, CI/CD pipelines help prevent integration errors, ensuring that code changes do not adversely affect the existing system. Tools like Jenkins, Travis CI, or GitLab CI can be used to create pipelines that automatically build, test, and deploy code. This not only speeds up the development process but also ensures that errors are caught and addressed promptly, thereby maintaining the stability and reliability of the software.</p><p>By implementing these practical Poka-Yoke techniques, Agile teams can significantly enhance the quality of their software products. Automated testing tools, code review checklists, and CI/CD practices are not just tools or procedures; they are manifestations of a mindset that prioritizes error prevention, simplification, and immediate feedback.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=df8d8b7dc6c0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating an Efficient Tableau Architecture for a Small Digital Startup: Key Strategies]]></title>
            <link>https://simboli.medium.com/creating-an-efficient-tableau-architecture-for-a-small-digital-startup-key-strategies-5362679013ab?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/5362679013ab</guid>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[startup]]></category>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-strategy]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Mon, 01 Jan 2024 08:31:01 GMT</pubDate>
            <atom:updated>2024-01-01T08:31:01.533Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*muuK9ODZ4iI76wcFGdon9Q.jpeg" /></figure><p>In the rapidly evolving data analytics landscape, small digital startups often find themselves at a crossroads when selecting and implementing the proper data visualization tools. With its comprehensive suite of services, including Tableau Cloud and Tableau Desktop, Tableau emerges as a leading choice. However, designing an effective Tableau architecture demands careful planning and strategic implementation. This article delves into ten practices for a Tableau data architect tasked with this responsibility.</p><h4>1. Assessing and Planning for Current and Future Needs</h4><p>The foundational step in establishing a Tableau infrastructure is a thorough assessment of the startup’s current and future data analytics needs. This assessment isn’t just about the volume of data or the number of users; it’s about understanding how data analytics aligns with the company’s goals. The architecture should be scalable, allowing for growth as the startup expands in terms of data volume and the complexity of data operations. This foresight will prevent bottlenecks and scalability issues in the future.</p><ul><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_deployment.htm">Tableau Deployment</a></li><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_overview.htm">Tableau Blueprint Overview</a></li></ul><h4>2. Optimizing for Cost-Effectiveness</h4><p>Startups operate under tight budgets, making cost-effective solutions crucial. Regarding Tableau, the data architect should carefully evaluate the different licensing options. Tableau offers a range of packages, each with its features and pricing models. The challenge lies in choosing a package that fits the current budget and offers the essential features required by the startup. Regular reviews and adjustments to the Tableau subscription are advisable as the company’s needs evolve.</p><ul><li><a href="https://www.tableau.com/pricing/teams-orgs">Decide the right mix of users for your team</a></li></ul><h4>3. Streamlining Data Sources and Connections</h4><p>Efficient data management is key to maximizing Tableau’s potential. For a small startup, it’s vital to integrate and streamline data sources effectively. This means establishing transparent and efficient connections to various databases and cloud services. The startup can ensure that its data analytics operations are agile and robust by optimizing data flow into Tableau. Utilizing Tableau’s diverse data connectors can simplify this process, enabling quick access to and processing data from multiple sources.</p><ul><li><a href="https://help.tableau.com/current/pro/desktop/en-us/datasource_plan.htm">Plan the Data Source</a></li><li><a href="https://help.tableau.com/current/pro/desktop/en-us/basicconnectoverview.htm">Connect to Your Data</a></li></ul><h4>4. Ensuring Data Quality and Consistency</h4><p>The integrity of data analytics is directly tied to the quality and consistency of the underlying data. A crucial role of the Tableau data architect is to set up routines and protocols that maintain high data quality standards. This involves implementing data cleaning processes, ensuring consistent data schemas across different sources, and establishing validation checks. Consistent and reliable data not only drives accurate analytics but also builds trust in the data-driven decision-making process within the startup.</p><ul><li><a href="https://help.tableau.com/current/pro/desktop/en-us/data_explore_analyze_interact.htm">Explore and Inspect Data in a View</a></li><li><a href="https://help.tableau.com/current/server/en-us/dm_dqw.htm">Set a Data Quality Warning</a></li></ul><h4>5. Implementing Robust Security Measures</h4><p>In the digital age, data security is non-negotiable, regardless of the organization’s size. For a startup, a breach can have far-reaching consequences. The data architect must enforce strict security protocols in setting up Tableau Cloud and Desktop. This includes secure user authentication processes, robust password policies, and meticulous user permissions management. Ensuring that sensitive data is accessed only by authorized personnel is critical to maintaining data integrity and protecting the startup from potential data breaches.</p><ul><li><a href="https://help.tableau.com/current/online/en-us/to_security.htm">Security in the Cloud</a></li><li><a href="https://help.tableau.com/current/server/en-us/security_data.htm">Data Security</a></li><li><a href="https://help.tableau.com/current/server/en-us/security.htm">Security</a></li></ul><h4>6. Simplify and Standardize Dashboards</h4><p>The power of Tableau lies in its ability to create intuitive and insightful dashboards. However, a standard approach can make dashboards more complex and clear. For startups, it is crucial to design user-friendly and visually appealing dashboards. This can be achieved by:</p><ol><li><strong>Using standardized templates</strong> to maintain consistency across different dashboards.</li><li><strong>Keeping designs simple and intuitive</strong> to enhance user understanding and engagement.</li><li><strong>Employing best practices in data visualization</strong> to ensure that the data is not just presented but tells a story.</li></ol><p>These practices help make the dashboards more accessible to all team members, regardless of their technical expertise, and facilitate quicker and more informed decision-making.</p><ul><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_analytics_best_practices_in_tableau.htm">Analytics Best Practices in Tableau</a></li><li><a href="https://help.tableau.com/current/pro/desktop/en-us/dashboards_best_practices.htm">Best Practices for Effective Dashboards</a></li><li><a href="https://help.tableau.com/current/pro/desktop/en-us/visual_best_practices.htm">Visual Best Practices</a></li></ul><h4>7. Promote a Data-Driven Culture</h4><p>The success of Tableau implementation partly depends on how well the team adapts to and embraces a data-driven culture. This involves:</p><ul><li><strong>Training employees</strong> not just on how to use Tableau but also on the importance of data in decision-making.</li><li><strong>Encouraging experimentation and exploration</strong> within Tableau, allowing team members to discover insights independently.</li><li><strong>Creating a collaborative environment</strong> where insights and data-driven strategies are shared and discussed.</li></ul><p>Promoting a culture where data is at the core of all significant decisions ensures that the investment in Tableau translates into tangible business benefits.</p><ul><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_data_literacy.htm">Data Literacy</a></li><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_data_management.htm">Tableau Data Management</a></li></ul><h4>8. Regular Maintenance and Updates</h4><p>Regular maintenance is essential to keep the Tableau environment efficient and secure. This includes:</p><ul><li><strong>Applying software updates and patches</strong> when they become available to access new features and security enhancements.</li><li><strong>Monitoring system performance</strong> to identify and resolve any issues promptly.</li><li><strong>Reviewing and refining data connections and sources</strong> to ensure they remain optimal as the business evolves.</li></ul><p>These practices help maintain a robust Tableau setup that continues to meet the changing needs of the startup.</p><ul><li><a href="https://help.tableau.com/current/online/en-us/to_maintenance_schedule.htm">Tableau Cloud System Maintenance</a></li><li><a href="https://help.tableau.com/current/server/en-us/monitor_maintenance.htm">Maintenance</a></li></ul><h4>9. Data Governance and Compliance</h4><p>Data governance is critical in managing the integrity, privacy, and accessibility of data. For startups, establishing a solid governance framework involves:</p><ul><li><strong>Developing clear policies</strong> on data access, usage, and sharing.</li><li><strong>Ensuring compliance with data protection laws</strong> like GDPR, CCPA, or others relevant to the startup’s operations.</li><li><strong>Implementing access controls and audit trails</strong> within Tableau to monitor data usage and protect sensitive information.</li></ul><p>Adequate data governance safeguards against legal and regulatory risks and builds stakeholders’ trust in the startup’s data handling practices.</p><ul><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_governance.htm">Tableau Governance</a></li><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_data_and_content_governance.htm">Tableau Data and Content Governance</a></li><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_governance_models.htm">Tableau Governance Models</a></li></ul><h4>10. Monitor Usage and Performance</h4><p>Finally, continually monitoring how Tableau is used and how it performs is key to realizing its full potential. This involves:</p><ul><li><strong>Tracking user engagement and adoption</strong> to understand how well the team integrates Tableau into their workflows.</li><li><strong>Analyzing system performance metrics</strong> to identify any bottlenecks or inefficiencies.</li><li><strong>Gathering user feedback</strong> to make necessary training, support, and dashboard design adjustments.</li></ul><p>Regular monitoring allows the startup to fine-tune its Tableau setup, ensuring it remains aligned with the company’s evolving needs and goals.</p><ul><li><a href="https://help.tableau.com/current/blueprint/en-us/bp_monitoring.htm">Tableau Monitoring</a></li><li><a href="https://help.tableau.com/current/server/en-us/perf_monitor.htm">Performance Monitoring Overview</a></li></ul><h3>Conclusion</h3><p>Designing a Tableau architecture for a small digital startup requires a balanced approach that caters to immediate needs while being mindful of future growth and challenges. A Tableau data architect can lay a solid foundation for a robust, scalable, and efficient data analytics environment by focusing on these ten key areas. This foundation supports the startup’s current operations and positions it well for future expansion and success in the data-driven digital world.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5362679013ab" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to add images as dimensions in a Tableau table DRAFT]]></title>
            <link>https://medium.com/data-science/how-to-add-images-as-dimensions-in-a-tableau-table-draft-219e65b7eced?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/219e65b7eced</guid>
            <category><![CDATA[tableau-server]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[tableau-desktop]]></category>
            <category><![CDATA[table]]></category>
            <category><![CDATA[tableau]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Thu, 07 Jan 2021 06:44:27 GMT</pubDate>
            <atom:updated>2021-01-07T13:57:47.764Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to add images as dimensions in a Tableau table</h3><h4><strong>In this tutorial, we will understand how to use images in one or more columns of a simple Tableau table.</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U4IwUA7QwERFr8HwmomqlQ.gif" /><figcaption>Image by Author</figcaption></figure><p><strong>You can download a zip file with everything covered in this tutorial from </strong><a href="https://www.simboli.eu/resources.html#search=columns%20of%20table.zip"><strong>this link</strong></a><strong>.</strong> In the zip archive, you will find the Tableau Workbook that we will produce in this tutorial or you can follow the instruction and make it by yourself.</p><p>In this story, I will use Tableau Desktop 2018.3 (trial) for macOS systems.</p><h4>Prepare the working environment</h4><p>First of all, we have to <a href="https://www.tableau.com/drive/custom-shapes">prepare a <strong>custom palette shape</strong></a>, it could sound difficult but it is very easy, the only thing we have to do is to copy the “<em>West Europe flags</em>” folder into one of the following, depending on if you are using a Mac or a Windows system.</p><blockquote><strong>Mac</strong></blockquote><blockquote><em>/Users/&lt;user&gt;/Documents/My Tableau Repository</em></blockquote><blockquote><strong>Windows</strong></blockquote><blockquote><em>C:\Users\&lt;user&gt;\Documents\My Tableau Repository</em></blockquote><p>Flags PNG files are provided from the <a href="https://flagpedia.net/download/icons">Flagpedia web site</a>.</p><h4>Dataset</h4><p>We will use the file World Health 2020 dataset produced by the World Health Organization (WHO) and, in particular, the “<em>alcoholSubstanceAbuse.csv</em>” file. The CSV file is included in the zip archive described above but you can find the whole dataset by clicking on <a href="https://www.kaggle.com/utkarshxy/who-worldhealth-statistics-2020-complete?select=roadTrafficDeaths.csv">this link to the relative Kaggle page</a>.</p><h4>Let’s go</h4><p>As we are data analysts and not magicians, there is no reason to keep secrets over our workaround to include images inside columns of a Tableau table. <strong>The workaround basically consists of making two tables with </strong><a href="https://anthonysmoak.com/2020/10/06/understanding-tableau-context-filters/"><strong>the same context</strong></a><strong> and overlap them, using transparent backgrounds.</strong> The best (and probably the only) way to include custom images into a Tableau table, is to create a custom shape palette and to use it inside the Marks area, this allows users to customize tables by adding dimensions related images directly inside the table, but does not allow to have full control over the positions of the images, as you could only decide to include them into the Marks area.</p><p>The following instruction will accompany you to all the steps in making a working example of this workaround.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ko5l0KAPqU7cUzq7cXjlXg.png" /><figcaption>Image by Author</figcaption></figure><p>As usual, we have to connect the Tableau Desktop workbook to our data source. In the “<em>Data Source</em>” tab, we insert a data source filter on the Location field, so we will work only with the Country of West Europe, and in particular:</p><ul><li>Austria</li><li>France</li><li>Germany</li><li>Ireland</li><li>Italy</li><li>Portugal</li><li>Spain</li><li>Switzerland</li><li>United Kingdom of Great Britain and Northern Ireland</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kPxLbl4dyWruPjKwmUYARg.png" /><figcaption>Image by Author</figcaption></figure><p>Now it’s time to move to “<em>Sheet 1”</em> and follow the steps described below:</p><ol><li>Rename “<em>Sheet 1</em>” to “<em>Legend</em>”;</li><li>In the Marks menu select “<em>Shape</em>” from the dropdown list;</li><li>Drag the “<em>Location</em>” pill from the Dimensions list and drop it on the “<em>Shape</em>” icon in the Marks menu;</li></ol><p>4. Click on the “<em>Shape</em>” icon;</p><p>5. Hit the “<em>Reload Shapes</em>” button, this should load our brand new custom shape palette with the flags of the Countries;</p><p>6. Select “<em>Flags West Europe</em>” from the drop-down palette;</p><p>7. Map every item (Country) with its flag and then click OK;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/636/1*pkT1J52SQAC3fOtjl0vAzw.png" /><figcaption>Image by Author</figcaption></figure><p>8. Create a new Worksheet and rename it “<em>Table data</em>”;</p><p>9. In the Analysis menu uncheck “<em>Aggregate Measures</em>”;</p><p>10. Drag and drop “<em>Location</em>” and “<em>Dim1</em>” pills in the Rows area;</p><p>11. Drag and drop the “<em>First Tooltip</em>” pill in the Marks area;</p><p>12. Drag and drop “<em>Period</em>” in the Filters area;</p><p>13. In the General tab, check “<em>Use all</em>”;</p><p>14. In the Rows area, right-click on “<em>Location</em>” and select “Show Filter”;</p><p>15. Click the down arrow on the “<em>Location</em>” filter controller on the right bar and set it to “<em>Single value (list)</em>”;</p><p>16. Click again, go to “<em>Customize</em>” and uncheck “<em>Show All Value</em>”;</p><p>17. In the Filters area, right-click on the “<em>Period</em>” pill and hit “Show filter”;</p><p>18. Click the down arrow on the “<em>Period</em>” filter controller on the right bar and set it to “<em>Single value (list)</em>”;</p><p>19. Click again, go to “<em>Customize</em>” and uncheck “<em>Show All Value</em>”;</p><p>20. Right on the “<em>Location</em>” pill in the Rows area and select “<em>Filter</em>”;</p><p>21. Go to the “<em>Top</em>” tab and select top 5 by field “<em>First Tooltip</em>”;</p><p>22. Right on the “<em>Location</em>” pill in the Rows area and select “<em>Sort</em>”;</p><p>23. Sort by “<em>First Tooltip</em>” filed ascending;</p><p>24. Create a calculated field named “<em>Flag icon</em>” and set it to blank;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/580/1*1cVnctkLCTa0bsEDGdCzAQ.png" /><figcaption>Image by Author</figcaption></figure><p>25. Drag the “<em>Flag icon</em>” pill and drop it in the Rows area between “<em>Location</em>” and “<em>Dim1</em>”;</p><p>26. Set the row height to (more or less) double of the actual height;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/913/1*qknyOg20oBYMSpiTjfJwgg.png" /><figcaption>Image by Author</figcaption></figure><p>27. Go to the “<em>Format Shading</em>” menu and set “<em>None</em>” to Default, Worksheet;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XcwdQkNq-WkJy9LOEJuKYg.png" /><figcaption>Image by Author</figcaption></figure><p>28. Duplicate the worksheet “<em>Table data</em>” and rename it to “<em>Table flags</em>”;</p><p>29. Go to “<em>Table flags</em>” worksheet;</p><p>30. In the Marks area select “<em>Shape</em>” from the dropdown list;</p><p>31. Drag the “<em>Location</em>” pill and drop it on the Shape icon;</p><p>32. Delete “<em>First Tooltip</em>” pill from the Marks area;</p><p>33. Right-click on the “<em>Location</em>” label and select “<em>Format…</em>”;</p><p>34. Set white color on the Default, Font window;</p><p>35. Right-click on “<em>Portugal</em>” (on any other Country) and select “<em>Format…</em>”;</p><p>36. Do the same on the “<em>Dim1</em>” values;</p><p>37. Go to the “<em>Borders</em>” tab in the Format menu and put “<em>None</em>” to Column Divider and Rows Divider;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/872/1*-ok0tH0viCLNObY7oHFLWg.png" /><figcaption>Image by Author</figcaption></figure><p>38. Create a new dashboard and rename it “<em>alcohol substance abuse</em>”;</p><p>39. Drag “<em>Table data</em>” and “<em>Table flags</em>” and drop them into the dashboard;</p><p>40. Set the tables to floating and remove any legends or filter controllers;</p><p>41. Click on the down arrow in the “<em>Table data</em>” area and select Filters, “<em>Period</em>”;</p><p>42. Click on the down arrow in the “<em>Period</em>” filter controller and select “<em>Single Value (list)</em>”;</p><p>43. Click on the down arrow in the “<em>Period</em>” filter controller and uncheck Customize, “<em>Show All Value</em>”;</p><p>44. Click on the down arrow in the “<em>Period</em>” filter controller and check to Apply to Worksheets, “<em>All Using This Data Source</em>”;</p><p>45. Do the same for the “<em>Dim1</em>” filter;</p><p>46. Click on the down arrow in the “<em>Table flags</em>” area and select Floating Order, “<em>Send to Back</em>”;</p><p>47. Click on the down arrow in the “<em>Table flags</em>” area and uncheck “<em>Title</em>”;</p><p>48. Click on the down arrow in the “<em>Table data</em>” area and select Floating Order, “<em>Bring to Front</em>”;</p><p>49. Click on the down arrow in the “<em>Table data</em>” area and uncheck “<em>Title</em>”;</p><p>50. In the layout tab set the same values for <em>x</em>, <em>y</em>, <em>w,</em> and <em>z</em> for “<em>Table data</em>” and “<em>Table flags</em>” as well;</p><p>51. Select “<em>Table data</em>” and, set “<em>None</em>” to the background in the layout panel;</p><p>51. Adjust manually the column width to make appear the icon under the “<em>Flag icon</em>” label;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9P_ZNwug01i3JIW5UqVWiw.png" /><figcaption>Image by Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t06QRekq8aeW4kywDOXY3g.png" /><figcaption>Image by Author</figcaption></figure><h4>Conclusions</h4><p>At the end of this long list of steps, you probably have developed your own Tableau Workbook with the Country flags in the second column. If you encountered any problems <a href="https://www.simboli.eu/resources.html#search=columns%20of%20table.zip">you can download the zip file</a> with the finished Tableau workbook. If you found any fasted solution feel free to share it in the comments area right down below and, of course, free to get in touch with me following my reference described below.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=219e65b7eced" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/how-to-add-images-as-dimensions-in-a-tableau-table-draft-219e65b7eced">How to add images as dimensions in a Tableau table DRAFT</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Probably the best introduction about JOIN, CROSS APPLY, UNION, CROSS JOINS and more in SQL Server]]></title>
            <link>https://medium.com/data-science/probably-the-best-introduction-about-join-cross-apply-union-cross-joins-and-more-in-sql-server-f2ee8f8af957?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/f2ee8f8af957</guid>
            <category><![CDATA[join]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[e-scooter]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Fri, 22 May 2020 09:02:42 GMT</pubDate>
            <atom:updated>2020-05-25T14:06:32.655Z</atom:updated>
            <content:encoded><![CDATA[<p>In this story, we will start from zero to understand most of the way in which we can combine two or more tables in SQL Server. We will use SQL Server 2019, but almost every query described below could be executed on older versions of SQL Server or even on other SQL databases like Oracle or MySQL with very small adjustments.</p><p><strong>Before start, it is important to know that the structure, the data, and all the queries in this article can be found </strong><a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&amp;fiddle=bb54aaea7e0a2527a7e5b14b6dc922e6"><strong>on this page of db&lt;&gt;fiddle</strong></a><strong>.</strong> You will find it very useful as they are both editable and executable, so feel free to experiment with everything that comes in your mind.</p><h3>Set up the environment</h3><p>We will use the following tables and example data for all the queries. Imagine that you are looking at (a very very simplified version of) the data warehouse of an e-scooter rental service like Bird, Lime, Dott.</p><p>We have the following tables:</p><ul><li><strong>Table [dbo].[Trip]</strong>: a transactional table with all the trips. It has one row per trip or, in other words, one row is generated every time a user unlocks the scooter, use it for commuting and lock it again.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/828/1*C5g6fKe9Y1mi03tIP3Y46g.png" /></figure><ul><li><strong>Table [dbo].[User]</strong>: is the dimension table of users.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/143/1*GK_AqUBZV-jSBbiYLzyFmQ.png" /></figure><ul><li><strong>Table [dbo].[Scooter]</strong>: is the dimension table of scooters.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/329/1*TvY8lWeDEuVYC0JIchIz8g.png" /></figure><ul><li><strong>Table [dbo].[RepairedScooter]</strong>: it is a table that contains the list of scooters that passed at least one time for the repair ward.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/469/1*pC5SzgE3zw_sbe5UQrwlgw.png" /></figure><p>You can view the code to create and populate the data from db&lt;&gt;fiddle but let see a preview as well.</p><h3>Finding a combination of different columns</h3><p>We can use CROSS JOIN (or Cartesian join) in different situations but, probably, its most used scope is when you want to SELECT all the combinations of two or more fields from one or more tables.</p><p>In the following example, you can use CROSS JOIN to make a SELECT with every combination of scooters and users. For example, it could be useful if you want to make the first table to analyze if users rent the same scooters more than once or maybe not.</p><blockquote><strong>— Query A001</strong><br>SELECT DISTINCT A.[ID],A.[Username],B.[ID],B.[Brand],B.[Model]<br>FROM [User] A,[Scooter] B<br>ORDER BY A.[ID],B.[ID]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/329/1*rnZxPmi9THehhnYalMwjrQ.png" /></figure><h3>Working with columns of different tables</h3><p>A JOIN is used when you want to add to a table (Table A) one or more columns from another table (Table B). It is probably the most important construct to master if you want to work with SQL based database.</p><h4>Introduction to syntax</h4><p>Before understanding the different types of joins lets have a quick overlook of the syntax to use when we want to make a JOIN between tables.</p><p>The following query is a basic JOIN from the table [Trip] (named with the alias “A”) and the table [Scooter] (name with the alias “B”). The type of join is INNER JOIN, we will understand what does it means later in this section of the article. The join conditions are listed after the ON clause. Here there is one simple equality between columns, but they could be more than one (in this case you should use the AND, OR, NOT operators) or could be something more sophisticated like BETWEEN or LIKE. In the JOIN conditions, you can also find functions applied to the columns (like LEFT for example) but this is an introduction so we will not find any of these cases.</p><blockquote><strong>— Query B001</strong><br>SELECT A.[ID],A.[DateTimeStart],A.[TripDuration],B.[Brand],B.[Model]<br>FROM [Trip] A<br>INNER JOIN [Scooter] B<br>ON A.[ID_Scooter]=B.[ID]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/556/1*n5kN7NeMRNtl60h3JO0oXA.png" /></figure><p>We used some aliases in the query but, we could also repeat the whole name of the table and get the same result.</p><blockquote><strong>— Query B003</strong><br>SELECT [Trip].[ID],[Trip].[DateTimeStart],[Trip].[TripDuration],[Scooter].[Brand],[Scooter].[Model]<br>FROM [Trip]<br>INNER JOIN [Scooter]<br>ON [Trip].[ID_Scooter]=[Scooter].[ID]</blockquote><p>It is not mandatory (but recommended) to specify the table name or alias if the column name exists only in one of the tables. Also, this query gives the same results as the other two.</p><blockquote><strong>— Query B002</strong><br>SELECT A.[ID],[DateTimeStart],[TripDuration],[Brand],[Model]<br>FROM [Trip] A<br>INNER JOIN [Scooter] B<br>ON [ID_Scooter]=B.[ID]</blockquote><p>Of course, you can extend all these rules with a JOIN of three or more tables, there are no limits in this way.</p><blockquote><strong>— Query B004</strong><br>SELECT A.[ID],A.[DateTimeStart],A.[TripDuration],B.[Brand],B.[Model],C.*<br>FROM [Trip] A<br>INNER JOIN [Scooter] B<br>ON A.[ID_Scooter]=B.[ID]<br>INNER JOIN [User] C<br>ON A.[ID_User]=C.[ID]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/693/1*867o7DMa9Us-dfr1W9cXYg.png" /></figure><p><em>Finally, you could find some JOIN clauses with different syntax (for example if you are using SAP Business Objects suite), similar to the one of the CROSS JOIN. This is also a valid syntax, know as ANSI-92, but in this article, we don’t use it, so feel free to find lots of articles about it on the Internet.</em></p><h4>FULL OUTER JOIN</h4><p>The results of a FULL OUTER JOIN will include at least one row from Table A and at least one row from Table B, even when the JOIN conditions are not satisfied, in this case a row of NULL values will come from the row that doesn’t match.</p><p>See the following example to clarify this concept.</p><blockquote><strong>— Query C001</strong><br>SELECT * <br>FROM [Trip] A<br>FULL OUTER JOIN [User] B<br>ON A.[ID_User]=B.[ID]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/961/1*abmFxEpYbaxaB-ZrPkVUKA.png" /></figure><h4>LEFT OUTER JOIN</h4><p>The LEFT OUTER JOIN is similar to the FULL OUTER JOIN but asymmetrically. This means that the results will have at least one row from the table on the left (Table A in the following example), but the rows of the table on the right (Table B in the example) which do not satisfy the JOIN rules, won’t be shown.</p><blockquote><strong>— Query D001</strong><br>SELECT * <br>FROM [Trip] A<br>LEFT JOIN [User] B<br>ON A.[ID_User]=B.[ID]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/959/1*3pPv04lPUGcsoK-SX77QaA.png" /></figure><p>Please note that we can use the LEFT OUTER JOIN construct to find the rows of the table on the left (Table A) that do not have a correspondence row in the table on the right (Table B) based on the JOIN rules. This is a very helpful application, that you can use just adding a WHERE clause under the query, like in the following example.</p><blockquote><strong>— Query D002</strong><br>SELECT A.* <br>FROM [Trip] A<br>LEFT JOIN [User] B<br>ON A.[ID_User]=B.[ID]<br>WHERE B.[ID] IS NULL</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/827/1*dRSvBbIJWoP5ri6FC2p__Q.png" /></figure><h4>RIGHT OUTER JOIN</h4><p>As you probably expect all the consideration that we made on the LEFT OUTER JOIN could be used symmetrically in the RIGHT OUTER JOIN.</p><p>Feel free to take a glance at the following two examples to confirm or deny you expectation about the RIGHT OUTER JOIN after having understood the LEFT OUTER JOIN the section above.</p><blockquote><strong>— Query E001</strong><br>SELECT * <br>FROM [Trip] A<br>RIGHT JOIN [User] B<br>ON A.[ID_User]=B.[ID]</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/959/1*ZUup8XO-wj5uU9pZ3j86Vg.png" /></figure><blockquote><strong>— Query E002</strong><br>SELECT B.* <br>FROM [Trip] A<br>RIGHT JOIN [User] B<br>ON A.[ID_User]=B.[ID]<br>WHERE A.[ID] IS NULL</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/147/1*UslAWIkHiLeAxZWtFh4gYA.png" /></figure><h4>INNER JOIN</h4><p>Conceptually, the INNER JOIN is the combination of the LEFT and the RIGHT OUTER JOIN, so the results of this JOIN will have only the rows that match the JOIN rules.</p><p><strong>— Query F001</strong><br>SELECT * <br>FROM [Trip] A<br>INNER JOIN [User] B<br>ON A.[ID_User]=B.[ID]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/962/1*Ofbc08SaWnUx6HvWjTbAPQ.png" /></figure><h4>ANTI JOIN</h4><p>In the opposite way, the ANTI JOIN will show only rows of table on the left (Table A) and rows of table on the right (Table B) that do not follow the JOIN rules.</p><p><strong>— Query G001</strong><br>SELECT * <br>FROM [Trip] A<br>FULL OUTER JOIN [User] B<br>ON A.[ID_User]=B.[ID]<br>WHERE A.[ID] IS NULL OR B.[ID] IS NULL</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*7a_eD-5J07F1mnwi_8nIZQ.png" /></figure><h4>CROSS APPLY</h4><p>CROSS APPLY is similar to the INNER JOIN but it is used when you want to specify some more complex rules about the number or the order in the JOIN. The most common practical use of the CROSS APPLY is probably when you want to make a JOIN between two (or more) tables but you want that each row of Table A math one and only one row of Table B. In the following example, in more detail, each user (Table A) will match with its longest trip (Table B). Please note that users that didn’t make any trip will not be included in the results, as we already said that CROSS APPLY is in some way similar to the INNER JOIN.</p><p><strong>— Query H001</strong><br>SELECT A.*,B.[TripDuration],B.[ID]<br>FROM [User] A<br>CROSS APPLY (<br> SELECT TOP 1 *<br> FROM [Trip] C<br> WHERE C.[ID_User]=A.[ID]<br> ORDER BY C.[TripDuration] DESC<br>) B</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/367/1*MvinQRU26LB6JbEGy3rgdw.png" /></figure><h4>OUTER APPLY</h4><p>The OUTER APPLY is a construct very close to the CROSS APPLY we just discussed. The main difference is that the OUTER APPLY will include in the results also the rows from Table A that do not follow the match rules defined in the WHERE clause.</p><p>The following example is similar to the one we made in the CROSS APPLY section but, as you can easily see, also users that do not have any trips are included in the results.</p><p><strong>— Query I001</strong><br>SELECT A.*,B.[TripDuration],B.[ID]<br>FROM [User] A<br>OUTER APPLY (<br> SELECT TOP 1 *<br> FROM [Trip] C<br> WHERE C.[ID_User]=A.[ID]<br> ORDER BY C.[TripDuration] DESC<br>) B</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*Uctu4_6UPPunY9_aHFI9Og.png" /></figure><h4>Usage of the WHERE conditions in JOINS</h4><p>Before moving forward it is important to understand the usage of the WHERE clause when you are using a JOIN.</p><p>We will consider four examples, with four completely different results, that I hope will help to understand how to correctly write the JOIN and the WHERE rules.</p><p>In the query L001 you can see an important fact about the JOIN, as the database will, first of all, make the JOIN between the tables, based on the JOIN rules, and only after that will filter using the WHERE clause. So the results will only include the rows WHERE B.[Username]=’Brenda’.</p><blockquote><strong>— Query L001</strong><br>SELECT * <br>FROM [Trip] A<br>LEFT JOIN [User] B<br>ON A.[ID_User]=B.[ID] <br>WHERE B.[Username]=’Brenda’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*mmkqGdtpt-vpMEHEbrnxMQ.png" /></figure><p>Another important fact about the JOIN is that you can specify some filter rules inside the JOIN rules. This is a good idea if you consider that the JOIN rules are applied before the WHERE clause so you will limit the number of rows that the database has to extract before filters.</p><p>The other side of the coin, is that you have to be aware and always pay attention that, in a LEFT OUT JOIN, only the filter rules applied on the right table (Table B) will be considered.</p><blockquote><strong>— Query L002</strong><br>SELECT * <br>FROM [Trip] A<br>LEFT JOIN [User] B<br>ON A.[ID_User]=B.[ID] AND B.[Username]=’Brenda’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/962/1*ZHqDvj4YYCX7866rGP-pNA.png" /></figure><blockquote><strong>— Query L003</strong><br>SELECT * <br>FROM [Trip] A<br>LEFT JOIN [User] B<br>ON A.[ID_User]=B.[ID] <br>WHERE A.[ID_Scooter]=’SR001&#39;</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*i44WUjN96MX_cfNoS0k4Pw.png" /></figure><p>As we already said in the example on query L002, in a LEFT OUT JOIN, only the filter rules applied on the right table (Table B) will be considered. In this example, you can see that filtering rule A.[ID_Scooter]=’SR001&#39; is ignored and all the scooters are extracted in the results. Please allocate some time to understand the difference between the results of query L003 and L004.</p><blockquote><strong>— Query L004</strong><br>SELECT * <br>FROM [Trip] A<br>LEFT JOIN [User] B<br>ON A.[ID_User]=B.[ID] AND A.[ID_Scooter]=’SR001&#39;</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*c5k5NWYP28znoVPty86daw.png" /></figure><h3>Working with rows of different tables</h3><p>After combining columns from different tables you are probably asking if is it possible to combine rows of different tables. The answer is definitely yes, but you have to master different constructs.</p><h4>UNION and UNION ALL</h4><p>The first important assumption about combining rows from different tables is that the column names and type have to be the same. For example, if you try to combine the table Scooter and RepairedScooter with a UNION ALL you will get an error as they have different columns.</p><p><strong>— Query M001</strong><br>SELECT * <br>FROM [Scooter]<br>UNION ALL<br>SELECT * <br>FROM [RepairedScooter]</p><p>Msg 205 Level 16 State 1 Line 2 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.</p><p>Without modifying the structure of the tables you can easily combine the two tables only selecting the columns that exist in both of them.</p><p><strong>— Query M002</strong><br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [Scooter]<br>UNION ALL<br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [RepairedScooter]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/332/1*M5tKLEKVXbr4wpMZLFy41A.png" /></figure><p>The difference between UNION and UNION ALL is that the first only include DISTINCT values in the results and, for this reason, it is slightly slower than the other.</p><p><strong>— Query M003</strong><br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [Scooter]<br>UNION<br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [RepairedScooter]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/332/1*BJUu1b6OqoZ7ZL3mOyWejA.png" /></figure><h4>EXCEPT</h4><p>We can use EXCEPT if we want to extract rows that are in the first table but not in the second table.</p><p><strong>— Query N001</strong><br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [Scooter]<br>EXCEPT<br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [RepairedScooter]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/329/1*Kw4ddvz0rAwuV4XbsAGUdQ.png" /></figure><h4>INTERSECT</h4><p>We can use INTERSECT if we want to extract rows that both in the first table and in the second table.</p><p><strong>— Query O001</strong><br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [Scooter]<br>INTERSECT<br>SELECT [ID],[Brand],[Model],[ManufacturingYear]<br>FROM [RepairedScooter]</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/293/1*Ete9TqtF41ORzz25a5SsMA.png" /></figure><h3>A small recap using a flow chart</h3><p>We saw a lot of constructs, and I hope this story helped you to have a better idea of the difference between them. In the figure below you can find a flow chart, you may find helpful in your first steps with the combination of data from different sources in SQL Server.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/806/1*o-u_62y6ZWIijwvqqd-Bmg.png" /></figure><p><a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&amp;fiddle=bb54aaea7e0a2527a7e5b14b6dc922e6">SQL Server 2019 | db fiddle</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f2ee8f8af957" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/probably-the-best-introduction-about-join-cross-apply-union-cross-joins-and-more-in-sql-server-f2ee8f8af957">Probably the best introduction about JOIN, CROSS APPLY, UNION, CROSS JOINS and more in SQL Server</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Analysis of BiciMAD trips]]></title>
            <link>https://medium.com/data-science/analysis-of-bicimad-trips-88ee2d0f7cdf?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/88ee2d0f7cdf</guid>
            <category><![CDATA[commuting]]></category>
            <category><![CDATA[bicimad]]></category>
            <category><![CDATA[micromobility]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[geodata]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Mon, 06 Apr 2020 07:37:50 GMT</pubDate>
            <atom:updated>2020-04-06T16:07:04.660Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>In </strong><a href="https://towardsdatascience.com/lets-analyze-e-bike-sharing-stations-of-madrid-e06b1c5af596"><strong>the previous article</strong></a><strong>, I analyzed the location and characteristics of the BiciMAD stations, now it is now time to continue the analysis to understand where users are going and what are the most popular routes, clustering on their category of <em>user_type</em> (subscribers, occasional users, …).</strong></p><p>Please note that the figures, the dataset, the Tableau reports and the Python notebook discussed in this article can be found (for free) on <a href="https://github.com/simboli/Analysis-of-BiciMAD">GitHub</a>.</p><h3>Dataset</h3><p>For the analysis of the routes, I used the data provided by the <em>Municipal de Transportes de Madrid</em> on their <a href="http://opendata.emtmadrid.es/Datos-estaticos/Datos-generales-(1)">Open Data web site</a>. Routes data are available in 18 archives with as many JSON files, from April 2017 to September 2018. Each line of the file is a beginning to end JSON that contains information about every time one e-bike was removed from its base (including when it was done from EMT employers).<br>Overall, the files contain the details of over 5 million withdrawals and each archive size 500 to 1500 Mb (while zipped), a remarkable point is that unfortunately there are 17 ZIP archive and 1 RAR archive, this is not good as the whole dataset is too big to be uncompressed and imported into a single Pandas Dataframe.<br>For this reason, I chose to go a different way: I identified some information to extract and then programmed Python to extract each month JSON file, open them and save the desired information at a time. The script processed 25,000 lines at a time, which were then aggregated together in several CSV files.</p><p>The EMT company also provides a short guide to understand the meaning of each field, the guide, available in Spanish, could be downloaded from <a href="http://opendata.emtmadrid.es/Documentos/Servicios-y-estructuras-Bicimad-V1-1.aspx">this link</a>.</p><h3>Missing data</h3><p>At first glance we can see that two interesting fields are missing: a bicycle ID and a user ID. The lack of user ID is probably due to privacy reasons, but I can’t understand the reason for the lack of a unique identifier of the bicycle. It would have been very interesting to extract information related to the use of each bike between the stations (by users and by EMT employers).</p><h3>Analysis target</h3><p>The target of this analysis are:</p><ul><li>to understand the habits of BiciMAD users, in particular about when they use the bikes and for which purpose;</li><li>to understand if there is some evident segmentation of users;</li><li>to understand the most frequent trips that BiciMAD users make with the bikes.</li></ul><h3>Exploratory data analysis</h3><p>First, we evaluate the number and the main statistical parameters of the data at our disposal.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/91f8108f5eed36c084fca564726d959f/href">https://medium.com/media/91f8108f5eed36c084fca564726d959f/href</a></iframe><pre>5478168</pre><p>After that we verify that the IDs of the plug and unplug stations are all present in the registry generated in the previous article.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3cb88364fd875c7e376e685bccb6ef16/href">https://medium.com/media/3cb88364fd875c7e376e685bccb6ef16/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/1*mVsQuV8NH5QW0hTzcorHlQ.png" /></figure><p>One of the aspects that is immediately visible, is the fact that the maximum value of the stations is 2008. From the previous post, we know that the stations are identified by an ID ranging from 1 to 175, so 2008 is an anomalous value.</p><h4>Station 2008</h4><p>Since the DataFrame was generated by a script I made, I checked if there were any lines transferred to the 2008 stations in the original data provided by EMT, just to make sure that was not my mistake. I take the month of December 2017 because (as winter month) the file is definitely smaller than others.<br>The 2008 value as a station ID is also present in the data provided originally, so it is not the result of errors related to my data aggregation script.</p><p>Intuitively, 2008 could be an ID of a logic station where are placed the bikes that have some anomalous, for example, those in maintenance or those stolen. For me it is really difficult to verify this fact, but considering that these lines are only a few on the total, I can remove them from the data frame without incurring the risk of having a clear effect of falsification on the final results.</p><h4>Station 22</h4><p>An additional station that is inside the routes is out of the station registry made in the last article: station number 22.<br>With two simple queries I understand that the station may have been active only between ‘2017–05–01’ and ‘2017–12–01’, and now it does not exist anymore, but the open data on the stations, provided by the EMT, only cover months between July and September 2018.</p><p>To get over of this issue, I searched some 2017 maps of the BiciMAD stations, but without achieving useful informations as stations on the maps are identified with the “number” field instead of the “ID”.</p><p>As a last check, I looked at the stations above and below the one with ID 22.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8651082c615d2e539ef6120825e0d943/href">https://medium.com/media/8651082c615d2e539ef6120825e0d943/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/770/1*qbz0YO0j9CuLMN2qjM5Z9w.png" /></figure><p>From the data, we can see that there is a station 20a. Usually when EMT identifies a station with alphabet letter, it is because there are at least two nearby stations, but in this case station 20b is missing: it is likely that the ID=22 station I am looking for was actually the number 22b.</p><p>These informations are sufficient to restrict my search perimeter:</p><ul><li>a station between ID=20</li><li>it was there since ‘2017–05–01’ to ‘2017–12–01’</li></ul><p>From Google Maps I can finally find the station ID=22, it is the 20b station that until a few months ago was located in <em>Calle Alcalá nº 49</em>.<br>As the station 22 really existed in the past, I decided to do not remove its lines from the files.</p><figure><img alt="Calle Alcalá nº 49 — May 2016" src="https://cdn-images-1.medium.com/max/1024/1*1nDD0VJOd0VBN0njiWuXNA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YvDr5VxMN0oM-zNgP34O9g.png" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/94c12435e151d012f57e4c1f1bebdedb/href">https://medium.com/media/94c12435e151d012f57e4c1f1bebdedb/href</a></iframe><h3>Analysis of trips</h3><p>In the following graphs, color <strong>red</strong> identify annual membership users, color <strong>blue</strong> identify casual users and color <strong>green</strong>, eventually, identify EMT employers.</p><p>First of all, I plot the entire data provided from EMT on a timeline with month granularity, to understand if there is some seasonality with the number of bikes rented.</p><p>The results (shown in this graph) is that there is a great seasonality in this service. We can see that both of lines have a peak during summer months, apart from the month of August where red line (annual membership user) drop-down, probably because during August lots of offices have a lighter workload due to holiday so it could demonstrate that <em>user_type=1</em> use bikes to go from home to their workplace and back.</p><p>From this graph we can make more conclusions:</p><ul><li>During winter the temperature in Madrid is very chill so both of <em>user_type</em> prefer to travel less by bike that with other transports;</li><li>There is a huge gap between the number of trips from <em>user_type=1</em> and <em>user_type=2</em>: the are plot together but check the scale on the y-axis on the left and on the right…they have different orders of magnitude!</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3wTWbm2nu9HuhVfarzYpIw.png" /></figure><p>This graph shows the trips per day of the week during the whole months provided by EMT, so from May 2017 to Sep 2018. It is clear that annual subscribers have a peak of use during the first and the last working days of the week while the casual users has nearly a complementary use.</p><figure><img alt="BiciMAD rides per day of week" src="https://cdn-images-1.medium.com/max/1024/1*-zeyVznPjmbWcLAD8LIy9g.png" /></figure><p>In this graph we can see the evolution of the trips during the hours of the day. I think that the red line support the thesis that a big part of annual subscriber use the e-bikes of BiciMAD for travels between home and office, we can easily see the user commuting from home to the workplace (7–9 AM), going out for lunch (2–3 PM) and going home 5–7 PM).</p><figure><img alt="BiciMAD rides per hour" src="https://cdn-images-1.medium.com/max/1024/1*Aq_Tugsi0RnqA0qpAxR_Lw.png" /></figure><p>After the series of plots I decided to go further and have a look at the whole one and half year provided by EMT to see if I can understand more information about the habits of users. For this reason I decided to make 3 <em>calmap graphs</em> to understand the distributions of trips along the weekdays and along the whole timeline.</p><figure><img alt="BiciMAD calmap user_type 1" src="https://cdn-images-1.medium.com/max/1024/1*5aZJr-3ZmuCOeo5GNWYtdw.png" /></figure><figure><img alt="BiciMAD calmap user_type 2" src="https://cdn-images-1.medium.com/max/1024/1*BLDEoOVgL3qtcCtm4bzoJA.png" /></figure><figure><img alt="BiciMAD calmap user_type 3" src="https://cdn-images-1.medium.com/max/1024/1*UnYL8qot8u4x_2Nxn8pBEQ.png" /></figure><p>These graphs show me some unexpected data that, if possible, it would be very interesting to investigate, maybe in future post on this blog:</p><ul><li>in May, Jun and August there are few days with almost zero travels from <em>user_type=1</em> (annual subscriber) and an explosion of user_type=2 (casual users) and user_type=3 (employers). This is very suspect! It could be an error during the data collection or maybe some technical problem that prevented annual subscribers to use their cards to rent the bikes so they had to rent as a casual users (and lots of EMT employers were working to solve the problem).</li><li>There is a surprising peak of EMT employers usage of bikes during the weekend, it would be interesting to understand if they are most of the maintenance of bikes or moving the fleet for the week that will start.</li><li>During the first days of the first week of May 2017, there is no one user_type=2 trips…is it correct?</li><li>During October 2017 there are deep blue days in the graph of user_type=2, this means a high number of trips during this period of time, more than the weeks before and after. It would be interesting to understand the causes of this peak, it could be some marketing campaign about BiciMAD service.</li></ul><p>For this analysis, the last point I investigated is the number of ring trips on the total. A ring trip is a trip that started and ended at the same station. Considering the price of renting a bike is very unlikely that users unplug the bike, go for example at the supermarket without plug the bike to another base and come back unless it is a very quick errand. Ring trips would be probably more likely between <em>user_type=2</em> as the can use the bikes to visit the city of Madrid and some point of interesting (for example the <em>Retiro park</em>) faster than just walking.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f7e6194f99eff3f78963e8d21164c1ec/href">https://medium.com/media/f7e6194f99eff3f78963e8d21164c1ec/href</a></iframe><pre>user_type <br>0     5.336718 <br>1     2.444495 <br>2    10.882962 <br>3    31.754358 <br>Name: count, dtype: float64</pre><p>As we can see there are more ring trips from users of group 2 than from users of group 1. There is also a surprising 31% of ring trips from group number 3 (EMT employers), it could be an indicator that more ore less 31% of “trips” are for maintenance the e-bikes and not for just moving the fleet!</p><p>If we have a look at the most frequent trips of <em>user_type=1</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/319/1*4x0RU_Ob58Lpewx5d6GG7A.png" /></figure><p>and <em>user_type=2</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/329/1*p7qsoRogctVAjgWTVTgYsw.png" /></figure><p>we could come to the same conclusions about ring trips.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=88ee2d0f7cdf" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/analysis-of-bicimad-trips-88ee2d0f7cdf">Analysis of BiciMAD trips</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Let’s analyze e-bike sharing stations of Madrid]]></title>
            <link>https://medium.com/data-science/lets-analyze-e-bike-sharing-stations-of-madrid-e06b1c5af596?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/e06b1c5af596</guid>
            <category><![CDATA[commuting]]></category>
            <category><![CDATA[bike-sharing]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pandas-dataframe]]></category>
            <category><![CDATA[micromobility]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Wed, 01 Apr 2020 17:41:36 GMT</pubDate>
            <atom:updated>2020-04-01T21:03:24.908Z</atom:updated>
            <content:encoded><![CDATA[<h4>BiciMAD is the e-bike sharing service of the Spanish Capital, started in 2014 and managed by the Municipal Company of Madrid from May 2016.</h4><p><strong>The purpose of this analysis is to understand how many stations are part of the network, how much their capability is and the extent of the network. I will also try to classify each station in the Madrid neighborhood.</strong></p><p>Please note that the figures, the dataset, the maps and the Python notebook discussed in this article can be found (for free of course) on <a href="https://github.com/simboli/Analysis-of-BiciMAD">GitHub</a>.</p><h3>Dataset</h3><p>The company <em>Empresa Municipal de Transportes de Madrid</em> provides lots of data in an open format on the <a href="https://opendata.emtmadrid.es/Datos-estaticos/Datos-generales-(1)">Open data EMT Madrid</a>. This dataset, limited to the BiciMAD service, concerns the hourly situation of the stations for the months of July, August and September 2018 and the details of the individual rentals from April 2017 to September 2018. As this analysis concerns the stations, the most interesting files are without any doubt the first ones.</p><p>Let’s download the <a href="https://opendata.emtmadrid.es/getattachment/8d0e5769-1fc2-4b62-ad1b-d0cc0315f3f8/Bicimad_Estacions_201809.aspx"><em>Situación estaciones bicimad por día y hora de Septiembre de 2018</em></a> archive to find out what data we have. Unlike data related to the rentals of bicycles, there is no guide explaining the fields and how the data were pretreated before being extracted, saved and uploaded online</p><p>The file is a RAR archive that contains a single JSON file. Each line of the file is a separate JSON file entity, that contains two elements at the first level: the date-time and a series of data related to the situation of the stations at that specific time.</p><p>The data have a good level of detail (hourly) and would allow a more in-depth analysis if the availability of months covered more than just three months. Considering the seasonality of the city’s tourist flow and the continental climate of Madrid, which is characterized by high temperatures during summer and low temperatures during winter, I cannot consider these three summer months as significant to estimate the rest of the year.</p><p>In this regard, as our goal is to build a registry of the stations, I considered the most updated data available, that is the first row of the September 2018 file, which is the situation of 2018–09–30T23:42:38.647290″</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9e4dba6416b3fe1ccc08f4f1112f2adb/href">https://medium.com/media/9e4dba6416b3fe1ccc08f4f1112f2adb/href</a></iframe><p>Since the file refers to September 2018, it counts in total (30 * 24) -1 = 719 lines.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K_YJjDrcI_KXU_t2bQhXmA.png" /></figure><p>At first glance it is evident that the data (in terms of columns) we need less than those provided by EMT, taking into account the purpose of this work and all the considerations made so far.</p><h3>Classification by neighborhood</h3><p>The first step of the analysis consists of mapping each BiciMAD station in the neighborhood it belongs to. This has as a side effect, a verification that all the GPS coordinates of the stations are geographically within the metropolitan area of Madrid.</p><p>In practical terms, it is necessary to find a list of Madrid neighborhoods and their boundaries in a lat / long coordinate system, to construct polygons using the available data and to evaluate which of these each station belongs.</p><p>What is the top web site for downloading free geographical data? Obviously <a href="https://openstreetmap.org/">Open Street Map</a>!</p><p>From <a href="https://wiki.openstreetmap.org/wiki/Tag:boundary%3Dadministrative">this table</a>, I could find the tag used to identify neighborhoods in Spanish cities, which is 9, so I generated a GeoJSON file from Madrid data which has <strong>admin_level=9 AND boundary=administrative</strong>.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/817b328f99b21801ca8d67f401eaf37c/href">https://medium.com/media/817b328f99b21801ca8d67f401eaf37c/href</a></iframe><p>At this point, I have defined three Python functions that can generate polygons and verify that each station belongs to one of them.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0a8925135f4a4ca5207958cf3fbd0dd9/href">https://medium.com/media/0a8925135f4a4ca5207958cf3fbd0dd9/href</a></iframe><blockquote><em>array([‘Centro’, ‘Chamberí’, ‘Moncloa-Aravaca’, ‘Arganzuela’, ‘Retiro’, ‘Salamanca’, ‘Tetuán’, ‘Chamartín’], dtype=object)</em></blockquote><p>The results of this elaboration are saved into <a href="https://www.leansolutions.it/static/BiciMAD/df_stations.csv">this CSV file</a>, it will be useful for future applications.</p><h3>Evaluation of the number of bases</h3><p>The second level of analysis has been conducted considering the number of bicycle bases for each station and, contrary to my expectations, they are made up almost of standard modules, in fact, the number is always between 18 and 30.</p><figure><img alt="BiciMAD bases per stations" src="https://cdn-images-1.medium.com/max/378/1*LP0LSOWIAOXfgouUfVecJQ.png" /></figure><p>As it is easy to verify, in the case of particularly barycentric points for cyclist flows (for example close to other nodes of the transport network), the company that manages BiciMAD bikes has positioned more than one stations rather than “at the logical level” consider a single station with more bases.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c5dbfe1d6eaa3ffb409c6e916e33fdf0/href">https://medium.com/media/c5dbfe1d6eaa3ffb409c6e916e33fdf0/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/210/1*iIvH24miQSKp9zvuYkP3Cg.png" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/95a6441ceefabc9f82e34d2d175d8722/href">https://medium.com/media/95a6441ceefabc9f82e34d2d175d8722/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/359/1*jksJ7bMfzEOqx5jSsB93vw.png" /></figure><p>At this point, I can also make some general assessments considering the total number of e-bike that the network can support (which is the total number of bases considering that every time a user should have al least one bases where park a bike), 4095 bases and evaluate their distribution in the individual neighborhoods.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2d3a078f3f03aa961646f67b86a88eb0/href">https://medium.com/media/2d3a078f3f03aa961646f67b86a88eb0/href</a></iframe><ul><li>Arganzuela 342</li><li>Centro 1380</li><li>Chamartín 306</li><li>Chamberí 437</li><li>Moncloa-Aravaca 243</li><li>Retiro 550</li><li>Salamanca 669</li><li>Tetuán 168</li></ul><p>As was easily expected, the Madrid Center neighborhood contains the largest number of seats. This is evident as it is in this district that the tourist flows are more concentrated and moreover it is here that we expect that the flows of workers who use the BiciMAD service to commute to work.</p><h3>Making a map of BiciMAD station</h3><p>As a “bonus” I decided to generate a map of the individual stations, this with a dual purpose: to give an easy tool to view roughly any areas of Madrid not covered by the stations and to provide not Madrid people with a representation of their location, to facilitate their life in case they decide to go to the Spanish capital city and use the BiciMAD service.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5c23fbf0416ab8005a341d8dddeb91d4/href">https://medium.com/media/5c23fbf0416ab8005a341d8dddeb91d4/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Of4PirKnjOQdVCjmBsqoHw.png" /></figure><p>The map, generated with Folium module, is available <a href="https://www.leansolutions.it/static/BiciMAD/map_stations.html">here</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e06b1c5af596" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/lets-analyze-e-bike-sharing-stations-of-madrid-e06b1c5af596">Let’s analyze e-bike sharing stations of Madrid</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Fast and easy way to dynamically import Open Street Map data into a Google Sheet file]]></title>
            <link>https://medium.com/data-science/fast-and-easy-way-to-dynamically-import-open-street-map-data-into-a-google-sheet-file-495a400ec25?source=rss-cd0fe0e8ffb4------2</link>
            <guid isPermaLink="false">https://medium.com/p/495a400ec25</guid>
            <category><![CDATA[google-sheets]]></category>
            <category><![CDATA[passover]]></category>
            <category><![CDATA[openstreetmap]]></category>
            <category><![CDATA[csv]]></category>
            <category><![CDATA[maps]]></category>
            <dc:creator><![CDATA[Nicola Simboli]]></dc:creator>
            <pubDate>Mon, 14 Oct 2019 20:00:46 GMT</pubDate>
            <atom:updated>2019-10-15T12:46:48.268Z</atom:updated>
            <content:encoded><![CDATA[<h3>Problem overview</h3><p>You work as a business analyst at the company that manages metro underground trains transport in a large Italian city and you are required to provide the marketing&amp;communication department a list of the bars and the cafés in the area, to distribute them maps that can be affixed to the windows or inside the rooms. In addition, the closest bars and cafés to the stations will be contacted by the sales department to propose to join and ticket sales program, for the transport service that your company provides.</p><p>This is a periodic request that occurs on a monthly basis.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iw0g8jCqFTuMPpqgfR7uAg.gif" /></figure><h3>Data sources</h3><p>One of the primary data sources for this kind of geography-related problems is Open Street Map.</p><p>There are several ways to access the Open Street Map database, but one of the most popular is using the Overpass APIs. <strong>In this article, we will see how to use these APIs to generate a dynamic list in Google Sheet, generated by a parameterized query and with values set by the final business user.</strong> In this way, other colleagues can get the data on their own and can copy, edit or save the list for future manipulations, for example assigning each bar or cafe to one key account manager that will contacts the structure.</p><figure><img alt="Logo of Overpass API and Google Sheet" src="https://cdn-images-1.medium.com/max/800/1*kVFK9VhD5u05jQzPIlXImg.png" /></figure><p>Queries via Overpass API can be made using two languages: Overpass XML or Overpass QL.</p><p>This time we will use Overpass QL, <em>an imperative programming language written with a C style syntax</em>. The main “getting started” guide to learn this language is available on <a href="https://wiki.openstreetmap.org/wiki/Overpass_API/Overpass_QL">this page</a>.</p><h3>Data extraction</h3><p>Let’s summarize what we need to extract:</p><ul><li>text output, <strong>we’ll use the standard CSV then with the header on the first line and with comma as column separator</strong>, Google Sheet will appreciate this choice!</li><li><strong>list of bars and cafés in the indicated area;</strong></li><li><strong>the premises must be within a radius of X meters from a subway station entrance.</strong></li></ul><p>The detail of the attributes to be extracted is very varied and not all the information for each premises is fully compiled, compared to other countries (first and foremost Germany) there could be a good number of detail fields that are still incomplete (for example the telephone number or opening hours).</p><p>The timeout will be fixed in 20 seconds. The business user will not be able to easily change this value. It is not appropriate to increase it, as the data we want to extract are not big and the following rule applies:</p><blockquote>“<em>If the query runs longer than this time, the server may abort the query with a timeout. The second effect is, this higher value, the server probably rejects the query before executing it. </em>“</blockquote><p>Let’s set up the query!</p><p>As anticipated, we want to extract the list of bars and cafés in a particular area. These elements are classified within the OSM database with the following tags:</p><ul><li><em>amenity = bar</em></li><li><em>amenity = cafe</em></li></ul><p>A good rule to follow, when we have to work with Overpass APIs, is to consult the TagInfo pages related to the tags we want to extract:</p><ul><li><a href="https://taginfo.openstreetmap.org/tags/?key=amenity&amp;value=cafe#overview">https://taginfo.openstreetmap.org/tags/?key=amenity&amp;value=cafe#overview</a></li><li><a href="https://taginfo.openstreetmap.org/tags/?key=amenity&amp;value=bar#overview">https://taginfo.openstreetmap.org/tags/?key=amenity&amp;value=bar#overview</a></li></ul><p>Here we find various useful information for our purposes. In the “<em>combination</em>” tab we find the list of features concerning that tag, which is a good starting point to make the list of columns we want to extract and which we will specify a little later in our project.</p><p><strong>In the “<em>overview</em>” tab, instead, we see how this place is coded in the database. In this case, we can easily see that bars and cafés can be nodes, way or relations.</strong> For our purposes, this information is fundamental, either because in the query we will specify to search in all these entities and because we know that the information can be stored as a point (<em>nodes</em>) or a wider area (<em>way</em>). In this project we obviously want to extract only the information related to that bar/cafés once, so the latitude and longitudes coordinates we want to get are those of the center of the area bounded by the lines.</p><p>Let’s take an example, in the case of a square bar there will be four way elements inside the OSM database, but we want that in the sheet all those are represented in a single line, considering that the bar is always the same (same name, same telephone number, …). This purpose will be achieved by indicating to Overpass to extract only the center of the area.</p><p><strong>The last element to consider in the query is the area we want to extract. We make the choice to extract the data using the boundaries of each municipality, which will be indicated in the input cells filled by the user of the sheet</strong>, bearing in mind that some metro stations are located in different Municipalities.</p><p>From <a href="https://wiki.openstreetmap.org/wiki/Tag:boundary=administrative#10_admin_level_values_for_specific_countries">this useful</a> table, we can learn that the administrative boundaries of Italian municipalities are indicated in the OSM database with level 8.</p><p>At this point we have all the elements to build our query:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c2ef66d6e123424061b7edf65c58f1de/href">https://medium.com/media/c2ef66d6e123424061b7edf65c58f1de/href</a></iframe><p>We considered the query with fixed distance values (50 meters) between the bars/cafés and the entrances of the stations and also a fixed municipality (ISTAT code number 015146 corresponds to the city of Milan) so we have to modify the query to make it usable with Google sheet.</p><h3>How to use the query into a spreadsheet</h3><p>We need to follow these steps:</p><ul><li>Include the query within the <a href="https://support.google.com/docs/answer/3093335?hl=en"><strong>IMPORTDATA</strong></a><strong> formula</strong> (read the Google Sheet official guide);</li><li>Replace the single single-double quotation marks with nested-double quotation marks;</li><li>Replace the distance value with <em>“&amp;B5&amp;”</em>;</li><li>Replace the value of the <em>ISTAT code</em> of the municipality with <em>“&amp;B4&amp;”</em>.</li></ul><p>We finally came to the following:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3f2c8d5a8fe72ddb62d1c76723326d29/href">https://medium.com/media/3f2c8d5a8fe72ddb62d1c76723326d29/href</a></iframe><p>With a data validation linked to a hidden sheet and a <a href="https://support.google.com/docs/answer/3093318?hl=en"><strong>VLOOKUP formula</strong></a> to get the <a href="https://en.wikipedia.org/wiki/Italian_National_Institute_of_Statistics">ISTAT code</a>, we can make a user-friendly drop-down menu from which the user can choose the name of the municipality and digit the value of the distance to consider.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OSUOnD_JiVFQGzlMCKwc6w.png" /></figure><p>Each time cell B3 or cell B4 changes, values of list of premises is updated.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=495a400ec25" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/fast-and-easy-way-to-dynamically-import-open-street-map-data-into-a-google-sheet-file-495a400ec25">Fast and easy way to dynamically import Open Street Map data into a Google Sheet file</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>