I Conducted a comprehensive analysis of crime trends in Winnipeg utilizing Excel and Power BI to extract, clean, process, and visualize data obtained from the Winnipeg Police Service website. Explored the correlation between types of crimes, their spatial distribution across communities and neighborhoods, and their temporal patterns over time.
The project answered 8 of the following questions:
1. Show the total community in Winnipeg?
2. Show the total neighborhood in Winnipeg?
3. Show the total report of crime in Winnipeg?
4. Make an interactive chart that can select a community or a combined of community to see the distribution of crime type?
5. Make a chart to compare the total of reports among community names?
6. Show the trend of total crime cases by year?
7. Show the top 3 neighborhoods with the highest kidnapping case?
8. Make a chart showing the correlation between the number of discharged firearms with intent and the total number of possession/trafficking methamphetamine across 12 months?
Conclusion:
-
Communities with a higher number of crime reports such as downtown and Point Douglas should be prioritized for intervention. This can include increased police presence, community outreach programs, targeted crime prevention strategies and utilizing technology such as surveillance cameras in these areas.
-
2022 shows a peak in crime reports, investigate what factors contributed to this increase. Was there a rise in specific crime types, or were there external factors like economic challenges?
-
2019 until 2021 shows a slow decrease number of crime reports and after 2022 shows a sharp decline in number of crime reports, we can investigate the measures taken during this period. Were there successful community programs, increased police presence, or other effective strategies?
The attachment button below contains a zip file. There are 3 files after unzipping. One file is the raw data I downloaded from here and the other 2 are Excel file which contains cleaned data after I applied many data cleaning methods such as filtering, using Find and Replace, remove duplicated data, Extract month, TRIM() and Power BI file that shows visualization from these data to answer 8 questions above.
I conducted an in-depth analysis of the DVD rental dataset. Leveraged advanced SQL techniques to perform complex table joins, extracting actionable insights to address key business questions. Utilized Power BI for visualization, ensuring a comprehensive understanding and validation of results.
The project answered 8 of the following questions:
1. Show the total customers of this business?
2. Show total sales?
3. Show total inventory of this business?
4. Calculate the average difference between replacement cost and rental rate?
5. Make a chart that shows transactions by day of the week?
6. Compare total sales by employee first name?
7. Show sales by film category?
8. Show an interactive map that illustrates the total payment amount by country around the world?
Conclusion:
-
Sunday has the highest number of transactions (2.2K), while Friday has the lowest (1.4K). This indicates that Sundays are the busiest days, requiring more staff to handle the increased customer flow. Conversely, Fridays are less busy, so fewer staff may be needed. Ensuring that the busiest days are well-staffed can improve overall customer satisfaction. Happy customers are more likely to return and recommend the business to others, leading to increased sales and customer loyalty.
-
With Sports, Sci-Fi, and Animation being the top-selling categories, consider expanding the selection in these genres and promoting them more heavily.
The attachment button below contains a zip file. There are 2 files after unzipping. One file is the raw data I downloaded from here and the other one is Power BI file that shows visualization from these data to answer 8 questions above.