<?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 Ramya Munagala on Medium]]></title>
        <description><![CDATA[Stories by Ramya Munagala on Medium]]></description>
        <link>https://medium.com/@ramyamunagala7102?source=rss-026e717be17a------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*PvqQrGWt2kUlgGwv</url>
            <title>Stories by Ramya Munagala on Medium</title>
            <link>https://medium.com/@ramyamunagala7102?source=rss-026e717be17a------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 09:06:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@ramyamunagala7102/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[DAX functions (Aggregation functions)]]></title>
            <link>https://medium.com/@ramyamunagala7102/dax-functions-aggregation-functions-edcfaed73707?source=rss-026e717be17a------2</link>
            <guid isPermaLink="false">https://medium.com/p/edcfaed73707</guid>
            <category><![CDATA[dax]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Ramya Munagala]]></dc:creator>
            <pubDate>Mon, 16 Sep 2024 13:37:36 GMT</pubDate>
            <atom:updated>2024-09-16T13:37:36.058Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/826/1*puQvUUQZ6tU8HaonMz6_9g.jpeg" /></figure><p>Introduction:</p><p>As an aspiring data analyst, mastering DAX (Data Analysis Expressions) is an essential step in becoming proficient with tools like Power BI. Today, we begin with the foundation: aggregation functions. These functions, such as SUM, AVERAGE, MIN, and MAX, are crucial for summarizing and analyzing data effectively.</p><ol><li>Average <br> Returns the average (arithmetic mean) of all the numbers in a column</li></ol><p><em>Syntax </em><strong><em>AVERAGE(&lt;column&gt;)</em></strong></p><p><strong>Use Case:</strong> Finding the average customer order value.</p><p><strong>Situation:</strong> A company wants to know how much, on average, a customer spends per order. The AVERAGE function calculates the mean order value by dividing the total amount spent by the number of orders.</p><p>2. AverageA</p><p>Returns the average (arithmetic mean) of the values in a column. Handles text and non-numeric values.</p><p><em>Syntax </em><strong><em>AVERAGEA(&lt;column&gt;)</em></strong></p><p><strong>Use Case:</strong> Calculating the average attendance based on attendance status (TRUE/FALSE).</p><p><strong>Situation:</strong> In an attendance sheet, TRUE means the person was present, and FALSE means absent. If you want to find the average attendance rate, <strong>AVERAGEA</strong> treats TRUE as 1 and FALSE as 0, allowing you to calculate the percentage of attendance.</p><p>Example:</p><p>If a column lists attendance statuses (TRUE for present, FALSE for absent), use <strong><em>AVERAGEA(Attendance)</em></strong> to get the average attendance percentage.</p><blockquote><strong>NOTE: “<em>AVERAGEA</em></strong><em> is especially useful in scenarios where your data might have text or logical values, ensuring a complete picture of averages even when the data isn’t strictly numerical”.</em></blockquote><p>3.Averagex</p><p>Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.</p><p><em>Syntax </em><strong><em>AVERAGEX(&lt;table&gt;,&lt;expression&gt;)</em></strong></p><p><strong>Use Case:</strong> Calculating the average profit margin for a product portfolio.</p><p><strong>Situation:</strong> A retail company wants to know the average profit margin across various products, where profit margin is calculated as (Revenue - Cost) / Revenue. <strong>AVERAGEX</strong> can be used to calculate the margin for each product and then find the overall average.</p><p><strong>Example:</strong><br><strong>AVERAGEX( Products_Table, (Products_Table[Revenue] - Products_Table[Cost]) / Products_Table[Revenue] )</strong><br>This calculates the profit margin for each product, then finds the average across all products.</p><p>And that’s a wrap on today’s deep dive into the world of <strong>AVERAGE</strong> DAX functions! 🧠 You’ve now mastered the art of calculating simple averages, tackled mixed data with <strong>AVERAGEA</strong>, and handled more complex expressions with <strong>AVERAGEX</strong>. From finding that perfect average order size to analyzing profit margins, you’re now equipped to turn data into meaningful insights.</p><p>But don’t get too comfortable! Tomorrow, we’ll be counting on you (pun intended) to join us as we dig into the <strong>COUNT</strong> functions. Whether it’s counting rows, distinct values, or figuring out who’s missing in action, we’re about to make numbers dance in ways you didn’t think were possible. So rest up, because tomorrow, we’re on a mission to <strong>count</strong> the uncountable. See you then! 🔢</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=edcfaed73707" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Entry Level Data Analyst Interview Questions]]></title>
            <link>https://medium.com/@ramyamunagala7102/entry-level-data-analyst-interview-questions-907ac861891a?source=rss-026e717be17a------2</link>
            <guid isPermaLink="false">https://medium.com/p/907ac861891a</guid>
            <category><![CDATA[interview]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Ramya Munagala]]></dc:creator>
            <pubDate>Wed, 04 Sep 2024 14:59:36 GMT</pubDate>
            <atom:updated>2024-09-04T15:01:49.372Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/284/1*_OYL63BbpYvlzyLXs6b4Dw.png" /></figure><p>Entry Level Data Analyst Interview Questions</p><p>Introduction:</p><p>Starting a career as a data analyst can be both exciting and challenging. Recently, I went through several interviews for entry-level data analyst positions and faced a variety of questions. In this article, I’ll share the common questions I encountered and how to prepare for them. Whether you’re a recent graduate or switching careers, this guide will help you get ready for your data analyst interviews</p><p>QUESTIONS:</p><ol><li>Tell me about yourself?</li><li>Project related questions (including internship)?</li><li>Any certifications?</li><li>Skills you are confident in?</li></ol><p>Technical questions:</p><ol><li>Explain the difference between Drop and Delete ?</li></ol><p>Drop command:</p><p>Drop is a DDL Command.</p><p>Drop command is used to remove entire column,table,databases and constraints.</p><p>When you drop a table, you remove the table structure and all its data permanently.</p><p>DROP TABLE Employees;</p><p>You cannot rollback data.</p><p>DELETE Command:</p><p>Delete is DML Command.</p><p>Delete command is used to remove data.It allows for conditional deletion using a WHERE clause, which means you can specify which rows to delete.</p><p>DELETE FROM Employees WHERE Department_ID = 3; will delete all employees in department 3.</p><p>2. Explain the use cases of IN and Exists ?</p><p>Both IN and EXISTS are used to filter data in SQL, but they are suited for different scenarios and have distinct use cases.</p><h3>Use Cases for IN Operator:</h3><ol><li>Fixed List of Values:</li></ol><ul><li>When you need to compare a column against a fixed list of values, IN is very convenient.</li></ul><pre>SELECT EmployeeName <br>FROM Employees <br>WHERE DepartmentID IN (1, 2, 3);</pre><p>This query retrieves the names of employees who belong to departments with IDs 1, 2, or 3.</p><h3>Use Cases for EXISTS Operator:</h3><ol><li>Checking for Existence:</li></ol><ul><li>EXISTS is ideal when you need to check if any rows exist that meet certain criteria.</li></ul><pre>SELECT DepartmentName<br> FROM Departments <br>WHERE EXISTS (    SELECT 1     <br>                  FROM Employees     <br>                  WHERE Employees.DepartmentID = Departments.DepartmentID );</pre><ul><li>This query retrieves the names of departments that have at least one employee.</li></ul><p>3. Write a query to remove duplicates from the table?</p><p>Let’s assume we have a table named Employees with duplicate records based on the emp column. We want to keep the row with the smallest id and delete the others.</p><ol><li>Identify Duplicates:</li></ol><ul><li>First, we need to identify the duplicate rows. We can do this by grouping the rows based on the emp column and using the MIN function to find the smallest id for each group.</li></ul><pre>SELECT MIN(id) AS id FROM Employees GROUP BY emp;</pre><ol><li>Delete Duplicates:</li></ol><ul><li>Next, we use the DELETE statement along with a subquery to remove the duplicate rows.</li></ul><pre>DELETE FROM Employees<br> WHERE id NOT IN <br>(     SELECT MIN(id)     <br>      FROM Employees     <br>      GROUP BY emp );</pre><ol><li>In this query:</li></ol><ul><li>The subquery (SELECT MIN(id) FROM Employees GROUP BY emp) returns the smallest id for each group of duplicates.</li><li>The DELETE statement then removes all rows where the id is not in this list, effectively deleting the duplicates while keeping the row with the smallest id.</li></ul><p>4.Write a query to find the 10 th highest salary ?</p><ol><li>Using LIMIT and OFFSET:</li></ol><ul><li>This approach sorts the salaries in descending order and then skips the first 9 rows to get the 10th highest salary.</li></ul><pre>SELECT salary FROM Employees ORDER BY salary DESC LIMIT 1 OFFSET 9;</pre><p>2. Using a Subquery with DENSE_RANK():</p><ul><li>This approach uses the DENSE_RANK() window function to rank the salaries and then filters for the 10th rank.</li></ul><pre>SELECT salary<br>FROM (<br>    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank<br>    FROM Employees<br>) AS ranked_salaries<br>WHERE rank = 10;</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=907ac861891a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[HR Analysis]]></title>
            <link>https://medium.com/@ramyamunagala7102/hr-analysis-8aa318f8ddaf?source=rss-026e717be17a------2</link>
            <guid isPermaLink="false">https://medium.com/p/8aa318f8ddaf</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[hr-analysis]]></category>
            <dc:creator><![CDATA[Ramya Munagala]]></dc:creator>
            <pubDate>Sat, 06 Apr 2024 08:59:40 GMT</pubDate>
            <atom:updated>2024-04-06T09:03:42.947Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S6Ha7cgHRdfJMw7RfgTG0w.png" /></figure><p>Introduction: Unlocking insights from raw data is akin to solving a captivating puzzle. As a budding data analyst, I embarked on a thrilling adventure to enhance my skills and showcase my abilities. The result? A robust project that combines the power of MySQL and the visual finesse of Power BI.</p><p>Objective:The primary objective of this guided project is to perform comprehensive data cleaning and analysis on employee data within a company using MySQL . Through this project, i have used essential techniques for data cleaning and analysis, including but not limited to date conversion, the implementation of subqueries, and the utilization of filters to obtain accurate and meaningful outputs.</p><p>Data Overview:</p><p>The data corresponding to the project deals with the personal details, and background of the employees of the company.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3JBNHMDZt2oBOH3C0z3hIQ.png" /><figcaption>Dataset</figcaption></figure><p>Data Cleaning: Setting the Foundation</p><p>The initial phase of our journey commenced with data cleaning, a crucial step . Leveraging MySQL’s capabilities, I employed SQL queries with precision, adeptly sifting through the dataset to purge inconsistencies, address missing values, and uphold data integrity. The culmination of these efforts yielded a refined dataset, poised for thorough exploration and analysis.</p><p>Data cleaning in mysql :</p><pre>CREATE DATABASE hr_project;<br><br>USE hr_project;<br><br>SELECT * FROM hr;<br><br>-- changing the column name<br>ALTER TABLE hr<br>CHANGE COLUMN ï»¿id emp_id VARCHAR(20) NULL;<br><br>DESCRIBE hr;<br><br>SELECT birthdate FROM hr;<br><br>SET sql_safe_updates = 0;<br><br>-- changing the data type format of birthdate column from text to date<br><br>UPDATE hr<br>SET birthdate = CASE <br>    WHEN birthdate LIKE &#39;%/%&#39; THEN date_format(str_to_date(birthdate,&#39;%m/%d/%Y&#39;),&#39;%Y-%m-%d&#39;)<br>    WHEN birthdate LIKE &#39;%-%&#39; THEN date_format(str_to_date(birthdate,&#39;%m-%d-%Y&#39;),&#39;%Y-%m-%d&#39;)<br>    ELSE null<br>END;<br><br><br>  ALTER TABLE hr<br>  MODIFY  COLUMN birthdate DATE;<br><br>-- changing the data type format of hire_date column from text to date  <br>UPDATE hr<br>SET hire_date = CASE <br>    WHEN hire_date LIKE &#39;%/%&#39; THEN date_format(str_to_date(hire_date,&#39;%m/%d/%Y&#39;),&#39;%Y-%m-%d&#39;)<br>    WHEN hire_date LIKE &#39;%-%&#39; THEN date_format(str_to_date(hire_date,&#39;%m-%d-%Y&#39;),&#39;%Y-%m-%d&#39;)<br>    ELSE null<br>END;<br><br>SELECT termdate FROM hr;<br><br>-- changing the data type format of termdate and filling missing values<br><br>UPDATE  hr<br>SET termdate = IF(termdate IS NOT NULL AND termdate!=&#39;&#39;,date(str_to_date(termdate,&#39;%Y-%m-%d %H:%i:%s UTC&#39;)),&#39;0000-00-00&#39;)<br>WHERE true;<br><br>SET sql_mode =&#39;ALLOW_INVALID_DATES&#39;;<br><br>ALTER TABLE hr<br>MODIFY COLUMN termdate DATE  ;<br><br>ALTER TABLE hr<br>MODIFY COLUMN hire_date DATE  ;<br><br>SELECT COUNT(*) FROM hr<br>WHERE age &lt; 18;<br><br><br>-- to add new column date to table<br><br>ALTER TABLE hr <br>ADD COLUMN age INT;<br><br>UPDATE hr<br>SET age = timestampdiff(YEAR,birthdate,CURDATE());<br><br></pre><p>Questions:</p><pre>-- 1.What is the gender breakdown of employees in the company?<br>SELECT gender,COUNT(*) AS count<br>FROM hr<br>WHERE age &gt; 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1023/1*i4fUYYs7hXHXoe_D3mIMrg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/1*CiuOAcoaxEFcCpcmdIWG5Q.png" /></figure><pre>-- 3. What is the age distribution of employees in the company<br><br>SELECT CASE WHEN age &gt; 18 AND age &lt;25 THEN &#39;18 - 24&#39;<br>            WHEN age &gt; 25 AND age &lt;35 THEN &#39;25 - 34&#39;<br>            WHEN age &gt; 35 AND age &lt;45 THEN &#39;35 - 44&#39;<br>            WHEN age &gt; 45 AND age &lt;=60 THEN &#39;45 - 60&#39;<br>            ELSE &#39;65+&#39;<br>            END AS Age_group, COUNT(*) AS count<br>            FROM hr<br>   WHERE  age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>            GROUP BY 1</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/942/1*CGonQiNNL8iAZZCoiKWJUQ.png" /></figure><pre>-- 4. How many employees work at headquarters versus remote locations?<br><br>SELECT location , COUNT(*) AS count<br>FROM hr<br>WHERE  age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1<br>ORDER BY 2 DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/784/1*8mmcFm1H0qZl5otRohZiIA.png" /></figure><pre>-- 5.What is the average length of employment for employees who have been terminated?<br><br>SELECT round(AVG(datediff(termdate,hire_date))/365,0) AS avg_duration<br>FROM hr<br>WHERE termdate&lt;= curdate() AND termdate &lt;&gt;&#39;0000-00-00&#39; AND age &gt;= 18;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/785/1*GOYiP44_kRH7JIEHdrsV3Q.png" /></figure><pre>-- 6. How does gender distribution vary across deparments and job titles?<br><br>SELECT department,gender,COUNT(*) AS count<br>FROM hr<br>WHERE age&gt; 18 AND termdate =&#39;0000-00-00&#39;<br>GROUP BY 1,2<br>ORDER BY 1 ;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/875/1*7xTfyvFcJsAPyieEcApOQg.png" /></figure><pre>-- 7.What is the distribution of job titles across thecompany?<br><br>SELECT jobtitle,COUNT(*) AS  count<br>FROM hr<br>WHERE age&gt; 18 AND termdate =&#39;0000-00-00&#39;<br>GROUP BY 1<br>ORDER BY 1 ;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g8zfhD51W23hfEOAZB4qxg.png" /></figure><pre>-- 8.Which department ha highest turnover rate?<br><br>SELECT department,total_count,terminated_count,<br>    terminated_count/total_count AS termination_rate<br>FROM(SELECT department,COUNT(*) AS total_count,<br>     SUM(CASE WHEN termdate &lt;&gt; &#39;0000-00-00&#39; AND termdate &lt;= CURDATE() THEN 1 ELSE 0 END) AS terminated_count<br>     FROM hr<br>     WHERE age &gt;= 18<br>     GROUP BY department) AS subquery<br>ORDER BY 4 desc;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/991/1*rvJH2dR53A70p-Z00YOmvw.png" /></figure><pre>-- 9.What is the distribution of employess across locations by city and state?<br><br>SELECT Location_state , COUNT(*) AS count<br>FROM hr<br>WHERE age &gt;= 18 AND termdate = &#39;0000-00-00&#39;<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/958/1*SmnsMK24as-SIv77J3Ieqg.png" /></figure><pre>-- 10.How has company&#39;s employee count changed over time based on hire and term dates?<br><br>SELECT years,hires,Terminations,<br>hires - terminations AS net_change,<br>round((hires-terminations)/hires *100, 2) AS net_change_percent<br>FROM ( SELECT YEAR(hire_date) AS years,<br>    COUNT(*) AS hires,<br>       SUM(CASE WHEN termdate &lt;&gt; &#39;0000-00-00&#39; AND termdate &lt;= CURDATE() THEN 1 ELSE 0 END) AS Terminations<br>       FROM hr<br>       WHERE age &gt;= 18<br>       GROUP BY 1) AS subquery<br>       ORDER BY 1 ;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/951/1*B90cnJUEw4SCvLrvoeZ0Uw.png" /></figure><pre>-- 11. What is the tenure distributon of each department?<br><br>SELECT department,ROUND(avg(datediff(termdate,hire_date)/365),0) AS Avg_tenure<br>FROM hr<br>WHERE age &gt;= 18 AND termdate &lt;&gt; &#39;0000-00-00&#39; AND termdate &lt;= CURDATE()<br>GROUP BY 1<br>ORDER BY 2;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/872/1*hmvVbDfQFyX-aSSM9YXBgw.png" /></figure><p>Leveraging CSV Outputs for Power BI Integration:</p><blockquote>After refining the dataset, we imported the query outputs in CSV format, ready to be seamlessly integrated into Power BI for visualization and further analysis.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i2xJPm3PeoC_cWsowVWoNA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DwtJHBXcnzVs3K0a_TN8NA.png" /></figure><p>Insights:</p><p>Insight 1: Gender Balance<br> — More men than women work here, suggesting we might need to focus on promoting gender equality in our hiring and promotion practices.</p><p>Insight 2: Racial Diversity<br> — Most employees are White, while Native Hawaiian and American Indian employees are few. We should work on recruiting a more diverse workforce.</p><p>Insight 3: Age Groups<br> — Most employees are between 25–44 years old, showing that we have a young workforce. We should consider tailoring our policies to meet the needs of this age group.</p><p>Insight 4: Workplace Preference<br> — Many employees prefer working at headquarters instead of remotely. Understanding why could help us improve our remote work policies.</p><p>Insight 5: Employee Loyalty<br> — Employees tend to stay for about 7 years before leaving. We should find out why they leave and try to keep them longer.</p><p>Insight 6: Departmental Turnover<br> — Some departments have higher turnover rates than others. We need to investigate why this is happening and find ways to improve retention.</p><p>Insight 7: Location of Employees<br> — Most employees are from Ohio, indicating we might need to expand our recruitment efforts to other regions for diversity.</p><p>Insight 8: Changes Over Time<br> — Our workforce has been growing or changing over the years. We should understand why and plan accordingly for the future.</p><p>Insight 9: Departmental Loyalty<br> — Employees in some departments stay longer than others. We should learn from these departments to improve retention across the organization.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8aa318f8ddaf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Power query vs Power pivot]]></title>
            <link>https://medium.com/@ramyamunagala7102/power-query-vs-power-pivot-9cf6aa91c9bd?source=rss-026e717be17a------2</link>
            <guid isPermaLink="false">https://medium.com/p/9cf6aa91c9bd</guid>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Ramya Munagala]]></dc:creator>
            <pubDate>Fri, 02 Feb 2024 10:53:54 GMT</pubDate>
            <atom:updated>2024-02-02T10:53:54.762Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/798/1*gGfT1GKuJFwZKtoRuxvNJQ.png" /></figure><p>Introduction:</p><p>Certainly! Let’s discuss Power Pivot and Power Query in the context of Power BI, using a simple example and highlighting key aspects.</p><p>Power Query(ETL):</p><p><em>Key Aspects:</em></p><ul><li>Data Transformation: Power Query is used for importing, transforming, and cleaning data from various sources before it’s loaded into the Power BI data model.</li><li>Query Editor: The Query Editor in Power Query provides a graphical interface for shaping and transforming data.</li></ul><blockquote><em>“</em><strong>Example</strong><em>:</em> Imagine you have a CSV file with sales data. You can use Power Query to import the data, filter out unnecessary columns, rename columns, and perform transformations like converting data types or merging tables. The steps you perform in Power Query are recorded in the “Applied Steps” window.”</blockquote><p>Power Pivot:</p><p><em>Key Aspects:</em></p><ul><li>Data Modeling: Power Pivot is used for creating relationships between tables, defining calculated columns, measures, and working with DAX formulas for advanced analytics.</li><li>In-Memory Engine: Power Pivot utilizes an in-memory engine, allowing for fast and efficient analysis of large datasets.</li></ul><blockquote><strong><em>Example</em></strong><em>:</em> Assuming you have a Power BI model with tables for Sales and Products, you can create a relationship between them. You might then create a calculated column to calculate the total revenue using a DAX formula.</blockquote><p>“As a Power BI user, I leverage Power Query for data import and transformation. For instance, I recently used Power Query to import a CSV file containing sales data, where I cleaned and transformed the data in the Query Editor.</p><p>On the other hand, Power Pivot is instrumental in creating relationships within my Power BI model. In a recent project, I established a relationship between Sales and Products tables. I then used Power Pivot to define a calculated column, ‘Total Revenue,’ using a DAX formula to multiply the quantity by the unit price. This allowed for more advanced analytics within the Power BI model, taking advantage of the in-memory engine for optimal performance.”</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9cf6aa91c9bd" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bank Loan Analysis]]></title>
            <link>https://medium.com/@ramyamunagala7102/bank-loan-analysis-301411d58f08?source=rss-026e717be17a------2</link>
            <guid isPermaLink="false">https://medium.com/p/301411d58f08</guid>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Ramya Munagala]]></dc:creator>
            <pubDate>Tue, 23 Jan 2024 07:33:00 GMT</pubDate>
            <atom:updated>2024-01-23T07:55:09.177Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*WqklkQSPbb96TIgs" /></figure><p>Introduction:</p><p>During my internship at Ai variant, I had the exciting opportunity to dig into a bank dataset and unravel the mysteries behind loan defaulters. It was like being handed a puzzle, and I was determined to decode it.</p><p>This post is a peek into my internship journey, where I’ll share the ups and downs of analyzing data to predict loan defaults. Let’s dive into the world of numbers together and discover the stories hidden behind the data. Join me as I break down the complexities, sharing the lessons learned and insights gained along the way.</p><p>Data Overview:</p><p>The data corresponding to the project deals with the personal details, professional and credential background of the customers of the bank.The dataset has two files finance_1 &amp; finance_2 .</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YWoEDEcznqm7BFC_kL6vrA.png" /><figcaption>Finance_1 dataset</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3oCTYcvvvT9BKbZRCdPIjA.png" /><figcaption>Finance_2 dataset</figcaption></figure><p>The both files has 39 thousand records and a common column id.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/225/1*g8x6rQGPKAsh1e7xbiysLA.png" /></figure><p>Objectives:</p><p>The main goal was to take a handful of important performance metrics (KPIs) and turn them into easy-to-understand visualizations. The aim was to create clear charts and graphs that not only tackled the specific KPIs given but also made it simple for everyone to grasp how the company was doing. This objective pushed me to enhance my data analysis skills and communicate insights visually, helping to make better-informed decisions based on the data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/128/1*Ed-ZZfIiX98Pa8266_3RVw.png" /></figure><p>KPI’s:</p><p>Ø.Year wise loan amount stats</p><p>Ø.Grade and subgrade wise revol_bal</p><p>Ø.Total Payment for Verified Status Vs Total Payment for Non-Verified Status</p><p>Ø.State wise and last_credit_pull_d wise loan status</p><p>Ø. Home ownership Vs last payment date stats</p><p>Data Cleaning:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/128/1*WUJnhAYQm2_CL6gH8EgI7w.png" /></figure><p>In preparing our dataset for analysis, the first crucial step involved meticulous data cleaning to ensure the integrity and relevance of the information. Here are the key actions taken:</p><p>1. Removing Duplicate or Irrelevant Observations:<br> — Duplicate entries and irrelevant observations, such as descriptions and loan titles, were identified and systematically removed. This process aimed to streamline the dataset, keeping only the essential information for our analysis.</p><p>2. Handling Empty (Null) Values:<br> — Null values in specific columns, including `Revol_util`, `last_credit_d_pull`, and `next_pymnt_d`, were addressed. To maintain the dataset’s completeness and accuracy, records with null values in these columns were removed.</p><p>3. Eliminating Redundant Column — `Payment_plan`:<br> — The column `Payment_plan` contained a single constant value ’n’ across all records, rendering it irrelevant for our analysis. To optimize the dataset’s simplicity and efficiency, the entire column was removed.</p><p>These data cleaning steps were crucial in ensuring that the dataset used for subsequent analysis was free from redundancies, irrelevant information, and missing values, setting the stage for a more accurate and insightful examination of the chosen key performance indicators.</p><p>Dashboard:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*onoBcbMF3UMSNBSrEaZ-RA.png" /></figure><p><strong>KPI 1<br> Year wise loan amount stats:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/715/1*1fbmfMjydBc2OWFTnwg5uQ.png" /><figcaption>Kpi 1</figcaption></figure><p>The KPI 1 is to find out the relation between loan amount taken by customers and issue_d.It compares the loan amount taken by customers from year to year.To show the raise or drop in loan amount taken line chart is used.</p><p>Sql query for KPI 1:</p><pre>SELECT RIGHT(issue_d,4) AS year,<br>CONCAT(CAST(SUM(loan_amnt)/100000 AS DECIMAL(10,2)),&#39;Millions&#39;) as total_loan_amount<br>from finance_1_new<br>group by 1<br>order by 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/490/1*-5gX04eO8A3XWWyGhp-ATg.png" /></figure><p><strong>KPI 2<br> Grade and subgrade wise revol_bal:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/468/1*6Kay9ilKzYZkrPoo5_W9Wg.png" /><figcaption>Subgrade wise</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/406/1*U0CR1SvcuNBAr6tJqVpH4g.png" /><figcaption>Grade wise</figcaption></figure><p>The KPI 2 is to find out the relation between Grade , sub-grade and revol_bal.It shows the total revol_bal contributed from each sub-grade and grade.To easily interpret the data matrix table is taken and data bars in matrix table shows revol_bal respective to grade &amp; sub-grade.</p><p>Sql query for KPI 2:</p><pre>-- Grade and sub grade wise revol_bal<br>SELECT f1.grade,f1.sub_grade,SUM(f2.revol_bal) AS sum_revol_bal<br>FROM finance_1_new AS f1<br>LEFT JOIN finance_2 AS f2<br>ON f1.id=f2.id<br>GROUP BY f1.grade,f1.sub_grade<br>ORDER BY f1.grade,f1.sub_grade;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/373/1*d6TWsgXVG-HtTiTu5UYqqg.png" /><figcaption>Grade &amp; sub grade wise</figcaption></figure><pre>SELECT <br>    f1.grade, <br>    CONCAT(CAST(SUM(f2.revol_bal) / 1000000 AS DECIMAL(10, 2)), &#39; millions&#39;) AS sum_revol_bal_in_millions<br>FROM <br>    finance_1_new AS f1<br>LEFT JOIN <br>    finance_2 AS f2 ON f1.id = f2.id<br>GROUP BY <br>    f1.grade<br>ORDER BY <br>    f1.grade;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/341/1*gawC-5psrNZ6PwKaoDWBcw.png" /><figcaption>Grade wise</figcaption></figure><p><strong>KPI 3<br> Total Payment for Verified Status Vs Total Payment for Non-Verified Status:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/574/1*J2t1jwsIWVEfRbn5dhGgfQ.png" /><figcaption>Kpi 3</figcaption></figure><p>The KPI 3 is to find out the relation between loan amount taken by customers and issue_d.To show verified vs non-verified customers contribution for total payments. We used donut chart.</p><p>Sql query for KPI 3:</p><pre>-- Total Payment for Verified Status Vs Total Payment for Non-Verified Status<br>SELECT f1.verification_status,<br>CONCAT(CAST(ROUND(SUM(f2.total_pymnt) / 1000000, 2) AS DECIMAL(10, 2)), &#39; millions&#39;) as total_payment<br>FROM finance_1_new f1<br>LEFT JOIN finance_2 f2 ON f1.id = f2.id<br>GROUP BY f1.verification_status<br>HAVING f1.verification_status IN (&#39;verified&#39;, &#39;not verified&#39;);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/391/1*C8-GKZ1emJ7NnUvWNnRYKg.png" /></figure><p><strong>KPI 4<br> State wise and last_credit_pull_d wise loan status:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/679/1*X6KEULReqTJocALv5zDhRQ.png" /><figcaption>Kpi 4</figcaption></figure><p>The stacked column chart is used to represent the state wise loan status.To represent the last_credit_pull_d wise loan status, the line chart has been used.</p><p>Sql query for KPI 4:</p><pre>-- State wise and last_credit_pull_d wise loan status<br>SELECT f1.addr_state,f1.loan_status,count(f2.last_credit_pull_d)AS Total_count<br>FROM finance_1_new f1<br>LEFT JOIN finance_2 f2<br>ON f1.id=f2.id<br>GROUP BY 1,2<br>ORDER BY 1,2;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/669/1*7FC06Wz_Hlz78ob4ueAQtQ.png" /></figure><p><strong>KPI 5<br> Home ownership Vs last payment date stats:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*R9dk-MugWTEtH77op8pCGw.png" /><figcaption>Kpi 5</figcaption></figure><p>The KPI5 is to find out the relation between home ownership of customers and last payment date.To compare the last payment of customers with their home ownership status.</p><p>Sql query for KPI 5:</p><pre>-- home ownership vs last payment stats<br>SELECT f1.home_ownership,count(last_pymnt_d) AS count_last_pymnt_d<br>FROM finance_1_new f1<br>LEFT JOIN finance_2 f2<br>ON f1.id=f2.id<br>GROUP BY 1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/415/1*AZAB2YtksCzlqfVuqbpYyQ.png" /></figure><p>Insights:</p><ol><li>We can clearly see that there is gradual increase in loan amount taken from one year to another year.</li><li>Revol_bal is more in grade B customers and least in grade G customers.</li><li>The data indicates a clear distinction in total payments between verified and non-verified status. Verified accounts, comprising 41.12% of the total, have contributed $153.54 million, while non-verified accounts, representing 58.88%, have contributed $219.89 million.</li><li>Fully Paid had the highest total Count of loan_status at 32,950, followed by Charged Off at 5627 and Current at 1140.</li><li>Across all 5 home ownership. Count of last payment range from 3 to 18899.Rent accounted for 47.54% of last payment.And none has least last payment count.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=301411d58f08" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[IMDb Data Analysis using Tableau]]></title>
            <link>https://medium.com/@ramyamunagala7102/imdb-data-analysis-using-tableau-f64f5bc05f62?source=rss-026e717be17a------2</link>
            <guid isPermaLink="false">https://medium.com/p/f64f5bc05f62</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <dc:creator><![CDATA[Ramya Munagala]]></dc:creator>
            <pubDate>Fri, 22 Dec 2023 07:57:57 GMT</pubDate>
            <atom:updated>2023-12-22T07:57:57.874Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d7Kb6KZnsYs9zqAIyLotrw.png" /></figure><h3><strong>Introduction:</strong></h3><p>IMDb stands for the Internet Movie Database. It is an online <a href="https://www.imdb.com/">database</a> of information related to films, television programs, home videos, video games, and streaming content. IMDb provides a wide range of information about movies, TV shows, and entertainment industry professionals.</p><figure><img alt="https://www.imdb.com/" src="https://cdn-images-1.medium.com/max/1024/1*mNJQfj6hslI7EpJddn2jfw.png" /></figure><p>The key features are:<br><strong>Movie and TV Show Database</strong>: IMDb contains a vast collection of information about movies and television shows, including details about the cast, crew, release dates, genres, plot summaries, and user ratings.</p><p><strong>User Ratings and Reviews</strong>: Users can rate and review movies and TV shows on IMDb, contributing to an overall user rating for each title. These ratings are often used as an indication of the popularity and perceived quality of a film or TV show.</p><p><strong>Celebrity Information</strong>: IMDb provides profiles for actors, directors, writers, and other entertainment industry professionals. These profiles include details about their filmography, awards, and biographical information.</p><p><strong>Box Office Information</strong>: IMDb includes box office data, providing information on the financial performance of movies in theaters.</p><p><strong>Lists and Rankings</strong>: IMDb generates various lists and rankings, such as top-rated movies, popular TV shows, and industry-related rankings.</p><h3><strong>Dataset:</strong></h3><p>The <a href="https://www.kaggle.com/datasets/harshitshankhdhar/imdb-dataset-of-top-1000-movies-and-tv-shows">dataset</a> used for this analysis is the IMDB Dataset of top 1000 movies and tv shows .It contains information regarding top 1000 movies and tv shows.<br>Contents of the dataset are:<br>Poster_Link — Link of the poster that imdb using<br>Series_Title = Name of the movie<br>Released_Year — Year at which that movie released<br>Certificate — Certificate earned by that movie<br>Runtime — Total runtime of the movie<br>Genre — Genre of the movie<br>IMDb_Rating — Rating of the movie at IMDB site<br>Overview — mini story/ summary<br>Meta_score — Score earned by the movie<br>Director — Name of the Director<br>Star1,Star2,Star3,Star4 — Names of the cast<br>No_of_votes — Total number of votes<br>Gross — Money earned by that movie</p><h3>Problem Statement:</h3><p>To create a dashboard in tableau using imdb top 1000 dataset.Initially i have framed questions to understand more about the dataset.The questions are<br>1. What are the top 10 movies based on the number of votes on IMDb?(It gives us idea about which film is most viewed and loved by viewers. )<br>2. How many movies have each certificate (e.g., G, PG, PG-13, R, etc.) on IMDb?(Examines the distribution of movies based on certificates.)<br>3. How many movies were released in each year according to IMDb’s database?(Gives info about count of movies released from the late 1950’s to present.)<br>4. What is the total gross for movies in each year?(Analyzing the total gross for movies each year can highlight the financial success of the film industry over time.)<br>5. What are the top 10 genres based on IMDb ratings, and what are their respective average ratings?(Understanding the IMDb ratings for top genres can provide insights into the genres that are generally well-received by the audience.)<br>6. Which are the top 10 genres based on Metascore, and what are their average Metascore ratings?(Comparing Metascore ratings with IMDb ratings for top genres can offer a perspective on how critical reviews align with audience opinions. )</p><h3><strong>Data Analysis:</strong></h3><p>Importing Data into Tableau Public<br> First, the Tableau Public software should be downloaded from the site <a href="https://public-pantheon.tableau.com/en-us/s/download">here</a> and installed. Next, we will be importing an Excel file (IMDB Top 1000 dataset) which contains a dataset of top 1000 movies and tv shows.<br> The imported dataset contains single sheet.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DdXCbU4U59xNfogT5CW-Pg.png" /><figcaption>open tableau public</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kXrL1l4y7unAg8yJVs_3-g.png" /><figcaption>connect to dataset</figcaption></figure><h3>1. What are the top 10 movies based on the number of votes on IMDb?</h3><p>From the Dimensions in the Data pane, drag Series title to the rows shelf. From Measures, drag Number of votes to the Columns shelf. Tableau generates a chart with top 10 movies based on the number of votes .</p><p>Next, we will be filtering our results by number of votes, so, drag the Series title on the Dimensions Data pane to the Filter card to include or exclude values in the view. Then apply filter on top 10 based on number of votes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oUjNt25Ak7LQ6AqBATDEng.png" /><figcaption>Top 10 movies by number of votes</figcaption></figure><h3>2.How many movies have each certificate (e.g., G, PG, PG-13, R, etc.) on IMDb?</h3><p>From the Dimensions in the Data pane, drag Certificates to the rows shelf and drag Series title to the Columns shelf.Aggregate the series title by count function. Tableau generates count of total movies per each category.<br> Next, we will get null values which does not have any certificate ,exclude the null values by dragging the certificates into filter card and exclude the null value</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*k-9zWJ1dO0SRM8ugp2TlpA.png" /><figcaption>certificates by total movies</figcaption></figure><h3>3.Genre</h3><p>Drag the genres into the text in marks card and it shows all the genres in the worksheet.To easily differentiate which genre has more movies drag the series title into shape in marks card.In this case if drama has more movies the font of drama is more compared to other genres.Then we can easily identify which genre has more movies.</p><p>“<em>To get genres which have more than 5 movies at least drag the series title and aggregate with count function then set range from 5 to maximum value.<br>In dashboard we use worksheet as filter.”</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6RXzwlJ3HLviy8GB1XVcdQ.png" /><figcaption>Genre</figcaption></figure><h3>4. How many movies were released in each year according to IMDb’s database?</h3><p>From the Dimensions in the Data pane, drag Release year to the rows shelf and drag series title to the Columns shelf.Aggregate the series title by count function. Tableau generates count of total movies per year.</p><p>Area visualization is used for this problem statement to easily interpret the data in the visualization.Whether there is an increase or decrease in production of movies.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ai8m7b7HHV4DLtMKLfLMBQ.png" /><figcaption>Total movies by release year</figcaption></figure><h3>5.What is the total gross for movies in each year?</h3><p>From the Dimensions in the Data pane, drag Release year to the rows shelf and drag series title to the Columns shelf.Aggregate the series title by count function. Tableau generates gross per year.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pbjvK0ZrcyeyuGEsU9-fvA.png" /><figcaption>Gross by year</figcaption></figure><h3>6.What are the top 10 genres based on IMDb ratings, and what are their respective average ratings?</h3><p>From the Dimensions in the Data pane, drag IMDb ratings to the rows shelf. From Measures, drag Genres to the Columns shelf. Tableau generates a chart with IMDb rating by top 10 genres .<br> Next, we will be filtering our results by count of series title belongs to genre, so, drag the Genre on the Dimensions Data pane to the Filter card to include or exclude values in the view. Then apply filter on top 10 based on count of series title.Drag release year and series title into detail in marks card to give additional information.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1jKDNRwqo6FM3DNuPEDJxA.png" /><figcaption>Filtering the top 10 genres based on count of movies</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b6oC4pxpMaD69JHe1jntEg.png" /><figcaption>IMDb rating by top 10 genres</figcaption></figure><h3>7.Which are the top 10 genres based on Metascore, and what are their average Metascore ratings?</h3><p>From the Dimensions in the Data pane, drag Meta score to the rows shelf. From Measures, drag Genres to the Columns shelf. Tableau generates a chart with Meta score by top 10 genres .<br> Next, we will be filtering our results by count of series title belongs to genre, so, drag the Genre on the Dimensions Data pane to the Filter card to include or exclude values in the view. Then apply filter on top 10 based on count of series title.Drag release year and series title into detail in marks card to give additional information.We can easily identify outliers in boxplot visualization.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eAOSL86pRvo6lNE1AWRQcA.png" /><figcaption>Meta score by top 10 genre</figcaption></figure><h3>DASHBOARD CREATION:</h3><p>In addition to above mentioned visuals additional kpis are used in the dashboard creation<br>To show more details about data kpi’s like release year,Genre,Cast,Duration of series title,overview(description of movie),IMDb rating,Meta score,Gross,<br>Number of votes are added.And for these kpis filter is applied based on series title and customized the filter in single dropdown.</p><p><em>“For example if 3 idiots is marked in filter then these kpis gives the release year,genre,cast and other information of the movie to make dashboard more interacting.”</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HF-avw0t9mM9hceIFpkUGQ.png" /><figcaption>Description of movie</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CfVoAM0KpdnsTVDPJEbv6g.png" /><figcaption>Genre of movie</figcaption></figure><p>Similarly we have taken all the information related to series title and used as additional KPI’s to the dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5kkydsUpUCMbczb7wDY5TQ.png" /></figure><p>we can select the movie from the filter and additional kpis will give detailed information about movie like Description,Release year genre,cast,IMDb rating,Meta score,Gross,Number of votes.</p><h3>Dashboard:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EWru6QlT79Y6qKd4bjsUmA.png" /><figcaption>Dashboard</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mdkUG22C2M4gUIPE5b-sGQ.png" /></figure><p>“<em>In the dashboard Genre is used as filter,so,when we click on single genre then the filter is applied to all the visuals in the dashboard.”</em></p><p>Insights:</p><p>1.Analyzing movies with the highest number of votes can provide an understanding of which films have garnered the most attention and engagement from the IMDb user community.Shawshank Redemption has more number of votes compared to others.</p><p>2. Examining the distribution of movie certificates can offer insights into the audience demographics and preferences. For example, a higher number of movies with a PG-13 certificate may indicate a focus on a teenage or young adult audience.Most rated movies are mostly under U certification and less under 16 certificate.</p><p>3.Observing the distribution of movies over the years can reveal trends in the film industry. A spike in a particular year might be associated with a significant cultural or technological shift.</p><p>4.Analyzing the total gross for movies each year can highlight the financial success of the film industry over time. Peaks and valleys in the gross may coincide with major blockbuster releases or economic factors.</p><p>5.Understanding the IMDb ratings for top genres can provide insights into the genres that are generally well-received by the audience. It can also help identify specific movies within each genre that have achieved high acclaim.</p><p>6. Comparing Metascore ratings with IMDb ratings for top genres can offer a perspective on how critical reviews align with audience opinions. Consistency or divergence between the two metrics may indicate interesting dynamics in genre preferences.</p><p>These insights can guide further analysis and decision-making, whether in the context of movie production, distribution, or audience targeting. Keep in mind that the actual insights will depend on the specific data and trends observed in your IMDb dataset.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f64f5bc05f62" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[McDonald’s Nutrition Analysis:]]></title>
            <link>https://medium.com/@ramyamunagala7102/purpose-c3bc1f8030d9?source=rss-026e717be17a------2</link>
            <guid isPermaLink="false">https://medium.com/p/c3bc1f8030d9</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Ramya Munagala]]></dc:creator>
            <pubDate>Thu, 14 Dec 2023 10:55:15 GMT</pubDate>
            <atom:updated>2023-12-14T11:00:50.897Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7WcrZKNsKMpxd9kQdqCD7A.jpeg" /></figure><h3><strong>Purpose:</strong></h3><p>The objective of this data analysis project was to explore and gain insights into the nutrition facts of McDonald’s menu items. The primary goal was to conduct a thorough exploration of the dataset, including the examination of data types, to gain valuable insights into the nutritional aspects of the menu items.</p><h3><strong>Dataset:</strong></h3><p>This dataset provides a nutrition analysis of every menu item on the US McDonald’s menu.</p><p>https://www.kaggle.com/<a href="https://www.kaggle.com/datasets/mcdonalds/nutrition-facts">datasets</a>/mcdonalds/nutrition-facts</p><h3>Business Question:</h3><ol><li>Which food categories exhibit the highest order counts, providing insights into customer preferences?</li><li>How does the average trans fat content vary across different food categories?</li><li>Is there a discernible relationship between protein and carbohydrate content in McDonald’s menu items?</li><li>What is the relationship between total calories and calories from fat, and are there items contributing significantly to calorie intake?</li><li>Which are the top 5 food categories with the highest percentage of saturated fat?</li></ol><h3>Let’s begin!</h3><h4><strong>Tools of the Trade:</strong></h4><p>Following libraries</p><p>NumPy-linear algebra, Pandas -data processing, matplotlib - graph plotting, and seaborn - data visualization.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1E_c79solaXXreDcMWqHxA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hS2gXPgAWak98nc6jsqfGw.png" /><figcaption><strong>dataset for analysis</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AVwF467H2JuNLCVOZJuHqw.png" /><figcaption><strong>first 2 columns of data</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Uqb1bnq6A5p6uVMaaN_L1g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*izIpxpMOrwxmfvraXYs4xA.png" /><figcaption><strong>Checking for the null values</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/732/1*vAyChFvdHk-u4BdB4JW5Mw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/882/1*SZVFqoFqdVR8Gk8HcascIQ.png" /><figcaption><strong>Categories in menu</strong></figcaption></figure><h3>Answer Business Questions with Visualization</h3><ol><li>Which food categories exhibit the highest order counts, providing insights into customer preferences?</li></ol><pre>plt.figure(figsize= (6,3))<br>menu_category=menu.Category.value_counts().plot(kind=&#39;bar&#39;,color=&#39;skyblue&#39;)<br>plt.title(&#39;Number of orders per each Food category&#39;)<br>plt.xlabel(&#39;Category&#39;)<br>plt.ylabel(&#39;Count of orders&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1022/1*Vyya1AzzsRMxVzIz6YlSVg.png" /></figure><p>2.How does the average trans fat content vary across different food categories?</p><pre># Assuming &#39;menu&#39; is your DataFrame<br>trans_val = menu.groupby(&#39;Category&#39;)[&#39;Trans Fat&#39;].mean()<br># Define pastel colors<br>pastel_colors = [&#39;#FFB6C1&#39;,&#39;#87CEEB&#39;,&#39;#98FB98&#39;,&#39;#FFD700&#39;,&#39;#FFA07A&#39;,&#39;#C71585&#39;,&#39;#00FA9A&#39;,&#39;#87CEFA&#39;,&#39;#FF6347&#39;]<br><br># Plotting the bar chart<br>plt.figure(figsize=(8, 3))<br>plt.bar(trans_val.index,trans_val,color=pastel_colors)<br>plt.title(&#39;Average Trans Fat for Each Food Category&#39;)<br>plt.xlabel(&#39;Category&#39;)<br>plt.ylabel(&#39;Average Trans Fat&#39;)<br>plt.xticks(rotation=90)<br><br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D_2GBQYDX_V9RrxFYXxJsg.png" /></figure><p>3.Is there a discernible relationship between protein and carbohydrate content in McDonald’s menu items?</p><pre># create a scatter plot of protein vs. carbohydrates<br>plt.rc(&#39;font&#39;, family=&#39;sans-serif&#39;, size=10)<br>plt.subplots(figsize = (8,4), dpi = 100)<br>sns.scatterplot(x=&#39;Protein&#39;,hue = &#39;Category&#39;, y=&#39;Carbohydrates&#39;, data=menu)<br><br># set the title and x and y axis labels<br>plt.title(&#39;Protein vs. Carbohydrates Scatter Plot&#39;)<br>plt.xlabel(&#39;Protein (g)&#39;)<br>plt.ylabel(&#39;Carbohydrates (g)&#39;)<br><br><br># show the plot<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_Clh-lwBc4kiG30I4DW_3A.png" /></figure><p>4.What is the relationship between total calories and calories from fat, and are there items contributing significantly to calorie intake?</p><pre>plt.figure(figsize=(10, 4), dpi=100)<br><br>correlation = menu[&#39;Calories&#39;].corr(menu[&#39;Calories from Fat&#39;])<br>plt.scatter(menu.Calories, menu[&#39;Calories from Fat&#39;], color=&#39;grey&#39;)<br>plt.text(500,450,&#39;r = {}&#39;.format(round(correlation,2)))<br>plt.xlabel(&quot;Calories&quot;)<br>plt.ylabel(&quot;Calories from Fat&quot;)<br>plt.title(&quot;Relationship between Calories and Calories from Fat&quot;)<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iu64Ne4YPrnxHa-24R9eKw.png" /></figure><p>5.Which are the top 5 food categories with the highest percentage of saturated fat?</p><pre>saturated_cholesterol = menu.groupby(&#39;Category&#39;)[&#39;Saturated Fat&#39;].mean().dropna().nlargest(5)<br>#Bar Graph for Percent of Saturated Fat<br>plt.figure(figsize=(10, 3), dpi=100)<br>saturated_cholesterol.sort_values(ascending=False).plot.bar(color = &#39;#87CEFA&#39;)<br>plt.title(&quot;Top 5 Categories that have the Highest Percent of Saturated Fat&quot;)<br>plt.ylabel(&quot;Percent&quot;)<br>plt.xlabel(&quot;Menu Category&quot;)<br>plt.xticks(rotation=90)<br>plt.show();</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S7kTtuWYNTamxTfqh9yHGg.png" /></figure><h3>Conclusions:</h3><ol><li>Identified popular food categories, providing a holistic view of customer preferences.</li><li>Uncovered variations in trans fat levels across different menu categories, offering insights into potential health considerations.</li><li>Examined the balance between protein and carbohydrate content, shedding light on nutritional composition.</li><li>Identified items contributing significantly to calorie intake and examined the proportion of calories derived from fat.</li><li>Identified categories that may have elevated health risks due to saturated fat content, facilitating informed decision-making.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c3bc1f8030d9" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>