<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Patrick Garcia  on Medium]]></title>
        <description><![CDATA[Stories by Patrick Garcia  on Medium]]></description>
        <link>https://medium.com/@josephpatrick.garcia.98?source=rss-f408bd1da4c3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*-vySnfRvZifzI50y-hGB8A.jpeg</url>
            <title>Stories by Patrick Garcia  on Medium</title>
            <link>https://medium.com/@josephpatrick.garcia.98?source=rss-f408bd1da4c3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 10:01:48 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@josephpatrick.garcia.98/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[Google Data Analytics Capstone Project: Cyclistic Case Study (SQL & Tableau)]]></title>
            <link>https://medium.com/@josephpatrick.garcia.98/google-data-analytics-capstone-project-cyclistic-case-study-sql-tableau-7381c4d69f8a?source=rss-f408bd1da4c3------2</link>
            <guid isPermaLink="false">https://medium.com/p/7381c4d69f8a</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[google-analytics]]></category>
            <dc:creator><![CDATA[Patrick Garcia ]]></dc:creator>
            <pubDate>Fri, 28 Jul 2023 17:54:50 GMT</pubDate>
            <atom:updated>2023-07-28T17:58:54.304Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*Fu7bMXqnbDD3lUZuNesnJA.png" /></figure><p><strong>BACKGROUND:</strong> The analysis is based on the first case study of the Google Data Analytics Certificate. I am assuming the position of a ‘Jr. Data Analyst’ role at Cyclitic, a bike-share company based in Chicago. The aim of the analysis is to understand the behaviors between Cyclistic’s two main users: members and non-members. The reason why the company would like to understand the behaviors between the two is that the company’s membership numbers have been stagnant and the management team does not have strategies to help increase it. Analyzing the behaviors between the two would create insights that would help create strategies to promote their membership plan.</p><p>The data will be cleaned and analyzed using SQL and Tableau and will follow Google’s six-step process of processing data: ask, prepare, clean and process, analyze, share, and act.</p><h4>Ask</h4><p>In this phase, it is important to understand what are the major questions that drive the analysis. Given that the company has a problem with increasing its membership numbers and it has data about its users, it begs the question: “<strong>How do annual members and casual riders use Cyclistic bikes differently?</strong>”</p><h4>Prepare</h4><p>The data for this analysis was collected from <a href="https://divvy-tripdata.s3.amazonaws.com/index.html">here</a>. It should be noted that only Quarter I data from 2023 were used due to the fact that not all data were available yet for 2023. Each table were in a .csv format and I was able to combine them all using the following function in SQL:</p><pre>--combines the data<br>CREATE TABLE `keen-airlock-388921.Cylistics.Q1_trips` AS <br>SELECT *<br><br>FROM (<br>SELECT * FROM `keen-airlock-388921.Cylistics.apr_trip`<br>UNION ALL<br>SELECT * FROM `keen-airlock-388921.Cylistics.feb_trips`<br>UNION ALL<br>SELECT * FROM `keen-airlock-388921.Cylistics.march_trip`<br>UNION ALL<br>SELECT * FROM `keen-airlock-388921.Cylistics.jan_trips`<br>  <br>);</pre><p>I also checked the length of ride_id’s (primary key) to make sure that I know what the primary key looks like. I also checked if there are any null values per row and if there are any duplicates.</p><pre>--checks length of ride_ids<br>SELECT length(ride_id) <br><br>FROM `keen-airlock-388921.Cylistics.Q1_trips` <br><br>--checks total rows (1066014)<br>SELECT count(*) <br>FROM `keen-airlock-388921.Cylistics.Q1_trips`<br><br>-- checks if ride_ids have any duplicates (returns 1066014 values which means no duplicate ride_ids)<br>SELECT count(distinct (ride_id)) <br>FROM`keen-airlock-388921.Cylistics.Q1_trips`<br><br><br>--check the number of nulls per row<br><br>SELECT <br>  COUNT(*) - COUNT(ride_id) AS ride_id_count,<br>  COUNT(*) - COUNT(rideable_type) AS rideable_type_count,<br>  COUNT(*) - COUNT(started_at) AS started_at_count,<br>  COUNT(*) - COUNT(ended_at) AS ended_at_count,<br>  COUNT(*) - COUNT(start_lat) AS start_lat_count,<br>  COUNT(*) - COUNT(start_lng) AS start_lng_count,<br>  COUNT(*) - COUNT(end_lat) AS end_lat_count, --861 null values<br>  COUNT(*) - COUNT(start_station_name) AS start_station_name_count, -- 151918 null values<br>  COUNT(*) - COUNT(start_station_id) AS start_station_id_count, -- 152050 null values<br>  COUNT(*) - COUNT(end_station_name) AS end_station_name_count, -- 161646 null values<br>  COUNT(*) - COUNT(end_station_id) AS end_station_id_count, -- 161787 null values<br>  COUNT(*) - COUNT(member_casual) AS member_casual_count,<br>  COUNT(*) - COUNT(end_lng) AS end_lng_count -- 861 null values<br><br>FROM<br>`keen-airlock-388921.Cylistics.Q1_trips`</pre><p>I also did the following queries to figure out how many stations there are, and how many member types there are</p><pre>-- checks how many stations there are (50 stations)<br>SELECT DISTINCT end_station_name AS end_station<br><br>FROM `keen-airlock-388921.Cylistics.Q1_trips_valid`<br><br>-- confirms the station count (50 stations still)<br>SELECT DISTINCT start_station_name AS start_station<br><br>FROM `keen-airlock-388921.Cylistics.Q1_trips_valid`<br><br>-- confirms that member type is only 2 values which are member and casual riders<br><br>SELECT DISTINCT member_casual AS member_Type<br><br>FROM `keen-airlock-388921.Cylistics.Q1_trips_valid`</pre><h4>Clean and Process</h4><p>Once I understood what values have NULL values, I created a new table where there would be no NULL values to continue cleaning</p><pre>-- removes nulls<br>-- due to limitations in BigQuery, I cannot use the DELETE FROM function. Instead, I will be selecting the tables and creating a new table called Q1_trips_valid to bypass the paywall<br><br>SELECT * <br><br>FROM `keen-airlock-388921.Cylistics.Q1_trips`<br><br>WHERE <br><br>start_station_name IS NOT NULL AND<br>end_station_name IS NOT NULL AND<br>end_station_id IS NOT NULL AND<br>end_lng IS NOT NULL AND<br>start_station_id IS NOT NULL;</pre><p>After there are no NULL values and duplicate values, it’s time to add new rows for analysis purposes. I appended the table to include the following:</p><ul><li>Day of the week AS day_of_week</li><li>Month AS month</li><li>Day AS day</li><li>Year AS year</li><li>ride length as ride_length</li><li>formatted timestamp AS time</li></ul><p>I also included data that have a valid time length (more than 1 minute and less than 24 hours)</p><pre>SELECT<br>  ride_id,rideable_type,start_station_name, end_station_name, start_lat, start_lng,end_lat, end_lng, member_casual AS member_type,started_at,<br>  CASE <br>    WHEN EXTRACT (DAYOFWEEK FROM started_at) = 1 THEN &#39;SUN&#39;<br>    WHEN EXTRACT (DAYOFWEEK FROM started_at) = 2 THEN &#39;MON&#39;<br>    WHEN EXTRACT (DAYOFWEEK FROM started_at) = 3 THEN &#39;TUE&#39;<br>    WHEN EXTRACT (DAYOFWEEK FROM started_at) = 4 THEN &#39;WED&#39;<br>    WHEN EXTRACT (DAYOFWEEK FROM started_at) = 5 THEN &#39;THU&#39;<br>    WHEN EXTRACT (DAYOFWEEK FROM started_at) = 6 THEN &#39;FRI&#39;<br>  ELSE &#39;SAT&#39;<br>  END AS day_of_week,<br><br>  CASE<br>    WHEN EXTRACT (MONTH FROM started_at) = 1 THEN &#39;JAN&#39;<br>    WHEN EXTRACT (MONTH FROM started_at) = 2 THEN &#39;FEB&#39;<br>    WHEN EXTRACT (MONTH FROM started_at) = 3 THEN &#39;MAR&#39;<br>    WHEN EXTRACT (MONTH FROM started_at) = 4 THEN &#39;APR&#39;<br>  ELSE &#39;UNKOWN&#39;<br>  END AS month,<br>  EXTRACT (DAY FROM started_at) AS day,<br>  EXTRACT (YEAR FROM started_at) AS year,<br>  TIMESTAMP_DIFF (ended_at, started_at, minute) AS ride_length_m,<br>  FORMAT_TIMESTAMP(&quot;%I:%M %p&quot;, started_at) AS time<br><br><br>FROM `keen-airlock-388921.Cylistics.Q1_trips_valid`<br><br>WHERE TIMESTAMP_DIFF (ended_at, started_at, minute) &gt; 1 AND TIMESTAMP_DIFF (ended_at, started_at, hour) &lt; 24</pre><h4>Analyze</h4><p>Once the data were cleaned, I can now analyze the data answering the question “<strong>How do annual members and casual riders use Cyclistic bikes differently?”, </strong>which can be answered in two main categories: behaviors in the difference between time spent on the bicycle and the routes each users take. The analysis on the time spent on the bicycle were made through many queries because I would like to know the length of the ride in terms of minutes and hours while also knowing the trends when people ride the bikes in days and months. The following queries were used:</p><p>Trip time to calculate analysis on bike ride minutes (average ride length, minimum ride length, and maximum ride length)</p><pre> -- trip time (minutes) <br>            -- average trip time this is for total users<br>            SELECT  ROUND(AVG(ride_length_m), 2)<br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            -- average trip time for each group. this shows 10.74 minutes for members and 19.64 for casual riders<br>            SELECT  ROUND(AVG(ride_length_m), 2) AS average, member_type<br>            <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            group by member_type<br>            <br>            --max trip time for both members<br>            SELECT  ROUND(max(ride_length_m), 2) AS max_trip_time, member_type<br>          <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            -- minimum amount of time spent on a bike divided per group<br>            group by member_type<br>          <br>            SELECT  ROUND(min(ride_length_m), 2) AS max_trip_time, member_type<br>            <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            group by member_type<br></pre><p>Trip time to calculate when the bike ride is mostly being used throughout the week (Monday — Sunday)</p><pre>-- trip time (day)<br>            -- this query checks what days are the most popular ones within the week <br>            SELECT  count(day_of_week) AS count, day_of_week<br>            <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            GROUP BY day_of_week<br>            <br>            -- this query checks for the most popular and least popular day for casual riders<br>            SELECT  count(day_of_week) AS count, day_of_week<br>            <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            WHERE member_type = &#39;casual&#39;<br>            <br>            GROUP BY day_of_week<br>            <br>            -- this query checks for the most popular and least popular day for members riders<br>            SELECT  count(day_of_week) AS count, day_of_week<br>            <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            WHERE member_type = &#39;member&#39;<br>            <br>            GROUP BY day_of_week</pre><p>Analysis of when bike rides are popular throughout the months of the first quarter (January — April)</p><pre> --trip time (month)<br>      <br>            -- this query selects the number of frequency of rides per month for members<br>            SELECT  count(month) AS count, month<br>            <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            WHERE member_type = &#39;member&#39;<br>            <br>            group by month<br>            <br>            -- this query selects the number of frequency of rides per month for casual riders<br>            SELECT  count(month) AS count, month<br>            <br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean` <br>            <br>            WHERE member_type = &#39;casual&#39;<br>            <br>            group by month</pre><p>Analysis of the popular hours each user rides the bikes</p><pre>      --trip time (hour)<br><br>            SELECT <br>              EXTRACT (HOUR from started_at) AS time_of_day, count (*) AS occurances, member_type<br>            FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br><br>            GROUP BY<br>              time_of_day , member_type<br>            ORDER BY<br>              time_of_day DESC</pre><p>Analysis of the stations of each bike ride (most and least popular start and end stations for members and non-members)</p><pre>-- shows the least and most frequented start station name for casual riders<br>            SELECT count(*) AS rank, start_station_name<br>              FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE member_type = &#39;casual&#39;<br>            <br>            GROUP BY <br>              start_station_name<br>            ORDER BY<br>              rank DESC<br>      <br>      -- shows the least and most frequented start station name for members riders<br>            SELECT count(*) AS rank, start_station_name<br>              FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE member_type = &#39;member&#39;<br>            <br>            GROUP BY <br>              start_station_name<br>            ORDER BY<br>              rank DESC<br>      -- shows least and most frequented end station name for members<br>      <br>            SELECT count(*) AS rank, end_station_name<br>              FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE member_type = &#39;member&#39;<br>            <br>            GROUP BY <br>              end_station_name<br>            ORDER BY<br>              rank DESC<br>      <br>      -- shows least and most frequented end station name for casual riders<br>            SELECT count(*) AS rank, end_station_name<br>              FROM `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE member_type = &#39;casual&#39;<br>            <br>            GROUP BY <br>              end_station_name<br>            ORDER BY<br>              rank DESC<br>      <br>      -- Checks the most popular routes<br>            SELECT<br>              COUNT(*) AS frequency,<br>              end_station_name,<br>              start_station_name<br>            FROM<br>              `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            GROUP BY<br>              start_station_name,<br>              end_station_name<br>            ORDER BY<br>              frequency DESC LIMIT 2;<br>      <br>      <br>      <br>      -- checks the most popular routes for casual riders<br>            SELECT<br>              COUNT(*) AS frequency,<br>              end_station_name,<br>              start_station_name<br>            FROM<br>              `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE<br>              member_type = &#39;casual&#39;<br>            GROUP BY<br>              start_station_name,<br>              end_station_name<br>            ORDER BY<br>              frequency DESC LIMIT 2;<br>      <br>      -- checks the most popular routes for members<br>            SELECT<br>              COUNT(*) AS frequency,<br>              end_station_name,<br>              start_station_name<br>            FROM<br>              `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE<br>              member_type = &#39;member&#39;<br>            GROUP BY<br>              start_station_name,<br>              end_station_name<br>            ORDER BY<br>              frequency DESC LIMIT 2;<br>      <br>      -- checks the least popular route for member<br>            SELECT<br>              COUNT(*) AS frequency,<br>              end_station_name,<br>              start_station_name<br>            FROM<br>              `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE<br>              member_type = &#39;member&#39;<br>            GROUP BY<br>              start_station_name,<br>              end_station_name<br>            ORDER BY<br>              frequency ASC LIMIT 2;<br>      <br>      <br>      -- checks the least popular route for casual riders<br>            SELECT<br>              COUNT(*) AS frequency,<br>              end_station_name,<br>              start_station_name<br>            FROM<br>              `keen-airlock-388921.Cylistics.Q1_trips_valid_clean`<br>            <br>            WHERE<br>              member_type = &#39;casual&#39;<br>            GROUP BY<br>              start_station_name,<br>              end_station_name<br>            ORDER BY<br>              frequency ASC LIMIT 2;</pre><h4>Share</h4><p>In this phase of the project, I am sharing the insights I received from my analysis through visualizations using Tableau. For a better experience, I highly recommend checking out my dashboard <a href="https://public.tableau.com/app/profile/patrick.garcia6896/viz/GoogleDataAnalyticsProjectCyclisticBikeVisualAnalysis/CyclisticDashboardMemberandCasualRiders">here</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/795/1*-30mg__4v63L0BAKDOTU4A.png" /><figcaption>Insight #1: The actual percent of casual rides there is in the first quarter is 27%. This means that 27% is the figure the company ought to push to decrease depending on their goal.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/834/1*WmAcPN-Zzs0sxJyhz3QUgQ.png" /><figcaption>Insight #2: It seems that classic bikes are more popular than electric bikes for both users (members and non-members). This means that classic bikes might become the most marketable bikes to use when advertising Cyclistics</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/837/1*2bzOX2n1k9n-ktk9rOS1mw.png" /><figcaption>Insight #3: Members might be using the bikes more for work while non-members are using the bikes for leisure due to the fact that non-members increase their use on Saturday and Sunday while members seem to use the bikes more during the weekdays.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*ZfSvZ8XzqAanavmF8JBktQ.png" /><figcaption>Insight #4: Member riders use the bikes for work (starts at 8 am and ends around 6 pm) while non-embers seem to not have a high of a spike when they start and when they finish. This means that to increase the number of members, the company could have a package for users who use bikes for leisure</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vB2RGGcaqJP3Tb9S6TsCMg.png" /><figcaption>Insight #5: This shows the increase of trend for each user. This could be due to many factors</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bLq5nXUqsRfriYnauqxjWw.png" /><figcaption>This shows a map on when each user often starts and ends</figcaption></figure><h4>Act</h4><p><em>Key Takeaways:</em></p><blockquote>Q1 members often use the bikes for work while non-members use the bike for leisure</blockquote><blockquote>Q1 members often use the bike during the weekdays while non-members often use the bikes during the weekends</blockquote><blockquote>Both users prefer classic bikes to electric</blockquote><blockquote>The Average ride time for members is less than casual riders (10.74 minutes for member riders and 19.64 minutes for casual riders)</blockquote><p><em>Recommendations:</em></p><ol><li>) Provide a membership package to categorize riders into two groups: leisure riders (people who use the bikes for leisure activities) and business riders (people who use the bikes for school or work).</li><li>) When marketing Cyclitic bikes, use classic bikes as the main model of the bikes because most users like that more</li><li>) Design classic bikes are unique for leisure riders and business riders. This enforces the idea of users being either a “leisure rider” or a “business riders”. This allows the user to be a part of a group identity and become a part of something bigger than them which could be attractive to some.</li><li>) Make sure that there are enough bikes to handle the volume of riders during the weekends for leisure riders and weekdays for business riders.</li><li>) Since the ride time for leisure riders are often longer, create a marketing effort to show them that users whose ride time is longer than 10 minutes can become a member</li></ol><p>Next steps:</p><p>Some recommendations for the next steps are to create a breakdown on the analysis of the drop-off and pick-up stations. This will allow the company to understand what locations are most popular for members and non-members.</p><p>Thank you for reading my article! Hope you learned a lot here and understood how data analysis can create impactful insights on organizations. To learn more about my methodology on this case study, you can view the related case studies I am attaching below. Also, if you have any feedback on how I can improve, I am always open to feedback. Leave a comment and don’t forget to clap! Thank you!</p><p>Inspiration:</p><p><a href="https://medium.com/@iadeniran.hse/google-data-analytics-capstone-project-cyclistic-how-does-a-bike-share-navigate-speedy-success-304036d4f371">https://medium.com/@iadeniran.hse/google-data-analytics-capstone-project-cyclistic-how-does-a-bike-share-navigate-speedy-success-304036d4f371</a></p><p><a href="https://medium.com/@iainselliott/google-data-analytics-capstone-project-cyclistic-case-study-8baed2f5a286">https://medium.com/@iainselliott/google-data-analytics-capstone-project-cyclistic-case-study-8baed2f5a286</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7381c4d69f8a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Cleaning with Excel: An Analysis of Customer Feedback Dataset]]></title>
            <link>https://medium.com/@josephpatrick.garcia.98/data-cleaning-with-excel-an-analysis-of-customer-feedback-dataset-5debed2a5f69?source=rss-f408bd1da4c3------2</link>
            <guid isPermaLink="false">https://medium.com/p/5debed2a5f69</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <dc:creator><![CDATA[Patrick Garcia ]]></dc:creator>
            <pubDate>Wed, 19 Jul 2023 23:59:59 GMT</pubDate>
            <atom:updated>2023-07-27T00:35:50.340Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0xry-kflWn-hRAN0onqCjg.jpeg" /></figure><p>Data cleaning is an essential step in the data analysis process, ensuring that raw datasets are transformed into a usable and accurate format. In this article, we will explore how to clean and preprocess the “Customer Feedback Dataset” sourced from Kaggle. By leveraging Excel’s powerful functions, we will separate, trim, and manipulate data to create a clean and organized dataset for further analysis.</p><p>Dataset Overview: The “Customer Feedback Dataset” obtained from Kaggle contains valuable feedback from customers, which can be used to gain insights and improve business operations. However, before conducting any analysis, we need to clean the data to ensure consistency and eliminate errors.</p><h3>Step 1: Importing the Data into Excel</h3><p>Downloading the Dataset: Start by downloading the “Customer Feedback Dataset” from the Kaggle website (<a href="https://www.kaggle.com/datasets/vishweshsalodkar/customer-feedback-dataset">https://www.kaggle.com/datasets/vishweshsalodkar/customer-feedback-dataset</a>).</p><h3>Step 2: Exploring the Data</h3><p>Before diving into data cleaning, take some time to explore the dataset and understand its structure, including the number of columns, data types, and any potential issues or inconsistencies.</p><blockquote>Finding #1: There are 97 rows of data and only 1 column. All data is combined using comma values. When using the function =TEXTSPLIT(A1, “,”), there is a total of 7 Columns and 97 rows</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7CWRbpdF0XFp9DhDaO8jLg.png" /></figure><blockquote>Finding #2: There are multiple duplicates in the dataset. When using conditional formatting, Column A has multiple duplicates.</blockquote><blockquote>Finding #3: There are no null values</blockquote><blockquote>Finding #4: The column “Sentiment” is a boolean value (Positve/Negative), Date/Time Value has the format yyyy-mm-dd hh:mm:ss, and confidence score is in an integer value with 2 decimal places, the rest are normal texts</blockquote><h3>Step 3: Cleaning the Data</h3><p>Checklist of cleaning data:</p><ul><li><strong>Trim White Spaces:</strong> Before anything, I wanted to remove whitespaces using =TRIM(A:H) and pasted it to a new sheet with only values.</li><li><strong>Removing Duplicates:</strong> I used =UNIQUE (A:H, FALSE) to keep only the unique values and pasted the values (75 rows were left)</li><li><strong>Separating Date and Time:</strong> I used =LEFT(e, 12) to get the date and =RIGHT(e,8) for time.</li><li><strong>Formatting Data:</strong> The dates were formatted yyyy-mm-dd and the time were formatted using hh:mm:ss. I used Text to Columns to activate each cell so that they were formatted.</li><li><strong>Rounding Time:</strong> For analysis purposes, I rounded the time (hh:m:ss) to the nearest hour using =MROUND(A2, “1:00”) to a new column named time_round.</li><li><strong>Geography Data Type:</strong> I changed the location data type to Geography by DATA &gt; Data Types&gt; Geography. This is done for analysis later.</li></ul><p>Now the dataset is cleaned, it is ready to be analyzed!</p><h3>Step 4: Analyzing the Data</h3><p>I analyzed the data set using pivot tables. I created 5 Pivot tables which created many insights about customer feedback.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iWHUlITNUiqeD5ARw7oa3A.png" /><figcaption>Pivot table #1: Average Confidence Score by Location, Pivot Table #2: Time of Reviews Being Sent, Pivot Table #3: Beakdown of Negative and Positive Reviews Per Location</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/510/1*HuLtDamp3av0miEWrTyliA.png" /><figcaption>Pivot Table #4: Count of Sentiments, Pivot Table #5: Composition of Sources</figcaption></figure><h3>Step 5: Visualizing the Data</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*frm18TmZ3HLoHGP3xdsrrg.png" /></figure><p>Using, Excel, I visualized data using the pivot tables I created which created three main insightsabout the dataset I received.</p><blockquote>Insight 1: There are more positive reviews, than negatives.</blockquote><blockquote>Insight 2: Berlin, New York, and Florida have all positive reviews, while Chicago and San Francisco have negative reviews.</blockquote><blockquote>Insight 3: Most people send their reviews around 9 AM</blockquote><p>Cleaning the data was definitely fun and I learned a lot of things going into it. It was fun because I was able to do a variety of things to the data, such as creating visualizations and pivot tables. Moreover, it was fun because I was able to gather insights about the data set. One main thing I learned about cleaning data is the importance of it. At first, when I received the data set, it did not make any sense, however, once the data set was cleaned, I was able to gather a lot of important information about and tell a lot of narratives from it. For future projects, this has been beneficial because I can use the skills I have learned here and apply them to others as well.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5debed2a5f69" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>