<?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 Furkan Akdağ on Medium]]></title>
        <description><![CDATA[Stories by Furkan Akdağ on Medium]]></description>
        <link>https://medium.com/@furkannakdagg?source=rss-dd7a541643d8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*H7NLgZ-00DsaB0_H9XTVsA.jpeg</url>
            <title>Stories by Furkan Akdağ on Medium</title>
            <link>https://medium.com/@furkannakdagg?source=rss-dd7a541643d8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 14:07:20 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@furkannakdagg/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[SQL Server Installation on Mac M1]]></title>
            <link>https://medium.com/@furkannakdagg/sql-server-installation-on-mac-m1-8c575094f73e?source=rss-dd7a541643d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c575094f73e</guid>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[m1]]></category>
            <category><![CDATA[macbook]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Furkan Akdağ]]></dc:creator>
            <pubDate>Fri, 02 Sep 2022 11:35:21 GMT</pubDate>
            <atom:updated>2022-09-02T11:35:21.086Z</atom:updated>
            <content:encoded><![CDATA[<p>SQL Server Installation on M1 Computer with Docker</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E_-hKrE2A4TgWU4sH7HV9Q.jpeg" /></figure><p>Hello everyone,</p><p>Although Windows users have options such as VMWare and Oracle VirtualBox to use SQL Server, one of the problems experienced by Mac users is the lack of such an application. Especially in computers with M1 chip introduced by Apple in 2020, the optimization process has not been completed yet. Even Apple’s own application, Boot Camp Assistant, which is frequently used by Apple users who want to install Windows on their computers, cannot yet be used on computers with M1 chips. That’s why users started looking for alternative ways. At this point, Docker saves us. We will see how to install SQL server on a computer with M1 chip in a few steps without the need for deep Docker knowledge.</p><h3>Step 1</h3><p>Let’s do our downloads first. The applications we will use are Docker and Azure Data Studio.</p><ul><li>Docker for Apple Silicon: <a href="https://docs.docker.com/desktop/mac/apple-silicon/">https://docs.docker.com/desktop/mac/apple-silicon/</a></li></ul><blockquote><em>We need to download Docker from the “Mac with Apple Chip” option from the “Download Docker Desktop” section.</em></blockquote><p>The file we download here comes with the “.dmg” extension, we install Docker by dragging and dropping it to the “Applications” file in a classic and easy way.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8DXvhEu2py2An07pdmfCDw.png" /></figure><ul><li>Azure Data Studio: <a href="https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16">https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16</a></li></ul><blockquote><em>We need to download the .zip file in front of macOS from the “Download Azure Data Studio” section.</em></blockquote><p>The file we download from this link comes ready as “Azure Data Studio”. If it downloaded as a zip file, you can access this application from within by opening the folder.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/208/1*SgvaMuRSxvEpoNTMP3CO9A.png" /></figure><h3>Step 2</h3><p>After our installations are complete, let’s open Docker. A screen like the one below will meet us and we click on the “Sign In” option from the top right.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C6TiDSWykYn3DHDvs3k2wg.png" /></figure><p>After clicking on the “Sign In” option, the window shown below will open in front of us. Those who do not have a Docker account need to quickly create an account at this stage, and those who have an account need to log in.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SjF0PV0uqm4J_iXG1buvjw.png" /></figure><p>If you do not have an account on this page, you can quickly create an account with “Sign Up” at the top right, or you can log in if you have an account. After logging into the account, the page will ask to redirect you back to the Docker application, accept this and proceed.</p><p>In order to check the process we have done, open Terminal and write docker. If you see a help menu like the one below, it means that the process has been successful.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*huo2HmCqMT34r0nTJaztzA.png" /><figcaption>When we type docker in Terminal, a help menu should come up, starting like this and continuing.</figcaption></figure><h3>Step 3</h3><p>Next is to perform the necessary SQL server installation operations via Docker.</p><p>Copy and paste the code below into the terminal.</p><pre>docker run -e “ACCEPT_EULA=1” -e “MSSQL_SA_PASSWORD=Miuulsql2022” \<br>-e “MSSQL_PID=Developer” -e “MSSQL_USER=SA” -p 1433:1433 -d \<br> — name=mssql mcr.microsoft.com/azure-sql-edge</pre><p>If you want, you can write the password you want in the MSSQL_SA_PASSWORD part, but it is important that DON’T FORGET the password you wrote. When you run this code on the terminal, the necessary files will be downloaded to your computer via Docker Hub. Download time will vary depending on your internet speed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0hKoOODy_RXSu0Z4u_8kaQ.png" /></figure><p>After these processes, let’s check if the Container has came up, it means that check everything is going well until here. In order to check this, let’s open our Docker application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QTL_WzPB8nkO_QQnDB_glQ.png" /></figure><p>When we come to the “Containers” section from the left menu, a container named “mssql” has been created as seen above, and if we look under STATUS, we can see that it is in Running mode, so our operations are successful.</p><p>Now we can perform our connection operations in Azure Data Studio.</p><h3>Step 4</h3><p>Open Azure Data Studio and click “New Connection” as below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NMbQf2qU9sJhv1fQ5NdWIQ.png" /></figure><p>After clicking, a window will open where we need to enter the Connection information.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ufCPEwIrrnus3GrvkoRDiQ.png" /></figure><ul><li>Server = localhost</li><li>User name = sa</li><li>Password = Miuulsql2022</li></ul><p>We do not need to make any adjustments for the other parts.</p><p><strong>NOTE:</strong> If you used a different password as the password in step 3, you must enter the password you chose here.</p><p>After clicking on the “Connect” option, a page like the one below will appear.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pr1Sr-KDmBVAJwwecwhumg.png" /></figure><p>This page shows that we were able to successfully connect to the SQL server.</p><h3>Step 5</h3><p>Next is the process of importing Database restore files into this SQL. What we will implement in this step is important.</p><p>We put the database you have and want to work on into a file and copy and paste that file under the “Documents” folder on your computer. For this, I put the database I want to examine in a file named “dbfiles” and copied and pasted it under the “Documents” option. Unfortunately, I cannot share my own file here, but you can easily find examples by searching the internet as “MSSQL Sample Database”. A site where you can find sample databases for guidance: <a href="https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&amp;tabs=ssms">https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&amp;tabs=ssms</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KIBYdnoHRNs5qEQb3wz_rg.png" /></figure><p>After adding this, open Terminal and write the following codes respectively.</p><pre>docker exec -it mssql mkdir “/var/opt/mssql/backup”</pre><pre>docker cp /Users/$USER/Documents/dbfiles/* mssql:/var/opt/mssql/backup/</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LNkpCNa0r4n_31Kiwv-EKQ.png" /></figure><p>These lines will not output any output, don’t worry.</p><p>To summarize the process we did here: We created an isolated environment inside our computer and sent the files we wanted from outside into this isolated environment. Currently, we can add the restore files to Azure Data Studio.</p><h3>Step 6</h3><p>Open Azure Data Studio. Click on the “Restore” option in the upper right.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tNKhVbfpbZA4x6Bu331CTw.png" /></figure><p>In the window that opens:</p><p>We change the Restore from option to Backup File, and add the database with Backup file path. Summarily,</p><ul><li>Restore from = Backup File</li><li>Backup file path = var-opt-mssql-backup-[YOUR FILE NAME]</li></ul><p><strong>NOTE:</strong> I have the file name SALES.BAK, it will be different for you!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kwsS0wunAkxhcRwLq2lW8Q.png" /><figcaption><strong>Backup file path</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B8mScCM9M6weQI6fHWPmLg.png" /><figcaption><strong>The final state of the screen after making the adjustments</strong></figcaption></figure><p>We do not need to make any changes other than the Restore from and Backup file path sections. My filename is SALES and it auto-filled the other parts. We continue by saying “Restore” from the bottom right.</p><p>When you see the text “Restore Database succeeded” at the bottom, it means that your restore operation was successful.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Hji2PzVz1NK8DJadgusmRg.png" /></figure><p>If you have the menu on the left closed, you can open it by clicking the Connection symbol above the magnifier symbol. After opening it, we refresh it by saying “Refresh” first.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cljApLLRMm9MeBsIDK9vrg.png" /></figure><p>Then we open our databases by clicking on the “Databases” folder and our database will be visible at the bottom.</p><h3>Step 7</h3><p>To create a new query, right click on our database and click “New Query”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IbN1ylKKhd15vLqn9s3YrQ.png" /></figure><p>We can make the queries we want on the screen that opens. To check, let’s quickly write 1 line and run it with the “Run” command above.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v-hh7dkQhsnu82XcH74VWA.png" /></figure><p>As you can see, we were able to successfully open a query and get our outputs.</p><h4>That’s all folks!</h4><p>In the next process, if our Container (mssql in step 3) is running in Docker Desktop, there will be no problems in our operations.</p><p>I hope it was helpful, thank you for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c575094f73e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mac M1 Bilgisayarlara SQL Server Kurulumu]]></title>
            <link>https://medium.com/@furkannakdagg/mac-m1-bilgisayarlara-sql-kurulumu-f3649731b1ec?source=rss-dd7a541643d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/f3649731b1ec</guid>
            <category><![CDATA[mssql]]></category>
            <category><![CDATA[macbook]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[m1]]></category>
            <category><![CDATA[azure-data-studio]]></category>
            <dc:creator><![CDATA[Furkan Akdağ]]></dc:creator>
            <pubDate>Fri, 02 Sep 2022 11:13:02 GMT</pubDate>
            <atom:updated>2022-09-02T11:38:50.730Z</atom:updated>
            <content:encoded><![CDATA[<p>Docker ile M1 Bilgisayara SQL Server Kurulumu</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E_-hKrE2A4TgWU4sH7HV9Q.jpeg" /></figure><p>Merhabalar,</p><p>SQL Server kullanmak için Windows kullanıcılarının elinde VMWare, Oracle VirtualBox gibi seçenekler olsa da Mac kullanıcılarının yaşadığı sıkıntılardan birisi bu tarz bir uygulamanın eksikliği. Özellikle 2020 yılında Apple’ın tanıttığı M1 çipli bilgisayarlarda henüz optimizasyon süreci tamamlanmamış durumda. Öyle ki bilgisayarlarına Windows kurmak isteyen Apple kullanıcılarının sıklıkla kullandığı ve Apple’ın kendi uygulaması olan Boot Camp Yardımcısı dahi M1 çipli bilgisayarlarda henüz kullanılamamakta. Durum böyle olunca kullanıcılar alternatif yollar aramaya başladılar. Bu noktada bizi Docker kurtarıyor. Derin bir Docker bilgisine gerek olmadan birkaç adımda M1 çipli bir bilgisayara nasıl SQL server kurulacağını göreceğiz.</p><h3>1. Adım</h3><p>Öncelikle indirmelerimizi yapalım. Kullanacağımız uygulamalar Docker ve Azure Data Studio.</p><ul><li>Apple Silicon için Docker: <a href="https://docs.docker.com/desktop/mac/apple-silicon/">https://docs.docker.com/desktop/mac/apple-silicon/</a></li></ul><blockquote><em>“Download Docker Desktop” bölümünden “Mac with Apple Chip” seçeneğinden Docker’ı indirmemiz gerekiyor.</em></blockquote><p>Burada indirdiğimiz dosya “.dmg” uzantısıyla geliyor, klasik ve kolay bir şekilde “Applications” dosyasına sürükle bırak yaparak Docker’ı kurmuş oluyoruz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8DXvhEu2py2An07pdmfCDw.png" /></figure><ul><li>Azure Data Studio: <a href="https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16">https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16</a></li></ul><blockquote><em>“Download Azure Data Studio” bölümünden macOS’un karşısındaki .zip file dosyasını indirmemiz gerekiyor.</em></blockquote><p>Bu linkten indirdiğimiz dosyaysa hazır olarak “Azure Data Studio” olarak geliyor. Eğer bir zip dosyası halinde indirdiyse, klasörü açarak içinden bu uygulamaya erişebilirsiniz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/208/1*SgvaMuRSxvEpoNTMP3CO9A.png" /></figure><h3>2. Adım</h3><p>Kurulumlarımız tamamlandıktan sonra Docker’ı açalım. Bizi aşağıdaki gibi bir ekran karşılayacak ve sağ üstten “Sign In” seçeneğine tıklıyoruz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C6TiDSWykYn3DHDvs3k2wg.png" /></figure><p>“Sign In” seçeneğine tıkladıktan sonra önümüze aşağıda gözüken pencere açılacak. Docker hesabı olmayanların bu aşamada hızlıca bir hesap oluşturması, hesabı olanlarınsa giriş yapması gerekiyor.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SjF0PV0uqm4J_iXG1buvjw.png" /></figure><p>Bu sayfada hesabınız yoksa sağ üstteki “Sign Up” ile hızlıca hesap oluşturabilir veya hesabınız varsa da giriş yapabilirsiniz. Hesaba giriş yaptıktan sonra sayfa sizi tekrardan Docker uygulamasına geri yönlendirmeyi soracaktır, bunu da kabul ederek ilerleyin.</p><p>Yaptığımız işlemi kontrol etmek adına Terminalimizi açıyoruz ve docker yazıyoruz. Eğer önünüze aşağıdaki gibi bir yardım menüsü çıktıysa işlem başarılı olmuş demektir.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*huo2HmCqMT34r0nTJaztzA.png" /><figcaption>Terminal’e docker yazdığımızda bu şekilde başlayıp daha da devam eden bir yardım menüsü gelmeli.</figcaption></figure><h3>3. Adım</h3><p>Sırada Docker üzerinden gerekli SQL server kurulum işlemlerini gerçekleştirmek var.</p><p>Aşağıdaki kodu kopyalayıp olduğu gibi terminalimize yapıştırıyoruz.</p><pre>docker run -e “ACCEPT_EULA=1” -e “MSSQL_SA_PASSWORD=Miuulsql2022” \<br>-e “MSSQL_PID=Developer” -e “MSSQL_USER=SA” -p 1433:1433 -d \<br> — name=mssql mcr.microsoft.com/azure-sql-edge</pre><p>Dilerseniz MSSQL_SA_PASSWORD kısmına istediğiniz şifreyi yazabilirsiniz fakat yazdığınız şifreyi unutmamanız önemli. Bu kodu terminalde çalıştırmanızla beraber Docker Hub üzerinden gerekli dosyaları bilgisayarınıza inecektir. İndirme süresi internet hızınıza göre değişiklik gösterecektir.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0hKoOODy_RXSu0Z4u_8kaQ.png" /></figure><p>Bu işlemlerden sonra Container ayağa kalkmış mı, yani her şey buraya kadar yolunda mı gidiyor diye kontrol edelim. Docker uygulamamızı açalım.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QTL_WzPB8nkO_QQnDB_glQ.png" /></figure><p>Soldaki menüden “Containers” kısmına geldiğimizde yukarıda da gözüktüğü gibi “mssql” adında bir container oluştu ve STATUS’un altına bakarsak Running modunda olduğunu görebiliriz, yani işlemlerimiz başarılı.</p><p>Artık Azure Data Studio içerisinde bağlantı işlemlerimizi gerçekleştirebiliriz.</p><h3>4. Adım</h3><p>Azure Data Studio’yu açıp aşağıdaki gibi “New Connection” seçeneğine tıklıyoruz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NMbQf2qU9sJhv1fQ5NdWIQ.png" /></figure><p>Tıkladıktan sonra önümüze Connection bilgilerini girmemiz gereken bir pencere açılacak.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ufCPEwIrrnus3GrvkoRDiQ.png" /></figure><ul><li>Server = localhost</li><li>User name = sa</li><li>Password = Miuulsql2022</li></ul><p>Diğer kısımlar için herhangi bir ayarlama yapmamız gerekmemekte.</p><p><strong>NOT: </strong>Eğer 3. adımda parola olarak farklı bir parola kullandıysanız, burada seçtiğiniz parolayı girmelisiniz.</p><p>“Connect” seçeneğine tıkladıktan sonra bizi aşağıdaki gibi bir sayfa karşılayacak.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pr1Sr-KDmBVAJwwecwhumg.png" /></figure><p>Bu sayfa, SQL server’a başarılı bir şekilde bağlanabildiğimizi gösterir.</p><h3>5. Adım</h3><p>Sırada, Database restore dosyalarını bu SQL içerisine import etme işlemi var. Bu adımda uygulayacaklarımız önemli.</p><p>Elinizde bulunan ve üzerinde çalışmak istediğiniz database’i bir dosyaya koyup o dosyayı bilgisayarınızdaki “Belgeler” klasörünün altına kopyalayıp yapıştırıyoruz. Ben bunun için incelemek istediğim database’i “dbfiles” adında bir dosyaya koydum ve kopyalayıp “Belgeler” seçeneğinin altına yapıştırdım. Burada kendi dosyamı ne yazık ki paylaşamıyorum fakat siz de internetten “MSSQL Sample Database” şeklinde aratarak örnekleri kolaylıkla bulabilirsiniz. Yol göstermesi adına örnek databaseler bulabileceğiniz bir site: <a href="https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&amp;tabs=ssms">https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&amp;tabs=ssms</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KIBYdnoHRNs5qEQb3wz_rg.png" /></figure><p>Bunu ekledikten sonra Terminalimizi açıyoruz ve sırasıyla aşağıdaki kodları yazıyoruz.</p><pre>docker exec -it mssql mkdir “/var/opt/mssql/backup”</pre><pre>docker cp /Users/$USER/Documents/dbfiles/* mssql:/var/opt/mssql/backup/</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LNkpCNa0r4n_31Kiwv-EKQ.png" /></figure><p>Bu satırlar herhangi bir çıktı vermeyecek, endişelenmeyin.</p><p>Burada yaptığımız işlemi özetlemek gerekirse: Biz bilgisayarımızın içerisine izole bir ortam oluşturduk ve bu izole ortamın içerisine dışarıdan istediğimiz dosyaları gönderdik. Şu anda Azure Data Studio içerisine istediğimiz restore dosyalarını ekleyebiliyoruz.</p><h3>6. Adım</h3><p>Azure Data Studio’yu açıyoruz. Sağ üstte yer alan “Restore” seçeneğine tıklıyoruz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tNKhVbfpbZA4x6Bu331CTw.png" /></figure><p>Açılan pencerede:</p><ul><li>Restore from = Backup File olacak şekilde değiştiriyoruz.</li><li>Backup file path = var-opt-mssql-backup-[DOSYANIZIN İSMİ]</li></ul><p><strong>NOT: </strong>Dosya ismi bende SALES.BAK, sizde farklı olacaktır!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kwsS0wunAkxhcRwLq2lW8Q.png" /><figcaption><strong>Backup file path</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B8mScCM9M6weQI6fHWPmLg.png" /><figcaption><strong>Ayarlamaları yaptıktan sonra ekranın son hali</strong></figcaption></figure><p>Restore from ve Backup file path kısımları dışında bir değişiklik yapmamıza gerek yok. Dosya ismim SALES ve diğer kısımları kendisi otomatik doldurdu. Sağ alttan “Restore” deyip devam ediyoruz.</p><p>Devamında altta “Restore Database succeeded” yazısını gördüğünüzde restore işleminiz başarılı olmuş demektir.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Hji2PzVz1NK8DJadgusmRg.png" /></figure><p>Eğer sizde soldaki menü kapalıysa, büyütecin üstündeki Connection sembolüne tıklayarak açabilirsiniz. Açtıktan sonra önce Refresh diyerek yeniliyoruz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cljApLLRMm9MeBsIDK9vrg.png" /></figure><p>Sonrasında “Databases” klasörüne tıklayarak database’lerimizi açıyoruz ve database’imiz altta gözüküyor olacak.</p><h3>7. Adım</h3><p>Yeni bir query oluşturmak için database’imize sağ tıklayıp “New Query” diyoruz.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IbN1ylKKhd15vLqn9s3YrQ.png" /></figure><p>Açılan ekranda istediğimiz sorguları yapabiliyoruz. Kontrol etmek için hızlıca 1 satır yazıp üstteki “Run” komutuyla çalıştıralım.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v-hh7dkQhsnu82XcH74VWA.png" /></figure><p>Görüleceği üzere başarılı bir şekilde sorgu açıp çıktılarımıza ulaşabildik.</p><h4>İşlemlerimiz bu kadar!</h4><p>Bundan sonraki süreç içerisinde Docker Desktop içerisinde Container’ımız(3. adımdaki mssql) çalışır durumdaysa işlemlerimizde herhangi bir sıkıntı çıkmayacaktır.</p><p>Umarım yararlı olmuştur, okuduğunuz için teşekkür ederim!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f3649731b1ec" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Time Series Statistical Methods Tutorial]]></title>
            <link>https://medium.com/@furkannakdagg/time-series-statistical-methods-tutorial-624ae62a5e29?source=rss-dd7a541643d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/624ae62a5e29</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[time-series-forecasting]]></category>
            <category><![CDATA[time-series-analysis]]></category>
            <category><![CDATA[time-series-model]]></category>
            <category><![CDATA[machine-learning]]></category>
            <dc:creator><![CDATA[Furkan Akdağ]]></dc:creator>
            <pubDate>Thu, 01 Sep 2022 23:23:38 GMT</pubDate>
            <atom:updated>2022-09-01T23:23:38.450Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/750/1*Q5I2EaFygA1EjoyEITjDMA.jpeg" /></figure><h3>TIME SERIES</h3><p>It is data consisting of observation units ordered by time. It could be weather data, stock market data. In other words, it is the data consisting of observation values ordered according to time.</p><p>There are 2 topics to explain:</p><ol><li>Smoothing Methods</li><li>Statistical Methods</li></ol><p>In here, Statistical Methods will be explained. Smoothing Methods has been explained on another article. You can find that article on the link: <a href="https://medium.com/@furkannakdagg/time-series-smoothing-methods-tutorial-3caf75aa8295">https://medium.com/@furkannakdagg/time-series-smoothing-methods-tutorial-3caf75aa8295</a></p><p>There are 4 structural concepts we need to know:</p><p>For both topic, there are 4 structural concepts we need to know:</p><h3>1. Stationary</h3><p>The statistical properties of the series do not change over time. In other words, if the mean, variance and covariance of a time series remain constant over time, the series is said to be stationary.</p><p>Suppose we take different periods in a time series. The fact that their properties do not change over time is that they are close to each other since the averages/standard deviations of the periods are taken. We are not interested in small differences, we are interested in statistical differences.</p><p><strong>Why is stationary significant?:</strong> Theoretically, there is an interpretation that the structure of the time series will be more predictable if it is at a certain stationarity, that is, in a certain pattern. In other words, the next step of a movement that shows a certain structure is also predictable. Therefore, there is usually an expectation: Is the time series stationary? There is an expectation that we can make predictions more easily if it is stationary. Because of this expectation, the first concept we focus on is the concept of stationary. To capture this, we can see this by looking at the time series image instead of statistical tests.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/468/0*JMTDxZY3CaqNC9Xu.png" /></figure><h3>2. Trend</h3><p>It is the structure of the long-term increase or decrease of a time series. If there is a trend, it is very unlikely that the series will be stationary because the statistics of the periods (mean, standard deviation, etc.) will change in an increasing or decreasing trend.</p><h3>3. Seasonality</h3><p>Seasonality is when a time series repeats a certain behavior at certain intervals.</p><h3>4. Cycle</h3><p>It contains repetitive patterns similar to seasonality and these two issues can be confused with each other. Seasonality can be mapped to specific time periods. It overlaps with structures such as day, week, year, season. For example, markets do more business on weekends or a product gets more attention in winter, etc. The cyclicity takes place in a longer time, in a more uncertain structure, in a way that does not overlap with structures such as day, week, year, season. It occurs mostly for structural reasons, with cyclical changes. For example, it is shaped by the speeches of some people from the business world and the speeches of politicians. Although this is not completely seasonal, it occurs in a certain period, but the period in which it will occur is not clear.</p><p><strong>Understanding the Nature of Time Series Models:</strong> We have seen the basic structures of time series above. The assumption underlying the time series: The value of the time series in a period t is most affected by its value in the previous time period (t-1). For example, today is Sunday, the value preceding it most explains the value of the time series on Sunday. Based on this assumption, averaging the previous observation units can be an interpretation. In other words, it is reasonable to carry a seasonality information from the past, it can be concluded that it is reasonable for us to focus on past values.</p><h3>Statistical Methods</h3><p>Smoothing methods can also be considered statistically. It was evaluated in this way because it was mostly referred to as smoothing methods.</p><h3>1. AR(p) — Autoregression</h3><p>A time series is estimated with the delays before it. Difference from smoothing methods: they are in linear form, so here is a coefficient a in a regression form. p =&gt; get delay, AR(2) means take 2 delays. The delays are the previous values of these observation units. For example, when we get 2 delays, the first 2 observation units are NA because there are no 2 values before them, for the 3rd value we bring the 1st value with it, that is, we bring the past values.</p><h3>2. MA(q) — Moving Average</h3><p>The AR model had past real values. Here are errors/residuals from past time periods. MA performs model building based on errors while AR model performs model based on historical real values. In other words, if we bring the AR and MA models together, we get a similarity to the SES method from the smoothing models. (ARMA model)</p><h3>3. ARMA(p, q) = AR(p) + MA(q) — AutoRegressive Moving Average</h3><p>It consists of past real values and residuals. We know this from the SES method, the ARMA model is the brother of SES. The difference: In the SES model, there was a coefficient called the smoothing factor(alpha), it weighted the effects of two terms (past real values and residuals). This model had a serious dependence on alpha and could be thought of as a single form, because they all take shape according to alpha. ARMA is in linear regression form and the weight of the values of the past real values is a1, the coefficient of the residual values formed over the predicted values is m1. They are completely independent of each other. While there is only one coefficient expressing the relative effects of the two terms in SES, the coefficients of the two terms are different from each other and in the form of linear regression in ARMA. In other words, while terms are shaped according to a parameter in Holt-Winters methods, in ARMA models, the terms have their own coefficients and we are interested in finding these coefficients. The data is modeled in a linear form and as a result of this model, the extract of the data is learned. In other words, in SES, we were trying to find the alphas ourselves from the outside, in ARMA we will find the degrees of the model by trying from the outside, but on the other hand, the past true values and coefficients of errors in these models will be found from the data through the model. ![arma.png](attachment:6b07b468–2818–473e-bac7–3d97711c4c20.png)</p><h3>4. ARIMA(p, d, q) — AutoRegressive Integrated Moving Average</h3><p>A similar operation to the above takes place after the difference operation. ARMA was the brother of SES, it was good in trendy and non-seasonal models. ARIMA and SARIMA are more successful models on trend and against seasonality. Therefore, we do not even need to look at ARMA in practice because ARIMA can do what ARMA does. * The prediction is made by a linear combination of differenced observations and errors from previous time steps. * It is suitable for univariate data with a trend but no seasonality. (So it is the brother of DES.) **Why is there an integration process?:** In time series, there was an assumption that forecasting performance degrades when the series is not stationary. A non-stationary series is more unpredictable. Therefore, if we have a series and it is not stationary, then it is necessary to make this series stationary for some statistical methods to be used. We assume that we can make more successful predictions when we make a series stationary. Therefore, the ARIMA method automatically performs the difference between the methods we will use and makes the series stationary. * The I statement in ARIMA means integrated and indicates how many differences will be received. * p (autoregressive degree) =&gt; the number of past true value delays. If p=2, yt-1 and yt-2 are in the model (i.e. added as a variable). * d (degree of difference) =&gt; stands for difference. (Denotes how many differences we want to take to stabilize.) * q (moving average degree) =&gt; represents how many delay expressions to get in residuals (error delay number). If q=2, et-1 and et-2 are in the model. * We’ll try to get the best of all three by trial and error.</p><h3>5. SARIMA(p, d, q)(P, D, Q)m — Seasonal AutoRegressive Integrated Moving Average(Mevsimsel Otoregresif Integrated MA)</h3><p>SARIMA = ARIMA + Seasonality * (p, d, q) parameters from ARIMA, so they are trend elements (because ARIMA is able to model the trend) * (P, D, Q) parameters of SARIMA, so they are seasonal elements. * m is seasonality parameter, the number of time steps for a single seasonal period. Expresses the structure of seasonality. This seasonality cycle refers to whether it is once a week, once a month, or every 12 months etc. The seasonality period is observable when we visualize the graph. It can be used in univariate series with trend and/or seasonality. It is the most advanced method compared to other methods we have seen.</p><h3>SUMMARY</h3><ul><li><strong>Stationary</strong> =&gt; SES, AR, MA, ARMA</li><li><strong>Trend</strong> =&gt; DES, ARIMA, SARIMA</li><li><strong>Trend + Seasonality</strong> =&gt; TES, SARIMA</li></ul><p>Let’s implement these methods on a dataset.</p><h3>Story of Dataset and EDA</h3><p>A dataset from the sm module will be used. It shows the atmospheric C02 in Hawaii according to time.</p><ul><li>Name: Atmospheric CO2 from Continuous Air Samples at Mauna Loa Observatory, Hawaii, U.S.A.</li><li>Period of Record: March 1958 — December 2001</li></ul><pre><em># Import Libraries</em><br>import itertools<br>import warnings<br>import matplotlib.pyplot as plt<br>import numpy as np<br>import pandas as pd<br>import statsmodels.api as sm<br>from sklearn.metrics import mean_absolute_error<br>from statsmodels.tsa.arima.model import ARIMA<br>from statsmodels.tsa.seasonal import seasonal_decompose<br>import statsmodels.tsa.api as smt<br>from statsmodels.tsa.statespace.sarimax import SARIMAX<br>warnings.filterwarnings(&#39;ignore&#39;)</pre><pre>data = sm.datasets.co2.load_pandas()  <em># A dataset from the sm module</em><br>y = data.data  <em># target varible<br></em>y</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/318/1*4_9qBVQb-FoOX0Omk_hiDw.png" /></figure><p>The dates are given in index and CO2’s are given according to dates. BY LOOKING AT THE DATES, WE CAN SAY THAT THIS DATASET IS WEEKLY!<br>WHEN LOOKING AT CO2, WE SHOULD CONVERT IT FROM WEEKLY TO MONTHLY BEFORE IT MORE SIGNIFICANT TO LOOK AT THE MONTHLY!</p><pre><em># MS =&gt; month</em><br>y = y[&#39;co2&#39;].resample(&#39;MS&#39;).mean()</pre><pre>y.isnull().sum()  # output =&gt; 5</pre><p>There are missing values. Missing values in time series are mean, median cannot be filled. It can be filled with values before or after it. It can also be filled by averaging the values before and after it. Since the data is the only variable and we are looking at it as a before and after, it can be filled in such a way that the average of the previous and next value is used instead of averaging the entire data set. Because if there is a trend or seasonality in the series, it is clear that the assignment here may be wrong.</p><pre><em># fill a missing values with the next value</em><br>y = y.fillna(y.bfill())<br>y.isnull().sum() # output =&gt; 0</pre><pre><em># visualize the series and look at its general structure</em><br>y.plot(figsize=(10, 7))<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VMSJysrAw-ktr79G.png" /></figure><p>There is a trend in the series, it is not stationary because its statistical properties change with time, there is also seasonality.</p><p>We will move on to the modeling process, but <strong>ATTENTION</strong>:</p><p>In classical statistical methods, instead of model validation methods such as train-test separation and CV method, the model is built on all data, the success of the model is tested on all data, and the necessary internal or external parameters are found with efforts to be made on all data.</p><h3>Hold-out</h3><p>Hold-out method is used because models tend to memorize and overfit, whether they are time series or deep learning methods when training models. Therefore, we should use some methods to prevent this and to evaluate the errors more accurately and to validate the models. That’s why we split the dataset as train-test.</p><pre><em># let the train set be the part from 1958 to the end of 1997</em><br>train = y[:&#39;1997-12-01&#39;]<br>print(&quot;Lenght of train&quot;, len(train))  <em># 478 months</em></pre><pre><em># test set from the first month of 1998 to the end of 2001.</em><br>test = y[&#39;1998-01-01&#39;:]<br>print(&quot;Lenght of test&quot;, len(test))  <em># 48 months</em></pre><p><strong>QUESTION:</strong> We were using CV methods in ML methods, isn’t CV more efficient?</p><blockquote><strong>ANSWER:</strong> The data we have is time series data. When we break it into pieces and test it, trends break, seasonality scatter. Therefore, this process will not be healthy. This process can be done, but time periods should be considered.</blockquote><h3>ARIMA(p, d, q) — (Autoregressive Integrated Moving Average)</h3><p>We can use it in series with trend, but not the series with the seasonality.</p><pre><em># build ARIMA model</em><br><em># order(p, d, q), </em><br>arima_model = ARIMA(train, order=(1, 1, 1)).fit()</pre><pre>arima_model.summary() <em># statistical output of the model</em></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/942/1*isC9xMEjqif2crPFMMSHpA.png" /></figure><ul><li>Model =&gt; shows the degrees of the model</li><li>AIC, BIC, HQIC =&gt; Akaike information criteria, the lower the better</li></ul><p><strong>ATTENTION:</strong> We are not interested in statistics, our main objective is to try the models and choosing the best model and estimate.</p><pre><em># prediction</em><br><em># predict after 48 periods (test set 48 units)</em><br>y_pred = arima_model.forecast(48)[0] <br><em># 0. index is the expressions we expect, we choose that index</em></pre><h3>Function for Visualization</h3><p>Since we will use the visualization often later, let’s make both the graph and the MAE functional.</p><pre>def plot_co2(train, test, y_pred, title):<br>    mae = mean_absolute_error(test, y_pred)<br>    train[&quot;1985&quot;:].plot(legend=True, label=&quot;TRAIN&quot;, title=f&quot;<strong>{</strong>title<strong>}</strong>, MAE: <strong>{</strong>round(mae,2)<strong>}</strong>&quot;)<br>    test.plot(legend=True, label=&quot;TEST&quot;, figsize=(6, 4))<br>    y_pred.plot(legend=True, label=&quot;PREDICTION&quot;)<br>    plt.show()</pre><p>Our prediction visualization function expects Pandas series and df from us, so we convert it to pandas series.</p><pre>y_pred = pd.Series(y_pred, index=test.index)</pre><pre>plot_co2(train, test, y_pred, &quot;ARIMA&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/816/1*1GvJhAwcRhVUEFe2n8tIZw.png" /></figure><h3>Hyperparameter Optimization (Determining Model Degree)</h3><p>It is referred to as model degree in statistical sources and as hyperparameter in ML sources. There are 2 common methods:</p><ol><li>Looking at AIC&amp;BIC Statistics</li><li>Determining the model grade according to the ACF&amp;PACF graphs</li></ol><p>There are points where ACF&amp;PACF graphs are insufficient, at these points, AIC&amp;BIC test statistics are used. So we will look at AIC&amp;BIC.</p><h3>Determining Model Grade Based on AIC &amp; BIC Statistics</h3><p>AIC &amp; BIC = Akaike Information Criteria, the lower the better</p><pre><em># values can be given smaller to faster process</em><br>p = d = q = range(0, 6)</pre><pre><em># brings together all possible combinations</em><br>pdq = list(itertools.product(p, d, q))</pre><p>In some cases, errors occur in ARIMA models, we use try-except because we want to ignore these errors and continue the loop. Our purpose is to say “don’t stop working, continue” to the function.</p><p>In [18]:</p><pre><em># report can be set as False to avoid output crowd</em><br>def arima_optimizer_aic(train, orders, report=False):<br>    best_aic, best_params = float(&quot;inf&quot;), None<br>    for order <strong>in</strong> orders:<br>        try:<br>            arima_model_result = ARIMA(train, order=order).fit()<br>            aic = arima_model_result.aic  <em># gets the AIC statistic</em><br>            if aic &lt; best_aic:<br>                best_aic, best_params = aic, order<br>                if report:<br>                    print(&#39;ARIMA<strong>%s</strong> AIC=<strong>%.2f</strong>&#39; % (order, aic))<br>        except:<br>            continue<br>    print(&#39;Best ARIMA<strong>%s</strong> AIC=<strong>%.2f</strong>&#39; % (best_params, best_aic))<br>    return best_params</pre><pre>best_params_aic = arima_optimizer_aic(train, pdq, report=True)</pre><p>So, our best p, d, q values are 4, 1, 4. Now, create a final model with these values.</p><h3>Final Model for ARIMA</h3><pre><em># final model</em><br>arima_model = ARIMA(train, order=best_params_aic).fit()</pre><pre><em># prediction</em><br>y_pred = arima_model.forecast(48)[0]</pre><pre>y_pred = pd.Series(y_pred, index=test.index)</pre><pre>plot_co2(train, test, y_pred, &quot;ARIMA&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/898/1*UzqK2Qj7_REPpdfsnQgqKA.png" /></figure><h3>SARIMA(p, d, q)(P, D, Q) — (Seasonal Autoregressive Integrated Moving-Average)</h3><p>We will have 6 values as those who will come from ARIMA(p, d, q) and those who will be found in SARIMA(P, D, Q) itself. So we have to get combinations of these.</p><p>Since there are many arguments, we make the model and the fit separately.</p><ul><li>order =&gt; trend components from ARIMA(p, d, q)</li><li>seasonal_order =&gt; seasonal components from SARIMA(P, D, Q)</li></ul><pre>model = SARIMAX(train, order=(1, 0, 1), seasonal_order=(0, 0, 0, 12))</pre><pre><em># disp=0 to avoid output crowd</em><br>sarima_model = model.fit(disp=0)  <em># fit the model</em></pre><p>We used .forecast() before for predicted values, here we use get_forecast()</p><pre><em># predict</em><br><em># steps=size of test data</em><br>y_pred_test = sarima_model.get_forecast(steps=48)</pre><pre><em># for the visualization function we need to convert it to pd series or df</em><br>y_pred = pd.Series(y_pred, index=test.index)</pre><pre>plot_co2(train, test, y_pred, &quot;SARIMA&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*8tHMGorx83owHeuU0OKtdA.png" /></figure><p>As it can be seen, even hyperparameter optimization has not implemented, MAE of SARIMA became close to MAE of ARIMA.</p><h3>Hyperparameter Optimization (Determining Model Degree)</h3><ul><li>p, d, q =&gt; ARIMA Parameters. Wider range can be entered, but here, narrow range is given to complete process in the short time.</li><li>seasonal_pdq =&gt; SARIMA Parameters(P, D, Q, s) =&gt; Seasonality expression(12) is added next to all possible combinations. Since we know that a cycle is completed once in 12 months, we write 12 directly.</li></ul><pre>p = d = q = range(0, 2)<br>pdq = list(itertools.product(p, d, q))<br>seasonal_pdq = [(x[0], x[1], x[2], 12) for x <strong>in</strong> list(itertools.product(p, d, q))]</pre><h3>Determining Model Grade Based on AIC Statistics</h3><pre>def sarima_optimizer_aic(train, pdq, seasonal_pdq):<br>    best_aic, best_order, best_seasonal_order = float(&quot;inf&quot;), None, None<br>    for param <strong>in</strong> pdq:<br>        for param_seasonal <strong>in</strong> seasonal_pdq:<br>            try:<br>                sarimax_model = SARIMAX(train, order=param, seasonal_order=param_seasonal)<br>                results = sarimax_model.fit(disp=0)<br>                aic = results.aic<br>                if aic &lt; best_aic:<br>                    best_aic, best_order, best_seasonal_order = aic, param, param_seasonal<br>                print(&#39;SARIMA<strong>{}</strong>x<strong>{}</strong>12 - AIC:<strong>{}</strong>&#39;.format(param, param_seasonal, aic))<br>            except:<br>                continue<br>    print(&#39;SARIMA<strong>{}</strong>x<strong>{}</strong>12 - AIC:<strong>{}</strong>&#39;.format(best_order, best_seasonal_order, best_aic))<br>    return best_order, best_seasonal_order</pre><pre>best_order, best_seasonal_order = sarima_optimizer_aic(train, pdq, seasonal_pdq)</pre><h3>Final Model for SARIMA</h3><pre>model = SARIMAX(train, order=best_order, seasonal_order=best_seasonal_order)<br>sarima_final_model = model.fit(disp=0)</pre><pre>y_pred_test = sarima_final_model.get_forecast(steps=48)</pre><pre>y_pred = y_pred_test.predicted_mean<br>y_pred = pd.Series(y_pred, index=test.index)</pre><pre>plot_co2(train, test, y_pred, &quot;SARIMA&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/1*nV3ygVUtoI3hDFO_S0COBg.png" /></figure><p>MAE decreases dramatically after hyperparameter tuning to SARIMA, eventhough narrow range has been given to p, d, q, P, D, Q.</p><h3>BONUS: SARIMA Optimization by MAE</h3><p>AIC result was considered above. The same process can be done with looking MAE values. A function will be written like above with a slight change.</p><pre>p = d = q = range(0, 2)<br>pdq = list(itertools.product(p, d, q))<br>seasonal_pdq = [(x[0], x[1], x[2], 12) for x <strong>in</strong> list(itertools.product(p, d, q))]</pre><pre>def sarima_optimizer_mae(train, pdq, seasonal_pdq):<br>    best_mae, best_order, best_seasonal_order = float(&quot;inf&quot;), None, None<br>    for param <strong>in</strong> pdq:<br>        for param_seasonal <strong>in</strong> seasonal_pdq:<br>            try:<br>                model = SARIMAX(train, order=param, seasonal_order=param_seasonal)<br>                sarima_model = model.fit(disp=0)<br>                y_pred_test = sarima_model.get_forecast(steps=48)<br>                y_pred = y_pred_test.predicted_mean<br>                mae = mean_absolute_error(test, y_pred)<br>                if mae &lt; best_mae:<br>                    best_mae, best_order, best_seasonal_order = mae, param, param_seasonal<br>                print(&#39;SARIMA<strong>{}</strong>x<strong>{}</strong>12 - MAE:<strong>{}</strong>&#39;.format(param, param_seasonal, mae))<br>            except:<br>                continue<br>    print(&#39;SARIMA<strong>{}</strong>x<strong>{}</strong>12 - MAE:<strong>{}</strong>&#39;.format(best_order, best_seasonal_order, best_mae))<br>    return best_order, best_seasonal_order</pre><pre>best_order, best_seasonal_order = sarima_optimizer_mae(train, pdq, seasonal_pdq)</pre><h3>Final Model for SARIMA with MAE</h3><pre>model = SARIMAX(train, order=best_order, seasonal_order=best_seasonal_order)<br>sarima_final_model = model.fit(disp=0)</pre><pre>y_pred_test = sarima_final_model.get_forecast(steps=48)<br>y_pred = y_pred_test.predicted_mean<br>y_pred = pd.Series(y_pred, index=test.index)</pre><pre>plot_co2(train, test, y_pred, &quot;SARIMA&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/852/1*G1Hb_hSnpK4h0cp3ImJ0KQ.png" /></figure><p>We got the lowest MAE, therefore, it can be said that the best values have been found.</p><h3>Final Model</h3><p>Now that I know my best parameters, I can apply this to all the data and build the final model and estimate the data I don’t have. <strong>WE MADE THE TRAIN-TEST SPLIT FOR MODEL VALIDATION AND HYPERPARAMETER OPTIMIZATION, SO WHEN THE PROCESSES ARE COMPLETE, WE MUST FIT ALL DATA AGAIN!</strong></p><pre><em># y =&gt; the whole dataset, it has been splitted to train-test at the # beginning</em><br>model = SARIMAX(y, order=best_order, seasonal_order=best_seasonal_order) <em># tuned_model</em><br>sarima_final_model = model.fit(disp=0)</pre><p>The model is ready to predict. Now, if we want to make a prediction about the next month that we don’t have:</p><pre>feature_predict = sarima_final_model.get_forecast(steps=1)  <em># 1 month later</em><br>feature_predict = feature_predict.predicted_mean <em># to get the result from the object</em><br>feature_predict</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/750/1*kQN88QYTTd7_ZiikxhYjHw.png" /></figure><p>We made a prediction about the first month of 2002, which I do not have.</p><p><strong>ATTENTION:</strong> We did not have data for the 1st month of 2002, this has not happened yet!</p><pre><em># If we want to obtain the 6-month forecast results</em><br>feature_predict = sarima_final_model.get_forecast(steps=6)  <br>feature_predict = feature_predict.predicted_mean<br>feature_predict</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/860/1*0IzQHqvMidM0QcBqny5KmA.png" /></figure><p>For full explanation and codes, check my Kaggle notebook and GitHub:</p><ul><li><a href="https://www.kaggle.com/code/furkannakdagg/time-series-statistical-methods-tutorial">Time Series Statistical Methods Tutorial</a></li><li><a href="https://github.com/furkannakdagg/time_series_tutorial/blob/main/time-series-statistical-methods-tutorial.ipynb">time_series_tutorial/time-series-statistical-methods-tutorial.ipynb at main · furkannakdagg/time_series_tutorial</a></li></ul><h3>REFERENCE</h3><p>This tutorial and the code on the tutorial have been prepared with the help of a Bootcamp. This Bootcamp is organized by Miuul, the instructor’s name is Vahit Keskin. Check out the website of the company and the LinkedIn page of the instructor if you are interested.</p><p>Company: [Miuul](<a href="https://www.miuul.com/">https://www.miuul.com</a>)</p><p>Instructor: [Vahit Keskin](<a href="https://www.linkedin.com/in/vahitkeskin/">https://www.linkedin.com/in/vahitkeskin/</a>)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=624ae62a5e29" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Time Series Smoothing Methods Tutorial]]></title>
            <link>https://medium.com/@furkannakdagg/time-series-smoothing-methods-tutorial-3caf75aa8295?source=rss-dd7a541643d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/3caf75aa8295</guid>
            <category><![CDATA[time-series-analysis]]></category>
            <category><![CDATA[time-series-model]]></category>
            <category><![CDATA[time-series-forecasting]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Furkan Akdağ]]></dc:creator>
            <pubDate>Thu, 01 Sep 2022 22:40:09 GMT</pubDate>
            <atom:updated>2022-09-01T23:26:29.612Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_nDLlUtS8qvAjbxnXUzHEA.png" /></figure><h3>TIME SERIES</h3><p>It is data consisting of observation units ordered by time. It could be weather data, stock market data. In other words, it is the data consisting of observation values ordered according to time.</p><p>There are 2 topics to explain:</p><ol><li>Smoothing Methods</li><li>Statistical Methods</li></ol><p>In here, Smoothing Methods will be explained. Statistical Methods is explained on another article. You can find that article on the link: <a href="https://medium.com/@furkannakdagg/time-series-statistical-methods-tutorial-624ae62a5e29">https://medium.com/@furkannakdagg/time-series-statistical-methods-tutorial-624ae62a5e29</a></p><p>For both topic, there are 4 structural concepts we need to know:</p><h3>1. Stationary</h3><p>The statistical properties of the series do not change over time. In other words, if the mean, variance and covariance of a time series remain constant over time, the series is said to be stationary.</p><p>Suppose we take different periods in a time series. The fact that their properties do not change over time is that they are close to each other since the averages/standard deviations of the periods are taken. We are not interested in small differences, we are interested in statistical differences.</p><p><strong>Why is stationary significant?:</strong> Theoretically, there is an interpretation that the structure of the time series will be more predictable if it is at a certain stationarity, that is, in a certain pattern. In other words, the next step of a movement that shows a certain structure is also predictable. Therefore, there is usually an expectation: Is the time series stationary? There is an expectation that we can make predictions more easily if it is stationary. Because of this expectation, the first concept we focus on is the concept of stationary. To capture this, we can see this by looking at the time series image instead of statistical tests.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/468/1*WqAjnggB1aBebAnFPjVP0Q.png" /></figure><h3>2. Trend</h3><p>It is the structure of the long-term increase or decrease of a time series. If there is a trend, it is very unlikely that the series will be stationary because the statistics of the periods (mean, standard deviation, etc.) will change in an increasing or decreasing trend.</p><h3>3. Seasonality</h3><p>Seasonality is when a time series repeats a certain behavior at certain intervals.</p><h3>4. Cycle</h3><p>It contains repetitive patterns similar to seasonality and these two issues can be confused with each other. Seasonality can be mapped to specific time periods. It overlaps with structures such as day, week, year, season. For example, markets do more business on weekends or a product gets more attention in winter, etc. The cyclicity takes place in a longer time, in a more uncertain structure, in a way that does not overlap with structures such as day, week, year, season. It occurs mostly for structural reasons, with cyclical changes. For example, it is shaped by the speeches of some people from the business world and the speeches of politicians. Although this is not completely seasonal, it occurs in a certain period, but the period in which it will occur is not clear.</p><p><strong>Understanding the Nature of Time Series Models:</strong> We have seen the basic structures of time series above. The assumption underlying the time series: The value of the time series in a period t is most affected by its value in the previous time period (t-1). For example, today is Sunday, the value preceding it most explains the value of the time series on Sunday. Based on this assumption, averaging the previous observation units can be an interpretation. In other words, it is reasonable to carry a seasonality information from the past, it can be concluded that it is reasonable for us to focus on past values.</p><h3>Smoothing Methods</h3><h3>1. Single Exponential Smoothing(Simple Exponential Smoothing — SES)</h3><p>It is successful only in stationary series. There should be no trends and seasonality. It can model the level(Level can be thought of as the average of the series.). The effects of the past are weighted on the assumption that “the future is more related to the recent past”. SES is suitable for univariate time series without trend and seasonality, it is successful in stationary series.</p><h3>2. Double Exponential Smoothing(DES)</h3><p>The basic approach is the same. In addition to SES, the trend is also taken into account. It is suitable for univariate time series with and without seasonality.</p><h3>3.Triple Exponential Smoothing(TES — Holt-Winters):</h3><p>It is the most advanced smoothing method. This method makes predictions by evaluating the effects of level, trend and seasonality dynamically. It can be used in univariate series with trend and/or seasonality.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Tlbx0jKJQmmT_jodY8UgZg.png" /></figure><p>Let’s implement these methods on a dataset.</p><h3>Story of Dataset and EDA</h3><p>A dataset from the sm module will be used. It shows the atmospheric C02 in Hawaii according to time.</p><ul><li>Name: Atmospheric CO2 from Continuous Air Samples at Mauna Loa Observatory, Hawaii, U.S.A.</li><li>Period of Record: March 1958 — December 2001</li></ul><pre><em># Import Libraries</em><br>import itertools<br>import warnings<br>import matplotlib.pyplot as plt<br>import numpy as np<br>import pandas as pd<br>import statsmodels.api as sm<br>from sklearn.metrics import mean_absolute_error<br><br>from statsmodels.tsa.holtwinters import ExponentialSmoothing<br>from statsmodels.tsa.holtwinters import SimpleExpSmoothing<br>from statsmodels.tsa.seasonal import seasonal_decompose  <em># to split the time series into components</em><br>import statsmodels.tsa.api as smt<br><br>warnings.filterwarnings(&#39;ignore&#39;)</pre><pre>data = sm.datasets.co2.load_pandas()  <em># A dataset from the sm module</em><br>y = data.data  <em># target varible<br></em>y</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/318/1*4_9qBVQb-FoOX0Omk_hiDw.png" /></figure><p>The dates are given in index and CO2’s are given according to dates. BY LOOKING AT THE DATES, WE CAN SAY THAT THIS DATASET IS WEEKLY!<br>WHEN LOOKING AT CO2, WE SHOULD CONVERT IT FROM WEEKLY TO MONTHLY BEFORE IT MORE SIGNIFICANT TO LOOK AT THE MONTHLY!</p><pre><em># MS =&gt; month</em><br>y = y[&#39;co2&#39;].resample(&#39;MS&#39;).mean()</pre><pre>y.isnull().sum() # output =&gt; 5</pre><p>There are missing values. Missing values in time series are mean, median cannot be filled. It can be filled with values before or after it. It can also be filled by averaging the values before and after it. Since the data is the only variable and we are looking at it as a before and after, it can be filled in such a way that the average of the previous and next value is used instead of averaging the entire data set. Because if there is a trend or seasonality in the series, it is clear that the assignment here may be wrong.</p><pre><em># fill a missing values with the next value</em><br>y = y.fillna(y.bfill())<br>y.isnull().sum() # output =&gt; 0</pre><pre><em># visualize the series and look at its general structure</em><br>y.plot(figsize=(10, 7))<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gKLgrqSkUw3VpXDtyWm-lQ.png" /></figure><p>There is a trend in the series, it is not stationary because its statistical properties change with time, there is also seasonality.</p><p>We will move on to the modeling process, but <strong>ATTENTION</strong>:</p><p>In classical statistical methods, instead of model validation methods such as train-test separation and CV method, the model is built on all data, the success of the model is tested on all data, and the necessary internal or external parameters are found with efforts to be made on all data.</p><h3>Hold-out</h3><p>Hold-out method is used because models tend to memorize and overfit, whether they are time series or deep learning methods when training models. Therefore, we should use some methods to prevent this and to evaluate the errors more accurately and to validate the models. That’s why we split the dataset as train-test.</p><pre><em># let the train set be the part from 1958 to the end of 1997</em><br>train = y[:&#39;1997-12-01&#39;]<br>print(&quot;Lenght of train&quot;, len(train))  <em># 478 months</em><br><br><em># test set from the first month of 1998 to the end of 2001.</em><br>test = y[&#39;1998-01-01&#39;:]<br>print(&quot;Lenght of test&quot;, len(test))  <em># 48 months</em></pre><p><strong>QUESTION:</strong> We were using CV methods in ML methods, isn’t CV more efficient?</p><blockquote><strong>ANSWER:</strong> The data we have is time series data. When we break it into pieces and test it, trends break, seasonality scatter. Therefore, this process will not be healthy. This process can be done, but time periods should be considered.</blockquote><h3>SES (Single Exponential Smoothing)</h3><p>SES = Level (Unsuccessful if Trend and Seasonality).</p><p>There is an argument named as “smoothing_level”. The SimpleExpSmoothing method would not give an error if we did not enter the smoothing_level. We’re entering it now, because in the future beta, gamma arguments will come. When we enter them, method will understand which SES, DES or TES method we are using. So we might not have entered smoothing_level here, we did it to raise awareness.</p><pre><em># build SES model</em><br>ses_model = SimpleExpSmoothing(train).fit(smoothing_level=0.5)</pre><pre><em># to predict, .forecast() is used in here</em><br><em># forecast(how many steps/periods will you predict?)</em><br>y_pred = ses_model.forecast(48)  <br><em># since our test set includes 48 months, enter 48</em></pre><p><strong>QUESTION:</strong> There are duplicate values in y_pred, why?</p><blockquote><strong>ANSWER:</strong> Because there is trend, seasonality in data, and SES is not so good when it comes to trend and seasonality. Therefore, the estimates are not that accurate.</blockquote><h3>Function for Visualization</h3><pre>def plot_co2(train, test, y_pred, title):<br>    mae = mean_absolute_error(test, y_pred)<br>    train[&quot;1985&quot;:].plot(legend=True, label=&quot;TRAIN&quot;, title=f&quot;<strong>{</strong>title<strong>}</strong>, MAE: <strong>{</strong>round(mae,2)<strong>}</strong>&quot;)<br>    test.plot(legend=True, label=&quot;TEST&quot;, figsize=(6, 4))<br>    y_pred.plot(legend=True, label=&quot;PREDICTION&quot;)<br>    plt.show()<br><br>plot_co2(train, test, y_pred, &quot;Single Exponential Smoothing&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/840/1*OY-wu3fw943syrTed6QiVw.png" /></figure><h3>Hyperparameter Tuning for SES</h3><pre>def ses_optimizer(train, alphas, step=48):  <em># step for length of test set</em><br><br>    best_alpha, best_mae = None, float(&quot;inf&quot;)<br><br>    for alpha <strong>in</strong> alphas:<br>        ses_model = SimpleExpSmoothing(train).fit(smoothing_level=alpha)<br>        y_pred = ses_model.forecast(step)<br>        mae = mean_absolute_error(test, y_pred)<br><br>        if mae &lt; best_mae:<br>            best_alpha, best_mae = alpha, mae<br><br>        print(&quot;alpha:&quot;, round(alpha, 2), &quot;mae:&quot;, round(mae, 4))<br>    print(&quot;best_alpha:&quot;, round(best_alpha, 2), &quot;best_mae:&quot;, round(best_mae, 4))<br>    return best_alpha, best_mae<br><br>alphas = np.arange(0.8, 1, 0.01)</pre><p>We generate alphas with 0.01 steps from 0.8 to 1. If we wish, we can write from 0.1 to 1 in increments of 0.1, but SES is already a weak model. We know that closer to the historical real values will yield more successful results, so we start from 0.8.</p><pre>best_alpha, best_mae = ses_optimizer(train, alphas) <br><em># save the results(return of func.)<br># best_alpha: 0.99 best_mae: 4.5451</em></pre><h3>Final SES Model</h3><pre>ses_model = SimpleExpSmoothing(train).fit(smoothing_level=best_alpha)<br>y_pred = ses_model.forecast(48)<br><em># In fact, the error we will find here is also found in the above <br># function. </em><br><em># We&#39;re posting here for awareness.</em><br><br>plot_co2(train, test, y_pred, &quot;Single Exponential Smoothing&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/824/1*uZcykmKXtLl_uYAdexA5ZQ.png" /></figure><h3>DES (Double Exponential Smoothing)</h3><p>DES: Level (SES) + Trend</p><p>Some of the series can be multiplicative, some additive.</p><ul><li>y(t) = Level + Trend + Seasonality + Noise =&gt; Additive</li><li>y(t) = Level <em>Trend </em>Seasonality * Noise =&gt; Multiplicative</li></ul><p>In the multiplicative, the functional structure changes in a more dependent way. If the seasonality and residual components are independent from the trend, the series is additive. Seasonality and residual components are multiplicative if they are shaped according to the trend.</p><p>Seasonality and residuals are randomly scattered around 0. The trend did not take them up with it, so it cannot affect them. So this series is <strong>additive.</strong></p><p>Normally we should build both models, we decide to use whichever has the lower error. Here, we examine the graph and say add directly, since residuals and seasonality are independent of the trend. Therefore, the argument is given as “add” directly.</p><pre><em># build DES model</em><br>des_model = ExponentialSmoothing(train, trend=&quot;add&quot;).fit(smoothing_level=0.5, smoothing_trend=0.5)</pre><pre><em># prediction</em><br>y_pred = des_model.forecast(48)</pre><pre><em># plot the results</em><br>plot_co2(train, test, y_pred, &quot;Double Exponential Smoothing&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/802/1*GgRWmv6tX9XMGGM1-cGMFw.png" /></figure><p>Although the result seems good, some unsuccessful estimations were obtained due to the lack of seasonality.</p><h3>Hyperparameter Optimization for DES</h3><pre>def des_optimizer(train, alphas, betas, step=48):<br>    best_alpha, best_beta, best_mae = None, None, float(&quot;inf&quot;)<br>    for alpha <strong>in</strong> alphas:<br>        for beta <strong>in</strong> betas:<br>            des_model = ExponentialSmoothing(train, trend=&quot;add&quot;).fit(smoothing_level=alpha, smoothing_slope=beta)<br>            y_pred = des_model.forecast(step)<br>            mae = mean_absolute_error(test, y_pred)<br>            if mae &lt; best_mae:<br>                best_alpha, best_beta, best_mae = alpha, beta, mae<br>            print(&quot;alpha:&quot;, round(alpha, 2), &quot;beta:&quot;, round(beta, 2), &quot;mae:&quot;, round(mae, 4))<br>    print(&quot;best_alpha:&quot;, round(best_alpha, 2), &quot;best_beta:&quot;, round(best_beta, 2), &quot;best_mae:&quot;, round(best_mae, 4))<br>    return best_alpha, best_beta, best_mae</pre><pre>alphas = np.arange(0.01, 1, 0.10)<br>betas = np.arange(0.01, 1, 0.10)<br><br>best_alpha, best_beta, best_mae = des_optimizer(train, alphas, betas)</pre><h3>Final DES Model</h3><pre>final_des_model = ExponentialSmoothing(train, trend=&quot;add&quot;).fit(smoothing_level=best_alpha, smoothing_slope=best_beta)<br>y_pred = final_des_model.forecast(48)<br>plot_co2(train, test, y_pred, &quot;Double Exponential Smoothing&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/794/1*F8LQDUyGoahMyzQD6qYLGg.png" /></figure><h3>TES (Triple Exponential Smoothing — Holt-Winters)</h3><p>TES = SES + DES + Seasonality</p><p>As we mentioned above, when we enter trend and seasonality as parameters, the method understands that we will use TES.</p><pre><em># build TES model</em><br>tes_model = ExponentialSmoothing(train,<br>                                 trend=&quot;add&quot;,<br>                                 seasonal=&quot;add&quot;,<br>                                 seasonal_periods=12).fit(smoothing_level=0.5,                                                          smoothing_slope=0.5,<br>smoothing_seasonal=0.5)</pre><p>seasonality period = 12, we should observe when we look at the graph =&gt; we enter it as 12 because the years in the data set are shaped by months. In other words, when we enter 12, we give the information that there is a seasonal pattern when the determined monthly period repeats 1 in 12.</p><pre><em># prediction and plot</em><br>y_pred = tes_model.forecast(48)<br>plot_co2(train, test, y_pred, &quot;Triple Exponential Smoothing&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/816/1*KvJWuC8GQzgolARIeT0mwA.png" /></figure><h3>Hyperparameter Optimization for TES</h3><pre>alphas = betas = gammas = np.arange(0.20, 1, 0.10)</pre><p>Normally we have to write 3 loops in the function since we have 3 values. We use itertools for code readability. As output, all possible values of 3 parameters in tuple form have been combined. If we choose 0th, 1st element of abg instead of 3 loops, then we go through all combinations.</p><pre>abg = list(itertools.product(alphas, betas, gammas))</pre><pre>def tes_optimizer(train, abg, step=48):<br>    best_alpha, best_beta, best_gamma, best_mae = None, None, None, float(&quot;inf&quot;)<br>    for comb <strong>in</strong> abg:<br>        tes_model = ExponentialSmoothing(train, trend=&quot;add&quot;, seasonal=&quot;add&quot;, seasonal_periods=12).\<br>            fit(smoothing_level=comb[0], smoothing_slope=comb[1], smoothing_seasonal=comb[2])<br>        <em># her satırın 0., 1., 2. elemanlarını seç ve model kur</em><br>        y_pred = tes_model.forecast(step)<br>        mae = mean_absolute_error(test, y_pred)<br>        if mae &lt; best_mae:<br>            best_alpha, best_beta, best_gamma, best_mae = comb[0], comb[1], comb[2], mae<br>        <em># print([round(comb[0], 2), round(comb[1], 2), round(comb[2], 2), round(mae, 2)])</em><br><br>    print(&quot;best_alpha:&quot;, round(best_alpha, 2), &quot;best_beta:&quot;, round(best_beta, 2), &quot;best_gamma:&quot;, round(best_gamma, 2),<br>          &quot;best_mae:&quot;, round(best_mae, 4))<br><br>    return best_alpha, best_beta, best_gamma, best_mae</pre><pre><br>best_alpha, best_beta, best_gamma, best_mae = tes_optimizer(train, abg)</pre><pre># best_alpha: 0.8 best_beta: 0.5 best_gamma: 0.7 best_mae: 0.606</pre><h3>Final TES Model</h3><p>The argument given above smoothing_slope is the same with smoothing_trend. smoothing_trend is used here to show that they are the same.</p><pre>final_tes_model = ExponentialSmoothing(train, trend=&quot;add&quot;, seasonal=&quot;add&quot;, seasonal_periods=12).\<br>            fit(smoothing_level=best_alpha, smoothing_trend=best_beta, smoothing_seasonal=best_gamma)</pre><pre><em># prediction and plot</em><br>y_pred = final_tes_model.forecast(48)<br><br>plot_co2(train, test, y_pred, &quot;Triple Exponential Smoothing&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/806/1*k8dcJV2tttg_2BiqKXk4pg.png" /></figure><p>The best MAE is obtained with TES.</p><p><strong>NOTE:</strong> If the data set had much data, it could be divided into train-validation-test sets according to certain time periods, then, set up a model with the train set, optimized the hyperparameters with the validation set, and tested with the test set. This is an interpretation and evaluation. We need to assess whether there will be a significant difference. Because it sees the test set we made and adjusts the hyperparameter, it may raise a question such as whether there is a leakage here.</p><p>For full explanation and codes, check my Kaggle notebook and GitHub:</p><ul><li><a href="https://www.kaggle.com/code/furkannakdagg/time-series-smoothing-methods-tutorial">Time Series Smoothing Methods Tutorial</a></li><li><a href="https://github.com/furkannakdagg/time_series_tutorial/blob/main/time-series-smoothing-methods-tutorial.ipynb">time_series_tutorial/time-series-smoothing-methods-tutorial.ipynb at main · furkannakdagg/time_series_tutorial</a></li></ul><h3>REFERENCE</h3><p>This tutorial and the code on the tutorial have been prepared with the help of a Bootcamp. This Bootcamp is organized by Miuul, the instructor’s name is Vahit Keskin. Check out the website of the company and the LinkedIn page of the instructor if you are interested.</p><p>Company: [Miuul](<a href="https://www.miuul.com/">https://www.miuul.com</a>)</p><p>Instructor: [Vahit Keskin](<a href="https://www.linkedin.com/in/vahitkeskin/">https://www.linkedin.com/in/vahitkeskin/</a>)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3caf75aa8295" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Preparation Tutorial]]></title>
            <link>https://medium.com/@furkannakdagg/data-preparation-tutorial-de24212bae1c?source=rss-dd7a541643d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/de24212bae1c</guid>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[data-preprocessing]]></category>
            <category><![CDATA[data-manipulation]]></category>
            <category><![CDATA[feature-engineering]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Furkan Akdağ]]></dc:creator>
            <pubDate>Tue, 02 Aug 2022 16:19:55 GMT</pubDate>
            <atom:updated>2022-08-02T16:19:55.871Z</atom:updated>
            <content:encoded><![CDATA[<h4>FEATURE ENGINEERING &amp; DATA PRE-PROCESSING</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-9Mu85Ur2EQs9AeJXTntBA.png" /></figure><p>Feature engineering and Data Pre-Processing are the most significant topics in data science. In a general opinion, %80 of the work is data preprocessing, and %20 of the work is modeling in a machine learning project, therefore, there are issues to be considered. The aim of this notebook is to be a tutorial on the approach to data preparation.</p><p>First of all, check the what is the difference between feature engineering and data preprocessing.</p><p><strong>Feature Engineering:</strong> The work performed on the features (such as preprocessing, generating a new variable, etc.), and generating variables from raw data.</p><p><strong>Data Preprocessing:</strong> The process of making the data suitable before implementing a model.</p><p>Actually, the aim is similar, make the dataset become much more suitable, and prepare it for the model. The difference can be shown in a figure:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/834/1*sCoM-hxdiEZW73coQYeQaw.png" /></figure><p>In this step, there are 4 topics to consider and handle to prepare the dataset.</p><ol><li>Outliers</li><li>Missing Values</li><li>Feature Extraction</li><li>Encoding &amp; Scaling</li></ol><p>All of these are the steps of data preprocessing and feature engineering, and each of them will be explained in the notebook.</p><p>Obviously, before the process, the dataset has to be explored, and the variables have to be understood. That means the dataset has to be prepared before preparing. Make ready to your popcorns for the movie named “Inception on Data Science”! Of course, it does not that hard, but there are rules and steps to be followed in order to not get lost.</p><h4>Table of Contents</h4><p>1. First look to the dataset</p><p>2. EDA (Exploratory Data Analysis)</p><p>— 2.1 Analyse outliers and missing values</p><p>3. Pre-processing (Outliers and Missing Values)</p><p>4. Feature Extraction</p><p>5. Encoding &amp; Scaling</p><p>6. Modeling</p><h3>1. First look to the Dataset</h3><p><strong>Business Problem:</strong> It is desired to develop a machine learning model that can predict whether people have diabetes when their characteristics are specified. You are expected to perform the necessary data analysis and feature engineering steps before developing the model.</p><h3>2. EDA (Exploratory Data Analysis)</h3><p>This is the part of the explore the dataset. The outliers and missing value analyses will be done in this part but the only point is to observe these in this part. The operation and editing for these will be done in Part 3: Pre-processing.</p><pre>import numpy as np<br>import pandas as pd<br>import seaborn as sns<br>from matplotlib import pyplot as plt<br><em># !pip install missingno</em><br>import missingno as msno<br>from datetime import date<br>from sklearn.metrics import accuracy_score<br>from sklearn.model_selection import train_test_split<br>from sklearn.neighbors import LocalOutlierFactor<br>from sklearn.preprocessing import MinMaxScaler, LabelEncoder, StandardScaler, RobustScaler</pre><pre>df = pd.read_csv(&quot;../input/diabetes/diabetes.csv&quot;)<br>df.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z3n2ySzp6QtczEVkhk_8Wg.png" /></figure><p>The interesting thing is there is no categorical or missing value. It will be examined.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b4cMB2Y2xse25Qwuafty0A.png" /></figure><p>Now, we looked at the data and checked the variables. The first step is to go deeper and understand the variables. Here, the questions have to be asked: “which variables are <em>really</em> numerical, and which of these are categorical? Is there any cardinal variable?”.</p><p>For instance, the target variable, Outcome, seems as a numerical variable, but it is known that it is categorical since this column only includes 1 and 0(disease or no disease). These kinds of variables have to be considered categorical.</p><p>Similarly, if there is a categorical variable that includes the names of the patients in a dataset, there would be considered a cardinal dataset because a name cannot carry any information. This is not valid for our dataset because there are no such columns, but it had to be considered if the dataset had.</p><pre>def grab_col_names(dataframe, cat_th=10, car_th=20):<br>    <em># cat_cols, cat_but_car</em><br>    cat_cols = [col for col <strong>in</strong> dataframe.columns if dataframe[col].dtypes == &quot;O&quot;]<br>    num_but_cat = [col for col <strong>in</strong> dataframe.columns if dataframe[col].nunique() &lt; cat_th <strong>and</strong><br>                   dataframe[col].dtypes != &quot;O&quot;]<br>    cat_but_car = [col for col <strong>in</strong> dataframe.columns if dataframe[col].nunique() &gt; car_th <strong>and</strong><br>                   dataframe[col].dtypes == &quot;O&quot;]<br>    cat_cols = cat_cols + num_but_cat<br>    cat_cols = [col for col <strong>in</strong> cat_cols if col <strong>not</strong> <strong>in</strong> cat_but_car]<br><br>    <em># num_cols</em><br>    num_cols = [col for col <strong>in</strong> dataframe.columns if dataframe[col].dtypes != &quot;O&quot;]<br>    num_cols = [col for col <strong>in</strong> num_cols if col <strong>not</strong> <strong>in</strong> num_but_cat]<br><br>    return cat_cols, num_cols, cat_but_car<br><br><br>cat_cols, num_cols, cat_but_car = grab_col_names(df)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8FjU2ifIJDxl_LCT8xf-uQ.png" /></figure><p>It can be seen that “Outcome” is a categorical variable, which is the target value.</p><h3>2.1 Analyse outliers and missing values</h3><p>Now, check the missing values and outliers. The only process in EDA is to analyze these values. Editing of missing values and outliers will be done in the next part.</p><p>Before analyzing, firstly, an explanation of these terms will be done, the codes will be placed right after each explanation.</p><h3>Outliers</h3><p>Values that deviate considerably from the general trend in the data are called outliers. Especially in linear problems, the effects of outliers are more severe. They have less impact on tree methods, but still, need to be considered.</p><p><strong>How are outliers determined?:</strong> The critical point is to determine the acceptable threshold value, which are up limit and low limit. After determining the threshold value, outliers are caught based on these values. Methods by which we can catch the threshold value:</p><ul><li>Industry knowledge</li><li>Standard deviation approach</li><li>Z-score approximation</li><li><strong>Boxplot(interquartile range-IQR) method (as univariate)</strong></li><li><strong>LOF Method =&gt; Multivariate</strong></li></ul><p>We will emphasize and examine Boxplot(IQR Method) and LOF Method to analyse. After getting outliers, they may be deleted, or reassigned with thresholds(pressing outliers values to thresholds). These solution approaches will be in the next part.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rGWN_miIRzakB6BC8vhT7Q.png" /></figure><p>There are 2 points that call attention to the first look:</p><ul><li>“Insulin” has a high standard deviation, the quartile values are large, and the outlier is clear.</li><li>“SkinThickness” quartile distribution is uneven.</li></ul><p>For better observation, plot these.</p><pre><em># Boxplot</em><br>sns.boxplot(x=df[&quot;Insulin&quot;])<br>plt.show()</pre><pre>sns.boxplot(x=df[&quot;SkinThickness&quot;])<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/828/1*NOC7Dgc6PyNazGbmoH16Tg.png" /></figure><p>It has been mentioned that the critical point of the outliers is to determine the threshold. In the boxplot, IQR method will be used. A range named IQR(Interquartile Range) is to be determined according to quartiles, then, up limit and low limit will be found. In literature:</p><p><strong><em>IQR = Q3 — Q1</em></strong></p><ul><li>Q3: 75% quantile</li><li>Q1: 25% quantile</li></ul><p>Up limit is defined as 1.5 times bigger than Q3, and the low limit is defined as a 1.5 times smaller value than Q1.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7VTeB2mzm2S7Y36X3hdEvA.png" /></figure><p>Defining Q3 and Q1 as 75th and 25th is not a rule. It is a common formula but these quartiles can be defined as 95–5, or 90–10 according to the problem. If there is a slight number of data on the dataset, separating it to 75–25% may not be appropriate because the loss of information may occur.</p><p>In light of this information, let’s write a function that finds the thresholds. Add the quantiles as arguments because we may want to change them.</p><pre>def outlier_thresholds(dataframe, col_name, q1=0.25, q3=0.75):<br>    quartile1 = dataframe[col_name].quantile(q1)<br>    quartile3 = dataframe[col_name].quantile(q3)<br>    interquantile_range = quartile3 - quartile1<br>    up_limit = quartile3 + 1.5 * interquantile_range<br>    low_limit = quartile1 - 1.5 * interquantile_range<br>    return low_limit, up_limit</pre><pre>for i <strong>in</strong> df.columns:<br>    print(&quot;Thresholds of <strong>{}</strong> : (<strong>{:.2f}</strong>, <strong>{:.2f}</strong>)&quot;.format(i, *outlier_thresholds(df,i)))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/988/1*yoG-PMd5fjLN5b_TgjZWbg.png" /></figure><h3>Missing Values</h3><blockquote><em>“The idea of imputation is both seductive and dangerous.” -R.J.A. Little &amp; D.B. Rubin</em></blockquote><p>It refers to the situation of lack of observations. It can be solved in 3 ways:</p><ul><li>Deleting</li><li>Value Assignment Methods (average, mode, median, etc.)</li><li>Predictive Methods (ML, statistical methods, etc.)</li></ul><p>One of the important issues to consider when working with missing data: The randomness of the missing data, that is, whether the missing data occur randomly or not, is the need to know. If it is random, we either delete it, fill it with the average, etc. However, there may be a dependency of the missing variable with another variable, hence, the missing may not be random. This situation is considered, as well.</p><p>At the first look, it seems there is no missing value but Glucose, Insulin, etc. observation units containing a value of 0 in the variables may represent the missing value. For example, a person’s glucose or insulin value can not be 0. Considering this situation, let’s assign the 0 values to the relevant values as NaN and then apply the operations to the missing values.</p><p>Here, the pregnancy value can be 0. It is a normal situation, hence, this column will be out of NaN.</p><pre>num_cols_miss = [i for i <strong>in</strong> num_cols if i != &quot;Pregnancies&quot;]<br>for i <strong>in</strong> num_cols_miss:<br>    df[i] = df.apply(lambda x: np.nan if x[i] == 0 else x[i], axis=1)</pre><pre><em># df after adding NaN</em><br>df.head(10)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v42nl476-yh_aNzE99nIlg.png" /></figure><p>For missing values, there a library named “missingno”, which was imported at the beginning. This library provides visual analysis for missing values.</p><pre><em># the number of non-missing data</em><br>msno.bar(df)<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gS69j1FenERLORuwiKXYbQ.png" /></figure><pre><em>&quot;&quot;&quot;</em><br><em>heatmap can be used rather than try to observe with eyes on matrix map</em><br><em>this heatmap shows the correlation of missing values on variables</em><br><em>&quot;&quot;&quot;</em><br>msno.heatmap(df)<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-C2Ax0-qbTPTpFiF4WS4Ig.png" /></figure><p>There is an interesting finding that there is a strong positive correlation between “SkinThickness” and “Insulin” variables’ missing values. It may be used later.</p><p>“missingno” library helps us to visualize the missing values, now let’s more mathematical approach. Let’s make a table that consists of missing values. For this, write a function that shows the missing values on a dataframe as a table.</p><pre>def missing_values_table(dataframe, na_name=False):<br>    <em># only take missing columns</em><br>    na_columns = [col for col <strong>in</strong> dataframe.columns if dataframe[col].isnull().sum() &gt; 0]<br><br>    n_miss = dataframe[na_columns].isnull().sum().sort_values(ascending=False)  <em># number of missing value</em><br>    ratio = (dataframe[na_columns].isnull().sum() / dataframe.shape[0] * 100).sort_values(ascending=False) <em># ratio</em><br>    missing_df = pd.concat([n_miss, np.round(ratio, 2)], axis=1, keys=[&#39;n_miss&#39;, &#39;ratio&#39;])  <em># make table</em><br>    print(missing_df, end=&quot;<strong>\n</strong>&quot;)<br><br>    if na_name:<br>        return na_columns</pre><pre>missing_values_table(df)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/584/1*atiXKipztECGogYE73yyXw.png" /></figure><h3>3. Pre-processing (Solve Outliers and Missing Values)</h3><p>In part 2, the analysis has been completed. Examination of thresholds for outliers and how to reach missing values have been done. The next step is to interfere with these and prepare the dataset. This part will be separated into 2, like part 2. Firstly, the outliers problem will be examined, then, missing values will be processed.</p><p>Our threshold values can be affected after the operations on the missing value. Also, an outlier method, LOF, cannot be used when the dataset includes NaN values. Therefore, the missing value part will be taken firstly on preprocessing.</p><h3>Missing Values</h3><p>It mentioned on analyze part that there is 3 solution to missing values.</p><ul><li><strong>Deleting:</strong> It means dropping the rows that include missing values. Especially in a small dataset, it creates a loss of information. For example, the dataset has 768 rows, but Insuline has 374 missing values. If missing values are dropped, half of the dataset has been lost, and the information will be lost, as well. If the dataset would be large, and there are a couple of missing values that can be sacrificed, deleting can be an option.</li></ul><pre><em># copy dataset to see effect without damage the main dataset<br></em>df_new = df.copy()<br>df_new.shape # =&gt; output: (768, 9)</pre><pre>df_new.dropna(inplace=True)<br>df_new.shape # =&gt; output: (392, 9)</pre><ul><li><strong>Value Assignment Methods</strong>: We can fill the NaN values with the column’s mean, median, mode, etc. If the distribution is homogeneous, filling with median or mean is logical. Also, in some scenarios, filling with a specific number like 0 would make sense.</li></ul><pre>df_fill = df.apply(lambda x: x.fillna(x.median()) if x.dtype != &quot;O&quot; else x, axis=0)<br>df_fill.head(10) <br><em># after filling</em></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wMFjtKwKv-p3zXdaqxjzcg.png" /><figcaption>After Filling</figcaption></figure><ul><li><strong>Predictive Methods:</strong> This method is based on machine learning, statistical methods, etc. It is an advanced level to fill NaN values. A model can be implemented and missing values are predicted by that model in this method. There are 2 points we have to consider:</li></ul><blockquote>1. We have to put categorical variables into a one-hot encoder. Since we will use a modeling technique, the model expects variables from us in some specific ways, and we have to comply with these.</blockquote><blockquote>2. Since KNN is a distance-based algorithm, we need to standardize the variables.</blockquote><p>Encoding and standardization will be explained in the next part, so that, only the predictive method process will be shown in this part. The only point, for now, since all variables are numerical, encoding will not be done.</p><pre>df_ml = df.copy()<br><br><em># standardization</em><br>scaler = MinMaxScaler()<br>df_ml = pd.DataFrame(scaler.fit_transform(df_ml), columns=df_ml.columns)<br>df_ml.head()</pre><pre><em># fill with KNN</em><br>from sklearn.impute import KNNImputer<br>imputer = KNNImputer(n_neighbors=5)<br>df_ml = pd.DataFrame(imputer.fit_transform(df_ml), columns=df_ml.columns)<br>df_ml.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2eFr3JeS5yW2eTqk4sapjg.png" /></figure><p>In this way, we have completed the filling process, but the problem is all values we filled become standardized. It can turn the normal values with “inverse_transform”.</p><pre>df_ml = pd.DataFrame(scaler.inverse_transform(df_ml), columns=df_ml.columns)<br>df_ml.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YCk1MuwVRF7fgnOm2SVH4Q.png" /></figure><p>We examined the possible ways to deal with missing data. In order to examine the ways, the methods have been implemented in different data frames. Now, we have to apply these methods to the main dataframe.</p><p>Firstly, check again the number of missing values with the function we defined on analyze part.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/664/1*ythsepsY3I1zXl5fQQy0eg.png" /></figure><p>Here, my approach will separate the process into 2. Since two variables have many missing values, making a prediction may not be logical for these. It is just an interpretation and this is open to comment. The data preprocessing part is subjective, but the common aim is to prepare data as clearly as possible before the model.</p><p>My comment is to use predict model for variables that include a low number of missing values and fill median for variables that have a high number of missing.</p><pre>na_cols = missing_values_table(df, na_name=True) <em># columns that includes missing values</em><br>n_miss = df[na_cols].isnull().sum() <em># number of missing values on variables</em><br><br><em># 100 as a threshold, it is open to comment</em><br>na_cols_ml = [i for i <strong>in</strong> n_miss.index if n_miss[i] &lt; 100]<br>na_cols_med = [i for i <strong>in</strong> n_miss.index if n_miss[i] &gt; 100]<br>print(&quot;Columns that will be applied ML model:&quot;, na_cols_ml)<br>print(&quot;Columns that will be filled with median:&quot;, na_cols_med)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ozSOMwy8f0Zt2qQbrWyDJA.png" /></figure><pre><em># for the number of missing value is less than 100</em><br>df[na_cols_med] = df[na_cols_med].apply(lambda x: x.fillna(x.median()) if x.dtype != &quot;O&quot; else x, axis=0)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RcYjMMiNHve7PrT-QGOhIA.png" /></figure><p>As it can be seen above, “SkinThickness” and “Insulin” have been filled with their median values. Now, variables that have slight number of missing values will be filled with ML model.</p><pre><em># standardization</em><br>scaler = MinMaxScaler()<br><br><em># take only needed columns</em><br>df[na_cols_ml] = pd.DataFrame(scaler.fit_transform(df[na_cols_ml]), columns=df[na_cols_ml].columns)<br>print(df[na_cols_ml].head())<br><br><em># fill with KNN</em><br>from sklearn.impute import KNNImputer<br>imputer = KNNImputer(n_neighbors=5)<br>df[na_cols_ml] = pd.DataFrame(imputer.fit_transform(df[na_cols_ml]), columns=df[na_cols_ml].columns)<br>print(df[na_cols_ml].head())<br><br><em># from standardized to non-standardized </em><br>df[na_cols_ml] = pd.DataFrame(scaler.inverse_transform(df[na_cols_ml]), columns=df[na_cols_ml].columns)<br>print(df[na_cols_ml].head())</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*0tqUKu-p3OBoK7jVILpTVw.png" /></figure><h3>Outliers</h3><p>We mentioned on analyze part that there are 2 ways to solve this problem: IQR Method for the find outliers on univariate, and LOF Method for getting multivariate outliers.</p><p>Firstly, LOF Method will be explained because it controls the variables’ meaningfulness to each other and gives a few outliers. Then, IQR Method will be implemented.</p><ul><li><strong>Local Outlier Factor (LOF):</strong> Another approach is the Local Outlier Factor. It helps us to define outliers accordingly by ordering the observations based on the density at their location. The local density of a point means the neighborhoods around that point. If a point is significantly less dense than its neighbors, then that point is in a more sparse region, so there may be an outlier. The LOF method allows us to calculate a distance score based on neighborhoods.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/250/1*KTDT3ex4n1aJilor4ZJ4xg.png" /></figure><p>The LOF method says: “I’ll give you a score, the closer the score to 1 the better”. Therefore, as you move away from 1, the probability of the observation being outlier increases. The LOF method is also used to determine thresholds.</p><p><strong>Example of usage:</strong> In our dataset, there are both “Age” and “Pregnancies”. For instance, when these variables are taken separately, there may not be any outlier. However, when these 2 variables get together, it may be the outlier. What it means is explained in the following example dataframe.</p><pre>df_ex = pd.DataFrame({&quot;age&quot;: [17, 35, 47],<br>                     &quot;pregnancy&quot;: [5, 2, 3]})<br>df_ex</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/284/1*mWiAUwe3VxM6ZcMksBmF-A.png" /></figure><p>When we take the variables separately, “Age” is looking fine. A person can be 17, 35, or 47 years old. Similarly, “Pregnancy” has no problem if we consider only the number of pregnancies because a person can be pregnant 5 times, twice, or 3 times.</p><p>Now, check the first index, the values have meanings separately, but together, a person 17 years old cannot be pregnant 5 times. This is an outlier row. LOF helps us to find these kinds of values and fix these.</p><p>The LOF scores will be considered, then, <strong>“elbow method”</strong> will be implemented to determine threshold.</p><pre><em># LOF</em><br>clf = LocalOutlierFactor(n_neighbors=20)<br>clf.fit_predict(df)  <em># returns LOF scores</em><br>df_scores = clf.negative_outlier_factor_ <em># keep scores to observe (negative)</em><br><em># df_scores = -df_scores # for changing to pozitive but we will use as negative</em></pre><p><strong>NOTE:</strong> “negative_outlier<em>factor</em>” method gives scores as negative. These can be changed to positive but the original negative values will be used in order to observe it more easily in the elbow method that will be used. Therefore, if scores are close to -1, the result becomes not an outlier, not being close to 1 like at the beginning. For instance, if we have scores from -1 to -10, it will be interpreted that values at -10 tend to be more outlier.</p><pre><em># elbow method</em><br>scores = pd.DataFrame(np.sort(df_scores))<br>scores.plot(stacked=True, xlim=[0, 20], style=&#39;.-&#39;)  <em># x=gözlemler, y=outlier skorları</em><br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*SS9O4wZt0h9e2Cqt4MWh8Q.png" /></figure><p>There are observations on the x-axis and outlier scores on the y-axis. Each point represents threshold values and a graph was created according to these threshold values. The threshold value has to be decided according to the table. The point where the steepest slope change is the 3rd index. Hence, the final comment can be:</p><p>There are some observations and their scores, and there is the problem of where to divide these scores. The most marginal change was in the 3rd index, so this is the point where the most drastic change is defined as the threshold value. Because the lower scores(more negative values) are the worse, then a threshold has to be set and cut it.</p><pre>th = np.sort(df_scores)[3]  <em># set any lower scores than that as outlier, 3 data found</em></pre><pre>print(&quot;Before delete outliers:&quot;, df.shape) # (768, 9)<br>df.drop(axis=0, labels=df[df_scores &lt; th].index, inplace=True) <br>print(&quot;After delete outliers:&quot;, df.shape) # (765, 9)</pre><ul><li><strong>IQR:</strong> We find the thresholds on analysis using IQR calculation. Now, re-assignment with thresholds will be done. It means the outliers will be replaced with upper and lower thresholds. Why this kind of process is made is based on preventing the loss of data. Especially in the small dataset, each data is significant.</li></ul><blockquote>In the literature, the upper limit is taken as 75% quantile(Q3), and a higher value than 1.5xQ3 will be replaced this threshold, a similar process is done with the lower value that is lower than 1.5xQ1, where Q1 is 25% quantile. We can also use quantiles as 5 by 95 because deleting or filling according to 25–75 would create a serious data loss and residue, we would add noise ourselves and create problems. Hence, the proportion of these depends according to the problems and datasets. If tree methods are used, not to touch outliers may be chosen, or approaching unacceptable outliers a shave from the end may be implemented.</blockquote><p>In the analysis part, thresholds have been found with the function “outlier_thresholds”. Now, write a function that takes these thresholds and makes re-assignment to outliers.</p><pre>def replace_with_thresholds(dataframe, variable):<br>    low_limit, up_limit = outlier_thresholds(dataframe, variable)<br>    dataframe.loc[(dataframe[variable] &lt; low_limit), variable] = low_limit<br>    dataframe.loc[(dataframe[variable] &gt; up_limit), variable] = up_limit</pre><pre>for col <strong>in</strong> num_cols:<br>    replace_with_thresholds(df, col)</pre><p>Min, max, event mean, and std values have changed according to thresholds. Solution of outliers has been completed with this process.</p><h3>4. Feature Extraction</h3><p>When a data set is prepared, not only existing variables are tried to be edited, but also new, meaningful variables have to be created. New columns sometimes can be created with mathematical operations, sometimes named a numerical value to categorical, or categorical values’ ranges, etc. This process is known as <strong>Feature Engineering</strong>, and this is one of the critical parts of data preparation.</p><pre><em># pregrancy cannot be float, it occurs due to calculation of IQR</em><br>df[&quot;Pregnancies&quot;] = df[&quot;Pregnancies&quot;].apply(lambda x: int(x))</pre><pre><em># create categorical columns from numerical columns</em><br><br><em># if bins are 0, 3, 6 =&gt; 0 values become NaN due to bins </em><br>df[&quot;NumOfPreg&quot;] = pd.cut(df[&quot;Pregnancies&quot;], bins=[-1, 3, 6, df[&quot;Pregnancies&quot;].max()], labels=[&quot;Normal&quot;, &quot;Above Normal&quot;,&quot;Extreme&quot;])<br>df[&quot;AgeGroup&quot;] = pd.cut(df[&quot;Age&quot;], bins=[18, 25, 40, df[&quot;Age&quot;].max()], labels=[&quot;Young&quot;, &quot;Mature&quot;, &quot;Old&quot;])<br>df[&quot;GlucoseGroup&quot;] = pd.qcut(df[&quot;Glucose&quot;], 3, labels=[&quot;Low&quot;, &quot;Medium&quot;, &quot;High&quot;])<br>df[&quot;Patient&quot;] = np.where(df[&quot;Outcome&quot;] == 1, &quot;Yes&quot;, &quot;No&quot;)</pre><pre><em># example of mathematical expression</em><br><br><em>&quot;&quot;&quot;Assume there is a variable named &quot;BMIns&quot;, and it can be found with the multiplication of BMI and Insuline. </em><br><em>Create and add it to data frame&quot;&quot;&quot;</em><br><br>df[&quot;BMIns&quot;] = df[&quot;BMI&quot;]*df[&quot;Insulin&quot;] <em># numerical</em><br>df[&quot;BMInsGroup&quot;] = pd.qcut(df[&quot;BMIns&quot;], 3, labels=[&quot;Low&quot;, &quot;Medium&quot;, &quot;High&quot;]) <em># categorical</em></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MbN0nblH88qr3hCjQnBg9A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-dgLhcq-bF4u7asEGBNSSg.png" /></figure><h3>5. Encoding &amp; Scaling</h3><h3>Encoding</h3><p>Changing the representation of variables. There are different types of encoding:</p><blockquote>If there are 2 classes, it is called <strong>“Binary Encoding”</strong>, if there are more than 2 classes, it is called <strong>“Label Encoding”</strong>.</blockquote><p>It converts in alphabetical order, giving 0 to the first one it sees.</p><pre>df_enc = df.copy()<br><br>le = LabelEncoder()<br>le.fit_transform(df_enc[&quot;Patient&quot;])[0:5]</pre><pre># Output: array([1, 0, 1, 0, 1])<br>#-------------------------------------------------------------------</pre><pre><em># let&#39;s say we forgot which 0 and which 1, inverse_transform is used to detect this</em><br>le.inverse_transform([0, 1])</pre><pre># Output: array([&#39;No&#39;, &#39;Yes&#39;], dtype=object)<br>#-------------------------------------------------------------------</pre><pre><em># detect variables that have 2 unique numbers for Binary Encoding</em><br>binary_cols = [col for col <strong>in</strong> df.columns if df[col].dtype <strong>not</strong> <strong>in</strong> [int, float]<br>               <strong>and</strong> df[col].nunique() == 2]<br>binary_cols</pre><pre># Output: [&#39;Patient&#39;]</pre><p>If a variable is converted like this to numeric values and sent to algorithms, the algorithm defines this variable as a variable ranging from an interval. In other words, this transformation will confuse the algorithms. The reason for its corruption is here, the categorical variable has turned into numerical values, and a categorical variable, which is normally a nominal scale type, is suddenly distorted by a transformation process and is subjected to a sorting process as if there is a difference between them. The characteristic of the nominal scale type is that the distance between classes is equal, but this equality is disturbed after this transformation.</p><p>This situation can cause biases and problems when we convert categorical variables to numeric values and confuse algorithms. In this case, we need to do <strong>one-hot encoding</strong>.</p><p>To fix this problem, one-hot conversion is done, but doing this also creates a trap named as “dummy variable trap.”</p><p>If we apply a transformation to the variables in the data set, if the new variables can be created over each other, this is a dummy variable trap. In other words, if there is a variable that represents another variable, this situation is called a dummy variable trap.</p><p>Let’s see how one-hot encoding is done, what the dummy variable trap is, and how to fix that.</p><pre>df_new = pd.get_dummies(df, columns=[&quot;Patient&quot;])<br>df_new.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Gl5d_Q3_2OxaxJVGCdLATQ.png" /><figcaption>Last columns</figcaption></figure><p>As it can be seen, Patient is dropped, and there are new variables: Patient_No and Patient_Yes. The problem is Patient_No is the reverse of the Patient_Yes, which means, Patient_No can be created over Patient_Yes. These both carry the same information, and both are on a data frame. In that situation, we have to drop one of these, and it helps to avoid creating over each other and duplication. The dummy variable trap is solved with this.</p><pre>df_new = pd.get_dummies(df, columns=[&quot;Patient&quot;], drop_first=True)<br>df_new.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vbAHhhAYBVxfGcxHoHpaxg.png" /><figcaption>Last columns</figcaption></figure><p>Now, apply this method to our main dataset’s categorical variables. Firstly, take categorical variables again with the function we write in the analysis part.</p><pre>cat_cols, num_cols, cat_but_car = grab_col_names(df)<br>cat_cols</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0ulhixvmKv_cqtDv8b8aCg.png" /></figure><p>The outcome is our target variable, and Patient is the variable created by us. They carry the same info, and I don’t want to encode the target variable since the aim of the final model will be to predict that. The rest of the numerical columns can be encoded.</p><pre>cat_cols_final = [i for i <strong>in</strong> cat_cols if i <strong>not</strong> <strong>in</strong> [&quot;Patient&quot;, &quot;Outcome&quot;]]<br>df = pd.get_dummies(df, columns=cat_cols_final, drop_first=True)<br>df.head()</pre><h3>Scaling</h3><p>It is a variable scaling operation. The aim is to eliminate the measurement difference between the variables and to try to ensure that the models to be used approach the variables under equal conditions. Tree-based methods are not affected by outlier, standardization. In the general trend, we may prefer to scale features.</p><p>The variance structure and information structure within the variable itself are not deteriorated, but are set to a certain standard. For example, let’s say a dataset has a value of 10 and is ranked 80th when ordered from smallest to largest. When this variable is standardized, the value of 10 will be something like between 1–2 or 0 to 1, but when the data set is again ordered from smallest to largest, this value will still correspond to the 80th value. Therefore, when a variable is standardized, the value of the variable will change, it will be put into a certain format, but the spread and the essence of the distribution information will not change. The values of the variable such as mean, standard deviation and variance will change,as well, but the spread, distribution and current state of the information it carries within the variable will not change.</p><p>There are 3 common scaling method:</p><ul><li><strong>Standard Scaler:</strong> Normalization. Subtract the mean, divide by the standard deviation. It can be called z-standardization. z = (x — u) / s.</li></ul><blockquote>The Standard Scaler subtracts the mean from all observation units and divides it by the standard deviation.<br>CAUTION: Both standard deviation and mean are metrics that are affected by outliers. Therefore, if we subtract the median from all observation units and divide by the IQR, which is not affected by outliers, we will both consider the central tendency and change, and perform a more robust standardization process.<br>Robust Scaler is more preferable because it is resistant to outliers compared to Standard Scaler, but its use is not common. Usually Standard Scaler and MinMax are used.</blockquote><ul><li><strong>Robust Scaler:</strong> Subtract median and divide by IQR.</li><li><strong>MinMaxScaler:</strong> It is used to convert values between 2 desired ranges. These 2 values may also be values that are not in our dataset.</li></ul><pre>df_scale = df.copy()<br><br><em># standart scaler</em><br>ss = StandardScaler()<br>df_scale[&quot;Age_standard_scaler&quot;] = ss.fit_transform(df_scale[[&quot;Age&quot;]])<br>df_scale.head()<br><br><em># robust scaler</em><br>rs = RobustScaler()<br>df_scale[&quot;Age_robuts_scaler&quot;] = rs.fit_transform(df_scale[[&quot;Age&quot;]])<br><br><em># min-max scaler</em><br><em># The range can be given with the feature_range=() argument</em><br>mms = MinMaxScaler() <em># default range from 0 to 1</em><br>df_scale[&quot;Age_min_max_scaler&quot;] = mms.fit_transform(df_scale[[&quot;Age&quot;]]) <br><br>df_scale.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*7PqQbsdt-izkZx7NX2eMig.png" /><figcaption>df_scale to not to damage to main dataset</figcaption></figure><p>The difference between scales can be observed above. In this problem, “robust scaler” will be used here. There is no specific reason, simply, all scaling types aim for the same thing, but impact in different ways.</p><p>Also, the process has to be applied to all numerical variables because the main point is the scaling is to remove the difference between variables’ ranges.</p><pre>rs = RobustScaler()<br>for i <strong>in</strong> num_cols:<br>    df[i] = rs.fit_transform(df[[i]])<br>df.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CuouC3S7mrba_2SA8tbDrw.png" /><figcaption>Original Dataset</figcaption></figure><p>In this way, our data set is ready to model. The model part is not on the topic of data preprocessing and feature engineering, obviously. It’s just to observe the effect of data cleaning.</p><h3>6. Model</h3><p>It is not within the scope of this topic, we are only doing it to see the model establishment while preparing the data.</p><p>Patient carries the same information with Outcome. It was created as an example of feature engineering. Also, BMIns is not a real feature, it was another example of feature engineering using mathematical expression. Therefore, these 2 variables will be dropped.</p><p>KNN algorithm will be used.</p><pre>y = df[&quot;Outcome&quot;]<br>X = df.drop([&quot;Outcome&quot;, &quot;Patient&quot;, &quot;BMIns&quot;, &quot;BMInsGroup_Medium&quot;, &quot;BMInsGroup_High&quot;], axis=1)<br><br>X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.20, random_state=17)<br><br>from sklearn.ensemble import RandomForestClassifier<br>rf_model = RandomForestClassifier(random_state=46).fit(X_train, y_train)<br>y_pred = rf_model.predict(X_test)<br>accuracy_score(y_pred, y_test)</pre><p>Output =&gt; 0.8104575163398693</p><h4>What would be the score without any adjustments?</h4><pre>df_no = pd.read_csv(&quot;../input/diabetes/diabetes.csv&quot;)<br>df_no.dropna(inplace=True) <em># there was no missing value </em><br><em># df_no = pd.get_dummies(df_no, columns=[], drop_first=True) # no cat. col. to encode</em><br><br>y = df_no[&quot;Outcome&quot;]<br>X = df_no.drop([&quot;Outcome&quot;], axis=1)<br>X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.20, random_state=17)<br>rf_model = RandomForestClassifier(random_state=46).fit(X_train, y_train)<br>y_pred = rf_model.predict(X_test)<br>accuracy_score(y_pred, y_test)</pre><p>Output =&gt; 0.7857142857142857</p><h3>FINAL</h3><p>In this project, the steps of preprocessing and data preparation has been explained. The effect of how a dataset cleaning affect the model performance was observed at the end of the project. This is the end of the tutorial, I hope it helped. Thanks for reading!</p><p>For Kaggle Notebook: <a href="https://www.kaggle.com/code/furkannakdagg/data-preparation-tutorial-with-diabetes-dataset">https://www.kaggle.com/code/furkannakdagg/data-preparation-tutorial-with-diabetes-dataset</a></p><p>For Dataset: <a href="https://www.kaggle.com/datasets/furkannakdagg/diabetes">https://www.kaggle.com/datasets/furkannakdagg/diabetes</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=de24212bae1c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Rating Product & Sorting Reviews in Amazon]]></title>
            <link>https://medium.com/@furkannakdagg/rating-product-sorting-reviews-in-amazon-e25a0fbbc7df?source=rss-dd7a541643d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/e25a0fbbc7df</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[rating-system]]></category>
            <category><![CDATA[sorting]]></category>
            <dc:creator><![CDATA[Furkan Akdağ]]></dc:creator>
            <pubDate>Sat, 09 Jul 2022 07:52:40 GMT</pubDate>
            <atom:updated>2022-07-09T07:54:04.617Z</atom:updated>
            <content:encoded><![CDATA[<p>One of the most significant problem in e-commerce is the correct calculation of the points given to the product after sales. The solution to this problem means providing greater customer satisfaction for the e-commerce site, prominence of the product for the sellers and a smooth shopping experience for the buyers. Another problem is to order correctly of the comments given to the products. Since become prominent of misleading comments affect the sale of the product directly, this will cause both financial loss and loss of customers. In the solution of these 2 basic problems, while the e-commerce site and the sellers will increase their sales, the customers will complete the purchasing journey without any problems.</p><p>There are 2 aims of the project:</p><ol><li>Finding the best average rating calculation of the product</li><li>Sorting reviews in a logical order</li></ol><p>The approach will be:</p><ul><li>Sorting average with the effect of time: The dataset will be separated into timeframes, and each timeframe will have a different weight and an upvote/downvote trend will be observed. (Time-Based Weighted Average)</li><li>Sorting helpful reviews by upvote/downvote numbers: Best reviews will be revealed with different approaches to upvote and downvote numbers. (Up-down difference, average rating, Wilson lower bound)</li></ul><pre><em># Libraries</em><br>import pandas as pd<br>import numpy as np<br>import math<br>import scipy.stats as st</pre><h3>1. Read and Prepare Data</h3><pre>df = pd.read_csv(&quot;../input/amazon-reviews/amazon_reviews.csv&quot;)<br>df = df[[&quot;overall&quot;, &quot;reviewTime&quot;, &quot;day_diff&quot;, &quot;helpful_yes&quot;, &quot;total_vote&quot;]]</pre><h3>2. Analyze Average Rating (Time-Based Weighted Average)</h3><p>The first and most intuitive approach to analyzing a rating is to look at its average. The problem with this approach is that this way ignores the trend. What trend means is that user satisfaction may increase or decrease according to time. A product may be liked when it is released to the market, but the satisfaction of users may be decreased in the process of time, or the reverse situation can occur. Trend, meanwhile time, the effect has to be regarded when the average rating analyzes, so that, it’s called “Time-Based Weighted Average”.</p><pre>df[&quot;overall&quot;].mean()</pre><p>The average rating seems 4.5874 at first look, but as mentioned, it is the unassured way so that perform another process that considers time on the following.</p><pre><em># convert reviewTime object to datetime</em><br>df[&quot;reviewTime&quot;] = pd.to_datetime(df[&quot;reviewTime&quot;])</pre><pre><em># the latest date on the date considers as today&#39;s date(analyze date)</em><br>current_date = df[&quot;reviewTime&quot;].max()<br>df[&quot;day_diff&quot;] = (current_date - df[&quot;reviewTime&quot;]).dt.days <em># recalculate day_diff variable</em></pre><p>It was mentioned above that add weight to calculation according to the effect of time, but the problem is how the time can be divided. Firstly, it has to be decided how many parts the dataset desired to divide, in this project, there will be 4 parts. In order to decide the bounds of the time, the quantiles will be checked, and each quantile will be taken as the bound of the timeframes.</p><pre>df[&quot;day_diff&quot;].quantile([.25, .5, .75]) # .25 =&gt; 280, .50 =&gt; 430, #.75 =&gt; 600</pre><p>So, 4 parts are shaped with these quantiles.</p><ul><li>&lt; 280</li><li>280–430</li><li>430–600</li><li>&gt; 600</li></ul><p>The point of the dividing day_diff is to add a calculation to the effect of time with the trend. The newest ratings(review date closer than 280 days) will have a higher weight, and the oldest ratings(review date further than 600 days) will have a lower weight in order to observe whether there is an uptrend or downtrend.</p><p>As a first look, check the average of the intervals.</p><pre>df.loc[(df[&quot;day_diff&quot;] &lt;= 280), &quot;overall&quot;].mean()  # 4.695</pre><pre>df.loc[(df[&quot;day_diff&quot;] &gt; 280) &amp; (df[&quot;day_diff&quot;] &lt;= 430), &quot;overall&quot;].mean()  # 4.636</pre><pre>df.loc[(df[&quot;day_diff&quot;] &gt; 430) &amp; (df[&quot;day_diff&quot;] &lt;= 600), &quot;overall&quot;].mean()  # 4.571</pre><pre>df.loc[(df[&quot;day_diff&quot;] &gt; 600), &quot;overall&quot;].mean()  # 4.446</pre><p>As it can be seen, the rating increase as time passes, therefore, it can be interpreted as here is an uptrend.</p><p>Now, define a function to weight adjustment in order to give the user to chance to adjust weights.</p><pre><em># Set default weights as 28, 26, 24, 22 from newest to oldest review</em><br>def time_based_weighted_average(dataframe, w1=28, w2=26, w3=24, w4=22):<br>    return dataframe.loc[(dataframe[&quot;day_diff&quot;] &lt;= 280), &quot;overall&quot;].mean() * w1 / 100 + \<br>           dataframe.loc[(dataframe[&quot;day_diff&quot;] &gt; 280) &amp; (dataframe[&quot;day_diff&quot;] &lt;= 430), &quot;overall&quot;].mean() * w2 / 100 + \<br>           dataframe.loc[(dataframe[&quot;day_diff&quot;] &gt; 430) &amp; (dataframe[&quot;day_diff&quot;] &lt;= 600), &quot;overall&quot;].mean() * w3 / 100 + \<br>           dataframe.loc[(dataframe[&quot;day_diff&quot;] &gt; 600), &quot;overall&quot;].mean() * w4 / 100</pre><pre>print(&quot;Time-Based Weighted Average: <strong>%.4f</strong>&quot; %time_based_weighted_average(df))<br># Output =&gt; 4.5956</pre><p>This calculation can be set however the user wants to define weights. For instance, a person wants to analyze it by giving dominant weight to the newest rating.</p><pre>print(&quot;Time-Based Weighted Average: <strong>%.4f</strong>&quot; %time_based_weighted_average(df, w1=32, w2=28, w3=24, w4=16))<br># Output =&gt; 4.6094</pre><p>The conclusion of this part is that taking a directly average of the ratings will ignore the time effect. If the dataset is divided according to time and defines weights to these created timeframes, much more valid results will be obtained. Therefore, an owner of the business can take action to weighted ratings. For instance, if there is an uptrend, some offers may be made in order to keep going on the uptrend, in the reverse situation, if there is a downtrend, the owner may decide even to stop the sale of the product.</p><h3>3. Sorting Reviews</h3><p>It is one of the significant points of e-commerce because a user can decide to buy a product after being convinced by ratings and reading reviews. All is very well but here is the problem how these reviews can be sorted. Nowadays, almost the whole e-commerce website has a system for voting the reviews, the point of voting is to put forward the most helpful reviews. The approach of this voting system is crucial to prevent fake reviews, and give the user a better idea about the product. Here are 3 approaches to analyzing the voting system, first 2 of them should not be used. The reasons for these will be explained.</p><ol><li>Up-Down Difference Score</li><li>Average Rating</li><li>Wilson Lower Bound Score</li></ol><p>The dataset does not include downvotes, firstly, add downvotes to the dataset with subtraction to upvote from the total vote.</p><pre><em># Add downvotes to the dataset</em><br>df[&quot;helpful_no&quot;] = df[&quot;total_vote&quot;] - df[&quot;helpful_yes&quot;]</pre><h4>Up-Down Difference Score</h4><p>Simply, upvotes differ from the downvotes.</p><pre>def score_up_down_diff(up, down):<br>    return up - down<br>df[&quot;score_pos_neg_diff&quot;] = score_up_down_diff(df[&quot;helpful_yes&quot;], df[&quot;helpful_no&quot;])</pre><p>The problem is we cannot know which one is better. For instance, there are 2 reviews: one of these has 600 upvotes 400 downvotes, and another one has 5500 upvotes 4500 downvotes. Can we directly say the second one is better because it has more upvotes? No, because it has more downvotes, as well. Therefore, checking the percentage of the upvotes can be thought of intuitively, so, it brings us to another approach, which is “Average rating”.</p><h4>Average Rating</h4><p>Calculate the percentage of the upvotes, it means Average rating = (up ratings) / (all ratings).</p><pre><em># if all_votes=0, it creates NaN values, there is an if statement to prevent NaN values </em><br>def score_average_rating(up, down):<br>    if up + down == 0:<br>        return 0<br>    return up / (up + down)</pre><p>The function cannot be used directly like above because there is an if statement. The code cannot control whether the addition of 2 columns equals 0 (which is one row 0). In order to make the iterative calculation to the specific rows, apply and lambda can be used, and axis=1 must be set.</p><pre>df[&quot;score_average_rating&quot;] = df.apply(lambda x: score_average_rating(x[&quot;helpful_yes&quot;], x[&quot;helpful_no&quot;]), axis=1)</pre><p>That brings another problem because this process ignores the review’s voting frequency. For instance, there are 2 reviews: the first of these has 2 upvotes 0 downvote, and the second one has 100 upvotes 1 downvote. The first one will be sorted on the above of the second but the second review is much more valid since the review is approved by much more users. Therefore, this is not applicable and reliable for real life.</p><p>This brings us to the 3rd approach that considers both frequency and percentage of reviews, which is the “Wilson Lower Bound Score”.</p><h4>Wilson Lower Bound Score (WLB Score)</h4><p>It is a much more technical approach to sorting. It calculates a confidence interval for the Bernoulli parameter(p) and defines the lower bound of the confidence interval as the WLB Score.</p><p>For instance, a review has 600 upvotes and 400 downvotes. The percentage of upvotes is 60%. When the confidence interval is calculated, assume the result is found as [0.5, 0.7], so this approach accepts the lower bound as 0.5. When this is done for all observations, it can be said that there is a guaranteed subscore, sorting can be done according to this subscore. In that way, both frequency and average are included the sorting algorithm.</p><p><strong>NOTE:</strong> Bernoulli is used to calculate the probability of binary events such as head-tail probability on a coin toss. The mathematical explanation of the WLB and confidence interval will not be examined in this project. This expression will be defined as a function, the detailed explanation can be found on the link below:</p><p><a href="https://medium.com/tech-that-works/wilson-lower-bound-score-and-bayesian-approximation-for-k-star-scale-rating-to-rate-products-c67ec6e30060">https://medium.com/tech-that-works/wilson-lower-bound-score-and-bayesian-approximation-for-k-star-scale-rating-to-rate-products-c67ec6e30060</a></p><pre>def wilson_lower_bound(up, down, confidence=0.95):<br>    n = up + down<br>    if n == 0:<br>        return 0<br>    z = st.norm.ppf(1 - (1 - confidence) / 2)<br>    phat = 1.0 * up / n<br>    return (phat + z * z / (2 * n) - z * math.sqrt((phat * (1 - phat) + z * z / (4 * n)) / n)) / (1 + z * z / n)<br>df[&quot;wilson_lower_bound&quot;] = df.apply(lambda x: wilson_lower_bound(x[&quot;helpful_yes&quot;], x[&quot;helpful_no&quot;]), axis=1)</pre><p>Now, compare both sorting, and see the difference.</p><pre>df[[&quot;score_average_rating&quot;, &quot;helpful_yes&quot;, &quot;helpful_no&quot;, &quot;wilson_lower_bound&quot;]] \<br>.sort_values(&quot;score_average_rating&quot;, ascending=False).head(20)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/954/1*hyB8Q7uGmPvP8YtuQVlF6w.png" /></figure><pre>df[[&quot;score_average_rating&quot;, &quot;helpful_yes&quot;, &quot;helpful_no&quot;, &quot;wilson_lower_bound&quot;]] \<br>.sort_values(&quot;wilson_lower_bound&quot;, ascending=False).head(20)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/948/1*JUDEI_eeUpteC4jmd7GsFA.png" /></figure><p>As it can be seen, since WLB Score considers frequency and average, it creates much more logical sorting than others. A review has to take enough number of vote and upvotes to be in the higher position.</p><h3>4. Conclusion</h3><p>In this project, both ratings and sorting reviews have been examined. There are 2 results for examinations:</p><ol><li>In the ratings, the average rating can ignore the effect of time, therefore, this effect has to be determined, and added to calculation as weights. The result of the Time-Based Weighted Average can give more chances to take action.</li><li>In a sorting algorithm, checking only the difference or distribution of upvotes/downvotes is not sufficient. Both given total votes(frequency) and distribution of upvotes/downvotes(average) can be considered. One of the best utilizable methods is Wilson’s Lower Bound Score. Since this method is based on statistics, the results are better than intuitive methods such as the difference or average of upvotes and downvotes.</li></ol><p>For all codes, check my Kaggle Notebook:</p><p><a href="https://www.kaggle.com/code/furkannakdagg/rating-product-sorting-reviews-in-amazon?scriptVersionId=100394042">Rating Product &amp; Sorting Reviews in Amazon</a></p><p>For dataset:</p><p><a href="https://www.kaggle.com/datasets/halimedogan/amazon-reviews">Amazon Reviews</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e25a0fbbc7df" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[AB Testi ile Bidding Yöntemlerinin Dönüşümünün Karşılaştırılması]]></title>
            <link>https://medium.com/@furkannakdagg/ab-testi-ile-bidding-y%C3%B6ntemlerinin-d%C3%B6n%C3%BC%C5%9F%C3%BCm%C3%BCn%C3%BCn-kar%C5%9F%C4%B1la%C5%9Ft%C4%B1r%C4%B1lmas%C4%B1-51e813070d4?source=rss-dd7a541643d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/51e813070d4</guid>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[independent-t-test]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[ab-testing]]></category>
            <dc:creator><![CDATA[Furkan Akdağ]]></dc:creator>
            <pubDate>Fri, 08 Jul 2022 18:44:50 GMT</pubDate>
            <atom:updated>2022-07-08T18:44:50.887Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JtspmnfTOebWrKvwqXH3bA.jpeg" /></figure><p>Merhabalar herkese,</p><p>Bugün ölçümleme problemlerinden olan AB testi ile inceleyeceğimiz veri setinde, bir şirketin satışlarındaki artışın istatistiki olarak bir anlam ifade edip etmediğini inceleyeceğiz. İki Örneklem T testi adıyla da ifade edebileceğimiz bu testin adımlarına geçmeden önce bu test nedir, neden kullanırız, nasıl uygulanır gibi sorulara cevap verdikten sonra örnek uygulama üzerinden pekiştireceğiz.</p><h3>AB Testi (Bağımsız İki Örneklem T Testi)</h3><p>“Bir test yapıyoruz ama bu testimizin amacı ne diye?” soracak olursak temel amacımız olası farklılıkların şans eseri ortaya çıkıp çıkmadığını göstermeye çalışmaktır. Örneğin şirket, bir makine öğrenmesi projesine yatırım yapmış ve ürettiği makine öğrenmesi modelleri ile kullanıcılarına çeşitli ürün tavsiyelerinde bulunuyor olsun. Öyle ki elimizde önceden makine öğrenmesi kullanılmayan eski sistemimiz vardı, bir de emek ve para harcanıp geliştirilen makine öğrenmesi projesi neticesinde kullanıcılara ürün önerilerinin akıllı bir şekilde yapıldığı yeni sistemimiz var. Peki bu yapılan bu yatırım gerçekten emeğimize, paramıza değiyor ve işe yarıyor mu? İlk bakışta matematiksel olarak fark gözükse de, bu fark gerçekten var mı, yoksa şans eseri mi ortaya çıktı? Başta da dediğimiz gibi aslında AB testinin amacının anahtar özeti de budur: Karşılaştırmalarında temel amaç, olası farklılıkların şans eseri ortaya çıkıp çıkmadığını göstermeye çalışmaktır.</p><p>Dikkat edilmesi gereken nokta, burada geliştirilen makine öğrenmesi modelinin başarısıyla ilgilenmiyoruz. İlgilendiğimiz şey, bu makine öğrenmesi modeli sisteme entegre edildiğinde ve bunun önerilerine göre kullanıcılar satın alma işlemlerini gerçekleştirdiğinde ortaya çıkacak olan gelirlerin, eski sisteme göre anlamlı ve istatistiki bir farklılık yaratıp yaratmadığıdır.</p><h3>AB Testi Nasıl Uygulanır?</h3><p>İki grubun ortalamaları veya oranları arasında karşılaştırma yapılmak istenildiğinde kullanılır.</p><p>A kontrol grubunu, B deney grubunu temsil eder.</p><h4>1. Hipotez Kurma</h4><p><strong>Hipotez:</strong> Bir inanışı, bir savı test etmek için kullanılan istatistiksel yöntemdir.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JFmWFe7ZRBHOaefHmBUg6w.png" /></figure><p>Buradaki H0, yokluk hipotezidir ve bizim sınayacağımız durumdur. “İki grubun ortalamaları arasında fark yoktur”, bir başka deyişle “İki grubun ortalamaları eşittir” hipotezini kurup bu hipotezi sınarız. Sınama sonucunda hipotezi reddetme veya reddedememeye göre iki grup ortalaması arasında fark olup olmadığını değerlendiririz.</p><p>Yapacağımız işlemlerde ortalamaların farklarını zaten biliyoruz, reddedecek olduğumuzda farkın hangisinden kaynaklandığını bilip buna göre yorum yapabilmek adına yukarıdaki karşılaştırmalardan en soldakini kullanacağız.</p><p>Özetle, testlerin sonucunda iki grubun ortalamalarını kıyaslayacağız ve bunların arasında “fark var” veya “fark yok” diyeceğiz.</p><h4>2. Varsayım Kontrolü</h4><p>Kullanacağımız, bize bilimsel bazı kıyaslamalar ve karara varma imkanları sağlayan yöntemlerin bazı varsayımları vardır. Bağımsız İki Örneklem T Testinin 2 varsayımı vardır:</p><ul><li><strong>Normallik Varsayımı:</strong> İki grubun da ayrı ayrı standart normal dağılması gerekmesi varsayımıdır. Shapiro-Wilks Testi kullanılır.</li><li><strong>Varyans Homojenliği:</strong> İki grubun varyanslarının homojenliği varsayımıdır. Yani iki grubun dağılımlarının birbirine benzer olup olmamasına bakılır. Levene Testi kullanılır.</li></ul><p><strong>Varsayım kontrollerini neden yapıyoruz?:</strong> Çünkü varsayım kontrolleri sonda yapacağımız bağımsız iki örneklem T testinin “parametrik” mi yoksa “non-parametrik” mi olacağını söyler. Varsayımlarımız için de “H0: Varsayım sağlanıyordur” şeklinde bir hipotez kurup yaptığımız testin sonucuna göre parametrik veya non-parametrik test yaparız.</p><p>Yani test içinde test mi çıktı?: Cevabımız evet. Varsayım testlerinin amacı sonda yapacağımız testin nasıl uygulanacağını belirlemek içindir. Varsayımlar içinde kendi içinde “H0: Varsayım sağlanıyordur” hipotezleri kurulur, bunların reddedilmesine veya reddedilmemesi T testini nasıl yapacağımıza dair bize yol gösterir.</p><h4>3. Testin Uygulanması</h4><p>Yukarıda da belirttiğimiz gibi burada uygulayacağımız test varsayım kontrollerimize göre şekillenir. Burada varsayım kontrollerinin reddedilmesine veya reddedilmemesine göre farklı yollar karşımıza çıkar:</p><ul><li>Eğer iki varsayım da sağlanıyorsa parametrik testimiz olan “bağımsız iki örneklem t testi” uygulanır.</li><li>Normallik sağlanıyor fakat varyans homojenliği sağlanmıyorsa yine parametrik testimiz “bağımsız iki örneklem t testi” uygulanır fakat bu durumda testin uygulanacağı fonksiyona varyans homojenliğinin sağlanmadığını belirten argüman eklenir.</li><li>Normallik sağlanmıyorsa direkt olarak non-parametrik testimiz olan “mannwhitneyu testi” uygulanır.</li></ul><h4>4. Bağımsız İki Örneklem T Testi ve Yorumlanması</h4><p>Testler için ilgili fonksiyonları kullanıp hipotez testlerini gerçekleştirdiğimizde, ilgili fonksiyonları kullandığımızda bu fonksiyonlar bize bir <strong>p-value </strong>değeri veriyor olacak. Kurduğumuz hipotezin sonucunu testlerin sonucunda elde edeceğimiz p-value değerine bakarak yorumlayacağız. Bu p-value değeri <strong>0.05’ten küçükse H0 reddedilir</strong> diyerek çalışmanın başında kurduğumuz H0 her neyse, ona göre yorumlarımızı yapacağız.</p><h3>ÖZET</h3><p>Kaybolmamak adına uygulacağımız adımların özetine tekrar bakalım.</p><ol><li>Hipotez kur.</li></ol><blockquote>H0: Karşılaştırdığımız iki ortalama arasında istatistiki olarak anlamlı bir farklılık vardır.<br>H1: Karşılaştırdığımız iki ortalama arasında istatistiki olarak anlamlı bir farklılık yoktur.</blockquote><p>2. Varsayım testlerini yaparak varsayımları incele.</p><blockquote>Normallik Varsayımı: Shapiro Testi <br>Varyans Homojenliği: Levene Testi<br>Ayrı ayrı test sonuçlarını incele, eğer p-value değeri 0.05&#39;ten küçük olan varsayım reddedildi demektir, yani p-value değeri 0.05&#39;ten küçük olan varsayım sağlanmamıştır.</blockquote><p>3. T testini uygula, p-value’ya bakarak final yorumunu yap.</p><blockquote>T testinin nasıl yapılacağı varsayımların reddedilme durumlarına göre şekillenir.<br> İki varsayım da sağlanıyorsa: Parametrik T Testi <br> Normallik sağlanıyor, varyans sağlanmıyorsa: Parametrik T Testi fakat varyansın sağlanmadığı argümanla belirlir.<br> Normallik sağlanmıyorsa: Non-parametrik Mannwhitneyu Testi<br>Uyguladığımız testin sonucundaki p-value 0.05&#39;ten küçükse başta kurduğumuz hipotez REDDEDİLİR, yani “Karşılaştırdığımız iki ortalama arasında istatistiki olarak anlamlı bir farklılık vardır” hipotezi reddedildi ve fark olduğu sonucuna varıldı.</blockquote><h4><strong>EK BİLGİ:</strong></h4><p>H0&#39;I YA REDDEDERİZ YA REDDEDEMEYİZ, H1&#39;İ KABUL ETMEK DİYE BİR YORUM YOKTUR!<br>İstatistik literatüründe hipotez kurulduğunda hipotezi(H0&#39;ı) reddederiz ya da reddedemeyiz. H1&#39;i kabul etmek veya reddetmekle ilgili bir yorumumuz OLAMAZ çünkü H0&#39;ı reddettiğimizde yapacak olduğumuz hata miktarını, yani p-value değerimizi biliriz(0.05). Ama H1&#39;i kabul etmekle yapacak olduğumuz hatayı bilemeyiz, bir yere dayandıramayız.</p><h3>PROJE</h3><h3>İş Problemi</h3><p>Bir şirket kısa süre önce mevcut “maximum bidding” adı verilen teklif verme türüne alternatif olarak yeni bir teklif türü olan “average bidding”i tanıttı.</p><p>Müşterilerimizden biri, bu yeni özelliği test etmeye karar verdi ve average bidding’in maximum bidding’den daha fazla dönüşüm getirip getirmediğini anlamak için bir A/B testi yapmak istiyor.</p><p>A/B testi 1 aydır devam ediyor ve müşterimiz şimdi bizden bu A/B testinin sonuçlarını analiz etmemizi bekliyor. Müşterimiz için nihai başarı ölçütü Purchase’dır. Bu nedenle, istatistiksel testler için Purchase metriğine odaklanılmalıdır.</p><p>Bundan sonrasındaysa da kodlamaya başlayabiliriz.</p><p>Öncelikle gerekli kütüphaneleri ekleyip düzenlemeler yapalım:</p><pre>import itertools<br>import numpy as np<br>import pandas as pd<br>import matplotlib.pyplot as plt<br>import seaborn as sns<br>import statsmodels.stats.api as sms<br>from scipy.stats import shapiro, levene, ttest_ind, mannwhitneyu<br>from statsmodels.stats.proportion import proportions_ztest<br><br>import warnings<br>warnings.filterwarnings(&quot;ignore&quot;) <br><br>pd.set_option(&#39;display.max_columns&#39;, None)<br>pd.set_option(&#39;display.max_rows&#39;, None)<br>pd.set_option(&#39;display.float_format&#39;, lambda x: &#39;<strong>%.5f</strong>&#39; % x)</pre><p>Kullanacağımız veri seti bir excel dosyası ve içinde 2 sayfa var: İlk sayfa “Control Group” eski sistemimiz olan “maximum bidding” yönteminden alınan verileri, diğer sayfa olan “Test Group” ise yeni sistemimiz olan “average bidding” yöntemimizin verilerinden oluşuyor. Bunları ayrı ayrı dataframe’lere kaydedelim.</p><pre>df_control = pd.read_excel(&quot;ab_testing.xlsx&quot;, sheet_name=&quot;Control Group&quot;)<br>df_test = pd.read_excel(&quot;ab_testing.xlsx&quot;, sheet_name=&quot;Test Group&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LaXvK97l4LpI9553KGzwaQ.png" /></figure><p>Veriye ilk bakışı attığımızda matematiksel olarak görebileceğimiz üzere yeni sistemin bir artış sağladığı gözüküyor. Burada bizi test yapma ihtiyacına iten soru ortaya çıkıyor: Bu fark istatistiki olarak da sağlanıyor mu, yoksa şans eseri mi oluştu?</p><p>Yazının devamında hipotez kurulması, varsayım kontrolleri ve hipotez sonuçlarını değerlendireceğiz.</p><p>Öncelikle ayrı yerlerde duran veri setlerinin değişken isimlerini düzenleyip bir datasette toplayalım.</p><pre>df_control.columns = [i+&quot;_control&quot; for i <strong>in</strong> df_control.columns]<br>df_test.columns = [i+&quot;_test&quot; for i <strong>in</strong> df_test.columns]<br>df_final = pd.concat([df_control, df_test], axis=1)</pre><h4>1. Hipotez</h4><ul><li><strong>H0: M1 = M2</strong></li><li><strong>H1: M1 != M2</strong></li></ul><blockquote>Buradaki hipotezlerimizin anlamlarını tekrar hatırlayacak olursak:</blockquote><blockquote><strong>H0:</strong> Average bidding ve Max. bidding’in Purchase ortalamaları arasında istatistiki olarak anlamlı bir fark yoktur.</blockquote><blockquote><strong>H1:</strong> Average bidding ve Max. bidding’in Purchase ortalamaları arasında istatistiki olarak anlamlı bir fark vardır.</blockquote><h4>2. Varsayım Kontrolleri</h4><p>Kontrol edeceğimiz 2 varsayım(Normallik Varsayımı, Varyans Homojenliği) ve bunlara uygulayacağımız testler vardı.</p><p>Burada yapmamız gereken şey 2 varsayım için de ayrı ayrı hipotez kurmak. Temelde yaptığımız şeyse “Varsayım sağlanıyor” hipotezini kurup reddedip reddetmediğimize bakmak</p><pre># <em>normallik varsayım testi</em><br>test_stat, pvalue = shapiro(df_final[&quot;Purchase_control&quot;])<br>test_stat, pvalue = shapiro(df_final[&quot;Purchase_test&quot;])</pre><pre><em># varyans homojenliği<br></em>test_stat, pvalue = levene(df_final[&quot;Purchase_control&quot;],<br>                           df_final[&quot;Purchase_test&quot;])</pre><p>Grupların normallik varsayımı için de varyans homojenliği varsayımı için de p-value değerleri 0.05&#39;ten yüksek. Bu durumda varsayımlar için ayrı ayrı kurduğumuz “H0: Normallik varsayımı sağlanmaktadır.” ve “H0:Varyanslar homojendir.” hipotezleri REDDEDİLEMEZ! Bu sebepler varsayımlar sağlanmıştır ve T testini de buna göre yapmamız gerekir.</p><h4>3. Testin Uygulanması ve Yorumu</h4><p>Burada uygulayacağımız testi varsayım kontrollerinin sonucunun belirleyeceğinden bahsetmiştik. İki varsayım hipotezleri de reddedilemedi, yani varsayımlar sağlandı. O halde biz doğrudan parametrik T testimizi uygulayıp sonucumuza bakabiliriz.</p><pre><em># eğer normallik sağlanıp varyans homojenliği sağlanmasaydı #equal_var=False girilirdi</em><br>test_stat, pvalue = ttest_ind(df_final[&quot;Purchase_control&quot;],<br>                              df_final[&quot;Purchase_test&quot;],<br>                              equal_var=True)<br>print(&quot;Test stat = <strong>%.4f</strong>, p-value = <strong>%.4f</strong>&quot; % (test_stat, pvalue))</pre><p>Son olarak asıl başta kurduğumuz hipotez için p-value değeri 0.3493 olarak bulundu. Bu da demektir ki <strong>p-value=0.3493 &gt; 0.05 olduğundan dolayı H0 hipotezi REDDEDİLEMEZ.</strong> Başta kurduğumuz hipotezi hatırlayalım:</p><blockquote><strong>H0:</strong> Average bidding ve Max. bidding’in Purchase ortalamaları arasında istatistiki olarak anlamlı bir fark yoktur.</blockquote><p>Biz H0 hipotezini REDDEDEMEDİK, bu demektir ki testten önce projenin başında Purchase ortalamalarında bir fark görmüştük fakat bu farklılık şans eseri sonucu ortaya çıktığı ve istatistiki olarak bir fark sağlanamamaktadır. “Average Bidding” sisteminin sonucunda önceki “Maximum Bidding” sisteminden Purchase anlamında farkı olmadığını ve bir katma değer uygulamadığı sonucuna varabiliriz. Dolayısıyla müşterimize buraya harcama ve yatırım yapmasını durdurup eski sistemden devam etmesinde bir sakınca olmadığı yönünde tavsiye verebiliriz.</p><p>Kodların tamamına ve detaylı açıklamalara Kaggle üzerinden ulaşabilirsiniz:</p><p><a href="https://www.kaggle.com/code/furkannakdagg/ab-testi-ile-bidding-y-ntemleri-kar-la-t-rmas?scriptVersionId=100362289">AB Testi ile Bidding Yöntemleri Karşılaştırması</a></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b185b1c9c4aff9bdd5dfc306c70f4b4e/href">https://medium.com/media/b185b1c9c4aff9bdd5dfc306c70f4b4e/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=51e813070d4" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>