<?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 MEHMETCAN ANGÜN on Medium]]></title>
        <description><![CDATA[Stories by MEHMETCAN ANGÜN on Medium]]></description>
        <link>https://medium.com/@Mehmtcnangn?source=rss-10fca026e62b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*m5aa06vZvRmLQEmcLHAhQA.jpeg</url>
            <title>Stories by MEHMETCAN ANGÜN on Medium</title>
            <link>https://medium.com/@Mehmtcnangn?source=rss-10fca026e62b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 16:25:29 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@Mehmtcnangn/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[From Raw Data to Insight: A Complete Data Engineering Project with Python, SQL Server, and Power BI]]></title>
            <link>https://medium.com/@Mehmtcnangn/from-raw-data-to-insight-a-complete-data-engineering-project-with-python-sql-server-and-power-bi-2fd5059b85f5?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/2fd5059b85f5</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Tue, 22 Apr 2025 17:36:57 GMT</pubDate>
            <atom:updated>2025-04-22T17:36:57.904Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/749/1*5Hn1-pDONfBaj5yxP0VQQg.gif" /></figure><h3>Introduction</h3><p>In the world of data engineering, real-time pipelines are often spotlighted — but batch ETL (Extract, Transform, Load) workflows are just as critical. In this project, I walk you through how I took raw e-commerce sales data, cleaned and structured it using Python and SQL Server, and finally visualized key insights with Power BI.</p><p>This article is ideal for aspiring data engineers who want to build an end-to-end project with practical tools.</p><h3>1. Project Goal and Scope</h3><p>⁍ Clean and prepare raw data for analysis</p><p>⁍ Create a data warehouse using dimensional modeling (star schema)</p><p>⁍ Build an ETL pipeline in Python</p><p>⁍ Visualize the data in Power BI</p><p>⁍ Deliver business-ready insights</p><h3>2. Technologies Used</h3><p>‣ <strong>Python</strong></p><p>‣ <strong>Microsoft SQL Server</strong></p><p>‣ <strong>Power BI</strong></p><p>‣ <strong>CSV file</strong> as data source</p><p>You can access the codes and dataset related to this project from <a href="https://github.com/Revealis/From-Raw-Data-to-Insight-A-Complete-Data-Engineering-Project-with-Python-SQL-Server-and-Power-BI">my github link.</a></p><h3>3.Project stages</h3><h4>Preparing SQL tables:</h4><p>Let’s log in to SSMS or the database interface you use and create a new query.Then let’s start writing our SQL code:</p><pre>USE master;<br>GO<br>IF EXISTS (SELECT name FROM sys.databases WHERE name = &#39;ECommerceAnalytics&#39;)<br>BEGIN<br>    ALTER DATABASE ECommerceAnalytics SET SINGLE_USER WITH ROLLBACK IMMEDIATE;<br>    DROP DATABASE ECommerceAnalytics;<br>END<br>GO<br>CREATE DATABASE ECommerceAnalytics;<br>GO</pre><p>This block:</p><p>◘ Connects to the master database.</p><p>◘ Checks if a database named <strong>ECommerceAnalytics</strong> already exists.</p><p>◘ If it does, it forcibly drops it (making it single-user and rolling back active transactions).</p><p>◘ Then it creates a new <strong>ECommerceAnalytics</strong> database from scratch.</p><pre>USE ECommerceAnalytics;<br>GO</pre><p>◘ This line switches the context to the newly created ECommerceAnalytics database to start creating tables.</p><pre>CREATE TABLE RawSalesData (<br>    InvoiceNo VARCHAR(20),<br>    StockCode VARCHAR(20),<br>    Description VARCHAR(255),<br>    Quantity INT,<br>    InvoiceDate DATETIME,<br>    UnitPrice DECIMAL(10, 2),<br>    CustomerID VARCHAR(20),<br>    Country VARCHAR(100),<br>    LoadDate DATETIME DEFAULT GETDATE()<br>);</pre><p><strong>Purpose:</strong> This is your staging table where you initially load the raw sales data from a CSV file.</p><p>▷ LoadDate captures when the data was inserted into this table.</p><p>▷ No constraints yet — this keeps it flexible for raw ingestion.</p><pre>CREATE TABLE CleanedSales (<br>    SalesID INT IDENTITY(1,1) PRIMARY KEY,<br>    InvoiceNo VARCHAR(20),<br>    StockCode VARCHAR(20),<br>    Description VARCHAR(255),<br>    Quantity INT,<br>    InvoiceDate DATETIME,<br>    UnitPrice DECIMAL(10, 2),<br>    CustomerID VARCHAR(20),<br>    Country VARCHAR(100),<br>    TotalAmount AS (Quantity * UnitPrice) PERSISTED,<br>    LoadDate DATETIME DEFAULT GETDATE()<br>);</pre><p><strong>Purpose:</strong> This table holds cleaned and validated data.</p><p>▷ SalesID: A unique primary key for each cleaned row.</p><p>▷ TotalAmount: A <strong>computed column</strong> (Quantity × UnitPrice) and <strong>persisted</strong> so it can be indexed and queried efficiently.</p><p>▷ Data here is derived from RawSalesData after cleaning logic is applied.</p><pre>CREATE TABLE DimCustomers (<br>    CustomerKey INT IDENTITY(1,1) PRIMARY KEY,<br>    CustomerID VARCHAR(20) UNIQUE,<br>    FirstPurchaseDate DATETIME,<br>    TotalOrders INT DEFAULT 0,<br>    TotalSpent DECIMAL(18, 2) DEFAULT 0<br>);</pre><p>This stores customer profiles:</p><p>❥ CustomerKey: Surrogate key used in the fact table.</p><p>❥ FirstPurchaseDate, TotalOrders, and TotalSpent will help in analytics like loyalty tracking and segmentation.</p><pre>CREATE TABLE DimProducts (<br>    ProductKey INT IDENTITY(1,1) PRIMARY KEY,<br>    StockCode VARCHAR(20) UNIQUE,<br>    Description VARCHAR(255),<br>    Category VARCHAR(100)<br>);</pre><p>Product-level info:</p><p>⇛ Includes Category for segmentation (e.g., Electronics, Clothing).</p><p>⇛ Will be used to analyze product performance.</p><pre>CREATE TABLE DimTime (<br>    TimeKey INT PRIMARY KEY,<br>    FullDate DATETIME,<br>    Day INT,<br>    Month INT,<br>    Year INT,<br>    Quarter INT,<br>    DayOfWeek INT,<br>    DayName VARCHAR(10),<br>    MonthName VARCHAR(10),<br>    IsWeekend BIT<br>);</pre><p>Essential for time-based analysis:</p><p>⇛ Each date gets a TimeKey, often derived from YYYYMMDD.</p><p>⇛ Enables quick grouping by Month, Quarter, DayName, etc.</p><pre>CREATE TABLE FactSales (<br>    SalesKey INT IDENTITY(1,1) PRIMARY KEY,<br>    TimeKey INT REFERENCES DimTime(TimeKey),<br>    CustomerKey INT REFERENCES DimCustomers(CustomerKey),<br>    ProductKey INT REFERENCES DimProducts(ProductKey),<br>    Quantity INT,<br>    UnitPrice DECIMAL(10, 2),<br>    TotalAmount DECIMAL(18, 2),<br>    Country VARCHAR(100)<br>);</pre><p><strong>Purpose:</strong> The main analytical table that stores measurable events — sales in this case.</p><p>➳ Links to dimension tables via foreign keys.</p><p>➳ Holds metrics like Quantity, TotalAmount, and Country (for geography-based filtering).</p><pre>CREATE OR ALTER PROCEDURE sp_CleanAndTransferData<br>AS<br>BEGIN<br>    INSERT INTO CleanedSales (<br>        InvoiceNo, StockCode, Description, <br>        Quantity, InvoiceDate, UnitPrice, <br>        CustomerID, Country<br>    )<br>    SELECT <br>        InvoiceNo, <br>        UPPER(TRIM(StockCode)) AS StockCode,<br>        LEFT(Description, 255) AS Description,<br>        Quantity, <br>        InvoiceDate, <br>        UnitPrice,<br>        CustomerID,<br>        Country<br>    FROM RawSalesData<br>    WHERE Quantity &gt; 0 AND UnitPrice &gt; 0 AND CustomerID IS NOT NULL;<br><br>    SELECT @@ROWCOUNT AS &#39;RowsAffected&#39;;<br>END;</pre><p><strong>Purpose:</strong> Automates the data cleaning process.</p><p>What it does:</p><p>➳ Removes rows with missing CustomerID, and rows with non-positive Quantity or Price.</p><p>➳ Cleans up the StockCode (trim and uppercase) and shortens long descriptions.</p><p>➳ Inserts cleaned data into the CleanedSales table.</p><p>➳ Finally, returns the number of rows affected.</p><p>You can check your tables from the interface:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/289/1*dCOHb9iEWYf4zNGH40B6Zw.png" /></figure><h4>Preparing Python Codes:</h4><pre>class ECommerceETL:<br>    def __init__(self, db_name):<br>        self.connection_string = (<br>            f&quot;Driver={{SQL Server}};&quot;<br>            f&quot;Server=.;&quot;<br>            f&quot;Database={db_name};&quot;<br>            f&quot;Trusted_Connection=yes;&quot;<br>        )</pre><p>✧ The ECommerceETL class manages the entire data processing workflow</p><p>✧ The __init__ method creates a SQL Server connection string</p><p>✧ Trusted_Connection=yes indicates Windows authentication is used</p><pre>def extract_from_csv(self, file_path):<br>    try:<br>        df = pd.read_csv(<br>            file_path,<br>            encoding=&#39;ISO-8859-1&#39;,<br>            dtype={<br>                &#39;InvoiceNo&#39;: &#39;str&#39;,<br>                &#39;StockCode&#39;: &#39;str&#39;,<br>                &#39;Description&#39;: &#39;str&#39;,<br>                &#39;CustomerID&#39;: &#39;str&#39;,<br>                &#39;Country&#39;: &#39;str&#39;<br>            },<br>            parse_dates=[&#39;InvoiceDate&#39;]<br>        )<br>        initial_count = len(df)<br>        df = df[df[&#39;CustomerID&#39;].notna()]<br>        print(f&quot;{initial_count - len(df)} rows filtered out&quot;)<br>        return df<br>    except Exception as e:<br>            print(f&quot;Error: {str(e)}&quot;)<br>            return None</pre><p>✧ Reads data from a CSV file</p><p>✧ Specifies data types for important columns (as strings)</p><p>✧ Converts InvoiceDate column to datetime format</p><p>✧ Filters out rows with missing CustomerID</p><p>✧ Prints how many rows were filtered</p><pre>def clean_data(self, df):<br>    df[&#39;Quantity&#39;] = pd.to_numeric(df[&#39;Quantity&#39;], errors=&#39;coerce&#39;).fillna(0).astype(&#39;int32&#39;)<br>    df[&#39;UnitPrice&#39;] = pd.to_numeric(df[&#39;UnitPrice&#39;], errors=&#39;coerce&#39;).fillna(0.0)<br>    df = df[(df[&#39;Quantity&#39;] &gt; 0) &amp; (df[&#39;UnitPrice&#39;] &gt; 0)]<br>    df[&#39;Description&#39;] = df[&#39;Description&#39;].str.slice(0, 255)<br>    df[&#39;StockCode&#39;] = df[&#39;StockCode&#39;].str.strip().str.upper()<br>    return df</pre><p>☸ Converts Quantity and UnitPrice to numeric, replacing invalid values with 0</p><p>☸ Keeps only rows with positive quantity and price</p><p>☸ Limits Description to 255 characters</p><p>☸ Cleans StockCode by stripping whitespace and converting to uppercase</p><pre>def load_to_sql(self, df, table_name, batch_size=1000):<br>    try:<br>        conn = pyodbc.connect(self.connection_string)<br>        cursor = conn.cursor()<br>        <br>        insert_sql = f&quot;&quot;&quot;<br>        INSERT INTO {table_name} (<br>            InvoiceNo, StockCode, Description, <br>            Quantity, InvoiceDate, UnitPrice, <br>            CustomerID, Country<br>        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)<br>        &quot;&quot;&quot;<br>        <br>        for i in range(0, len(df), batch_size):<br>            batch = df.iloc[i:i + batch_size]<br>            data = [tuple(row) for row in batch.to_records(index=False)]<br>            cursor.executemany(insert_sql, data)<br>            conn.commit()<br>        return True<br>    except Exception as e:<br>            print(f&quot;SQL Error: {str(e)}&quot;)<br>            return False</pre><p>☸ Loads data into the specified SQL table</p><p>☸ Processes data in batches of 1000 rows for performance</p><p>☸ Commits after each batch</p><p>☸ Returns True on success</p><pre>def execute_sql_procedure(self, procedure_name):<br>    try:<br>        conn = pyodbc.connect(self.connection_string)<br>        cursor = conn.cursor()<br>        cursor.execute(f&quot;EXEC {procedure_name}&quot;)<br>        result = cursor.fetchone()<br>        conn.commit()<br>        return result[0] if result else 0<br>    except Exception as e:<br>            print(f&quot;Procedure Error: {str(e)}&quot;)<br>            return None</pre><p>◈ Executes a stored procedure in the database</p><p>◈ Retrieves the first value returned (typically affected row count)</p><p>◈ Returns the result</p><pre>def populate_dimensions(self):<br>    try:<br>        conn = pyodbc.connect(self.connection_string)<br>        cursor = conn.cursor()<br>        <br>        # Customers dimension<br>        cursor.execute(&quot;&quot;&quot;<br>        INSERT INTO DimCustomers (CustomerID, FirstPurchaseDate, TotalOrders, TotalSpent)<br>        SELECT <br>            CustomerID,<br>            MIN(InvoiceDate),<br>            COUNT(DISTINCT InvoiceNo),<br>            SUM(Quantity * UnitPrice)<br>        FROM CleanedSales<br>        GROUP BY CustomerID<br>        &quot;&quot;&quot;)<br>        <br>        # Products dimension (using MERGE)<br>        cursor.execute(&quot;&quot;&quot;<br>        MERGE INTO DimProducts AS target<br>        USING (<br>            SELECT <br>                StockCode,<br>                MAX(Description) AS Description,<br>                MAX(LEFT(Description, CHARINDEX(&#39; &#39;, Description + &#39; &#39;) - 1)) AS Category<br>            FROM CleanedSales<br>            GROUP BY StockCode<br>        ) AS source<br>        ON (target.StockCode = source.StockCode)<br>        WHEN NOT MATCHED THEN<br>            INSERT (StockCode, Description, Category)<br>            VALUES (source.StockCode, source.Description, source.Category);<br>        &quot;&quot;&quot;)<br>        <br>        # Time dimension<br>        cursor.execute(&quot;&quot;&quot;<br>        INSERT INTO DimTime (TimeKey, FullDate, Day, Month, Year, <br>                          Quarter, DayOfWeek, DayName, MonthName, IsWeekend)<br>        SELECT DISTINCT<br>            CONVERT(INT, CONVERT(VARCHAR, CAST(InvoiceDate AS DATE), 112)),<br>            CAST(InvoiceDate AS DATE),<br>            DAY(InvoiceDate),<br>            MONTH(InvoiceDate),<br>            YEAR(InvoiceDate),<br>            DATEPART(QUARTER, InvoiceDate),<br>            DATEPART(WEEKDAY, InvoiceDate),<br>            DATENAME(WEEKDAY, InvoiceDate),<br>            DATENAME(MONTH, InvoiceDate),<br>            CASE WHEN DATEPART(WEEKDAY, InvoiceDate) IN (1, 7) THEN 1 ELSE 0 END<br>        FROM CleanedSales<br>        WHERE NOT EXISTS (<br>            SELECT 1 FROM DimTime <br>            WHERE TimeKey = CONVERT(INT, CONVERT(VARCHAR, CAST(InvoiceDate AS DATE), 112))<br>        &quot;&quot;&quot;)<br>        conn.commit()<br>        return True<br>    except Exception as e:<br>            print(f&quot;Size Error: {str(e)}&quot;)<br>            return False</pre><p>Populates three core dimension tables:</p><ol><li>DimCustomers: Customer information</li><li>DimProducts: Product information (using MERGE)</li><li>DimTime: Time dimension attributes</li></ol><pre>def populate_facts(self):<br>    try:<br>        conn = pyodbc.connect(self.connection_string)<br>        cursor = conn.cursor()<br>        <br>        cursor.execute(&quot;&quot;&quot;<br>        INSERT INTO FactSales (TimeKey, CustomerKey, ProductKey, <br>                             Quantity, UnitPrice, TotalAmount, Country)<br>        SELECT <br>            CONVERT(INT, CONVERT(VARCHAR, CAST(cs.InvoiceDate AS DATE), 112)),<br>            dc.CustomerKey,<br>            dp.ProductKey,<br>            cs.Quantity,<br>            cs.UnitPrice,<br>            cs.Quantity * cs.UnitPrice,<br>            cs.Country<br>        FROM CleanedSales cs<br>        JOIN DimCustomers dc ON cs.CustomerID = dc.CustomerID<br>        JOIN DimProducts dp ON cs.StockCode = dp.StockCode<br>        &quot;&quot;&quot;)<br>    except Exception as e:<br>            print(f&quot;Fact Error: {str(e)}&quot;)<br>            return False</pre><p>◈ Populates the FactSales table with transaction facts</p><p>◈ Joins with dimension tables to get surrogate keys</p><pre>def run_full_pipeline(self, csv_path):<br>    start_time = time.time()<br>    <br>    # 1. Extract<br>    raw_data = self.extract_from_csv(csv_path)<br>    if raw_data is None: return False<br>    <br>    # 2. Clean<br>    cleaned_data = self.clean_data(raw_data)<br>    <br>    # 3. Load<br>    if not self.load_to_sql(cleaned_data, &quot;RawSalesData&quot;): return False<br>    <br>    # 4. Transform<br>    cleaned_rows = self.execute_sql_procedure(&quot;sp_CleanAndTransferData&quot;)<br>    if not cleaned_rows: return False<br>    <br>    # 5. Dimensions<br>    if not self.populate_dimensions(): return False<br>    <br>    # 6. Facts<br>    if not self.populate_facts(): return False<br>    <br>    duration = time.time() - start_time<br>    print(f&quot;ETL completed. Duration: {duration:.2f} seconds&quot;)<br>    return True</pre><p>◈ Coordinates the entire ETL process</p><p>◈ Executes steps sequentially</p><p>◈ Checks success status of each step</p><p>◈ Calculates and prints total processing time</p><pre>if __name__ == &quot;__main__&quot;:<br>    etl = ECommerceETL(&quot;ECommerceAnalytics&quot;)<br>    success = etl.run_full_pipeline(&quot;data.csv&quot;)<br>    print(&quot;Success!&quot; if success else &quot;Error occurred!&quot;)</pre><p>◍ Creates class instance (connects to “ECommerceAnalytics” database)</p><p>◍ Processes data from “data.csv” file</p><p>◍ Reports final status to user</p><p>When you run the above Python code, if there is no problem, your tables will start filling automatically.You can then go in and check. For example:</p><pre>SELECT <br>    (SELECT COUNT(*) FROM RawSalesData) AS RawCount,<br>    (SELECT COUNT(*) FROM CleanedSales) AS CleanedCount,<br>    (SELECT COUNT(*) FROM DimCustomers) AS CustomerCount,<br>    (SELECT COUNT(*) FROM DimProducts) AS ProductCount,<br>    (SELECT COUNT(*) FROM FactSales) AS FactCount;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/528/1*TQms_h_olSPmuCUG4FvNuA.png" /></figure><p>or you can check your data integrity:</p><pre>SELECT COUNT(*) AS MissingCustomerLinks<br>FROM FactSales fs<br>LEFT JOIN DimCustomers dc ON fs.CustomerKey = dc.CustomerKey<br>WHERE dc.CustomerKey IS NULL;<br><br>SELECT COUNT(*) AS MissingProductLinks<br>FROM FactSales fs<br>LEFT JOIN DimProducts dp ON fs.ProductKey = dp.ProductKey<br>WHERE dp.ProductKey IS NULL;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/330/1*J7IRHLXDg5AtB7AwXf55IQ.png" /></figure><p>Let’s use query to analyze:</p><pre>SELECT TOP 10 <br>    dp.Description, <br>    SUM(fs.Quantity) AS TotalQuantity,<br>    SUM(fs.TotalAmount) AS TotalRevenue<br>FROM FactSales fs<br>JOIN DimProducts dp ON fs.ProductKey = dp.ProductKey<br>GROUP BY dp.Description<br>ORDER BY TotalRevenue DESC;<br><br>SELECT <br>    dt.Year,<br>    dt.MonthName,<br>    SUM(fs.TotalAmount) AS MonthlySales,<br>    COUNT(DISTINCT fs.CustomerKey) AS UniqueCustomers<br>FROM FactSales fs<br>JOIN DimTime dt ON fs.TimeKey = dt.TimeKey<br>GROUP BY dt.Year, dt.MonthName<br>ORDER BY dt.Year, MIN(dt.Month);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/861/1*v1RMIQVfsZcuWBDBLRxy7w.png" /></figure><h4>Data Visualization:</h4><p>In this section we will create some tables using power BI.We have examined the Monthly Sales Trends in the following output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4so2GdD0-w6efIqP33t8vA.png" /></figure><p>Best Selling Products:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1004/1*xLm-U_hX_6370liwMY5BTQ.png" /></figure><p>Sales Distribution by Country:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CvIMouhfxIETFhqBjG96uw.png" /></figure><p>Customer Segmentation:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FmeppWZGkqOC1WRcWNvLxw.png" /></figure><p>Number of Daily Orders:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DZiUfmR32qk03ozvykWkTA.png" /></figure><h4>Conclusion</h4><p>In this article, we built an end-to-end ETL process on an e-commerce dataset. We extracted data with Python, cleaned it, loaded it into SQL Server, and then created our dimension and fact tables to make it analyzable.</p><p>This project provides a very educational example, especially for those who are new to the field of data engineering. It can be an ideal starting point to develop both your Python and SQL skills, work with real-world data, and build your own data warehouse.</p><p>ETL processes are one of the cornerstones of the data world. You can adapt this example to your own projects and build more advanced pipelines and automation systems. Remember, every line of code makes you a better data engineer.</p><p>Thank you for reading this article. I look forward to your feedback!</p><p>Have a good day and happy coding!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*WTRcCLZVBg9Te9WW0aEUBA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2fd5059b85f5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Queries on MSSQL Northwind Database: Step-by-Step Analysis - Part 1]]></title>
            <link>https://medium.com/@Mehmtcnangn/sql-queries-on-mssql-northwind-database-step-by-step-analysis-part-1-a66952790b01?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/a66952790b01</guid>
            <category><![CDATA[practice]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[microsoft]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Wed, 02 Apr 2025 09:46:34 GMT</pubDate>
            <atom:updated>2025-04-02T09:46:34.809Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/371/1*xflg--un29CbGmwohR1ItA.png" /></figure><p>The Northwind database is a great resource for those who want to practice SQL. In this article, we will explore some basic and advanced queries performed on the Northwind database. We will explain the purpose of each query and detail its applications in SQL.</p><p>First, let’s get our tables in script format from <a href="https://github.com/Revealis/MSSQL-TECHCAREER-PROJECT-PRESENTATION/blob/main/northwind.sql">this github link</a> and add them to our nortwind database.</p><p>Then, let’s run SQL Server Management Studio 20 and create a new database named northwind. (This depends on the person, you can also use your existing database.)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/385/1*sAmjXmAaxAYyf6ou-4WONw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2tNgA8RpkC3j6jMKW6eu7g.png" /></figure><p>When you enter the name for the new database and press the OK button, you will see your database in place number 3 (If you cannot see it, try reflesh.)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/680/1*PmRdNrSolG54pCLT9WVFZQ.png" /></figure><p>You can view our tables as above.</p><p>Next, we need to add the tables we got from github. Let’s click on the New Query option and then run the script in the file we downloaded.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*3TcskT8d9GqSf6qG-wPRog.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9k-QppHfh4239gRT-_V83g.png" /></figure><p>Let’s start our exercises by creating a new query or using this query if you wish.</p><h3>1. Number of Tables in the Northwind Database and Their Names</h3><p>First, let’s determine the total number of tables in the database and list their names:</p><pre>SELECT COUNT(*) AS &quot;Table Count&quot; FROM INFORMATION_SCHEMA.TABLES;<br>SELECT TABLE_NAME as &quot;Table Names&quot; FROM INFORMATION_SCHEMA.TABLES;</pre><p>These queries help us find the total number of tables and the names of the tables in the Northwind database. I’m leaving the output below.Since I have worked on this script before, the number of tables may be different for you. Don’t worry, just try to understand the logic of the code.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DenvpNxCtvQUFMwHF3UzTg.png" /></figure><h3>2. Listing Order Information</h3><p>To retrieve the company name, employee name, order date, and shipping company for each order, we can use the following query:</p><pre>SELECT o.OrderID, c.CompanyName  AS &quot;Company Name&quot;, <br>       e.FirstName + &#39; &#39; + e.LastName AS &quot;Worker Name-Surname&quot;, <br>       o.OrderDate, s.CompanyName AS &quot;Supplier&quot;<br>FROM Orders o<br>JOIN Customers c ON o.CustomerID = c.CustomerID<br>JOIN Employees e ON o.EmployeeID = e.EmployeeID<br>JOIN Shippers s ON o.ShipVia = s.ShipperID;</pre><p>This query associates orders with customers, employees, and shipping companies to provide detailed information.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d7TNITKGNzdbVa2WhYOMrg.png" /></figure><h3>3. Total Order Amount</h3><p>To calculate the total order amount, we can use the following query:</p><pre>SELECT SUM(od.Quantity * od.UnitPrice) AS &quot;Total Order&quot; <br>FROM [Order Details] od;</pre><p>This query multiplies the quantity of each ordered product by its unit price to calculate the total order value.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/252/1*S3PEbrabCK4bHpcD1Te_Lg.png" /></figure><h3>4. Number of Customers by Country</h3><p>To determine the number of customers from each country, we can run this query:</p><pre>SELECT Country, COUNT(*) AS &quot;Customer Count&quot;<br>FROM Customers <br>GROUP BY Country;</pre><p>This query groups customer records by country and returns the number of customers in each group.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/642/1*6g_Movj5ZhERvJuPkODKYg.png" /></figure><h3>5. Most Expensive Product</h3><p>To find the name and price of the most expensive product, we can use the following queries:</p><pre>SELECT TOP 1 ProductName, UnitPrice <br>FROM Products <br>ORDER BY UnitPrice DESC;</pre><p>Alternatively:</p><pre>SELECT ProductName AS &quot;Product Name&quot;, MAX(UnitPrice) AS &quot;Unit Price&quot; FROM Products<br>GROUP BY ProductName<br>ORDER BY MAX(UnitPrice) DESC<br>OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;</pre><p>These queries list the most expensive product by sorting products based on their prices.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/831/1*cTH4bGgq4aoUjLfBS1k-xg.png" /></figure><h3>6. Number of Orders per Employee</h3><p>To find out how many orders each employee has handled, we can use the following query:</p><pre>SELECT e.FirstName + &#39; &#39; + e.LastName AS &quot;Employee Name-Surname&quot;, COUNT(o.OrderID) AS &quot;Order Count&quot;<br>FROM Employees e<br>LEFT JOIN Orders o ON e.EmployeeID = o.EmployeeID<br>GROUP BY e.FirstName, e.LastName;</pre><p>This query associates employees with orders, helping us analyze their workload.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/676/1*2wz1m9q4cQoGQNZZdhkNzA.png" /></figure><h3>7. Orders Placed in 1997</h3><p>To list orders placed in the year 1997, we can use the following query:</p><pre>SELECT * FROM Orders WHERE YEAR(OrderDate) = 1997;</pre><p>Alternatively:</p><pre>SELECT * FROM Orders WHERE OrderDate between &#39;1997-01-01&#39; and &#39;1997-12-31&#39;;</pre><p>These queries help us retrieve orders placed within a specific year.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Avm3Z-0NXdDHKqkZ9Fh7jA.png" /></figure><h3>8. Categorizing Products Based on Price Ranges</h3><p>To categorize products based on their price ranges, we can use the following query:</p><pre>SELECT ProductName as &quot;Product Name&quot;, UnitPrice as &quot;Unit Price&quot;, <br>       CASE <br>           WHEN UnitPrice &lt; 20 THEN &#39;Cheap&#39;<br>           WHEN UnitPrice BETWEEN 20 AND 50 THEN &#39;Medium&#39;<br>           ELSE &#39;Expensive&#39;<br>       END AS &quot;Category&quot;<br>FROM Products;</pre><p>This query divides products into three categories: “Cheap,” “Medium,” and “Expensive.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AnSNiP_mKAgTI1kS0tclWA.png" /></figure><h3>9. Most Ordered Product</h3><p>To find the most frequently ordered product and its total quantity, we can use the following query:</p><pre>SELECT TOP 1 p.ProductName as &quot;Product Name&quot;, SUM(od.Quantity) AS &quot;Total Quantity&quot;<br>FROM [Order Details] od<br>JOIN Products p ON od.ProductID = p.ProductID<br>GROUP BY p.ProductName<br>ORDER BY &quot;Total Quantity&quot; DESC;</pre><p>Alternatively:</p><pre>SELECT p.ProductName as &quot;Product Name&quot;, SUM(od.Quantity) AS &quot;Total Quantity&quot;<br>FROM [Order Details] od<br>JOIN Products p ON od.ProductID = p.ProductID<br>GROUP BY p.ProductName<br>ORDER BY &quot;Total Quantity&quot; DESC<br>OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;</pre><p>These queries use data from the order details table to identify the most frequently ordered product.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*15AOYL6LrmRlrab_MU9Cjg.png" /></figure><p>In this article, we created a new database and examined the basic SQL queries in the Northwind database step by step. These types of queries are very useful for anyone who wants to practice SQL. I will share our subsequent exercises as Part-2.</p><p>I hope it will be useful for you. I am waiting for your feedback. Have a nice day and happy coding!</p><p>Source: <a href="https://github.com/hamitmizrak/Bootcamp_2_Mssql">https://github.com/hamitmizrak/Bootcamp_2_Mssql</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*ofCMnVftWJBWS7vRQKvhhA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a66952790b01" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Earthquake Data Analysis and Prediction Using Machine Learning]]></title>
            <link>https://medium.com/@Mehmtcnangn/earthquake-data-analysis-and-prediction-using-machine-learning-d32c12c59d35?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/d32c12c59d35</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[earthquake]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Sat, 08 Mar 2025 07:53:47 GMT</pubDate>
            <atom:updated>2025-03-08T07:53:47.696Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lqEM1KrSnSwjxkaRyUpzYw.jpeg" /></figure><h3>Introduction</h3><p>Earthquakes are natural disasters that cause significant destruction and loss of life. By analyzing past earthquake data, we can gain insights into patterns and attempt to predict future occurrences. In this blog, we will explore earthquake data, visualize it using maps and graphs, and implement machine learning models for prediction.</p><p>This project includes:</p><p>🔸 Data preprocessing and visualization</p><p>🔸 Heatmap representation of earthquake locations</p><p>🔸 Machine learning models (Random Forest, XGBoost, and LSTM) for magnitude prediction</p><h4>What is the Folium Library and How to Use It?</h4><p>Folium is a Python library that allows users to create interactive maps. It is based on Leaflet.js, making it easy to generate visually appealing and user-friendly maps. It is particularly useful for analyzing and visualizing geographic data.</p><h3>Use Cases of Folium</h3><p>Folium is mainly used in projects that require geographic location data to be displayed on a map. Some key use cases include:</p><p>🔹 <strong>Displaying city or region-based data</strong>: Specific locations can be marked using markers, making it easier to visualize events occurring in particular places. For example, earthquake data across major cities can be represented with markers.</p><p>🔹 <strong>Creating heatmaps</strong>: Heatmaps help visualize the intensity of data points within a dataset. They are useful for identifying earthquake-prone areas or analyzing traffic patterns.</p><p>🔹 <strong>Visualizing multiple data layers</strong>: Different data layers can be displayed on the same map. For instance, earthquakes of different magnitudes can be separated into layers, allowing users to toggle them on and off for better insights.</p><h3>Key Features of Folium</h3><p>When using Folium to create maps, some key features include:</p><ol><li><strong>Map creation</strong>: Folium’s primary function is to generate a map centered at a specific coordinate (latitude and longitude).</li><li><strong>Adding markers</strong>: Specific points of interest can be highlighted on the map using markers.</li><li><strong>Generating heatmaps</strong>: Heatmaps can be used to visualize the density of location-based data, such as earthquake frequency in a particular region.</li><li><strong>Layer management</strong>: Multiple layers can be added to differentiate between various data categories, such as earthquake magnitudes. Users can toggle these layers to explore the data more effectively.</li></ol><h3>Dataset</h3><p>We use an earthquake dataset stored in veriler.csv, which contains attributes such as:</p><p>◎ Olus tarihi (Date of occurrence)</p><p>◎ Enlem (Latitude)</p><p>◎ Boylam (Longitude)</p><p>◎ ML (Local Magnitude)</p><p>◎ Mw (Moment Magnitude Scale)</p><p>◎ Ms (Surface Wave Magnitude)</p><p>◎ Mb (Body Wave Magnitude)</p><p>◎ Der(km) (Depth in kilometers)</p><p>This dataset contains earthquakes that occurred in Türkiye. You can find the dataset in <a href="https://github.com/Revealis/Earthquake-Data-Analysis-and-Prediction-Using-Machine-Learning/tree/main">my github link.</a></p><h3>Data Preprocessing</h3><p>Before applying any analysis or machine learning models, we need to preprocess the data to ensure it is clean and structured correctly.</p><h4>Loading the Dataset:</h4><pre>import pandas as pd<br><br>file_path = &quot;veriler.csv&quot;<br>df = pd.read_csv(file_path)</pre><h4>Converting Date Column to Datetime Format:</h4><p>The Olus tarihi column represents the date when an earthquake occurred. To perform time-based analysis, we need to convert this column into a proper datetime format.</p><pre>df[&#39;Olus tarihi&#39;] = pd.to_datetime(df[&#39;Olus tarihi&#39;], format=&#39;%Y.%m.%d&#39;)</pre><h4>Handling Missing Values:</h4><p>Missing values can affect our analysis and machine learning models. We first check for any missing values and decide how to handle them.</p><pre>print(df.isnull().sum())</pre><p>If there are missing values, we can either drop them or fill them with appropriate values. For this project, we drop rows with missing values to ensure model accuracy.</p><pre>df.dropna(inplace=True)</pre><h4>Feature Selection:</h4><p>Since we are predicting earthquake magnitude (ML), we need to select relevant features. We extract important numerical features for machine learning.</p><pre>df_ml = df[[&#39;Der(km)&#39;, &#39;Enlem&#39;, &#39;Boylam&#39;, &#39;ML&#39;, &#39;Mw&#39;, &#39;Ms&#39;, &#39;Mb&#39;]]</pre><p>We remove any remaining missing values after selection:</p><pre>df_ml.dropna(inplace=True)</pre><p>Now that our data is cleaned and structured, we can proceed to visualization and machine learning modeling.</p><h3>Visualization</h3><h4>Earthquake Frequency by Year:</h4><p>Let’s create a column chart using basic python chart plotting codes.</p><pre>import matplotlib.pyplot as plt<br><br>plt.figure(figsize=(12,6))<br>df[&#39;Olus tarihi&#39;].dt.year.value_counts().sort_index().plot(kind=&#39;bar&#39;, color=&#39;b&#39;)<br>plt.xlabel(&#39;Year&#39;)<br>plt.ylabel(&#39;Number of Earthquakes&#39;)<br>plt.title(&#39;Earthquake Count by Year&#39;)<br>plt.show()</pre><p>Our output will be like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gyvcmLTW7hPEteKYn4bLJQ.png" /></figure><h4>Earthquake Heatmap:</h4><pre>import folium<br>from folium.plugins import HeatMap<br><br>m = folium.Map(location=[df[&#39;Enlem&#39;].mean(), df[&#39;Boylam&#39;].mean()], zoom_start=6)<br><br>bins = {<br>    &quot;4-4.9&quot;: df[(df[&#39;ML&#39;] &gt;= 4.0) &amp; (df[&#39;ML&#39;] &lt; 5.0)],<br>    &quot;5-5.9&quot;: df[(df[&#39;ML&#39;] &gt;= 5.0) &amp; (df[&#39;ML&#39;] &lt; 6.0)],<br>    &quot;6-6.9&quot;: df[(df[&#39;ML&#39;] &gt;= 6.0) &amp; (df[&#39;ML&#39;] &lt; 7.0)],<br>    &quot;7+&quot;: df[df[&#39;ML&#39;] &gt;= 7.0]<br>}<br><br>for label, data in bins.items():<br>    heat_data = list(zip(data[&#39;Enlem&#39;], data[&#39;Boylam&#39;], data[&#39;ML&#39;]))<br>    HeatMap(heat_data, name=label).add_to(m)<br><br>folium.LayerControl().add_to(m)<br><br>m.save(&quot;earthquake_map.html&quot;)</pre><p>🟣 <strong>folium</strong>: This library is used to create interactive maps.</p><p>🟣 <strong>HeatMap from folium.plugins</strong>: This module helps generate heatmaps, which visualize the density of earthquake occurrences.</p><p>🟣 folium.Map() creates a map centered around the <strong>mean latitude (</strong><strong>Enlem) and longitude (</strong><strong>Boylam)</strong> of all earthquake data points.</p><p>🟣 The zoom_start=6 parameter sets the initial zoom level of the map.</p><p>🟣 The code <strong>categorizes earthquake data</strong> into four groups based on magnitude (ML):</p><p>‣ <strong>4.0–4.9</strong></p><p>‣ <strong>5.0–5.9</strong></p><p>‣ <strong>6.0–6.9</strong></p><p>‣ <strong>7.0+</strong> (Strongest earthquakes)</p><p>This classification helps to visualize different intensity levels separately.</p><p>🟣 The loop iterates through each <strong>magnitude range</strong> (bins dictionary).</p><p>🟣 The zip() function combines <strong>latitude (</strong><strong>Enlem), longitude (</strong><strong>Boylam), and magnitude (</strong><strong>ML)</strong> into a list.</p><p>🟣 HeatMap(heat_data, name=label).add_to(m) creates a heatmap for each magnitude range and adds it to the map.</p><p>🟣 LayerControl()enables a <strong>layer toggle control</strong> on the map, allowing users to switch between different earthquake magnitude layers.</p><p>🟣 The final interactive map is saved as earthquake_map.html.</p><p>🟣 This file can be opened in any web browser to explore the earthquake heatmap.</p><p>ℹ️ If you have difficulty viewing your map, you can copy it to your desktop or right click &gt; open &gt; browser &gt; select the browser you are using in the editor you are using.</p><p>Our map output will look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9vkk0FbQ9--UT8fQXKqyzg.png" /></figure><p>ℹ️ If we want to filter our map, we can use the filter marked in the upper right corner.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v57Jxqp5E6SJa1borVdN2g.png" /></figure><h3>Machine Learning for Earthquake Magnitude Prediction</h3><h4>Data Preparation:</h4><p>In this section, we will compare the performance of several machine learning models.</p><pre>from sklearn.model_selection import train_test_split<br>from sklearn.preprocessing import MinMaxScaler<br><br>X = df_ml.drop(columns=[&#39;ML&#39;])<br>y = df_ml[&#39;ML&#39;]<br><br>X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)</pre><p>We selected our features for use in machine learning and split into training and testing sets.</p><h4>Random Forest Model</h4><pre>from sklearn.ensemble import RandomForestRegressor<br>from sklearn.metrics import mean_absolute_error, mean_squared_error<br>import numpy as np<br><br>rf_model = RandomForestRegressor(n_estimators=100, random_state=42)<br>rf_model.fit(X_train, y_train)<br>y_pred_rf = rf_model.predict(X_test)<br><br>mae_rf = mean_absolute_error(y_test, y_pred_rf)<br>rmse_rf = np.sqrt(mean_squared_error(y_test, y_pred_rf))<br>print(f&quot;Random Forest - MAE: {mae_rf}, RMSE: {rmse_rf}&quot;)</pre><p>◈ <strong>RandomForestRegressor</strong> is an ensemble learning method that builds multiple decision trees and averages their predictions for better accuracy.</p><p>◈ n_estimators=100: The model creates 100 decision trees.</p><p>◈ The model is trained using fit(X_train, y_train).</p><p>◈ Predictions are made using predict(X_test), and errors are measured with <strong>Mean Absolute Error (MAE)</strong> and <strong>Root Mean Squared Error (RMSE)</strong>.</p><h4>XGBoost Regressor</h4><pre>import xgboost as xgb<br><br>xgb_model = xgb.XGBRegressor(objective=&#39;reg:squarederror&#39;, n_estimators=100, random_state=42)<br>xgb_model.fit(X_train, y_train)<br>y_pred_xgb = xgb_model.predict(X_test)<br><br>mae_xgb = mean_absolute_error(y_test, y_pred_xgb)<br>rmse_xgb = np.sqrt(mean_squared_error(y_test, y_pred_xgb))<br>print(f&quot;XGBoost - MAE: {mae_xgb}, RMSE: {rmse_xgb}&quot;)</pre><p>➾ <strong>XGBoost (Extreme Gradient Boosting)</strong> is a powerful boosting algorithm that improves prediction accuracy by sequentially training trees while correcting previous errors.</p><p>➾ objective=&#39;reg:squarederror&#39; tells XGBoost to use regression.</p><p>➾ It builds 100 trees and predicts values, similar to Random Forest.</p><p>➾ The model performance is measured using <strong>MAE</strong> and <strong>RMSE</strong>.</p><h4>LSTM (Long Short-Term Memory)</h4><pre>from tensorflow.keras.models import Sequential<br>from tensorflow.keras.layers import LSTM, Dense, Dropout<br><br>scaler = MinMaxScaler()<br>X_scaled = scaler.fit_transform(X)<br>y_scaled = scaler.fit_transform(y.values.reshape(-1, 1))<br><br>X_train_lstm, X_test_lstm, y_train_lstm, y_test_lstm = train_test_split(X_scaled, y_scaled, test_size=0.2, random_state=42)<br>X_train_lstm = X_train_lstm.reshape((X_train_lstm.shape[0], X_train_lstm.shape[1], 1))<br>X_test_lstm = X_test_lstm.reshape((X_test_lstm.shape[0], X_test_lstm.shape[1], 1))<br><br>lstm_model = Sequential([<br>    LSTM(50, return_sequences=True, input_shape=(X_train_lstm.shape[1], 1)),<br>    Dropout(0.2),<br>    LSTM(50, return_sequences=False),<br>    Dropout(0.2),<br>    Dense(25),<br>    Dense(1)<br>])<br><br>lstm_model.compile(optimizer=&#39;adam&#39;, loss=&#39;mean_squared_error&#39;)<br>lstm_model.fit(X_train_lstm, y_train_lstm, epochs=50, batch_size=16, verbose=1)<br>y_pred_lstm = lstm_model.predict(X_test_lstm)<br><br>mae_lstm = mean_absolute_error(y_test_lstm, y_pred_lstm)<br>rmse_lstm = np.sqrt(mean_squared_error(y_test_lstm, y_pred_lstm))<br>print(f&quot;LSTM - MAE: {mae_lstm}, RMSE: {rmse_lstm}&quot;)</pre><p>❖ <strong>LSTM (Long Short-Term Memory)</strong> is a type of recurrent neural network (RNN) that is great for time-series forecasting.</p><p>❖ <strong>Data Scaling:</strong> The input data is normalized using MinMaxScaler() to improve model performance.</p><p>❖ <strong>Reshaping Data:</strong> The input is reshaped into a 3D format (samples, time steps, features) required by LSTMs.</p><p><strong>Building the Model:</strong></p><p>❖ First <strong>LSTM layer</strong> with 50 units and return_sequences=True (keeps sequence information for the next layer).</p><p>❖ <strong>Dropout layers</strong> to reduce overfitting.</p><p>❖ Second <strong>LSTM layer</strong> with 50 units.</p><p>❖ <strong>Dense layers</strong> to refine output prediction.</p><p><strong>Compilation &amp; Training:</strong></p><p>❖ The model is compiled using <strong>Adam optimizer</strong> and <strong>mean squared error loss</strong>.</p><p>❖ Trained for <strong>50 epochs</strong> with a batch size of <strong>16</strong>.</p><h3>Conclusion</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oUgM8p1T4NkjpekjyNbC0g.png" /></figure><p>In this project, we analyzed earthquake data, visualized it using maps and graphs, and applied machine learning models to predict earthquake magnitudes. The performance of our models was evaluated using <strong>Mean Absolute Error (MAE)</strong> and <strong>Root Mean Squared Error (RMSE)</strong>, allowing us to compare their predictive accuracy.</p><p>Our results show that:</p><p>☆ <strong>Random Forest</strong> achieved an MAE of <strong>0.1508</strong> and an RMSE of <strong>0.2832</strong>, indicating a stable but slightly less precise model.</p><p>☆ <strong>XGBoost</strong> performed similarly with an MAE of <strong>0.1523</strong> and an RMSE of <strong>0.2862</strong>, leveraging boosting techniques for better optimization.</p><p>☆ <strong>LSTM</strong> significantly outperformed both models, achieving an MAE of <strong>0.0199</strong> and an RMSE of <strong>0.0346</strong>, demonstrating its ability to capture complex patterns in earthquake data.</p><p>The <strong>LSTM model’s superior performance</strong> highlights the strength of deep learning in time-series prediction tasks, as it effectively captures dependencies in sequential data. However, LSTMs require larger datasets and longer training times compared to traditional machine learning models like Random Forest and XGBoost.</p><p>These findings suggest that while Random Forest and XGBoost offer good baseline performance with easier interpretability, deep learning models like LSTM provide the best accuracy for earthquake magnitude prediction. Future improvements could involve <strong>hyperparameter tuning</strong>, <strong>feature engineering</strong>, and <strong>the inclusion of external factors</strong> (e.g., seismic activity trends or environmental conditions) to further enhance prediction accuracy.</p><p>I hope this project was useful for you.I’m waiting for your feedback. Have a nice day and happy coding!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*WTRcCLZVBg9Te9WW0aEUBA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d32c12c59d35" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cracking the Turkish Caesar Cipher with Python and Tkinter]]></title>
            <link>https://medium.com/@Mehmtcnangn/cracking-the-turkish-caesar-cipher-with-python-and-tkinter-39ab3f3020ab?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/39ab3f3020ab</guid>
            <category><![CDATA[python-programming]]></category>
            <category><![CDATA[caesar-cipher]]></category>
            <category><![CDATA[encryption]]></category>
            <category><![CDATA[algorithms]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Thu, 06 Feb 2025 13:55:53 GMT</pubDate>
            <atom:updated>2025-02-06T13:58:36.103Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/220/1*dWLuxgraiEObBmnbiYbtug.jpeg" /></figure><h3>Introduction</h3><p>The <strong>Caesar cipher</strong> is one of the oldest and simplest encryption techniques, where each letter in the plaintext is shifted by a fixed number of places in the alphabet. While this method is easy to implement, it is also easy to break, making it a great example for learning about encryption and decryption.</p><p>In this article, we will build a <strong>Turkish Caesar Cipher Decryptor</strong> using <strong>Python and Tkinter</strong>. This tool will allow users to enter encrypted text and see all possible shift values, making it easier to identify the correct decryption.</p><h4>What Is Caesar Cipher?</h4><p>The Caesar cipher is one of the oldest and simplest encryption methods. It is named after the Roman Emperor Julius Caesar who used it around 58 BC. This encryption method encrypts each letter in the text by shifting it a certain number of times.</p><h4>How Does It Work?</h4><p>The basic principle of the Caesar cipher is to shift each letter of the alphabet forward by one number. For example, when a shift amount is chosen (say 3), each letter is moved forward by 3 positions. This requires the same shift amount to be applied in reverse to decipher the ciphertext.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VWIQwEMkSvF-Hest35hzKA.png" /></figure><h3>Understanding the Turkish Caesar Cipher</h3><p>The classic Caesar cipher is designed for the English alphabet, but in Turkish, we have additional characters such as <strong>Ç, Ğ, İ, Ö, Ş, Ü</strong>. To correctly decrypt messages, we must adapt the cipher to work with the <strong>Turkish alphabet</strong>:</p><pre>ABCÇDEFGĞHIİJKLMNOÖPRSŞTUÜVYZ</pre><p>Since the Caesar cipher shifts characters, we need to make sure our implementation supports the <strong>full Turkish alphabet</strong> and handles uppercase and lowercase letters properly.</p><p>You can find the source code of the project from my <a href="https://github.com/Revealis">github link.</a></p><h3>Setting Up the Project</h3><p>We will use <strong>Tkinter</strong>, Python’s built-in GUI library, to create an easy-to-use interface for decrypting messages. The application will:</p><ol><li>Ask the user for an encrypted text.</li><li>Try all possible shift values (except shift 0, which would return the original text).</li><li>Display all possible decrypted versions so that the user can find the meaningful output.</li></ol><h3>Implementing the Code</h3><h4>1. Import Required Libraries</h4><p>First, we import the necessary modules:</p><pre>import tkinter as tk<br>from tkinter import scrolledtext</pre><p>⇝ tkinter: Used to create the graphical user interface (GUI).</p><p>⇝ scrolledtext: Provides a scrollable text box to display multiple results</p><h4>2. Define the Decryption Function</h4><p>We create a function to decrypt the message by shifting each letter backward through the Turkish alphabet:</p><pre>def caesar_cipher_decrypt(ciphertext, shift):<br>    turkish_alphabet = &quot;ABCÇDEFGĞHIİJKLMNOÖPRSŞTUÜVYZ&quot;<br>    result = &quot;&quot;<br><br>    for char in ciphertext:<br>        if char.upper() in turkish_alphabet:<br>            is_upper = char.isupper()<br>            index = turkish_alphabet.index(char.upper())<br>            new_index = (index - shift) % len(turkish_alphabet)<br>            new_char = turkish_alphabet[new_index]<br>            result += new_char if is_upper else new_char.lower()<br>        else:<br>            result += char<br><br>    return result</pre><p>💠 ciphertext: Encrypted text</p><p>💠 shift: Shift amount (example:4)</p><p>💠 The result variable is used to store the decrypted (original) text.</p><p>💠 The function processes each character in ciphertext one by one.</p><p>💠 It checks whether the character exists in the Turkish alphabet, regardless of case (char.upper()).</p><p>💠 If the character is in the alphabet, the decryption process is applied.</p><p>💠 It checks whether the character is uppercase or lowercase.</p><p>💠 It finds the character’s index in the alphabet.</p><p>💠 It shifts the index backward using (index - shift) % len(turkish_alphabet).</p><p>💠 It finds the new character (new_char).</p><p>💠 If the character is uppercase, it remains uppercase.</p><p>💠 If the character is lowercase, it remains lowercase.</p><p>💠 If the character is not a letter (e.g., space, punctuation marks, etc.), it is preserved as it is.</p><h4>3. Try All Possible Shifts</h4><p>This function will test all possible shift values and display the results:</p><pre>def try_all_shifts():<br>    ciphertext = entry_text.get()<br>    alphabet_length = len(&quot;ABCÇDEFGĞHIİJKLMNOÖPRSŞTUÜVYZ&quot;)<br>    <br>    results_box.delete(&quot;1.0&quot;, tk.END)  <br><br>    for shift in range(1, alphabet_length):<br>        decrypted_text = caesar_cipher_decrypt(ciphertext, shift)<br>        results_box.insert(tk.END, f&quot;Shift {shift}: {decrypted_text}\n&quot;)</pre><p>🔘 The function starts by retrieving the encrypted text from the input field (entry_text.get()).</p><p>🔘 It determines the length of the Turkish alphabet (alphabet_length).</p><p>🔘 It clears any previous results in the results_box using delete(&quot;1.0&quot;, tk.END).</p><p>🔘 It then iterates through all possible shift values from 1 to alphabet_length - 1.</p><p>🔘 For each shift value:</p><p>🔘 It calls caesar_cipher_decrypt(ciphertext, shift) to decrypt the text.</p><p>🔘 It inserts the decrypted text into the results_box, labeling each result with its corresponding shift value.</p><h4>4. Create the GUI with Tkinter</h4><p>Finally, we set up the Tkinter interface:</p><pre><br>root = tk.Tk()<br>root.title(&quot;Turkish Caesar Cipher Decryptor&quot;)<br>root.geometry(&quot;500x400&quot;)<br><br><br>label_text = tk.Label(root, text=&quot;Enter Encrypted Text:&quot;, font=(&quot;Arial&quot;, 12))<br>label_text.pack(pady=5)<br><br>entry_text = tk.Entry(root, width=50, font=(&quot;Arial&quot;, 12))<br>entry_text.pack(pady=5)<br><br><br>button_decrypt = tk.Button(root, text=&quot;Decrypt&quot;, command=try_all_shifts, font=(&quot;Arial&quot;, 12))<br>button_decrypt.pack(pady=5)<br><br><br>results_box = scrolledtext.ScrolledText(root, width=50, height=15, font=(&quot;Arial&quot;, 10))<br>results_box.pack(pady=5)<br><br>root.mainloop()</pre><p>🔶 tk.Tk() initializes the Tkinter application.</p><p>🔶 root.title() sets the window&#39;s title to &quot;Turkish Caesar Cipher Decryptor&quot;.</p><p>🔶 root.geometry() sets the window size to 500x400 pixels.</p><p>🔶 label_text is a label (text) that asks the user to &quot;Enter Encrypted Text&quot;.</p><p>🔶 entry_text is an input field where the user can type the encrypted text.</p><p>🔶 Both elements are added to the window using the pack() method, with 5 pixels of padding (space) around them.</p><p>🔶 button_decrypt creates a button labeled &quot;Decrypt&quot;.</p><p>🔶 When clicked, it will call the try_all_shifts function.</p><p>🔶 This button is also packed into the window with 5 pixels of padding.</p><p>🔶 results_box is a scrollable text box that will display the decryption results.</p><p>🔶 The text box has a width of 50 characters, a height of 15 lines, and uses the “Arial” font with size 10.</p><p>🔶 It’s also packed into the window with 5 pixels of padding.</p><p>🔶 root.mainloop() : This line starts the Tkinter main loop, which keeps the window open and interactive, waiting for user actions (such as clicking the “Decrypt” button).</p><h3>Running the Application</h3><p>1.Save the script as turkish_caesar_cipher.py.</p><p>2.Run the script using Python:</p><pre>python turkish_caesar_cipher.py</pre><p>3.Enter an encrypted text and click <strong>Decrypt</strong>.</p><p>4.The application will display all possible decrypted outputs.</p><h3>Conclusion</h3><p>We successfully built a <strong>Turkish Caesar Cipher Decryptor</strong> that allows users to analyze encrypted text and find the correct plaintext. This project demonstrates how <strong>basic cryptography</strong> and <strong>GUI development</strong> can be combined to create useful tools.</p><p>I hope this project helps you understand the Caesar cipher and inspires you to explore cryptography further!</p><p>I’m waiting for your feedback. Have a nice day and happy coding!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*ofCMnVftWJBWS7vRQKvhhA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=39ab3f3020ab" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introduction to Python Programming Language(Basic Concepts and Examples)]]></title>
            <link>https://medium.com/@Mehmtcnangn/introduction-to-python-programming-language-basic-concepts-and-examples-ff9caca56c03?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/ff9caca56c03</guid>
            <category><![CDATA[introduction]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[example]]></category>
            <category><![CDATA[beginners-guide]]></category>
            <category><![CDATA[python-programming]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Sat, 01 Feb 2025 12:44:05 GMT</pubDate>
            <atom:updated>2025-02-01T12:44:44.639Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/520/1*mtL0lZqOgrurdKpeKU65hw.gif" /></figure><h3>1. Declaring Variables in Python</h3><p>Variables in Python have a data type, and certain rules must be followed when assigning values to a variable:</p><h3>1.1 Variable Naming Rules</h3><p>Variable names cannot start with a number.</p><pre>3_kg_apple = &quot;5 USD&quot;  # Invalid</pre><p>Variable names cannot start with arithmetic operators.</p><pre>+value = 4568  # Invalid</pre><p>Variable names must start with a letter or an underscore (_).</p><pre>_value = 4568  # Valid<br>value = 4568   # Valid</pre><p>Reserved keywords in Python cannot be used as variable names.</p><pre>elif = &quot;beautiful girl&quot;  # Invalid</pre><h3>2. Data Types in Python</h3><p>Commonly used data types in Python:</p><ul><li><strong>int</strong> (Integer): x = 5</li><li><strong>float</strong> (Floating Point): y = 3.14</li><li><strong>str</strong> (String): z = &quot;Python&quot;</li><li><strong>bool</strong> (Boolean Values): a = True</li></ul><h3>2.1 Dynamic Typing in Python</h3><p>Python has a dynamic typing system, meaning that variables are not defined with a specific type:</p><pre>x = 5       # int<br>x = &quot;Hello&quot;  # str (Same variable can have different types)</pre><h3>2.2 Checking Variable Type</h3><p>You can check a variable’s type using the type() function:</p><pre>print(type(10))       # &lt;class &#39;int&#39;&gt;<br>print(type(3.14))     # &lt;class &#39;float&#39;&gt;<br>print(type(&quot;Python&quot;)) # &lt;class &#39;str&#39;&gt;</pre><p>However, if you assign type as a variable, the function will no longer be usable:</p><pre>type = &quot;Example&quot;  # This prevents type() function from being used</pre><h3>3. Arithmetic and Mathematical Operations in Python</h3><p>Python supports basic mathematical operations:</p><pre>print(6 ** 2)  # 36(Exponentiation)<br>print(pow(81, 0.5))  # 9.0 (Square Root)</pre><p>The third parameter of the pow() function performs a modulus operation:</p><pre>print(pow(16, 2, 2))  # 0 (16**2 = 256, 256 % 2 = 0)<br>print(pow(11, 3, 4))  # 3 (11**3 = 1331, 1331 % 4 = 3)</pre><h3>3.1 Basic Arithmetic Operators</h3><p>Python supports the following basic arithmetic operators:</p><pre>print(10 + 3)  # 13 (Addition)<br>print(10 - 3)  # 7 (Subtraction)<br>print(10 * 3)  # 30 (Multiplication)<br>print(10 / 3)  # 3.3333 (Division)<br>print(10 // 3) # 3 (Floor Division)<br>print(10 % 3)  # 1 (Modulo - Remainder)<br>print(10 ** 3) # 1000 (Exponentiation)</pre><h3>3.2 Using the Math Module</h3><p>Python’s math module is used for advanced mathematical operations:</p><pre>import math<br>print(math.sqrt(16))  # 4.0 (Square Root)<br>print(math.factorial(5))  # 120 (Factorial Calculation)<br>print(math.ceil(3.2))  # 4 (Ceiling - Round Up)<br>print(math.floor(3.8))  # 3 (Floor - Round Down)</pre><h3>4. Changing Variable Values in Python</h3><p>To assign the same value to multiple variables:</p><pre>a = b = 4  # Both a and b will have the value 4</pre><p>You can also swap variable values easily:</p><pre>mark, john = &quot;Data Engineer&quot;, &quot;Data Analyst&quot;<br>mark, john = john, mark<br>print(mark)  # Data Analyst<br>print(john) # Data Engineer</pre><h3>5. Print Function and Formatting</h3><p>You can use the print() function to display output:</p><pre>print(&quot;The name of the Python programming language does not come from the &#39;python&#39; snake&quot;)</pre><p>To print multiple statements with a separator:</p><pre>print(&quot;T&quot;, &quot;C&quot;, sep=&quot;.&quot;)  # T.C</pre><p>To create a new line:</p><pre>print(&quot;Apple\nCheery&quot;)</pre><p>Writing to a file:</p><pre>file = open(&quot;example.txt&quot;, &quot;w&quot;)<br>print(&quot;I am Newbie!&quot;, file=file)<br>file.close()</pre><h3>6. Escape Sequences</h3><ul><li>\n: New line</li><li>\t: Tab</li><li>\a: Beep sound</li><li>\r: Carriage return</li><li>\v: Vertical tab</li></ul><pre>print(&quot;We will have a picnic tomorrow.&quot;)<br>print(&quot;Medium is a blogging platform created by Twitter founders Evan Williams and Biz Stone\tin August 2012\tfor users who cannot be satisfied with 140 characters.&quot;)</pre><h3>7. Practice Exercises</h3><h4>7.1 Variable Assignment and Data Types</h4><p>To reinforce your understanding of Python basics, try solving the following exercises:</p><ol><li>Create three variables:</li></ol><ul><li>An integer (age = 25)</li><li>A float (height = 1.75)</li><li>A string (name = &quot;Alice&quot;)</li></ul><p>2. Print the type of each variable using the type() function.</p><h4>7.2 Arithmetic Operations</h4><ol><li>Write a Python script that takes two numbers as input and prints their:</li></ol><ul><li>Sum</li><li>Difference</li><li>Product</li><li>Quotient (both normal and floor division)</li><li>Remainder</li><li>Power</li></ul><h4>7.3 Using the math Module</h4><p>Import the math module and calculate:</p><ul><li>The square root of 144</li><li>The factorial of 6</li><li>The ceiling and floor values of 4.7</li></ul><h4>7.4 String Manipulation</h4><p>Given the string text = &quot;Python Programming&quot;, write a script to:</p><ul><li>Convert it to uppercase and lowercase</li><li>Find the length of the string</li><li>Extract the first 6 characters</li><li>Replace &quot;Python&quot; with &quot;Java&quot;</li></ul><h4>7.5 Swapping Variable Values</h4><p>Swap the values of two variables without using a temporary variable</p><h4>7.6 Print Formatting</h4><p>Print the following sentence using escape sequences:</p><pre>&quot;Python is fun,&quot; he said.</pre><p>Print the following formatted output using print():</p><pre>Name: John  <br>Age: 30  <br>Country: USA<br><br>#(Hint: Use \t and \n for formatting.)</pre><p>In the upcoming parts of this blog series, we will delve into more advanced topics in Python. For now, we have covered the basics, but in future articles, we will explore functions, object-oriented programming, and data structures in detail!</p><p>I wish everyone good work and good coding!</p><p>Source:<a href="https://python-istihza.yazbel.com/">https://python-istihza.yazbel.com/</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*WTRcCLZVBg9Te9WW0aEUBA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ff9caca56c03" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Python Basics for Absolute Beginners]]></title>
            <link>https://medium.com/@Mehmtcnangn/python-basics-for-absolute-beginners-505f6456a008?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/505f6456a008</guid>
            <category><![CDATA[python-programming]]></category>
            <category><![CDATA[learning]]></category>
            <category><![CDATA[beginner]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Fri, 24 Jan 2025 12:46:58 GMT</pubDate>
            <atom:updated>2025-01-24T12:46:58.338Z</atom:updated>
            <content:encoded><![CDATA[<p>Python is a versatile and beginner-friendly programming language. In this blog, I will explain the fundamental concepts and commands of Python with examples, making it easy for you to get started.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fji-0k-XofFvPVuvEBvEUg.gif" /></figure><h4>1. Print Command</h4><p>The print() function in Python is used to display output on the screen.</p><p><strong>Example Code:</strong></p><pre># Hello World!<br>print(&quot;Hello, World!&quot;)</pre><p><strong>Output:</strong></p><pre>Hello, World!</pre><h4>2. Variables and Data Types</h4><p>In Python, variables can store different data types. For example:</p><p><strong>Example Code:</strong></p><pre># Numeric variable<br>number = 10<br># String variable<br>text = &quot;I am learning Python&quot;<br># Floating-point number<br>temperature = 36.6<br>print(number, text, temperature)</pre><p><strong>Output:</strong></p><pre>10 I am learning Python 36.6</pre><h4>3. String Operations</h4><p>Strings can be manipulated in various ways.</p><p><strong>Example Code:</strong></p><pre># Convert to uppercase<br>name = &quot;mehmetcan&quot;<br>print(name.upper())<br># String length<br>print(len(&quot;mehmetcan&quot;))<br># Length of a number as a string<br>print(len(str(30512151)))</pre><p><strong>Output:</strong></p><pre>MEHMETCAN<br>9<br>8</pre><h4>4. Lists</h4><p>Lists are used to store multiple items and can be manipulated using various methods.</p><p><strong>Example Code:</strong></p><pre># Creating a list<br>random_numbers = [9, 8, 8, 4, 5, 7, 5, 1, 5, 6]<br>random_numbers.append(3)  # Add an element<br>random_numbers.sort()      # Sort the list<br>random_numbers.reverse()   # Reverse the list<br>random_numbers.pop(-1)     # Remove the last element<br>print(random_numbers)</pre><p><strong>Output:</strong></p><pre>[9, 8, 7, 6, 5, 5, 5, 4, 3]</pre><h4>5. Dictionaries</h4><p>Dictionaries store key-value pairs.</p><p><strong>Example Code:</strong></p><pre># Creating a dictionary<br>who_is_mehmetcan = {<br>    &quot;name&quot;: &quot;Mehmetcan&quot;,<br>    &quot;surname&quot;: &quot;ANGUN&quot;,<br>    &quot;phone_number&quot;: &quot;5553335555&quot;,<br>    &quot;color&quot;: &quot;black&quot;,<br>    &quot;favorite_numbers&quot;: [1, 4, 7]<br>}<br><br># Adding and updating elements<br>who_is_mehmetcan[&quot;favorite_currency&quot;] = &quot;Euro&quot;<br>print(who_is_mehmetcan)</pre><p><strong>Output:</strong></p><pre>{&#39;name&#39;: &#39;Mehmetcan&#39;, &#39;surname&#39;: &#39;ANGUN&#39;, &#39;phone_number&#39;: &#39;5553335555&#39;, &#39;color&#39;: &#39;black&#39;, &#39;favorite_numbers&#39;: [1, 4, 7], &#39;favorite_currency&#39;: &#39;Euro&#39;}</pre><h4>6. Conditional Statements</h4><p>Conditional statements allow different blocks of code to execute based on specific conditions.</p><p><strong>Example Code:</strong></p><pre>name = input(&quot;name:&quot;)<br>if name == &quot;mehmetcan&quot;:<br>    print(&quot;What&#39;s up, buddy?&quot;)<br>elif name == &quot;angun&quot;:<br>    print(&quot;Still, what&#39;s up, buddy?&quot;)<br>else:<br>    print(&quot;Who are you?&quot;)<br><br>&quot;If you enter mehmetcan for output, the answer will be as follows.&quot;</pre><p><strong>Output:</strong></p><pre>What&#39;s up, buddy?</pre><h4>7. Functions</h4><p>Functions allow you to define reusable code blocks.</p><p><strong>Example Code:</strong></p><pre>def greet(name):<br>    print(&quot;Welcome, &quot; + name + &quot;!&quot;)<br><br>names = [&quot;jack&quot;, &quot;john&quot;, &quot;Tim&quot;, &quot;mark&quot;]<br>for name in names:<br>    greet(name)<br>    print(&quot;Keep going&quot;)</pre><p><strong>Output:</strong></p><pre>Welcome, jack!<br>Keep going<br>Welcome, john!<br>Keep going<br>Welcome, Tim!<br>Keep going<br>Welcome, mark!<br>Keep going</pre><h4>8. User Input and Average Calculation</h4><p>Use input() to take data from users and perform operations with it.</p><p><strong>Example Code:</strong></p><pre>midterm = input(&quot;Midterm Grade: &quot;)<br>final = input(&quot;Final Grade: &quot;)<br>project = input(&quot;Project Grade: &quot;)<br>average = (float(midterm) * 0.35 + float(final) * 0.34 + float(project) * 0.31)<br><br>if average &gt;= 90:<br>    print(&quot;You passed with an AA!&quot;)<br>elif average &gt;= 80:<br>    print(&quot;You passed with a BA!&quot;)<br>else:<br>    print(&quot;You need to study more!&quot;)</pre><p><strong>Example Output:</strong></p><pre>Midterm Grade: 85<br>Final Grade: 90<br>Project Grade: 80<br>You passed with an BA!</pre><h4>9. Nested Conditions and List Manipulation</h4><p>You can create lists based on multiple conditions.</p><p><strong>Example Code:</strong></p><pre>numbers = []<br>for i in range(3, 2065):<br>    if i % 9 == 0 and i % 8 == 0:<br>        numbers.append(i)<br><br>print(numbers[5])  # Print the sixth element</pre><p><strong>Output:</strong></p><pre>432</pre><h3>Practice Exercises</h3><p>Now that you have learned the basics, try out these exercises to test your understanding:</p><ol><li><strong>Reverse a String</strong> Write a program that takes a string as input and prints it in reverse order.</li></ol><p><strong>Hint:</strong> Use slicing.</p><p><strong>2. Check Even or Odd</strong> Write a program that accepts a number and checks if it is even or odd.</p><p><strong>Hint:</strong> Use the modulus operator %.</p><p><strong>3. Find the Largest Number</strong> Create a program that takes three numbers as input and prints the largest of the three.</p><p><strong>Hint:</strong> Use if-elif-else conditions.</p><p><strong>4. Sum of a List</strong> Write a program to calculate the sum of all numbers in a list provided by the user.</p><p><strong>Hint:</strong> Use a for loop.</p><p><strong>5. Simple Calculator</strong> Develop a program that takes two numbers and an operator (+, -, *, /) as input and performs the respective calculation.</p><p><strong>Hint:</strong> Use functions and conditional statements.</p><p>Python is an excellent language for beginners due to its simplicity and readability. Mastering these basics will serve as a strong foundation for your programming journey. Keep practicing, experimenting, and exploring new concepts.</p><p>If you enjoyed this guide and found it helpful, feel free to share it with others who are starting their programming journey. I will try to explain the Python programming language in more detail in the upcoming articles.<br>Stay curious, and happy coding!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*ofCMnVftWJBWS7vRQKvhhA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=505f6456a008" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Python IDEs for Data Engineers: Which One is Right for You?]]></title>
            <link>https://medium.com/@Mehmtcnangn/python-ides-for-data-engineers-which-one-is-right-for-you-697dedcbad73?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/697dedcbad73</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[coding]]></category>
            <category><![CDATA[vscode]]></category>
            <category><![CDATA[beginners-guide]]></category>
            <category><![CDATA[pycharm]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Fri, 29 Nov 2024 11:52:01 GMT</pubDate>
            <atom:updated>2024-11-29T11:52:01.128Z</atom:updated>
            <content:encoded><![CDATA[<p>Python is a powerhouse in the world of data engineering. With its flexibility, extensive library support, and vibrant community, it’s a go-to language for projects of all scales. But success in any project isn’t just about the programming language; it’s also about choosing the right tools. <strong>So, how do you pick the best Python IDE for your needs?</strong></p><p>In this blog, we’ll explore some of the <strong>most popular Python IDEs</strong>, their strengths, and their limitations. Whether you’re a beginner or a seasoned data engineer, this guide will help you make an informed decision.</p><h4>PyCharm: The Professional’s Choice</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OQPOdcPT0PJxZXYkOJx4hw.gif" /></figure><p>PyCharm, developed by JetBrains, is a favorite for complex and large-scale projects. This feature-rich IDE is designed to boost coding productivity and streamline workflows.</p><h4>Advantages:</h4><p>✧ <strong>Code Completion:</strong> PyCharm reduces the risk of errors with its intelligent code completion and error catching features.</p><p>✧ <strong>Database Integration:</strong> With the Pro version, you can directly connect to databases, run SQL queries, and perform analysis.</p><p>✧ <strong>Advanced Debugger:</strong> It has tools that simplify the debugging process.</p><p>✧ <strong>Multiple Framework Support:</strong> Supports popular Python frameworks like Django and Flask.</p><p>✧ <strong>Refactoring:</strong> Helps with organizing and refactoring your code easily.</p><h4>Disadvantages:</h4><p>✧ <strong>Paid Pro Version:</strong> You need to purchase a license to access all features.</p><p>✧ <strong>High System Requirements:</strong> Can be resource-intensive in terms of RAM and CPU usage.</p><h4>VS Code: Lightweight Yet Powerful</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*46YDsOSYFal58b9b5xU5wQ.gif" /></figure><p>Microsoft’s Visual Studio Code has quickly become a favorite among data engineers. Its speed, customizability, and extensive extension library make it a versatile choice.</p><h4>Advantages:</h4><p>☸ <strong>Free and Open-Source:</strong> Completely free and has strong community support.</p><p>☸ <strong>Rich Extension Ecosystem:</strong> Can be customized with extensions specifically for Python. It is compatible with tools such as SQL, Docker, and BigQuery for data engineering projects.</p><p>☸ <strong>Lightweight and Fast:</strong> Ideal for those seeking a high-performance, non-bulky IDE.</p><p>☸ <strong>Git Integration:</strong> Easily manage your projects with Git.</p><h4>Disadvantages:</h4><p>☸ <strong>Less Specialized:</strong> Does not offer as many Python-specific tools as PyCharm.</p><p>☸ <strong>Complex for Beginners:</strong> The need to install extensions for customization may be confusing for newcomers.</p><h4>Jupyter Notebook: A Data-Driven Approach</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iGMClCbnpOvUlZ5xoiAASA.gif" /></figure><p>Jupyter Notebook is a popular tool, especially for data analysis and machine learning projects. It is frequently used during ETL (Extract, Transform, Load) processes in data engineering workflows.</p><h4>Advantages:</h4><p>◍ <strong>Interactive Environment:</strong> Allows you to run your code step by step and see results instantly.</p><p>◍ <strong>Graphical Outputs:</strong> Makes it easy to visualize data using libraries like Matplotlib and Seaborn.</p><p>◍ <strong>Lightweight and User-Friendly:</strong> Works through a browser and does not require complex installations.</p><p>◍ <strong>Community Support:</strong> Widely used within the data science community.</p><h4>Disadvantages:</h4><p>◍ <strong>Weak Debugging:</strong> Debugging capabilities are not as advanced as in PyCharm or VS Code.</p><p>◍ <strong>Inadequate for Large Projects:</strong> Not as powerful for modular, large-scale projects as other IDEs.</p><h4>Spyder: The Scientific Choice</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/720/1*pvGF7ilAceEkEUWV1ECPWg.gif" /></figure><p>Spyder is an IDE preferred for scientific computing and data analysis projects. It is ideal for data engineers working with libraries such as NumPy, Pandas, and Matplotlib.</p><h4>Advantages:</h4><p>⦿ <strong>Matlab-Like Interface:</strong> Provides an easy transition for those familiar with scientific computing environments.</p><p>⦿ <strong>Data Visualization:</strong> Allows you to explore and manipulate variables and datasets directly within the interface.</p><p>⦿ <strong>Free and Open-Source:</strong> Bundled with Anaconda and doesn’t require any additional costs.</p><h4>Disadvantages:</h4><p>⦿ <strong>Performance Issues:</strong> May slow down when working with very large datasets.</p><p>⦿ <strong>Limited Extensions:</strong> Customization options are more limited than those of other IDEs.</p><h4>Google Colab: Cloud-Based Convenience</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9mXZKYfIcQOt63QdUCVFBQ.png" /></figure><p>If you’re struggling with local resources, Google Colab offers a cloud-based Jupyter experience. It’s a fantastic choice for experimenting with large datasets and models.</p><h4>Advantages:</h4><p>◎ <strong>Cloud-Based:</strong> Doesn’t put strain on your local system.</p><p>◎ <strong>GPU/TPU Support:</strong> Offers speed advantages when working with large datasets.</p><p>◎ <strong>Easy Sharing:</strong> Makes it simple to share your projects.</p><p>◎ <strong>Free:</strong> Basic features are available for free.</p><h4>Disadvantages:</h4><p>◎ <strong>No Offline Use:</strong> Requires an internet connection.</p><p>◎ <strong>Resource Limits:</strong> The free version has limited access to GPU/TPU usage.</p><h4>Thonny: A Beginner’s Companion</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ipWF45TOZraPnS5lxk7xVQ.gif" /></figure><p>If you’re new to Python and data engineering, Thonny is a great starting point. Its minimalist and beginner-friendly design makes it an excellent introduction to coding environments.</p><h4>Advantages:</h4><p>✤ <strong>User-Friendly Interface:</strong> Features a simple and minimal design.</p><p>✤ <strong>Debugging:</strong> Offers effective debugging tools for beginner users.</p><p>✤ <strong>Easy Setup:</strong> Can be quickly installed and used.</p><h4>Disadvantages:</h4><p>✤ <strong>Limited Features:</strong> May not be sufficient for more complex projects.</p><p>✤ <strong>Less Community Support:</strong> Since it’s less popular, there are fewer resources available.</p><h3>Which IDE is Right for You?</h3><p>When selecting an IDE, consider these questions:</p><p>➤ <strong>How complex are your projects?</strong> For larger projects, PyCharm or VS Code might be more suitable.</p><p>➤ <strong>Is resource consumption a concern?</strong> If you prefer a lightweight IDE, VS Code or Thonny may be a better choice.</p><p>➤ <strong>Are you working in the cloud?</strong> Google Colab could be ideal for you.</p><p>Ultimately, the best IDE for your data engineering projects depends on your workflow and project requirements. The IDEs introduced in this blog offer various advantages for users at all levels.</p><p>Thank you for reading my blog. I want this blog to be a guide for beginners and those curious about the world of data. I am putting in the effort to achieve this. Thank you for your feedback<br><strong>Happy coding!</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*WTRcCLZVBg9Te9WW0aEUBA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=697dedcbad73" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Top 5 Python Libraries for Data Engineering]]></title>
            <link>https://medium.com/@Mehmtcnangn/top-5-python-libraries-for-data-engineering-3926fb795ce6?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/3926fb795ce6</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python-programming]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Fri, 22 Nov 2024 16:43:53 GMT</pubDate>
            <atom:updated>2024-11-22T16:43:53.912Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2VLCI1P1cw-KlW_g3wDHLg.gif" /></figure><p>Data engineering is a critical field that involves organizing, processing, and preparing data for analysis. Python, one of the most popular programming languages, offers a range of libraries that make data engineering tasks efficient and streamlined. In my blog, we’ll explore the top 5 Python libraries commonly used by data engineers and how they can help you handle data more effectively.</p><h4>1. <strong>Pandas: Data Manipulation and Analysis</strong></h4><p><strong>Pandas</strong> is the go-to library for data manipulation and analysis in Python. It is particularly known for its powerful <strong>DataFrame</strong> structure, which organizes data in a tabular format, making it easy to manipulate and analyze.</p><h4>Key Features:</h4><p>✓ <strong>DataFrame</strong>: A tabular data structure with labeled rows and columns, ideal for working with structured data.</p><p>✓ <strong>Data Cleaning</strong>: Handle missing values, remove duplicates, and fix inconsistencies effortlessly.</p><p>✓ <strong>Data Transformation</strong>: Convert raw data into formats suitable for analysis.</p><h4>When to Use:</h4><p>✓ Cleaning and transforming data before analysis.</p><p>✓ Merging and aggregating data from multiple sources.</p><p>✓ Quick exploratory data analysis.</p><h4>Example:</h4><pre>import pandas as pd<br><br># Load data<br>data = pd.read_csv(&#39;data.csv&#39;)<br><br># Clean missing values<br>data.dropna(inplace=True)<br><br># Group and summarize data<br>grouped_data = data.groupby(&#39;category&#39;)[&#39;value&#39;].mean()<br>print(grouped_data)</pre><h4>2. <strong>Numpy: Numerical Computing and Matrix Operations</strong></h4><p><strong>Numpy</strong> is a foundational library for numerical computations in Python. It provides powerful tools for working with arrays and matrices, offering superior performance compared to Python’s built-in lists.</p><h4>Key Features:</h4><p>❖ <strong>Arrays</strong>: Efficient storage and manipulation of numerical data.</p><p>❖ <strong>Mathematical Functions</strong>: Perform vectorized operations for faster calculations.</p><p>❖ <strong>Linear Algebra</strong>: Handle matrix operations and other numerical techniques seamlessly.</p><h4>When to Use:</h4><p>❖ Numerical computations and matrix operations.</p><p>❖ Enhancing performance for large-scale numerical datasets.</p><p>❖ Supporting libraries like Pandas for efficient data analysis.</p><h4>Example:</h4><pre>import numpy as np<br><br># Create a NumPy array<br>arr = np.array([1, 2, 3, 4, 5])<br><br># Square each element<br>squared = np.square(arr)<br>print(squared)</pre><h4>3. <strong>SQLAlchemy: Database Management and Querying</strong></h4><p>Database interaction is a core aspect of data engineering, and <strong>SQLAlchemy</strong> is one of the most robust libraries for managing and querying databases. It simplifies the process of connecting to databases and performing CRUD operations.</p><h4>Key Features:</h4><p>✧ <strong>Database Connections</strong>: Easily connect to various databases like SQLite, PostgreSQL, or MySQL.</p><p>✧ <strong>ORM (Object-Relational Mapping)</strong>: Represent database tables as Python classes for more intuitive data manipulation.</p><p>✧ <strong>Query Flexibility</strong>: Write complex queries in Python without diving deep into SQL syntax.</p><h4>When to Use:</h4><p>✧ Extracting and loading data from/to databases.</p><p>✧ Building pipelines that require regular database interactions.</p><p>✧ Managing database schema and performing optimizations.</p><h4>Example:</h4><pre>from sqlalchemy import create_engine<br>import pandas as pd<br><br># Connect to an SQLite database<br>engine = create_engine(&#39;sqlite:///database.db&#39;)<br><br># Read data from the database<br>data = pd.read_sql(&#39;SELECT * FROM table_name&#39;, engine)<br><br># Write data to the database<br>data.to_sql(&#39;table_name&#39;, engine, if_exists=&#39;replace&#39;)</pre><h4>4. <strong>Apache Airflow: Workflow Automation for ETL</strong></h4><p><strong>Apache Airflow</strong> is a workflow orchestration tool that helps automate and schedule ETL (Extract, Transform, Load) pipelines. It enables data engineers to define tasks and their dependencies, ensuring smooth execution of data workflows.</p><h4>Key Features:</h4><p>✤ <strong>DAGs (Directed Acyclic Graphs)</strong>: Define workflows as a sequence of tasks.</p><p>✤ <strong>Task Dependencies</strong>: Automatically handle task execution order.</p><p>✤ <strong>Scheduling</strong>: Automate recurring tasks based on a predefined schedule.</p><h4>When to Use:</h4><p>✤ Automating repetitive ETL tasks.</p><p>✤ Managing complex data pipelines with multiple dependencies.</p><p>✤ Monitoring and debugging workflows in production.</p><h4>Example:</h4><pre>from airflow import DAG<br>from airflow.operators.dummy_operator import DummyOperator<br>from datetime import datetime<br><br># Define a DAG<br>dag = DAG(&#39;data_pipeline&#39;, description=&#39;A simple ETL pipeline&#39;,<br>          schedule_interval=&#39;@daily&#39;, start_date=datetime(2024, 1, 1), catchup=False)<br><br># Define tasks<br>start = DummyOperator(task_id=&#39;start&#39;, dag=dag)<br><br># More tasks can be added<br># task = SomeOtherOperator(task_id=&#39;do_something&#39;, dag=dag)<br><br>start</pre><h4>5. <strong>Dask: Distributed Data Processing</strong></h4><p><strong>Dask</strong> extends the functionality of Pandas and Numpy for large datasets that don’t fit into memory. It enables distributed computation across multiple cores or nodes, making it perfect for processing big data.</p><h4>Key Features:</h4><p>◍ <strong>Parallel Processing</strong>: Perform computations faster by leveraging multiple CPU cores.</p><p>◍ <strong>Big Data Handling</strong>: Work with datasets larger than memory.</p><p>◍ <strong>Familiar API</strong>: Similar to Pandas, which makes it easy to learn for existing Pandas users.</p><h4>When to Use:</h4><p>◍ Handling large datasets in a distributed environment.</p><p>◍ Scaling up data processing tasks for big data.</p><p>◍ Speeding up computation by parallelizing operations.</p><h4>Example:</h4><pre>import dask.dataframe as dd<br><br># Read a large dataset<br>df = dd.read_csv(&#39;large_data.csv&#39;)<br><br># Perform operations<br>result = df.groupby(&#39;category&#39;).mean().compute()<br>print(result)</pre><p>These five Python libraries — Pandas, Numpy, SQLAlchemy, Apache Airflow, and Dask — are some of the indispensable tools for data engineers. Each serves a purpose, from cleaning and analyzing data to managing workflows and processing large datasets. Mastering these libraries will not only make your data engineering tasks more efficient, but will also prepare you for more complex challenges in the field.</p><p>If you’re starting out on your data engineering journey, these libraries are excellent stepping stones. We’ll be putting some of these libraries to practical use in the articles and projects I’ll cover later on in my data engineering articles.</p><p>See you in the next article!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*WTRcCLZVBg9Te9WW0aEUBA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3926fb795ce6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[First Step into Data Engineering: The Journey of Data]]></title>
            <link>https://medium.com/@Mehmtcnangn/first-step-into-data-engineering-the-journey-of-data-4a96aff2e664?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/4a96aff2e664</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[beginners-guide]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Sat, 16 Nov 2024 18:10:47 GMT</pubDate>
            <atom:updated>2024-11-16T18:10:47.435Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8kJ1MMqKtQXsCNRPOrl27Q.gif" /></figure><p>Imagine you work at an e-commerce site. You arrive at the office in the morning, sit down at your computer, and while enjoying your first cup of coffee, you see an urgent email from the CEO. Sales have been declining for the past few months, and management wants to analyze user behavior to figure out the cause of this decline.</p><p>However, there’s a problem: the clicks, product searches, cart activities, and purchase information from users are all stored in a heap. This raw data is like a treasure, but before we can understand it, we need to mine its gold. And that’s where data engineering comes into play!</p><h4>Data: From Disparate Pieces to Consistent Integrity</h4><p>Making sense of raw data is like putting together pieces of a puzzle. First, we need to gather these pieces. So how do we do that?</p><p>For example, the data from our e-commerce site could come from many different sources:</p><ul><li>Users’ clicks and search history from web servers,</li><li>Order information from databases,</li><li>User feedback from the email system.</li></ul><p>It’s the data engineer’s job to merge data from these scattered sources. But simply collecting it isn’t enough. We also need to fix issues, such as when users mistakenly type “televzyon” instead of “televizyon” or when a product added to the cart is missing. This process is called data cleaning, and it’s one of the most important tasks of data engineers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*NJCcUShbglcr0nbs2CKbsA.gif" /></figure><h4>The Birth of an ETL Process</h4><p>After finishing your coffee, you sit down and start thinking about a plan: setting up the ETL process. ETL stands for Extract, Transform, and Load — the process of collecting data from sources (Extract), cleaning and transforming it (Transform), and making it ready for analysis and storing it (Load).</p><p>To automate this process, you can use a popular tool: Apache Airflow. Airflow moves the data along a “pipeline” and processes it in an orderly manner. Imagine, thousands of data points now flowing like a well-organized river.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*ZKkN2eoUC-MWU-jzwfOq8A.gif" /></figure><p>So, what can you now tell the CEO?</p><ul><li>Which products are added to carts most frequently but not purchased,</li><li>Which categories are attractive to users but hard to find,</li><li>Which campaigns attracted the most attention?</li></ul><p>The answers to all these questions are now at your fingertips. As the CEO thanks you, you think to yourself: “This is how we turn data into a meaningful story.”</p><h4>The Dance Between Data Scientists and Data Engineers</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*IkI30QRzll6x42VSkBZsxg.gif" /></figure><p>At this point, data scientists step in. They use the clean, organized data you’ve prepared to conduct more advanced analyses. For example:</p><ul><li>A model predicting which hours users are more likely to shop,</li><li>An algorithm suggesting the best delivery options to reduce return rates.</li></ul><p>But without data engineers, who prepare the gold-standard data, the data scientists’ work wouldn’t be possible. For a data scientist to do their job, a solid data infrastructure is required, and that’s what we build.</p><h4>Why Data Engineering?</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tQFDw4dIgpjFkERvosggRw.png" /></figure><p>Data engineering isn’t just a technical job. It directly contributes to organizational decision-making processes. Whether it’s detecting fraud in a bank, providing better diagnostic data for doctors in a hospital, or increasing sales on an e-commerce site, all of this happens thanks to the infrastructure provided by data engineering.</p><p>My own journey into data engineering started with a need. At first, I realized that to make sense of data, I had to work with many different systems. Sometimes I queried databases with SQL, sometimes manipulated data with Python, and at times, I spent hours reading Airflow documentation to set up an ETL process.</p><p>This journey not only developed me technically but also enhanced my problem-solving skills. Looking back now, I realize that data engineering isn’t just a job, it’s a journey to solve complex problems.</p><h4>The End of the Story (And the Beginning)</h4><p>Data engineering is like ensuring the smooth flow of a river, carrying data from one place to another and making it usable. If you’re thinking about choosing this career, remember: it’s a team effort. By collaborating with data scientists, analysts, and business units, you can achieve much bigger results.</p><p>Our story ends here, but for the world of data engineering, this is just the beginning.I will share with you what I know in our article adventure that I have planned and plan to continue.</p><p>See you in new articles 🙂</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*WTRcCLZVBg9Te9WW0aEUBA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4a96aff2e664" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Detailed Data Engineering Project with Spark: ETL and Analysis on Online Retail Data]]></title>
            <link>https://medium.com/@Mehmtcnangn/detailed-data-engineering-project-with-spark-etl-and-analysis-on-online-retail-data-dec905bf963b?source=rss-10fca026e62b------2</link>
            <guid isPermaLink="false">https://medium.com/p/dec905bf963b</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[MEHMETCAN ANGÜN]]></dc:creator>
            <pubDate>Fri, 08 Nov 2024 12:27:05 GMT</pubDate>
            <atom:updated>2024-11-08T12:27:05.436Z</atom:updated>
            <content:encoded><![CDATA[<h4>Hello data enthusiasts! In this blog post, we will learn how to perform an ETL process and analysis on e-commerce data using PySpark and Spark. We will cover steps like cleaning, transforming, and visualizing the data. Using a real-world dataset called the Online Retail Data Set, we will analyze e-commerce metrics such as customer types, sales trends, and the best-selling products.</h4><p><strong>Why Spark?</strong></p><p>Working with large datasets like e-commerce data requires powerful tools for data analysis. Spark is an open-source big data platform that enables us to perform fast and efficient operations on large datasets. In this post, we will utilize the power of Spark by writing code with PySpark.</p><h4>Steps of the Project</h4><p>✤ Data Loading and Exploration</p><p>✤ Data Cleaning and Filtering</p><p>✤ Feature Engineering and Data Transformation</p><p>✤ Data Analysis and Visualization</p><h4>Required Tools and Setup</h4><p>In this project, we will use the following tools and libraries:</p><p>✤ PySpark</p><p>✤ Pandas</p><p>✤ Matplotlib</p><p>✤ Databricks Community Edition</p><p>You can find the dataset and code used in the project on <a href="https://github.com/Revealis">my GitHub link</a>.</p><h4>Data Loading and Exploration</h4><p>Let’s first load the libraries we will use in our project:</p><pre>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import col, to_date, countDistinct, lit, when<br>import pandas as pd<br>import matplotlib.pyplot as plt</pre><p>⇝ SparkSession: Allows us to create a session for Spark operations.</p><p>⇝ pyspark.sql.functions: Contains the necessary functions for working with Spark DataFrames. We will use functions like column access (col), date format conversion (to_date), counting distinct values (countDistinct), conditional assignment (when), etc.</p><p>⇝ pandas and matplotlib.pyplot: Pandas will be used to convert PySpark DataFrames to traditional Python DataFrames, and Matplotlib will allow us to create visualizations.</p><p>Let’s start by initializing a Spark session:</p><pre>spark = SparkSession.builder.appName(&quot;AdvancedOnlineRetailETL&quot;).getOrCreate()</pre><p>This line creates a session for Spark operations. The appName parameter is used to give the session a descriptive name. You can choose any name you like.</p><p>Now, let’s load the data. For those using Databricks like me, you can easily upload your data in the Databricks environment by navigating to Catalog &gt; dbfs &gt; fileStore &gt; tables in the left menu and selecting the upload button to get the path:</p><pre>df = spark.read.csv(&quot;dbfs:/FileStore/tables/Online_Retail.csv&quot;, header=True, inferSchema=True)</pre><p>The csv() function reads the CSV file.</p><p>⇝ header=True: Defines the first row of the CSV file as column names.</p><p>⇝ inferSchema=True: Automatically infers data types (e.g., numerical or date values are correctly loaded).</p><p>Let’s check the schema of the loaded data with .printSchema() and view the first 5 rows with df.show(5).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/944/1*bs70Qu5Sx09v_B_FaUY0rQ.png" /></figure><p>Since the InvoiceNo column serves as a unique identifier for each sale, it may appear as an integer (int) at first glance. However, in many datasets like this one, InvoiceNo can contain alphanumeric characters (both numbers and letters), especially in cases where certain letters are added to invoices of canceled orders (e.g., &quot;C12345&quot;). Therefore, I left the column as a string type (or StringType in Spark). If you are sure that the dataset contains only numbers, you can change the format with the following code:</p><pre>df = df.withColumn(&quot;InvoiceNo&quot;, col(&quot;InvoiceNo&quot;).cast(&quot;integer&quot;))</pre><h4>Handling Missing Data and Basic Filtering</h4><p>E-commerce data often contains faulty or missing records. Therefore, we will remove rows with missing values in columns like InvoiceNo, StockCode, Description, Quantity, UnitPrice, and CustomerID. We will also convert the Quantity and UnitPrice columns to appropriate data types:</p><pre>df_cleaned = df.dropna(subset=[&#39;InvoiceNo&#39;, &#39;StockCode&#39;, &#39;Description&#39;, &#39;Quantity&#39;, &#39;UnitPrice&#39;, &#39;CustomerID&#39;])<br>df_cleaned = df_cleaned.withColumn(&quot;Quantity&quot;, col(&quot;Quantity&quot;).cast(&quot;integer&quot;))<br>df_cleaned = df_cleaned.withColumn(&quot;UnitPrice&quot;, col(&quot;UnitPrice&quot;).cast(&quot;double&quot;))<br>df_cleaned = df_cleaned.withColumn(&quot;TotalValue&quot;, col(&quot;Quantity&quot;) * col(&quot;UnitPrice&quot;))</pre><p>✧ dropna(): This function removes rows with missing data in specific columns.</p><p>✧ withColumn(): Adds or updates a column in the DataFrame.</p><p>✧ The Quantity and UnitPrice columns are converted to integer and double types, respectively, so we can perform numerical operations.</p><p>✧ TotalValue: A new column is added to calculate the total value of each order, which is derived by multiplying Quantity (quantity) by UnitPrice (unit price). This gives us the monetary value of each order.</p><h4>Feature Engineering and Data Transformation</h4><p>Now, let’s transform the date column format:</p><pre>df_cleaned = df_cleaned.withColumn(&quot;InvoiceDate&quot;, to_date(col(&quot;InvoiceDate&quot;), &quot;MM/dd/yyyy&quot;))</pre><p>☸ to_date(): Converts the InvoiceDate column into the MM/dd/yyyy format. This ensures compatibility when performing date-based analysis and reduces error risks.</p><h4><strong>Calculating Customer Shopping Frequency</strong></h4><p>Customer shopping frequency is an important metric for sales optimization. We will see this more clearly in the visualization section. Let’s start with the necessary steps:</p><pre>customer_frequency = df_cleaned.groupBy(&quot;CustomerID&quot;).agg(countDistinct(&quot;InvoiceNo&quot;).alias(&quot;PurchaseCount&quot;))<br>df_cleaned = df_cleaned.join(customer_frequency, on=&quot;CustomerID&quot;, how=&quot;left&quot;)</pre><p>☸ groupBy(&quot;CustomerID&quot;): Groups the data by the CustomerID column, so rows with the same CustomerID are grouped together.</p><p>agg(countDistinct(&quot;InvoiceNo&quot;).alias(&quot;PurchaseCount&quot;)):</p><p>☸ countDistinct(&quot;InvoiceNo&quot;): Counts the distinct InvoiceNo values within each group, which indicates the number of purchases made by each customer.</p><p>☸ alias(&quot;PurchaseCount&quot;): Assigns a name to the resulting column, which shows the shopping frequency.</p><p>join(customer_frequency, on=&quot;CustomerID&quot;, how=&quot;left&quot;):</p><p>☸ join(): Joins two DataFrames.</p><p>☸ on=&quot;CustomerID&quot;: Uses CustomerID as the common column to join the two DataFrames.</p><p>☸ how=&quot;left&quot;: Performs a left join, keeping all rows from the df_cleaned table and adding the PurchaseCount values from the customer_frequency table. If no match is found in customer_frequency, the PurchaseCount will be null.</p><h4><strong>Identifying Customer Types (B2B and B2C)</strong></h4><p>We will categorize customers based on their shopping frequency:</p><pre>df_cleaned = df_cleaned.withColumn( &quot;CustomerType&quot;, when(col(&quot;PurchaseCount&quot;) &gt; 1, lit(&quot;B2B&quot;)).otherwise(lit(&quot;B2C&quot;)) )</pre><p>➾ when(): Creates a conditional expression. If PurchaseCount &gt; 1, the customer is labeled as B2B (business); otherwise, they are B2C (individual).</p><p>➾ lit(): Adds fixed values, such as &quot;B2B&quot; and &quot;B2C&quot;.</p><p>We can now view the results:</p><pre>distinct_customers = df_cleaned.select(&quot;CustomerID&quot;, &quot;PurchaseCount&quot;, &quot;CustomerType&quot;, &quot;InvoiceDate&quot;).distinct() distinct_customers.show(10) </pre><p>➾ select(): Selects only the columns CustomerID, PurchaseCount, CustomerType, and InvoiceDate.</p><p>➾ distinct(): Retrieves unique rows.</p><p>➾ show(10): Displays the first 10 rows, showing each customer&#39;s type, shopping frequency, and last purchase date.</p><p>Our output will be like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/944/1*DWYS4nqzuK4Vrz9E5XEn-g.png" /></figure><p><strong>Data Analysis and Visualization</strong></p><p>Let’s start by analyzing the top-selling products:</p><pre>top_products = df_cleaned.groupBy(&quot;Description&quot;).sum(&quot;Quantity&quot;).orderBy(col(&quot;sum(Quantity)&quot;).desc())<br>top_products.show(10)</pre><p>➳ groupBy(&quot;Description&quot;): Groups the data by product description.</p><p>➳ sum(&quot;Quantity&quot;): Calculates the total quantity sold for each product.</p><p>➳ orderBy(col(&quot;sum(Quantity)&quot;).desc()): Sorts by the total sales quantity in descending order, so the best-selling products appear first.</p><p>➳ show(10): Displays the top 10 best-selling products.</p><p>Let’s examine our output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/943/1*f_MpMp7zfhG7M95Ywco28w.png" /></figure><p>Next, let’s analyze the sales trends over time:</p><pre>sales_trend = df_cleaned.groupBy(&quot;InvoiceDate&quot;).sum(&quot;Quantity&quot;).orderBy(&quot;sum(Quantity)&quot;, ascending=False)<br>sales_trend.show(10)</pre><p>orderBy(&quot;sum(Quantity)&quot;, ascending=False): Sorts by total sales in descending order, with the most sales occurring first.</p><p>Let’s check our output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/944/1*DmFlYP_EsgVjbtzL7y0Fwg.png" /></figure><p>We can also explore sales trends annually and monthly:</p><pre>from pyspark.sql.functions import year, month<br><br>df_cleaned = df_cleaned.withColumn(&quot;Year&quot;, year(col(&quot;InvoiceDate&quot;)))<br>df_cleaned = df_cleaned.withColumn(&quot;Month&quot;, month(col(&quot;InvoiceDate&quot;)))</pre><p>➳ year(col(&quot;InvoiceDate&quot;)): Extracts the year from the InvoiceDate column and adds it as a new Year column.</p><p>➳ month(col(&quot;InvoiceDate&quot;)): Extracts the month from the InvoiceDate column and adds it as a new Month column.</p><pre>monthly_sales = df_cleaned.groupBy(&quot;Year&quot;, &quot;Month&quot;).sum(&quot;Quantity&quot;).orderBy(&quot;Year&quot;, &quot;Month&quot;)<br>monthly_sales.show(24)</pre><p>➣ groupBy(&quot;Year&quot;, &quot;Month&quot;): Groups the data by year and month, so sales within the same period are aggregated.</p><p>➣ orderBy(&quot;Year&quot;, &quot;Month&quot;): Sorts by year and month in ascending order to get a chronological sequence.</p><p>Now let’s look at our output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/944/1*EzvnL7UUtfJ8EIXwB4DBqA.png" /></figure><p>Now let’s calculate the average order value per customer and sort it. First, let’s add a new column:</p><pre>df_cleaned = df_cleaned.withColumn(&quot;TotalValue&quot;, col(&quot;Quantity&quot;) * col(&quot;UnitPrice&quot;))</pre><p>➣ withColumn(&quot;TotalValue&quot;, ...): Creates a new column to calculate the total value of each order (Quantity × UnitPrice).</p><p>Finally, we group by CustomerID and sort by Avg Order Value:</p><pre>avg_order_value = df_cleaned.groupBy(&quot;CustomerID&quot;).agg(round(avg(&quot;TotalValue&quot;), 2).alias(&quot;Avg Order Value&quot;)).orderBy(col(&quot;Avg Order Value&quot;).desc())<br>avg_order_value.show()</pre><p>➢ groupBy(&quot;CustomerID&quot;): Groups the data by customer.</p><p>➢ agg(): Aggregates the data to calculate the average total value.</p><p>➢ round(avg(&quot;TotalValue&quot;), 2).alias(&quot;Avg Order Value&quot;): Calculates the average order value for each customer and rounds it to two decimal places.</p><p>➢ orderBy(col(&quot;Avg Order Value&quot;).desc()): Sorts by average order value in descending order.</p><p>Let’s check our output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/944/1*BOfckUH3WJFDMhYHeYnK9A.png" /></figure><h4><strong>Visualization</strong></h4><p>Let’s now visualize the data using Pandas and Matplotlib. We will convert the top products DataFrame to Pandas and create a horizontal bar chart.Let’s visualize the top 10 best-selling products:</p><pre>top_products_pd = top_products.limit(10).toPandas()<br><br>plt.figure(figsize=(12, 6))<br>plt.barh(top_products_pd[&#39;Description&#39;], top_products_pd[&#39;sum(Quantity)&#39;], color=&#39;skyblue&#39;)<br>plt.xlabel(&quot;Total Sales&quot;)<br>plt.ylabel(&quot;Products&quot;)<br>plt.title(&quot;Top 10 Best Selling Product&quot;)<br>plt.gca().invert_yaxis()<br>plt.show()</pre><p>top_products_pd = top_products.limit(10).toPandas():</p><p>↠ top_products: This is likely a Spark DataFrame that contains information about products and their sales.</p><p>↠ .limit(10): This method selects only the first 10 rows (the top 10 best-selling products).</p><p>↠ .toPandas(): This method converts the Spark DataFrame into a Pandas DataFrame. Pandas is a Python library commonly used for data manipulation and analysis. This conversion allows for easier manipulation and visualization of the data.</p><p>↠ plt.figure(figsize=(12, 6)):This function, from the matplotlib.pyplot library, sets the size of the plot. figsize=(12, 6) specifies that the width of the plot is 12 units and the height is 6 units.</p><p>plt.barh(top_products_pd[&#39;Description&#39;],top_products_pd[&#39;sum(Quantity)&#39;], color=&#39;skyblue&#39;):</p><p>↠ plt.barh(): This function creates a horizontal bar chart.</p><p>↠ top_products_pd[&#39;Description&#39;]: This column contains the names or descriptions of the products, which are displayed on the y-axis of the chart.</p><p>↠ top_products_pd[&#39;sum(Quantity)&#39;]: This column contains the total sales quantity for each product (e.g., total units sold). This determines the length of each bar.</p><p>↠ color=&#39;skyblue&#39;: Sets the color of the bars to light blue.</p><p>↠ plt.xlabel(&quot;Total Sales&quot;): This line adds the label “Total Sales” to the x-axis.</p><p>↠ plt.ylabel(&quot;Products&quot;): This line adds the label “Products” to the y-axis.</p><p>↠ plt.title(&quot;Top 10 Best Selling Product&quot;): This line sets the title of the chart to “Top 10 Best Selling Product,” which describes the data being visualized.</p><p>↠ plt.gca().invert_yaxis(): This line inverts the y-axis order. By default, the y-axis is ordered from bottom to top, but this command places the best-selling product at the top. It improves the readability of the chart.</p><p>↠ plt.show(): This command displays the plot. It makes the chart visible on the screen.</p><p>Let’s check our output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/945/1*xyYLToaFsLrx3-drGaJXLw.png" /></figure><p>Now let’s examine the monthly sales trend:</p><pre>monthly_sales_pd = monthly_sales.toPandas()<br><br>plt.figure(figsize=(14, 6))<br>plt.plot(monthly_sales_pd[&#39;Month&#39;], monthly_sales_pd[&#39;sum(Quantity)&#39;], marker=&#39;o&#39;, linestyle=&#39;-&#39;, color=&#39;blue&#39;)<br>plt.xlabel(&quot;Month&quot;)<br>plt.ylabel(&quot;Total Sales&quot;)<br>plt.title(&quot;Monthly Sales Trend&quot;)<br>plt.grid(True)<br>plt.show()</pre><p>⊛ marker=&#39;o&#39;: This option adds circular markers at each data point on the line.</p><p>⊛ linestyle=&#39;-&#39;: This specifies that the line connecting the data points will be solid.</p><p>⊛ color=&#39;blue&#39;: This sets the color of the line to blue.</p><p>⊛ plt.grid(True):This line adds a grid to the plot, making it easier to read and interpret the data points along the axes.</p><p>Let’s check our output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/945/1*pKxW8eaw9VvrFBqwQ7bjIw.png" /></figure><p>Let’s look at the sales distribution by customer type:</p><pre>customer_type_counts = df_cleaned.groupBy(&quot;CustomerType&quot;).count().toPandas()<br><br>plt.figure(figsize=(8, 6))<br>plt.pie(customer_type_counts[&#39;count&#39;], labels=customer_type_counts[&#39;CustomerType&#39;], autopct=&#39;%1.1f%%&#39;, colors=[&#39;lightcoral&#39;, &#39;skyblue&#39;])<br>plt.title(&quot;Sales Distribution by Customer Types (B2B vs B2C)&quot;)<br>plt.show()</pre><p>Let’s check our output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/943/1*nEYIF2Ld-4c13AEhcL_I4g.png" /></figure><p>Finally, let’s examine the sales distribution by day of the week. First, let’s write our code:</p><pre>from pyspark.sql.functions import dayofweek<br><br>df_cleaned = df_cleaned.withColumn(&quot;DayOfWeek&quot;, dayofweek(col(&quot;InvoiceDate&quot;)))<br>weekly_sales = df_cleaned.groupBy(&quot;DayOfWeek&quot;).sum(&quot;Quantity&quot;).orderBy(&quot;DayOfWeek&quot;).toPandas()<br><br>plt.figure(figsize=(10, 6))<br>plt.bar(weekly_sales[&#39;DayOfWeek&#39;], weekly_sales[&#39;sum(Quantity)&#39;], color=&#39;orange&#39;)<br>plt.xlabel(&quot;Day of the Week&quot;)<br>plt.ylabel(&quot;Total Sales&quot;)<br>plt.title(&quot;Sales distribution by day of the week&quot;)<br>plt.show()</pre><p>from pyspark.sql.functions import dayofweek:</p><p>◍ This imports the dayofweek function from PySpark&#39;s SQL functions module. The dayofweek function extracts the day of the week from a date or timestamp column, where Sunday is 1, Monday is 2, and so on, until Saturday, which is 7.</p><p>Let’s examine our final output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/945/1*pnbGC13BkpJO9Uoq30nCiQ.png" /></figure><h4><strong>Conclusion</strong></h4><p>In this article, we thoroughly explored the data analysis process using the “Online Retail” dataset. After data cleaning and preparation, we generated key features like shopping frequency and customer types. We also analyzed metrics such as top-selling products, sales trends, and weekly sales distributions. The visualizations provided valuable insights, helping e-commerce businesses optimize their sales strategies.</p><p>As we continue advancing in data engineering and analytics, working with large datasets and deriving meaningful insights becomes increasingly important. This project provides a solid foundation for those who want to learn how to analyze and visualize e-commerce data, and it serves as a starting point for more advanced studies and models.</p><p>Your feedback is highly valuable to me! Thanks for reading my project, and I wish you all the best in your work!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*WTRcCLZVBg9Te9WW0aEUBA.gif" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=dec905bf963b" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>