Düsseldorf, Germany
Hello, I'm
Viktor Hudkov
Experienced IT Professional with Knowledge in SQL, Python, Linux and Mentoring.
Certified by Google Cybersecurity Program.
Certified by Google Cybersecurity Program.
About me
My proficiency in managing projects was demonstrated by providing new user guidelines for over 200 data categories. I conducted educational meetings and presentations on topics of PII Security, data optimization and data querying.
Beyond my technical skills, I'm experienced in self-management, critical-thinking and mentorship. I am a swift and curious learner, highly-adaptable, trustworthy and reliable team member.
Berlin Bike Theft
April 2021 – September 2022
Bike thefts
Bike theft is a prevalent issue today. Being a bike rider myself, I've derived thought-provoking insights on the topic of bicycle stealing in such a major city as Berlin.
This analysis serves as a foundation for a more thorough case study, a base from where one can further decide in which direction to dig deeper.
Its goal is to showcase my skill and abilities in the realm of Data Transformation, Data Modeling, Data Analytics and Data Visualization.
The End Result: Visualization of the Data and Deriving Insights
Buro Engineers
Interactive Power BI Report
Once all the data had passed all the levels of transformation and formatting, visualizations are created to derive insights from analysis and to display compelling finds.
First page displays theft statistics narrated by crime location. The heat-map visual is able to be filtered by neighborhood and areas of selection. Zoom function allows further analysis of a more precise position.
The second page presents theft numbers by day of the month and week during which the crime occurred. Bars with the values that exceed specific limit are highlighted in blue, while others remain gray. That has been done to not overload users with information and to quickly draw attention to important numbers.
The third page demonstrates relations between bike theft and the hour of the day. Additionally, it shows the average time people wait before reporting the loss to the police and length of the period for which the bike was left unattended.
April 2018 – March 2021
Data Preparation
I have obtained the datasets from public and government resources including the:
The offical portal for European data
Berlin Open Datata
The first major dataset relates to police reports regarding bike thefts. The second one describes city area codes and zones of urban development.
That all is further used for analyzing the crime activity by loction in Berlin.
Once the data was aqcuired, I had imported the entries into desktop SQL Server Management Studio using Python connector script that I developed. This code makes it possible to upload numerous csv files at once, so the importing process became automated.
Since the data derived from the offical resources was in German language, I have translated the csv files using Python scripts and Google Translate API.
Data Transformation and Data Modeling
Buro Engineers
After having all the necessary data translated and imported into my SQL Server Management Studio, I proceeded with formatting and transforming the data to prepare it for the end goal analysis. At first, I have separated the data into two main tables, defining the columns and information we need and the ones to remove.
I have also renamed and formatted particular columns for better readability and analytics purpose.
Two tables are joined via Location ID values that I've configured to be present in both datasets.
Git codes
Importing the Tables into Power BI
Buro Engineers
After the initial data modeling and transformation, I had the primary datasets ready to be imported into Power BI for further formatting and analysis.
I have added the Tables into Power BI and created a simple data model starring the Bike Theft Stats and the Location Description tables, having Berlin city area code IDs as a relation.
Additional data formatting via Power BI interface took place, to make the entries better suited for analysis and further visualizations. As a next step, I created additional calculated columns and measures using DAX scripts and Power BI functionality.
Insights:
Buro Engineers
• Total Thefts registered in the period from rom December 2022 to April 2023: 5447. Total finacial damage caused by crimes: 6.5M EUR.
• One might think that once a bike has been left unattended for a long period of time, it is highly likely to be stolen, yet the data says that it is not the case. The determining factors were rather the hour of the day and the location where bikes were parked.
• The most enticing hour for bike thefts turned out to be between 1 and 2 in the night. Interestingly enough, the second position for the top-theft hour takes the time between 11 and 12 in the midday. It could mean that people leave their bikes out of reach unsafely around the time they have lunch, which has contributed to bike thefts statistics during the middle of the day.
• There were a few cases where the wait-period until reporting the crime to police is quite long, some outliers lasted for more than a year. It's rather a strange tendency that people still decide to apply to police after such a long time. The reasoning behind it could be some potential social benefits or insurance payments, yet the cases remain individual for every specific bike loss.
• The most popular day for a theft was Monday (the night from Sunday to Monday). Not a great surprise, since it's a given that the night between these two is reserved for a good sleep before start of the work week, granting less people out in the streets.
• The most affected neighborhoods were Wilmersdorf, Kreuzberg, Lichtenberg, Prenzlauerberg. Overall, mostly central regions of the city.
• The target number one for thefts was a Men's Bike. The minimum amount of stolen units goes to Cargo Bikes (most likely due to their scarcity on the streets, in favor to other bike types).
• The average wait-period until reporting a bike theft was 6 days, which demonstrates that, usually, people do not rush immediately to the police. The reasoning behind it is either they do not have the time to start this process right away due to intensive work week, or people simply try to recover their bikes themselves at first.
That sums up my Data Analysis Project on the topic of Bike Theft in Berlin.
.png)
