<?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 Patrick Okore on Medium]]></title>
        <description><![CDATA[Stories by Patrick Okore on Medium]]></description>
        <link>https://medium.com/@paddyokore39?source=rss-ddf17542ecab------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*mQCv4LAMej90DDOMOWjnSg.jpeg</url>
            <title>Stories by Patrick Okore on Medium</title>
            <link>https://medium.com/@paddyokore39?source=rss-ddf17542ecab------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 10:26:29 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@paddyokore39/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[Using SQL To Understand Deforestation Trends]]></title>
            <link>https://medium.com/learning-sql/using-sql-to-understand-forestation-trends-52c771514877?source=rss-ddf17542ecab------2</link>
            <guid isPermaLink="false">https://medium.com/p/52c771514877</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[editors-pick]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[learning-data-science]]></category>
            <dc:creator><![CDATA[Patrick Okore]]></dc:creator>
            <pubDate>Mon, 06 Nov 2023 13:17:18 GMT</pubDate>
            <atom:updated>2023-12-02T21:51:37.926Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QtC4AN1c5UKCZ6opyHWI3g.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@szmigieldesign?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Lukasz Szmigiel</a> on <a href="https://unsplash.com/photos/forest-trees-jFCViYFYcus?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><h3>Introduction</h3><p>In my journey learning data science, I found that more emphasis was placed on learning Python or R, and less on SQL. However, when I truly discovered SQL, and its advantages in data analysis over python, my mind was blown. Some tasks that would take several lines of code and maneuvering in python, were pretty easy to execute on SQL. I have therefore decided to share my experiences with SQL, to help others see the light .</p><p>In this blog we will explore SQL for data analysis using a case study on deforestation, with World Bank data⁴. The queries were written in PostgreSQL, but should be transferrable to other SQL versions with minimal edits. By the end of this analysis, you will have a grasp of SQL for the following:</p><ul><li>Translating a business problem into an SQL problem</li><li>Breaking down a complex analysis problem into smaller, less complex parts, and solving the smaller parts to solve the larger problem</li><li>Using CREATE to create a view</li><li>Simple SQL queries for filtering (WHERE), aggregation (GROUP BY) and sorting (ORDER BY)</li><li>Combining separate tables on one or multiple columns using JOIN</li><li>Writing complex SQL commands using sub queries and common table expressions</li></ul><p>In this analysis, we seek to understand deforestration trends from 1990 to 2016 at a global, regional and country level.</p><p>We have a database with 3 tables, as outlined in the schema below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*kqM98tYU71GcUGKFzWJXHw.png" /><figcaption>image by author: schema</figcaption></figure><p>To work through this project, we will follow the structure outlined below:</p><ul><li>Data Preparation</li><li>Global Scenario</li><li>Regional Snapshot</li><li>Country Trends</li><li>Summary &amp; Implications</li><li>Instructions To Setup Database</li></ul><p>For those who want to follow along by running the queries, refer to the last section of the blog: Instructions To Setup Database. Now let’s dig in!</p><h3>Data Preparation</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*npRcY6LcdJeCVvJTvPuJWg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@isaacmsmith?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Isaac Smith</a> on <a href="https://unsplash.com/photos/pen-on-paper-6EnTPvPPL6I?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><p>We will start by checking for missing values — this will enable us to know whether we need to fill any missing values, depending on the extent of missing data. Further investigation revealed less than 6% missing data on either forest area or land area. We will exclude these countries in the analysis in our next step. From the query below, we see 20 countries missing either land or forest area</p><pre>--check for missing values<br>SELECT country_name<br>FROM forest_area<br>WHERE forest_area_sqkm IS NULL<br>UNION<br>SELECT country_name<br>FROM land_area<br>WHERE total_area_sq_mi IS NULL;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2XQIVSYmii9DidjlmUyApQ.png" /><figcaption>image by author: 20 countries missing land or forest area</figcaption></figure><p>Next, we will create a view called forestation using JOIN clauses on the three tables from our schema. Our challenge here is that we need to use all the information from the 3 tables to conduct our analysis. However, it will be impractical and inefficient to write joins every other time we need to perform an analysis, hence views. A view⁶ allows you to create virtual tables from your queries, to perform complex operations, pull a wide range of data from different tables or select specific data. As we create the view:</p><ul><li>We will create a <strong>new column</strong> with the percentage <strong>of the forested land area</strong>.</li></ul><pre>-- create view joining all tables and new column with % forest area<br>CREATE VIEW forestation AS<br>SELECT fa.country_code AS country_code, <br>       fa.country_name AS country_name,<br>       fa.year AS year, <br>       fa.forest_area_sqkm ,<br>       la.total_area_sq_mi, <br>       -- create new column with % forest area in sq km<br>       -- convert total_area_sq_mi into sq km. 1 sq mi = 2.59 sq km<br>       ((fa.forest_area_sqkm / (la.total_area_sq_mi * 2.59)) * 100)<br>AS fore_pct_km, r.region, r.income_group<br>FROM forest_area AS fa<br>INNER JOIN land_area AS la<br>USING(country_code, year)<br>-- use inner join since the country TWN in regions but not in la or fa<br>INNER JOIN regions AS r <br>ON la.country_code = r.country_code<br>-- exclude countries with missing values<br>WHERE forest_area_sqkm IS NOT NULL <br>AND total_area_sq_mi IS NOT NULL;</pre><p>Next, we confirm that the filter applied to the view to exclude NULL values works:</p><pre>--confirm no missing values<br>SELECT DISTINCT country_name<br>FROM forestation<br>WHERE forest_area_sqkm IS NULL<br>OR total_area_sq_mi IS NULL;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QObzYPHKDLs_R0z1Yh4_lw.png" /><figcaption>image by author: confirm 0 missing values in the created view</figcaption></figure><p>As we have completed data preparation, we will proceed to data analysis.</p><h3>Global Scenario</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*72NyOw2b59Ft0mAtO01ZXQ.jpeg" /><figcaption>Photo by <a href="https://www.pexels.com/@markusspiske/">Markus Spiske</a> from <a href="https://www.pexels.com/photo/climate-sign-outside-blur-2990617/">Pexels</a></figcaption></figure><p>We will set the context by looking at how the world’s forestation levels has evolved from 1990 to 2016:</p><ul><li>What was the total world forest area (in km²) 1990?</li><li>What was the total world forest area (in km²) in 2016?</li><li>Find the absolute change (in km²) in the world forest area from 1990 to 2016</li><li>Find the % change (in km²) in the world forest area from 1990 to 2016</li><li>If you compare the amount of forest area lost between 1990 and 2016, to which country’s total area in 2016 is it closest to?</li></ul><h4>Total Forest Area (in km²) of the World in 1990</h4><p>We start off by exploring the contents of our view in 1990, to understand how best to answer the question</p><pre>SELECT *<br>FROM forestation<br>WHERE year = 1990<br>ORDER BY country_name DESC<br>LIMIT 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FrziAwAzy9RXT_mjpFqe2w.png" /><figcaption>image by author: view exploration</figcaption></figure><p>From the above, we notice that we have a country_name “World”, which aggregates forest area for all nations in the database. This makes our work easier as all we need to do is to filter by the year 1990, and by the name “World” as shown below:</p><pre>-- create view to be used<br>-- in subsequent analyses<br>CREATE VIEW fa_1990 AS <br>SELECT country_name, <br>       forest_area_sqkm AS fa_1990_sqkm<br>FROM forestation<br>WHERE country_name = &#39;World&#39;<br>AND year = 1990;<br><br>-- show output of view<br>SELECT *<br>FROM fa_1990;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/398/1*ltt4oupBzGxAqKURSFi0IA.png" /><figcaption>image by author: total forest area of the world in 1990</figcaption></figure><h4>Total Forest Area (in km²) of the World in 2016</h4><p>We replicate the above query and simply change the year to 2016</p><pre>-- create view to be used<br>-- in subsequent analyses<br>CREATE VIEW fa_2016 AS <br>SELECT country_name,<br>        forest_area_sqkm AS fa_2016_sqkm<br>FROM forestation<br>WHERE country_name = &#39;World&#39;<br>AND year = 2016;<br><br>-- show output of view<br>SELECT *<br>FROM fa_2016;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/408/1*Wy4o3sOh0AXHXI7u8y5Wwg.png" /><figcaption>image by author: total forest area of the world in 2016</figcaption></figure><h4>Change in the World’s forest area from 1990 to 2016</h4><p>To solve this, we will use the 2 views created above for forest area:</p><ul><li>subtract the output of 1990 from that of 2016 to calculate absolute change</li><li>divide the output of 2016 by that of 1990 to calculate % change</li></ul><pre>SELECT fa_1990_sqkm, fa_2016_sqkm,<br>       (fa_2016_sqkm - fa_1990_sqkm) AS change,<br>       ROUND(((fa_2016_sqkm / fa_1990_sqkm)::NUMERIC - 1) * 100, 3)<br>       AS pct_change<br>FROM fa_1990<br>JOIN fa_2016<br>USING (country_name);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/577/1*_izClr-NN2FGh9jradkNoA.png" /><figcaption>image by author: change in global forest area from 1990–2016</figcaption></figure><p>We can therefore summarize that the global forest area has declined from 41,282,694.9 km² in 1990 to 39,958,245.9 km² in 2016. This is a decrease of 1,324,449 km² or 3.208%.</p><h4>If You Compare Forest Area Lost Between 1990 and 2016, To Which Country’s Total Area In 2016 Is It Closest To?</h4><p>To solve this problem, we need to break it down into smaller parts to understand the steps we need to take:</p><ul><li>convert loss in forest area to square miles (country total area is in mi²)</li><li>filter the data by year to only deal with 2016</li><li>subtract loss in forest area from each country’s total area. This gives us a comparison of loss in forest area vs. equivalent country area. For an exact match, the difference would be zero. Thus we need to look for the country with the smallest difference vs. loss in forest area</li><li>filter the output by a sensible range to identify any country areas closest 1,324,449 km² (above and/or below)</li></ul><pre>-- use common table expression for<br>-- better query design<br>WITH closest AS(<br> SELECT country_name,<br>        total_area_sq_mi::NUMERIC,<br>        -- calcualte loss in forest area, as delta_mi<br>        -- convert loss in forest area to square miles<br>        (((SELECT fa_1990_sqkm::NUMERIC FROM fa_1990) - <br>        (SELECT fa_2016_sqkm::NUMERIC FROM fa_2016)) / 2.59) AS delta_mi<br> FROM forestation<br> WHERE year = 2016<br>       AND total_area_sq_mi <br>       --identify sensible range and convert to sqmi<br>       BETWEEN 1000000/2.59 AND 1400000/2.59<br> )<br><br>SELECT country_name, <br>       total_area_sq_mi, ROUND(delta_mi, 1), <br>       /* subtract forest area, delta_mi from<br>          each country&#39;s total area */<br>       ROUND(delta_mi - total_area_sq_mi, 1) AS diff<br>FROM closest<br>ORDER BY diff ASC<br>LIMIT 3;</pre><p>As you can see, translating these steps into a query involved a few other maneuvers that I expound on below:</p><p><strong>Common Table Expressions (CTE)</strong>¹ — help to improve readability as queries increase in length and complexity. A CTE is declared before the main query. It is an excellent tool for organizing information and placing it in a logical order. In the above query it takes the form WITH closest AS () .</p><p><strong>Sub queries in FROM clause</strong>² allow for more complex workings. They are an effective way of answering detailed questions that requires filtering or transforming data before including it in your final results. The content in the bracket WITH closest AS () is a sub query in a FROM clause.</p><p><strong>Sub queries in SELECT</strong>³ help to bring summary values into a detailed dataset — useful since you can’t return aggregate values in un-grouped SQL queries. They help in performing complex mathematical equations on your dataset — e.g. by how much does the global loss in forest area differ from each country’s total area?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xRD80HAaxtylauyZTzIkTA.png" /><figcaption>If you compare the forest area lost between 1990 and 2016, it is closest to Peru’s total area in 2016</figcaption></figure><h3>Regional Snapshot</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*n9yV26M52NcRGBhF9lHjqA.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@hdbernd?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Bernd 📷 Dittrich</a> on <a href="https://unsplash.com/photos/aerial-view-of-city-during-daytime-dv9MCgkOQ5U?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><p>We will now move one level down, to get a regional snapshot:</p><ul><li>Find the proportion (in %) of forested area of the world in 2016. Find the regions with the highest and lowest percentage of forested area in 2016</li><li>Find the proportion (in %) of forested area of the world in 1990. Find the regions with the highest and lowest percentage of forested area in 1990</li><li>Identify regions of the world that declined in forest area from 1990 to 2016</li></ul><h4>Highest &amp; Lowest Regions by % Forested Area in 1990 &amp; 2016</h4><p>When we created the forestation view, we also created a column fore_pct_km, which shows % forested area for each country, as shown below:</p><pre>-- explore 2016 data<br>SELECT country_name,<br>       region, <br>       ROUND(fore_pct_km::NUMERIC, 1) fore_pct_km<br>FROM forestation<br>WHERE year = 2016<br>LIMIT 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/997/1*7itnWrOFnNvHFBU1NZLKPA.png" /><figcaption>image by author: data exploration</figcaption></figure><p>From the output we see that we need to:</p><ul><li>aggregate all percentages for each region, for 1990 and 2016</li><li>to aggregate the percentages, we will use AVG()</li><li>filter for 1990 or 2016</li><li>GROUP BY region</li><li>JOIN the views created for each year to display both years in one table</li></ul><pre>CREATE VIEW pct_2016 AS<br>SELECT region,<br>       -- aggregate all percentages<br>       ROUND(AVG(fore_pct_km)::NUMERIC,2) AS reg_pct_16<br>FROM forestation<br>WHERE year = 2016<br>-- show percentages for each region<br>GROUP BY region;<br><br>CREATE VIEW pct_1990 AS<br>SELECT region, <br>       -- aggregate all percentages<br>       ROUND(AVG(fore_pct_km)::NUMERIC,2) AS reg_pct_90<br>FROM forestation<br>WHERE year = 1990<br>-- show percentages for each region<br>GROUP BY region;<br><br>-- show both outputs in 1 table by joining the views<br>SELECT region, reg_pct_16,<br>       reg_pct_90<br>FROM pct_2016<br>INNER JOIN pct_1990<br>USING(region)<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/975/1*MAm4N4mGPG4CgdkAnwltsw.png" /><figcaption>image by author: highest &amp; lowest regions by % forested area in 1990 &amp; 2016</figcaption></figure><p>From the output above, we note that East Asia &amp; Pacific has the highest % of forested area (47% in 1990 &amp; 50% in 2016), while Middle East &amp; North Africa has the lowest (2.7% in 1990 &amp; 3.2% in 2016).</p><h4>Regions That Declined in Forest Area From 1990 To 2016</h4><p>Using the 2 views created in the previous section, we create a column change, which shows whether % forested area has increased or decreased from 1990 to 2016. This is done using a sub query in the FROM clause, enclosed in a common table expression below:</p><pre>WITH pct_changes AS (<br>    SELECT region, reg_pct_90, reg_pct_16,<br>           (reg_pct_16 - reg_pct_90) AS change<br>    FROM pct_2016<br>    INNER JOIN pct_1990<br>    USING(region)<br>)<br><br>SELECT region,<br>       reg_pct_90,<br>       reg_pct_16,<br>        change<br>FROM pct_changes<br>ORDER BY change;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mbPBOmgoTbOEfOHPbCRtMA.png" /><figcaption>image by author: regions that declined in % forest area from 1990 to 2016</figcaption></figure><p>The only regions of the world that decreased in percent forest area from 1990 to 2016 were, Sub-Saharan Africa (dropped from 35.26% to 31.28%) and Latin America &amp; Caribbean (43.34% to 41.64%). All other regions actually increased in forest area over this time period. However, the drop in forest area in the two aforementioned regions was so large, the percent forest area of the world decreased over this time period from 32.42% to 31.38%.</p><h3>Country Trends</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1rAGmDYKxyOcjiy6M8YghQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@rgaleriacom?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Ricardo Gomez Angel</a> on <a href="https://unsplash.com/photos/green-banana-leaf-with-water-droplets-BzrCZPaNCvQ?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><p>Lastly we will deep dive into the country trends below:</p><ul><li>Find the 5 countries with the highest absolute decline in forest area from 1990 to 2016</li><li>Find the 5 countries with the highest % decline in forest area from 1990 to 2016</li><li>Find all of the countries with % forested area greater than 75% in 2016</li><li>Find the top 5 countries with the lowest forest cover in %</li></ul><h4>Five Countries With Largest Absolute Drop in Forest Area From 1990–2016</h4><p>Which countries are seeing deforestation to the largest degree? We can answer this question in two ways. First, we will look at the absolute square kilometer decrease in forest area from 1990 to 2016. To tackle this problem:</p><ul><li>we need to create 2 tables, one with 1990 forest area, and one with 2016</li><li>subtract 1990 forest area from 2016 forest area for each country</li><li>order the result by ascending order of difference</li><li>the first 5 countries will be those with the largest drop</li></ul><pre>WITH fa_1990 AS (-- create 1990 table<br>  SELECT country_name, region,<br>       ROUND(forest_area_sqkm::NUMERIC,2) AS fa_90_sqkm<br>  FROM forestation<br>  WHERE year = 1990 AND forest_area_sqkm IS NOT NULL<br>  ),<br>-- create 2016 table<br>fa_2016 AS (<br>  SELECT country_name, region,<br>       ROUND(forest_area_sqkm::NUMERIC,2) AS fa_16_sqkm<br>  FROM forestation<br>  WHERE year = 2016 AND forest_area_sqkm IS NOT NULL<br>  )  <br><br>SELECT country_name, <br>       fa_1990.region, <br>       fa_16_sqkm, <br>       fa_90_sqkm, <br>       -- subtract 1990 from 2016 forest area<br>       (fa_16_sqkm - fa_90_sqkm) AS change<br>FROM fa_1990<br>INNER JOIN fa_2016<br>USING(country_name)<br>WHERE country_name != &#39;World&#39;<br>ORDER BY change ASC<br>-- top 5 countries with largest drop<br>LIMIT 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XPkSnBo7Y27WPYNO7bbmXg.png" /><figcaption>image by author: top 5 countries with largest drop in absolute forest area from 1990–2016</figcaption></figure><p>From the above table, we see that the top 5 countries with the largest drop in absolute forest area come from 3 regions:</p><ul><li>Brazil (541,510 km²) from Latin America &amp; Caribbean</li><li>Indonesia (282,194 km²) &amp; Myanmar (107,234 km²) from East Asia &amp; Pacific</li><li>Nigeria (106,506 km²) &amp; Tanzania (102,320) from Sub-Saharan Africa</li></ul><h4>Five Countries With Largest % Drop in Forest Area From 1990–2016</h4><p>Which countries are seeing deforestation to the largest degree? We can answer this question in two ways. The second way, is to look at the % square kilometer decrease in forest area from 1990 to 2016. We use the same approach as above, and just substitute from the column with absolute values, to that with % values</p><pre>WITH fa_1990_pct AS (<br>  SELECT country_name, region,<br>       ROUND(fore_pct_km::NUMERIC,2) AS fa_90_pct<br>  FROM forestation<br>  WHERE year = 1990 AND fore_pct_km IS NOT NULL<br>  ),<br>fa_2016_pct AS (<br>  SELECT country_name, region,<br>       ROUND(fore_pct_km::NUMERIC,2) AS fa_16_pct<br>  FROM forestation<br>  WHERE year = 2016 AND fore_pct_km IS NOT NULL<br>  )  <br><br>SELECT fa_1990_pct.country_name, fa_1990_pct.region, fa_16_pct, <br>       fa_90_pct, (fa_16_pct - fa_90_pct) AS change<br>FROM fa_1990_pct<br>INNER JOIN fa_2016_pct<br>USING(country_name)<br>WHERE country_name != &#39;World&#39;<br>ORDER BY change ASC<br>LIMIT 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b7S2QZsbk45ki8A_JR0PrA.png" /><figcaption>image by author: top 5 countries with largest drop in % forest area from 1990–2016</figcaption></figure><p>The countries with the largest percentage drop are different from those with largest absolute drop.</p><h4>Find All Of The Countries With % Forested Area Greater Than 75% In 2016</h4><pre>SELECT country_name, region, <br>       ROUND(fore_pct_km::NUMERIC, 2) fore_pct_km<br>FROM forestation<br>WHERE year = 2016 AND fore_pct_km &gt; 75<br>ORDER BY fore_pct_km DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*V7KmeH7hGKJkAYsC5zPSxg.png" /><figcaption>image by author: countries with highest forest %</figcaption></figure><h4>Top 5 Countries With Lowest Forest Cover In %</h4><pre>SELECT country_name, region, <br>       ROUND(fore_pct_km::NUMERIC, 2) fore_pct_km<br>FROM forestation<br>WHERE year = 2016<br>ORDER BY fore_pct_km<br>LIMIT 5;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1016/1*ElqqzlcoOcVYplx2-L0OYQ.png" /><figcaption>image by author: top 5 countries with the lowest forest cover in 2016</figcaption></figure><p>The 5 countries with the lowest forest cover are in 2 regions, namely Europe &amp; Central Asia and Middle East &amp; North Africa.</p><h3>Summary</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Udo3RUVSm9SiBKhZpZNuqQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@uxindo?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">UX Indonesia</a> on <a href="https://unsplash.com/photos/person-in-blue-shirt-writing-on-white-paper-8mikJ83LmSQ?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><ul><li>The world lost 1,324,449 km² (3.208%) of its forest area from 1990 to 2016. This is equivalent to the land area of Peru</li><li>There is some disparity in forest cover across regions. East Asia &amp; Pacific has the highest % forest cover (50% in 2016), while Middle East &amp; North Africa has the lowest (3% in 2016)</li><li>Latin America &amp; Caribbean and Sub-Saharan Africa experienced decreases in % forest cover from 1990 to 2016. This was significant enough to negatively impact the global forestation percentage.</li><li>Brazil, Indonesia, Myanmar, Nigeria &amp; Tanzania have seen the largest absolute square kilometer decreases in forest area.</li><li>Honduras, Korea (Dem. People’s Rep.), Zimbabwe, Cambodia, and Timor-Leste have experienced the largest percent decreases in forest area.</li><li>Suriname, Micronesia, Gabon, Seychelles, Palau, American Samoa, Guyana, Lao PDR, and Solomon Islands, are among the countries with highest % in forest cover in 2016</li></ul><h3>Implications</h3><ul><li>Target awareness campaigns and conservation efforts in regions experiencing the largest decreases in forest area, such as Brazil and Indonesia.</li><li>Collaborate with countries experiencing severe deforestation, like Honduras and Zimbabwe, to identify strategies for conservation.</li><li>Support programs and initiatives that have successfully increased forest area. Promote policies and practices to maintain high levels of forestation in countries like Suriname, Micronesia, and Gabon.</li><li>Spearhead efforts to conserve and rehabilitate forests in regions with the greatest potential, such as East Asia &amp; Pacific.</li><li>Advocate for sustainable forestry practices and afforestation projects</li></ul><h3>Instructions To Setup Database</h3><p>This section is for anyone who wants to follow along by running the queries on their machine. As they were written using PostgreSQL, those using other versions of SQL need to make necessary adjustments. Go <a href="https://github.com/paddyokore/sql-reforestration">here</a> to download the csv data from my GitHub account. The queries below should set up the relevant tables and import the required data. Make sure to change the file paths in the FROM clauses below. You might want to save the downloaded data in the program folder where you installed your SQL software. Initially you might get a “Permission Denied” error, if you save it in a folder that your SQL has no read access. I found this to be the easiest solution.</p><pre>-- Create the land_area table<br>CREATE TABLE &quot;land_area&quot; (<br>    &quot;country_code&quot; VARCHAR(3),<br>    &quot;country_name&quot; VARCHAR(255),<br>    &quot;year&quot; INT,<br>    &quot;total_area_sq_mi&quot; NUMERIC<br>);<br><br>-- Import data from land_area.csv into the land_area table<br>COPY land_area <br>FROM &#39;C:\Program Files\PostgreSQL\16\data\forestation_data\la.csv&#39; <br>DELIMITER &#39;,&#39; CSV HEADER;<br><br>-- Create the forest_area table<br>CREATE TABLE forest_area (<br>    country_code VARCHAR(3),<br>    country_name VARCHAR(255),<br>    year INT,<br>    forest_area_sqkm NUMERIC<br>);<br><br>-- Import data from forest_area.csv into the forest_area table<br>COPY forest_area <br>FROM &#39;C:\Program Files\PostgreSQL\16\data\forestation_data\fa.csv&#39; <br>DELIMITER &#39;,&#39; CSV HEADER;<br><br>-- Create the regions table<br>CREATE TABLE regions (<br>    country_name VARCHAR(255),<br>    country_code VARCHAR(3),<br>    region VARCHAR(255),<br>    income_group VARCHAR(255)<br>);<br><br>-- Import data from regions.csv into the regions table<br>COPY regions <br>FROM &#39;C:\Program Files\PostgreSQL\16\data\forestation_data\r.csv&#39; <br>DELIMITER &#39;,&#39; CSV HEADER;</pre><p>If this project piqued your interest in exploring other SQL projects, check out:</p><ul><li><a href="https://medium.com/@Armonia1999/data-analysis-project-using-sql-to-clean-and-analyse-data-64a24e84b730">this</a> one by <a href="https://medium.com/u/fdfaf0a0a610">Armonia</a>.</li><li><a href="https://medium.com/u/f6366993e3b5">Andreas Martinson</a> and others in <a href="https://medium.com/u/e422a84aeb24">Learning SQL Editors</a></li></ul><h4>References</h4><ol><li>Mona Khalil, <a href="https://campus.datacamp.com/courses/data-manipulation-in-sql/correlated-queries-nested-queries-and-common-table-expressions?ex=7">Data Manipulation in SQL — Common Table Expressions, datacamp</a></li><li>Mona Khalil, <a href="https://campus.datacamp.com/courses/data-manipulation-in-sql/short-and-simple-subqueries?ex=5">Data Manipulation in SQL — Sub Queries in FROM, datacamp</a></li><li>Mona Khalil, <a href="https://campus.datacamp.com/courses/data-manipulation-in-sql/short-and-simple-subqueries?ex=8">Data Manipulation in SQL — Sub Queries in SELECT, datacamp</a></li><li><a href="https://data.worldbank.org/indicator/AG.LND.TOTL.K2?view=chart">world bank</a> data</li><li><a href="https://data.worldbank.org/indicator/AG.LND.FRST.K2?view=chart">world bank</a> data</li><li>Avinash Navlani,<strong> </strong><a href="https://www.datacamp.com/tutorial/views-in-sql">Views in SQL, datacamp</a></li></ol><p><strong>Go from SELECT * to interview-worthy project. </strong><a href="https://learning-sql.ck.page/aa03293372"><strong>Get our free 5-page guide</strong></a><strong>.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=52c771514877" width="1" height="1" alt=""><hr><p><a href="https://medium.com/learning-sql/using-sql-to-understand-forestation-trends-52c771514877">Using SQL To Understand Deforestation Trends</a> was originally published in <a href="https://medium.com/learning-sql">Learning SQL</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding Gun Violence in American Schools]]></title>
            <link>https://medium.com/@paddyokore39/understanding-gun-violence-in-american-schools-bd99a57d45dd?source=rss-ddf17542ecab------2</link>
            <guid isPermaLink="false">https://medium.com/p/bd99a57d45dd</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[makeovermonday2020]]></category>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[storytelling]]></category>
            <dc:creator><![CDATA[Patrick Okore]]></dc:creator>
            <pubDate>Fri, 27 Oct 2023 20:26:39 GMT</pubDate>
            <atom:updated>2023-10-29T18:59:07.643Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DwtTFnZISMGsvnSJM9iugQ.png" /><figcaption>Photo by <a href="https://www.istockphoto.com/photo/gun-violence-in-school-setting-gm821547664-132921723">iStock</a></figcaption></figure><h3>Introduction</h3><p>We have all at one point heard of the school shootings in America. I know that whenever I do, several questions race through my mind like : “how can a student buy a gun?”, “why would they do that to their fellow students?” to “why does this keep happening in America?” In short, endless questions about so many things surrounding the incident.</p><p>Thus when I came across the <a href="https://www.makeovermonday.co.uk/data/data-sets-2022/">makeovermonday</a> project on school shootings, I had high expectations that my endless questions would be answered. I was however deflated to find these simple <a href="https://k12ssdb.org/">visualizations,</a> which left me with more questions than answers. Given the sensitive and polarizing nature of this topic, it makes sense to do a deep-dive on this topic to inform the public the inner workings of gun violence in American schools. The viz glossed over the topic by showing one side of the story only : major incidents and fatalities.</p><p>Armed with the <a href="https://data.world/makeovermonday/2022w48">data</a> for shootings from 1970 to 2022, I will offer a different perspective by doing a deep-dive that spans incidents, victims and perpetrators, to give a holistic view on this topic.</p><h3>Setting The Context: Issue Tree</h3><p>Having looked at the data, I came up with the following problem statement:</p><blockquote>What are the 9 underlying patterns, characteristics, causes and outcomes of American school shootings from 1970–2022?</blockquote><p>The diagram below shows an issue tree that deconstructs this problem statement. Click (on computer) or tap (on phone) the image below to zoom in. Same applies for all charts displayed throughout the story</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XuV7c5UYMlsty8Xk_zhgfw.png" /><figcaption>Issue tree</figcaption></figure><p>From the above issue tree, you can see that my analysis will be broken down into 3 hypotheses, namely:</p><ul><li>What are the patterns of a school shooting?</li><li>Who are the affected victims?</li><li>Who are the perpetrators?</li></ul><p>Each hypothesis then has sub-hypotheses under it as illustrate above that scope out, distill and structure the hypotheses into analyzable questions.</p><h3>Hypothesis 1: What Are The Patterns of A School Shooting?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tZkLeZCK9gOeYhNMKMAQIg.jpeg" /><figcaption>Photo by <a href="https://www.pexels.com/@thatguycraig000/">Craig Adderley</a> from <a href="https://www.pexels.com/photo/concrete-road-between-trees-1563356/">Pexels</a></figcaption></figure><p>Here we will tackle the sub-hypotheses below:</p><ul><li>Investigate seasonality — are school shootings more likely to occur during certain time periods?</li><li>Regional disparities — are they more likely to occur in certain states?</li><li>What is the profile of the affected schools?</li></ul><h4>Are School Shootings More likely to Occur During Certain Time Periods?</h4><p>The chart below shows that the number of shootings and victims have increased over the years, and peaked in 2022 with 265 incidents and 248 victims. Click or tap on the chart below to zoom in.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gP7Zj6VjRTP0phWKWCSOTg.png" /><figcaption>Number of incidents &amp; victims</figcaption></figure><p>The most notable fatalities have occurred in Sandy Hook (26), Robb Elementary (21), Marjory Stoneman (17), Columbine (13) &amp; Santa Fe (10) as illustrated below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pvLbO8yKlWu7pR8-T1QVYg.png" /></figure><p>Below we see that incidents are higher at the beginning of the year, and towards the end of the year. They coincide with closing cycles since incidents decline during summer break (early June — end Aug). They however spike to highest levels immediately after summer break (Sept and Oct). Highest deaths occur in Feb (45%), May (43%) and Dec (43%).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d7qVBkoQXAE8up4_0sEtUQ.png" /><figcaption>Incidents and fatalities by month &amp; season</figcaption></figure><h4>Are Shootings More Likely to Occur in Some States?</h4><p>High prevalence of shootings in the Southern schools which account of nearly half of deaths. Other regions trailing behind by nearly half (or less) of that rate. California (West) has the highest fatality rate (86) followed by Texas (74) in the South. Leading states in Midwest (Illinois — 34) and Northeast (Pennsylvania — 32) pale in comparison.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m3jAVMzFKlkCakvK9ueVkA.png" /><figcaption>School shooting fatalities by state and region</figcaption></figure><h4>What is the Profile of Affected Schools?</h4><p>High school accounts for the 3 out of every 4 deaths. Elementary and middle schools experience less severe casualties in comparison. Shootings happen both inside and outside buildings within premises, with parking lots, hallways and classrooms being the top hot spots. More detail below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qm6e-TptfmuJ5jLZzIJeyQ.png" /><figcaption>School level and location fatalities</figcaption></figure><p>Incidents are more likely to happen during class time. High fatalities during morning (158) and afternoon classes. A likely indication that perpetrators plan their attack when there is high concentration of human traffic in confined spaces. More detail below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gLgdxdINaT9rOjYkK--dCA.png" /><figcaption>Fatalities by day part</figcaption></figure><h3>Hypothesis 2: Who Are the Affected Victims?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UMQMw1Od82NurCp40Y-U9w.jpeg" /><figcaption>Photo by <a href="https://www.pexels.com/@yankrukov/">Yan Krukau</a> from <a href="https://www.pexels.com/photo/people-laughing-together-8199173/">Pexels</a></figcaption></figure><p>Here we will tackle the sub-hypotheses below:</p><ul><li>How old are they?</li><li>What is their gender?</li><li>How are they related to the school?</li><li>What is their fate?</li></ul><h4>How Old Are They?</h4><p>A typical victim has a median age of 17 years, since an overwhelming majority of school shootings occur in high school. Those over 19 years are most likely school faculty, or relatives of students who were in the school during the incident.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*djGWsijwuLo-72hEsETAzA.png" /><figcaption>Victim’s age distribution</figcaption></figure><h4>What Is Their Gender &amp; School Affiliation?</h4><p>Victims are more likely to be male (58%) than female (17%). They are also more likely to be students. Among non-students, male victims are non-affiliated with the school. On the other hand, female victims are more likely to be teachers</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VrLDbelNwxZ9XlFxQAAaEA.png" /><figcaption>Victim’s gender and school affiliation</figcaption></figure><h4>What Is Their Fate?</h4><p>The viz below shows similar fatality rates by gender</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F2phSFDOBA2sfjqYZVtDkA.png" /><figcaption>Fatality rate by gender</figcaption></figure><h3>Hypothesis 3: Who Are the Perpetrators?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*juZUD_36xgsemDR71giM4w.jpeg" /><figcaption>Photo by <a href="https://www.pexels.com/@karolina-grabowska/">Karolina Grabowska</a> from <a href="https://www.pexels.com/photo/a-person-holding-a-handgun-5202421/">Pexels</a></figcaption></figure><p>Here we will tackle the sub-hypotheses below:</p><ul><li>How old are they?</li><li>What is their gender?</li><li>How are they related to the school?</li><li>What is their fate?</li><li>Do they have any accomplices?</li><li>What weapons do they use?</li><li>Why do they kill?</li></ul><h4>How Old Are They?</h4><p>The viz below shows that 72% of shooters are between 15–19 years old. Surprisingly, 25% of shooters are aged between 20 and 78 years.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C67tsCFwrbaWlug1QAnuQQ.png" /><figcaption>Perpetrator’s age</figcaption></figure><h4>What Is Their Gender &amp; School Affiliation?</h4><p>Perpetrators are more likely to be students, and male. Even more surprising is the appearance of parents, though a small fraction of the offenders. However, there is a huge chunk of missing gender (43%), that could mean the trends below are underestimations</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qEsDjWQqZ8tRkIAPBhM-YA.png" /><figcaption>Perpetrator’s gender and school affiliation</figcaption></figure><h4>What Is Their Fate?</h4><p>Perpetrators are either apprehended, at-large or dead. Female perpetrators are more likely to commit suicide (14%) after their heinous acts than the males (9%).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eDIOQ4vzpXqv4mZKFrJxaw.png" /><figcaption>Perpetrator’s fate</figcaption></figure><h4>Do They Have Any Accomplices?</h4><p>Male perpetrators are more likely to have an accomplice (22%) than females (17%)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sqIRImdxH0dGtLSaAbCFVg.png" /><figcaption>Accomplice</figcaption></figure><h4>What Weapons Do They Use?</h4><p>An overwhelming majority use handguns. However, higher fatality rates are caused by shotguns (45%) and rifles (28%) — which have much lower likelihood of usage</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P3CpDm6quwM4Sq5G4NPlEQ.png" /><figcaption>Top weapons used</figcaption></figure><h4>Why Do They Kill?</h4><p>Nearly half of all shooting incidents arise from escalation of a dispute. Accidental (12%) and drive-by shootings (8%) trail by a large margin. However, less prevalent reasons like illegal activity, indiscriminate shooting and targeted victim cause much higher fatalities (39%, 30% &amp; 38% respectively) than the top 3</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*h8UlL78y4nTbJFCXQBrZSA.png" /><figcaption>Motive</figcaption></figure><h3>Biases &amp; Limitations</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NDEK-aNkSJ073zKYDnYZZg.png" /><figcaption>Photo by Pixabay from Pexels</figcaption></figure><p>Data as collected and treated currently, operates within certain constraints. Below we tackle these and their implications for our analysis</p><p><strong>Data collection biases:</strong> There are missing variables to help us understand these incidents better e.g:</p><ul><li>the neighborhood that the school is located in — are incidents evenly matched across rich and poor areas?</li><li>proximity of school to a police station — does this act as a deterrent?</li><li>the ease of purchasing a gun in each state</li></ul><p><strong>Data processing biases</strong></p><ul><li>Outliers: 79% of the incidents lasted 1 minute. The rest lasted 6 minutes — 720 minutes. However, this variable was not used in the analysis as there was no noteworthy insight on incident duration</li><li>Missing values: variables have some type of missingness, captured either as ‘NULL’ or ‘Unknown’. For example shooter gender has 43% null vs. victim gender at 23% null.</li><li>Due to the sensitive nature of the subject matter, it is not realistic to impute missing values. The missing data is in most cases less than 50%, thus does not skew the analysis</li></ul><p><strong>Data insights biases </strong>— confounding variables</p><ul><li>We say that fatality rate is disproportionately higher for illegal activity, indiscriminate shooting and targeted victim incidents. We however don’t know if there is another unseen variable at play that drives this trend</li></ul><h3>Summary</h3><p>In the above article, we have seen:</p><p><strong>Incident trends:</strong> School shootings have increased since 1970, peaking in 2022 with 265 incidents and 248 victims. Notable fatalities include Sandy Hook, Robb Elementary, Marjory Stoneman High, Columbine High and Santa Fe High.</p><p><strong>Seasonal patterns</strong>: Shootings are more frequent at the beginning and end of the school year, with a decline during the summer break — highest death rates in February, May, and December</p><p><strong>Regional disparities</strong>: The Southern region has a significantly higher prevalence of shootings, accounting for nearly half of all deaths. Most affected states are California and Texas</p><p><strong>Victims</strong></p><ul><li>High school accounts for 75% of fatalities followed by elementary and middle schools experiencing much fewer casualties</li><li>Shootings mainly occur during class time, when there is high traffic in confined spaces — classrooms, parking lots, hallways. Students suffer the highest number of casualties, especially male.</li></ul><p><strong>Perpetrators</strong></p><ul><li>Shooters are either apprehended, at-large, or deceased. They are more likely to be students or male. Female perpetrators are more likely to kill themselves after the act.</li><li>Handguns are the most common weapon, but shotguns and rifles result in higher fatality rates.</li></ul><p><strong>Motive</strong></p><ul><li>Nearly half of shooting incidents stem from the escalation of disputes. Less prevalent motivations, such as illegal activity, indiscriminate shooting, and targeted victims, result in higher fatality rates than the top three motivations.</li></ul><h3>Implications</h3><p>Minds more capable than mine have most likely looked into this tragedy and come up with more detailed implications. Based on the findings above, I propose that the implications below likely feature in their list</p><p><strong>Early Intervention and Mental Health Support:</strong></p><ul><li>Implement early intervention programs and provide access to mental health support for students to address potential issues before they escalate.</li><li>Address the underlying causes of school shootings, focusing on preventing disputes from escalating and reducing violence related to illegal activities and targeted victims.</li></ul><p><strong>Increased Security Measures:</strong></p><ul><li>Enhance security measures in schools, such as surveillance systems, controlled access points, and trained security personnel.</li><li>Focus on security measures during high-traffic times, especially in the morning and afternoon, to deter potential shooters.</li><li>Provide school staff, students, and parents with training in school safety protocols and procedures.</li></ul><p><strong>Policy Development:</strong></p><ul><li>Implement (or at least try) stricter gun control measures to reduce access to firearms, particularly shotguns and rifles, which have higher fatality rates. I say try because this is a polarizing topic with deep divisions along political lines, beyond the scope of this discussion.</li></ul><p><strong>Inter-agency Collaboration:</strong></p><ul><li>Encourage collaboration among law enforcement, educational institutions, mental health professionals, and community organizations to identify and address potential threats.</li></ul><p>Thank you for reading! Kindly share to spread the awareness and sensitization on this tragic but perennial occurrence. My thoughts and prayers with those affected</p><p>To learn more on issue trees and how to use them in structuring your story, check out this <a href="https://medium.com/jong-park/how-to-apply-ghost-aka-shell-and-skeleton-decks-7692a942c802">article </a>by <a href="https://medium.com/u/dafd2a1befec">Jay</a></p><p>You can also check out <a href="https://medium.com/p/f2016182f509">this</a> by <a href="https://medium.com/u/52630cde42cf">Nicole Dake</a> where she explores whether school shootings are an American phenomenon.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bd99a57d45dd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Uncovering Flight Delays: A Tableau-Powered Data Exploration]]></title>
            <link>https://medium.com/@paddyokore39/uncovering-flight-delays-a-tableau-powered-exploration-of-us-domestic-flights-134536414bb6?source=rss-ddf17542ecab------2</link>
            <guid isPermaLink="false">https://medium.com/p/134536414bb6</guid>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[travel-insight]]></category>
            <category><![CDATA[kpi-dashboard]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <dc:creator><![CDATA[Patrick Okore]]></dc:creator>
            <pubDate>Sun, 08 Oct 2023 19:06:07 GMT</pubDate>
            <atom:updated>2023-11-12T11:54:04.046Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M1RZeweSQNkUsyiXEtgh5A.jpeg" /><figcaption>Photo by <a href="https://www.pexels.com/@goumbik/">Luka </a>on <a href="https://www.pexels.com/photo/paper-on-gray-laptop-669617/">Pexels</a></figcaption></figure><h3>Introduction</h3><p>This post intends to showcase the power of Tableau as a business intelligence tool for dissecting complex datasets and extracting valuable insights for informed decision-making. We will use a real-world dataset show how this can be done. The data comes from a Kaggle dataset, and tracks the on-time performance of US domestic flights operated by large air carriers in 2015. To work through the steps below in tableau, you can download the data <a href="https://www.kaggle.com/datasets/usdot/flight-delays/data">here</a>.</p><p>For this project, we will seek to address the following business questions:</p><ol><li>Uncover airlines and airports with the worst delays</li><li>Understand flight distribution per state, and the proportion that are early, delayed, on time and diverted/cancelled</li><li>Find out what causes delays</li><li>See if the delay of flights changes by month</li></ol><p>We will do this by creating specific visualizations, and a dashboard. For each visualization, we will discuss:</p><ul><li>The business question it seeks to address</li><li>Design considerations</li><li>Creating calculated fields</li><li>Filtering — in worksheets and dashboards</li><li>Using the marks cards</li><li>Advanced tool-tip functionalities</li></ul><p>Let us dig in!</p><h3><strong>Data Connection</strong></h3><p>The data comes in 3 csv files:</p><ul><li>flights: main dataset with all flight details in 33 columns</li><li>airports: full airport name, location details</li><li>airlines: full airline name</li></ul><p>I connected flights.csv to 2 versions of airports.csv, to be able to map origin &amp; destination airport codes from flights.csv codes to their proper names. Used `destination` &amp; `origin` airport columns from flights to link to data code from airports.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/608/1*XrkGb6rwfkNJy9z-RCdgEA.png" /><figcaption>data connection</figcaption></figure><h3>Top Airport Delays Viz to Know: Which Airport Arrival Destinations Have the Worst Delays?</h3><p>The `Arrival Delay` column showed by how many minutes a plane was delayed. I noticed 4 key categories; early flights, on-time flights, delayed flights, and cancelled/diverted flights. I thus created an `Arrival Type` variable as shown below, to help me filter for the delayed flights:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/162faa6d0fe6a41f399ed7f042b0bab0/href">https://medium.com/media/162faa6d0fe6a41f399ed7f042b0bab0/href</a></iframe><p>To visualize this data, it thus made sense to show the count of flights for each airport that was delayed. A bar chart is appropriate as we have numerous categories to display.</p><p>I started off with a stacked bar chart below showing the count of flights across all airports. I added the flights.csv(count) to columns and airport names from airports_destination.csv, then ordered by descending order of count to arrange from highest to lowest counts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*atdoWVa1uAmcphm5_9UgZw.png" /><figcaption>Flight distribution by airport</figcaption></figure><p>I then added arrival type to color marks so I could separate out the 4 categories</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d8D-mhuCh5Tk5pbWvl2ORw.png" /><figcaption>Number of flights per airport segmented by delay type</figcaption></figure><p>Once this was done, I added arrival type to the filters card to only show delayed flights below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W7CXqGnNHo--RC9t1JMd4w.png" /><figcaption>Number of delayed flights by airport</figcaption></figure><p>The next step involved reducing the 322 to 10, to show the top 10 airports notorious for flight delays. To do this, I dragged airports to the filter button and used the top tab to specify top 10 by count of number of flights.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/606/1*gE_6TTCYA_A3qO8-95cJVg.png" /><figcaption>Filter Airport Name by top 10 count</figcaption></figure><p>From this, I was able to deduce the airports with the highest number of delays: Hartsfield-Jackson and Chicago O’Hare, Dallas/Fort Worth</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_uYYkfHSJYj2psyQ-rCYgA.png" /><figcaption>Top 10 notorious airports for delays</figcaption></figure><p>In terms of design, avoided chart junk by:</p><ul><li>converting the stacked bar chart to a simple bar chart by only filtering to show delayed flights</li><li>focusing on the top 10 airlines with the most delays</li><li>showing value counts on bar charts and removing the x-axis</li></ul><h3>Top Airline Delays to Know: Which Airlines Have the Worst Delays?</h3><p>The set up for this viz is like the top airport delays, except we now look at airlines. For this reason, I decided to duplicate the top airlines delays worksheet, renamed it to top airline delays, then replaced airports in the rows, with airline from flights csv.</p><p>However, before making this change, I created a calculated variable called `Airline Full Name`, from airlines, which was then used in the rows — initial variable only had airline initials which would have been cumbersome to interpret.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c2b899ded9872c109074b0190dd39891/href">https://medium.com/media/c2b899ded9872c109074b0190dd39891/href</a></iframe><p>A final step was to link `Arrival Type` and `Airport` filters from the “Top Airport Delays” sheet to work on this sheet as well. To achieve this, follow the 2 steps below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/537/1*DmCqk_sgsfsWGmZ3fYcM3g.png" /><figcaption>Step 1: choose selected worksheets</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/791/1*To6SVL6TTWr0ZsKPnwJi4Q.png" /><figcaption>Step 2: mark Top Airline Delays in the dialog box</figcaption></figure><p>The final output looked as shown below. Thus, showing airlines notorious for delays in the airports with the worst delays. From this, Airlines with the highest number of delays are Southwest, American, United, Delta and SkyWest</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Tzw47LmHyAQVdJLy7sbu2w.png" /><figcaption>Top airlines notorious for delays</figcaption></figure><h3><strong>Flight Distribution per State</strong></h3><p>This sheet’s objective was to illuminate: what is the flight distribution per state, and what proportion of these are early, delayed, on time and diverted/cancelled? This entailed a 2-pronged approach:</p><ol><li>a map was used to show flight distribution per state</li><li>then used advanced tool-tip to show breakdown for each state by `Delay Type`</li></ol><p><strong>Step 1</strong>: used the generated latitude and longitude values to generate a map plot from “Show Me”. I added state to details marks card, and number of flights to colour. The viz thus shows the distribution of flights per state. The darker the shade of blue, the more flights a state has.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4SdETKfBHsXPMAeOQFgY4A.png" /><figcaption>Step 1: flights map</figcaption></figure><p><strong>Step 2</strong>: created a “Delay Type” worksheet. This shows the number of flights that were early, delayed, on time and diverted/converted in %. I dragged arrival type to rows, and number of flights to text. I then used quick table calculations to get the % of total. I then excluded on time, diverted/cancelled because of negligible proportions</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/160/1*y6zulXloJNy96kPAnYgCEQ.png" /><figcaption>step 2: worksheet for tool-tip</figcaption></figure><p>Then I went back to the “Number of Flights per State” worksheet and inserted the delay type sheet as a tool tip.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/983/1*lCynrW5amyltN-_-PcCKrw.png" /><figcaption>Step 2: advanced map tool-tip with delay type sheet</figcaption></figure><p>I formatted state and number of flights values in the tool tip to font size 11, tableau bold — to make it stand out. As a result of this, one can see the number of flights per state, and the proportion by delay type, from the tool tip. The example below shows that California has the highest number of total flights, with 40% being delayed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1009/1*sa1EmX3PlNAnbhiGr-Isjw.png" /><figcaption>Number of flights per state with delay type %</figcaption></figure><h3>What Causes Flight Delays and Does This Change Over Time?</h3><p>For this I opted to look at the frequency of occurrence of the delays by using count, rather than the duration of delays e.g. by using average duration. I went for frequency of occurrence to explore how often this was a problem.</p><p>First, I created a calculated field `Date` by combining `Year,` `Month` and `Day` columns, to create a date data type. As shown below</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/d249b0a2d683def7f1cf6754476e08a9/href">https://medium.com/media/d249b0a2d683def7f1cf6754476e08a9/href</a></iframe><p>I then dragged the date variable to columns and showed continuous month. To the rows I added the columns `Arrival Delay`, `Departure Delay`, `Airline Delay`, `Air System Delay`, ‘Late Aircraft Delay`, `Security Delay` and `Weather Delay`.</p><p>Activated the `Airport` filter from the “Top Airport Delays” sheet so that we can limit this analysis to the 10 airports with the worst delays</p><p>The chart below shows that arrival and departure are the most frequently occurring delay causes. This is consistent throughout the year. February and September have the lowest arrival and departure delays</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vwcmBpLTD3yXt31plFW5CA.png" /><figcaption>Delay causes over time</figcaption></figure><p>Design considerations</p><ul><li>Opted for an area chart over a line chart because it better underscores the magnitude of each delay type over time</li><li>Edited the y axis values for each delay type to match by setting the max value at 10,000. This enables the reader easily to see the differences across delay type visually</li><li>Formatted the y axis label of each chart by removing the word ‘delay’ to make it less cluttered</li></ul><h3>Delay Over Time: Does the Delay of Flights Vary by Month?</h3><p>Add date to columns and show as monthly continuous. Add the number of flights to columns, and arrival type to colour to compare delays to other arrival types. Use quick table calculations to get proportions by percent of total. Percentages calculates proportion of arrival types for each month.</p><p>Activated the airport filter from the top airport delays sheet so that we can limit this analysis to the 10 airports with the worst delays</p><p>The analysis below shows most flights tend to be early, however, roughly 40% of flights tend to have some type of delay. Delays are highest in February and June, and lowest in September. Surge of delays in December most likely due to holiday rush during the Christmas holiday</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y1ofJX26czFqbrAAN2C3Jg.png" /><figcaption>Delay over time</figcaption></figure><h3><strong>Delays Dashboard</strong></h3><p>The objective of this dashboard is to enable the audience dig into the data for any questions they may have. We will make it interactive so the audience can get multiple perspectives from the same piece of information</p><p>To create the dashboard we, will use 3 major visualizations namely:</p><ul><li>Top Airport Delays</li><li>Top Airline Delays</li><li>Flight Distribution per State</li></ul><p>In this post I will gloss over the intricacies of creating a dashboard, as that is a whole subject matter to be discussed in a future blog post.</p><p>Click on the dashboard sheet to create a new dashboard, then drag the relevant worksheets to the empty dashboard canvas. Use the flight distribution map as a filter, to see the most notorious airports and airlines in each state for delays. The example below shows that Dallas/Fort Worth and George Bush Airports are the worst airports in Texas, American Airlines and Atlantic SouthEast are the worst airlines.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_6hvgdr23mfwZcwmY2BkTg.png" /><figcaption>Dashboard filter for number of flights per state</figcaption></figure><p>The next filter is on the airports bar chart. For each of the top 10 notorious airports, we can know their state, and the airlines responsible for the delays. For example, below, the worst airport in delays is Hartsfield Jackson, located in Georgia. The worst airlines in this airport are Delta, Atlantic Southeast, and Southwest</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ev8miV8pGtX9iGBuFp8DKQ.png" /><figcaption>Dashboard filter for airport delays</figcaption></figure><p>The last filter is on the airlines bar chart. This enables us to drill down and see the airports and states affected by delays from each airline. For example, below, Southwest airlines is responsible for delays in 7 of the 10 worst airline delays, and in majority of the states</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xBehZYtb6VfaSQMUOahtgg.png" /><figcaption>Dashboard filter for airline delays</figcaption></figure><h3>Summary</h3><p>From the above viz, we can see that:</p><ul><li>Most flights go to the states of California, Texas, Florida, Illinois, and Georgia.</li><li>Majority of flights tend to be early, however, roughly 40% of flights tend to have some type of delay. Delays are highest in February and June, and lowest in September. Surge of delays in December most likely due to holiday rush during the Christmas holiday</li><li>Most delays due to arrivals and departures — consistent throughout the year. February and September have the lowest arrival and departure delays. Analysis based on number of times each delay occurred.</li><li>Airports with the highest number of delays: Hartsfield-Jackson, Chicago O’Hare, and Dallas/Fort Worth.</li><li>Airlines with the highest number of delays: Southwest, American, United, Delta, Skywest.</li></ul><p>Thank you for reading. Overall, this blog showcased the power of Tableau as a data intelligence tool for dissecting complex datasets and extracting valuable insights for informed decision-making in the aviation industry. It also show cased how to use various functionalities and it is my hope that you learned something new. In case you are curious to see my workings above in tableau, refer to <a href="https://public.tableau.com/views/FlightDelaysProject_Edited1/DelaysDashboard?:language=en-US&amp;:display_count=n&amp;:origin=viz_share_link">this</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=134536414bb6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Navigating The Curves and Bumps: Data Cleaning and Analysis in The Movie Industry]]></title>
            <link>https://medium.com/@paddyokore39/navigating-the-curves-and-bumps-data-cleaning-and-analysis-in-the-movie-industry-baef53703a?source=rss-ddf17542ecab------2</link>
            <guid isPermaLink="false">https://medium.com/p/baef53703a</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[moviesreviewcentral]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[storytelling]]></category>
            <dc:creator><![CDATA[Patrick Okore]]></dc:creator>
            <pubDate>Sun, 19 Mar 2023 10:40:50 GMT</pubDate>
            <atom:updated>2023-11-12T12:28:06.809Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lWfq9tMVV99BClFCetlpKg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@benjagremler?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Benjamín Gremler</a> on <a href="https://unsplash.com/photos/_kFAMj5D4dI?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>When I started learning data science, I worked on a project which required me to clean, analyze and report on some data in the movie industry. This blog outlines the key things I gleaned about data science in general, while working on this project. These can be summarized into the points below:</p><ul><li>You will spend more than 50% of your time gathering, understanding, and cleaning data</li><li>Domain knowledge is crucial and will determine how you treat your data — from cleaning to analysis and interpretation</li><li>Outputs from statistical analyses should be implemented and interpreted within the context of the domain &amp; business problem at hand</li></ul><h3>You will spend more than 50% of your time gathering, understanding and cleaning data</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*puOF7isuRU3mqrb0maHaKg.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@flub?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Maxime Lebrun</a> on <a href="https://unsplash.com/photos/6g3Akg708E0?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>For this project we were given data from the following sources: <a href="https://www.boxofficemojo.com/">Box Office Mojo</a>, <a href="https://www.imdb.com/">IMDB</a>, <a href="https://www.rottentomatoes.com/">Rotten Tomatoes</a>, <a href="https://www.themoviedb.org/">TheMovieDB</a> and <a href="https://www.the-numbers.com/">The Numbers</a>. After getting a sense of what each dataset contained, I started off with IMDB, which was an SQL database with 7 tables.</p><p>I narrowed down to 2 tables which contained movie info (runtime, genre, name, release date) and a second table which contained movie ratings. To work efficiently, I merged the 2 tables via an inner join so that I could only focus on cleaning the table that would be used in the analysis. After some iterations, I finally settled on the following steps:</p><ul><li>Checking for outliers</li><li>Investigating duplicates in movie names</li><li>Handling missing values — the following columns had missing values:<br>- <strong>movie runtime</strong>: 10% of movies had missing values. I filled the missing values with the median runtime. A more elegant solution would have been to scrape the web for the actual missing values. I went with the first since this was a class project with a time limit. This will however be updated with the more elegant approach when I publish the jupyter notebook.<br>- <strong>movie genre:</strong> 1% of movies had missing values. I dropped those values, but in the notebook, I will update these with scraped web data as well.</li><li>Cleaning movie genres column where we had multiple genres for some movies — created separate columns for each additional genre mentioned. Had 3 columns with genres1, genres2, genres3.</li></ul><p>This process was grueling, required a lot of attention to detail, and was like going down a rabbit hole where one thing led to another, with no end in sight. It also required maintaining a balance between looking into the details, but not losing sight of the big picture, to give order and structure to the entire process</p><h3><strong>Domain knowledge is crucial, and will determine how you treat your data — from cleaning to analysis and interpretation</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Yduo0ofRAaoQSWQom5x0bA.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/es/@alexandrevanthuan?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Alexandre Van Thuan</a> on <a href="https://unsplash.com/photos/mr9FouttLGY?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Since I have zero experience in the movie industry, it was safe to assume that I had minimal domain knowledge. My online search yielded 5 books below, which discuss various aspects of the movie industry:</p><ul><li>“The Business of Media Distribution” by Jeffrey C. Ulin — an overview of media distribution types</li><li>“The Hollywood Economist 2.0” by Edward Jay Epstein — an economic analysis of movie industry</li><li>“The Producer’s Business Handbook” by John J. Lee Jr — a detailed guide to movie production</li><li>“The Big Picture: The Fight for the Future of Movies” by Ben Fritz — challenges of movies in the digital age</li><li>“The Power of the Dog” by James C. Scott — discusses social and cultural dynamics of the movie industry</li></ul><p>These all seemed like great resources, but since I did not have the time to read all the books, I looked for online/YouTube discussions about the key take out from each. That seemed like a good starting point for someone with no knowledge of the industry.</p><p>During data cleaning and EDA, the domain knowledge helped me get a deeper understanding of the data I was dealing with, and how to treat it.</p><p>When checking for outliers:</p><ul><li>I discovered that the longest movie (<a href="https://www.google.com/search?client=firefox-b-d&amp;q=logistics+2012&amp;si=AEcPFx71Yi7YECiZ82vRWCw7NKNJMT9Iv-NFgCv51fzOTfFapqSRglP--7Ml843pwu4TClirYsk9nQZ5vKm6zkNQY-LBCxZaSwThcsS5NLg1T-U_m2vutE4%3D&amp;ictx=1&amp;ved=2ahUKEwjy6IPPnNL9AhUcX_EDHZszCkEQnZMFegQIXRAC#wptab=si:AEcPFx5m7Nylg00wpE-iI3fN8-AfSDNfZqA7NKPDMKmG1MgjOhCtz3XVxKdMdK29gG4M7YXdCjTNRNFsUMqV3SMWmlvjlhHM-z3RNd1kFbhuwYweFpTDZEVOentU3B2CeI65zAs0U_XoioMpIXWXzJKDMkvnZj8B2Q%3D%3D">Logistics</a>) had a runtime of 51,420 minutes. The shortest movie in the data was 3 minutes. The shortest movie to have ever been nominated for an Oscar (<a href="https://www.google.com/search?client=firefox-b-d&amp;q=the+shortest+movie+ever">Fresh Guacamole</a>) was even shorter than that, shy of 2 minutes. Had I not done research into these outliers, I would have immediately dismissed them as invalid and excluded them without a second thought</li><li>When checking for release year, I discovered movies with values beyond 2023. All these were valid and either showed sequels to already existing movies, or major movie projects in the production pipeline. The most eye-catching was a release year of 2115. Dubbed “the film you will never see”, <a href="https://www.google.com/search?client=firefox-b-d&amp;sxsrf=AJOqlzUfZDGgchr3REXf224KJB5JV-LuXQ:1679147169527&amp;q=2115+movie&amp;spell=1&amp;sa=X&amp;ved=2ahUKEwi_vcLUzuX9AhVHs6QKHeB6DXgQirwEKAB6BAgGEAE&amp;biw=1696&amp;bih=829&amp;dpr=1.13">100 years</a> is an experimental sci-fi which will be released on 18th November 2115 — not sure how they have worked out the logistics for that, but anyway, dreams are valid! It is however worth noting that since these movies had no metrics like ratings or gross earnings, I excluded them from the analysis.</li></ul><p>When analyzing duplicated movie names, duplicates existed for 2 reasons:</p><ul><li>they were duplicated because we had different movies with the same name. For example, we had 2 movies with the name “100% Love”. A quick Google search showed that the one produced in <a href="https://www.google.com/search?q=100+love&amp;client=firefox-b-d&amp;ei=85AMZJeCCofbkgXGuLTACg&amp;gs_ssp=eJzj4tLP1TdIz7IsL0gyYPTiMDQwUMjJL0sFAEhtBk0&amp;oq=100%25+love&amp;gs_lcp=Cgxnd3Mtd2l6LXNlcnAQARgGMgUIABCABDIFCC4QgAQyBQgAEIAEMgUIABCABDIFCAAQgAQyBQgAEIAEMgQILhBDMgUIABCABDIFCAAQgAQyBAguEEM6CggAEEcQ1gQQsAM6BggAEBYQHjoICAAQFhAeEA86FggAEOoCELQCEIoDELcDENQDEOUCGAE6EwguEI8BEOoCELQCEIwDEOUCGAI6EwgAEI8BEOoCELQCEIwDEOUCGAI6BQgAEJECOgUILhCRAjoHCC4Q1AIQQzoRCC4QgAQQsQMQgwEQxwEQ0QM6CwgAEIAEELEDEIMBOggILhCxAxCDAToICC4Q1AIQkQI6CgguELEDENQCEEM6BwgAELEDEEM6BAgAEEM6BwguELEDEEM6CgguENQCELEDEEM6CggAELEDEIMBEEM6CAgAEIAEELEDOggIABCxAxCRAjoLCC4QgAQQsQMQgwE6CAguEIAEELEDOgUILhCxAzoICC4QsQMQkQJKBAhBGABQlRhY1NMDYIz3A2gHcAF4BIABswKIAfojkgEGMi0xOC4xmAEAoAEBsAETyAEIwAEB2gEECAEYB9oBBggCEAEYCg&amp;sclient=gws-wiz-serp">2011</a> is different from that in <a href="https://www.google.com/search?q=100+love+2012&amp;client=firefox-b-d&amp;ei=7xwMZMPxCpuEhbIPgKK9gAs&amp;gs_ssp=eJzj4tLP1TfIMk5Ji08xYPTiNTQwUMjJL0tVMDIwNAIAZ18HDg&amp;oq=100%25+love&amp;gs_lcp=Cgxnd3Mtd2l6LXNlcnAQARgJMgUIABCABDIFCC4QgAQyBQgAEIAEMgUIABCABDIFCAAQgAQyBQgAEIAEMgUILhCABDIFCAAQgAQyBQgAEIAEMgQILhBDOhEILhCDARDHARCxAxDRAxCABDoLCAAQgAQQsQMQgwE6CAgAEIAEELEDOgsILhCABBCxAxCDAToICAAQsQMQgwE6BQguEJECOgcILhDUAhBDOggILhCABBCxAzoFCC4QsQNKBAhBGABQsxZYjjVgz6MBaABwAXgAgAGAAogB0RGSAQQyLTEwmAEAoAEBwAEB&amp;sclient=gws-wiz-serp">2012</a></li><li>they were also duplicated due to repetition of the same movie name as in the case of <a href="https://www.google.com/search?client=firefox-b-d&amp;sxsrf=AJOqlzU3nZFpP-IA_vBND1XKMbSsri616Q:1678543316627&amp;q=1989&amp;si=AEcPFx6vY0DhKadBB5SBEToMU9VHAhZfqDGw6RWqRSw7Z2ppKDiQdW4Vw7uSrw25sUtZ8ITFcRiymecpWMqDWxeNy_a9Gm_Spylsmbe9ucx4XEsQJcwM9jlDfkQK-mCSSSPWeKjlUiAOF9EHMPIBKtlkPOJSbZsHvq0nN-wFTSz8Vob0GXLFJQ0%3D&amp;sa=X&amp;ved=2ahUKEwjstYKRhdT9AhXpsKQKHdunCK4Q6RN6BAhIEAE&amp;biw=1696&amp;bih=829&amp;dpr=1.13">1989</a> which appeared twice, with the same production year of 2014</li><li>It was therefore important to distinguish between the 2 reasons to understand which ones were actual duplicates and to treat them as such</li></ul><h3><strong>Outputs from statistical analyses should be implemented and interpreted within the context of the domain &amp; business problem at hand</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YS07pCN7Bj5CUMe7O0zIlQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@marcsm?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Marc Sendra Martorell</a> on <a href="https://unsplash.com/photos/2BrdNFxW0UY?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Armed with the insights into the movie runtime distribution above, I decided the business objective would be best served cleaning the movie runtime to only include what represented most of the movies. Initial analysis using the <a href="https://www.google.com/search?q=box+plot+iqr+outliers&amp;client=firefox-b-d&amp;biw=1696&amp;bih=829&amp;sxsrf=AJOqlzVxAOJWJ8Feh3qhUaJtxDE8XMXlzQ%3A1679216271387&amp;ei=j84WZJKmF4P8sAeo9qGQAw&amp;oq=box+plot+iqr&amp;gs_lcp=Cgxnd3Mtd2l6LXNlcnAQARgFMgUIABCABDIFCAAQgAQyBQgAEIAEMgUIABCABDIFCAAQgAQyBggAEBYQHjIGCAAQFhAeMgYIABAWEB4yBggAEBYQHjIGCAAQFhAeOgoIABBHENYEELADOgUIABCRAjoECAAQQzoLCAAQgAQQsQMQgwE6CAgAELEDEIMBOgsILhCABBCxAxCDAToECCMQJzoECAAQAzoHCAAQsQMQQzoHCC4QsQMQQzoNCC4QsQMQxwEQ0QMQQzoICAAQgAQQsQM6CggAEIAEEBQQhwJKBAhBGABQmxxY8UZgqHpoAXABeACAAecCiAGHF5IBBjItMTAuMpgBAKABAcgBCMABAQ&amp;sclient=gws-wiz-serp">box-plot IQR</a> method meant that I should treat any movie below 46.5 minutes, or above 138.5 minutes as an outlier.</p><p>However, I used 250 minutes as the upper limit outlier — given that successful movies like <a href="https://www.google.com/search?q=avatar%3A+the+way+of+water&amp;client=firefox-b-d&amp;sxsrf=AJOqlzVngEJ-oGMNGFPJKmHNCFTxl1TEHg%3A1678480975101&amp;ei=T5YLZNDXBaCSxc8PtsSR4AY&amp;gs_ssp=eJzj4tLP1TdIz03KNkw3YPSSSCxLLEksslIoyUhVKE-sVMhPA1IlqUUA6gcMnw&amp;oq=ava&amp;gs_lcp=Cgxnd3Mtd2l6LXNlcnAQARgAMgQILhAnMgQIIxAnMgQIIxAnMgcILhCxAxBDMgQILhBDMgQILhBDMgQIABBDMgcILhDUAhBDMgcILhDUAhBDMggIABCABBCxAzoKCC4QxwEQ0QMQQzoLCC4QgwEQsQMQgAQ6CgguELEDENQCEENKBAhBGABQxRFY5hVgsCtoAHABeACAAbMDiAHeCpIBBzItMi4wLjKYAQCgAQHAAQE&amp;sclient=gws-wiz-serp">Avatar 2</a> and <a href="https://www.google.com/search?q=endgame&amp;client=firefox-b-d&amp;sxsrf=AJOqlzVb2kk7E1Qhi1tmZl4jpvanADbZ3Q%3A1678515114690&amp;ei=qhsMZKTgKYmokdUP9YCykAs&amp;gs_ssp=eJzj4tbP1TcwNDJLssguM2D0Yk_NS0lPzE0FAERaBng&amp;oq=end&amp;gs_lcp=Cgxnd3Mtd2l6LXNlcnAQARgAMgoILhCxAxDUAhBDMgoILhDUAhCxAxBDMgQIABBDMgQIABBDMgoIABCxAxDJAxBDMggIABCABBCxAzIKCAAQgAQQFBCHAjIICAAQgAQQsQMyCwgAEIAEELEDEIMBMggIABCABBCxAzoHCC4QsAMQJzoKCAAQRxDWBBCwAzoECC4QJzoECCMQJzoECC4QQzoICC4QsQMQgwE6CAgAELEDEIMBOgcILhDUAhBDOgoIABCxAxCDARBDSgQIQRgAUN8JWIAMYPUhaAFwAXgAgAG9AogB_QaSAQUyLTEuMpgBAKABAcgBCcABAQ&amp;sclient=gws-wiz-serp">Avengers: Endgame</a> clocked over 180 minutes. <a href="https://www.google.com/search?q=justice+league+snyder+cut&amp;client=firefox-b-d&amp;sxsrf=AJOqlzWALsP9vjqjjz9AyUA5Huk7Fc4FYg%3A1678514522943&amp;ei=WhkMZKCSOZqakdUP-vG2yAI&amp;gs_ssp=eJzj4tVP1zc0zDI0Mqg0TjMwYPSSzCotLslMTlXISU1ML01VKM6rTEktUkguLQEADL8NvQ&amp;oq=justice+league+s&amp;gs_lcp=Cgxnd3Mtd2l6LXNlcnAQARgAMgQILhBDMgUILhCABDIFCAAQgAQyCAguEIAEENQCMgUIABCABDIFCAAQgAQyCAguEIAEENQCMgUIABCABDIFCAAQgAQyBQgAEIAEOgoIABBHENYEELADOgQIIxAnOgcILhDUAhBDOgQIABBDOgoILhCxAxCDARBDOgoILhCxAxDUAhBDOgoILhDHARDRAxBDOgcILhCxAxBDOgoIABCxAxCDARBDOgQIABADOgoIABCABBAUEIcCOgsIABCABBCxAxCDAToKCC4Q1AIQsQMQQzoFCAAQkQI6BQguEJECSgQIQRgAUPQHWLA_YKtZaAFwAXgAgAHiAogBmCGSAQYyLTE1LjGYAQCgAQHIAQjAAQE&amp;sclient=gws-wiz-serp">Zack Snyder’s Justice League</a> was over 240 minutes long. Again, without domain knowledge I would have stuck to the values given to me by the box-plot, at the exclusion of some major data points.</p><h3>Conclusion</h3><p>In this blog I discussed three main points:</p><ul><li>the value of spending more time gathering, understanding, and cleaning data</li><li>the importance of domain knowledge in treating data</li><li>implementing statistical analyses in the context of the domain and business problem.</li></ul><p>I believe these steps helped me to come up with a practical and relevant report addressing the business problem.</p><p>And that’s it! Thank you for reading thus far, I will appreciate any feedback in the comments section. Once I have made some modifications to the notebooks, I will link them on this page for anyone who wants to play with the data.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=baef53703a" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>