Embedded Files
Introduction
This is a personal project in which I applied my knowledge of Excel to prepare and process data, and also create visualizations. Before explaining the project step by step, it’s important to provide some context for better understanding.
I am a big fan of video games, but one of my all-time favorites is called Dark Souls. This game belongs to the RPG genre, which requires a lot of attention and analysis of the data provided within the game, since every item or object has values that can either benefit or disadvantage you in different situations.
To summarize, in this game your character always carries weapons and armor, each with specific stats. The set of armor or weapon you equip affects both your defense and the damage you deal to enemies. However, you cannot use just any item, since your character’s stats limit which weapons and armor can be equipped.
This project specifically focuses on analyzing the statistics of each armor in the game, in order to help determine which one is the most suitable depending on the area you are in or the type of enemy you are facing. For example, you may want higher fire defense in a fire-filled area, or lightning defense if you are fighting an enemy that deals lightning damage. At the same time, the armor must be compatible with your character’s stats.
For this project, I created multiple visualizations so that users can choose the one that best helps them find the information they need. The project includes:
A general visualization of all armor statistics.
A comparison view of two armors.
A stat-focused visualization of all armors.
And finally, a breakdown by armor type (heavy, medium, or light), allowing stats comparisons across each armor type.
Data source
The data was obtained from a Reddit forum, where someone had already compiled all the armor stats from the game into an Excel file. I verified the information using other websites as well as the game itself.
The Excel file I downloaded contained only the statistics for each individual armor piece, divided into four sheets: head, chest, hands, and legs.
I’ve attached the link in case you’d like to check out the forum where I originally downloaded the data.
Cleaning Data
As I mentioned in the data source section, the data I obtained was provided in Excel tables but was separated into different sheets (head, chest, hands, and legs). Additionally, some armor pieces had unrelated names across the sheets despite belonging to the same armor set. There were also some values that were not relevant to my analysis, so I discarded them and did not include them when creating the pivot tables.
Raw Data - hands table
Therefore, the first thing I did was to combine all the data from the four tables into a single table and modify the abbreviated column names by writing out the full words. Then, I created a new column called Armor Set, which groups each armor piece into its respective set, since sometimes the names of individual pieces did not match. For example, there is an armor set called Adventurer, which consists of four pieces, each with a different name. The helmet is called Helm of the Wise, while the gauntlets are called Gauntlets of the Vanquisher, so the piece names had no clear relation to the set name. I also added a column for the armor type, since it is important to identify whether they are heavy, medium, or light armors, as players can only use certain types of armor depending on their character’s stats. There are a total of 58 armor sets.
Cleaned data
Document structure
This is the structure of the Excel file I created. As shown in the image, I made multiple sections that the user can access, either to go directly to a visualization or, if they prefer to view the data from the tables I built, they just need to click on the option and will be redirected to another window. I also added a brief explanation to each option so the user knows what they can do in each section.
Armor pieces table
In this section, the user can view the table I created, already combined and modified for better insights.
Armor set pivot table
The user can easily view the armor statistics, since this pivot table shows the armor sets with their total stats, rather than displaying them piece by piece unless the set is expanded. It is important to note that any modification to this pivot table will affect the chart in the ‘Armor Set Stats Chart’ section.
Armor set stats chart
This chart displays all the armors with their statistics in a horizontal layout. It provides a general overview and can be filtered by armor type.
Armor set stats comparison
This visualization allows the user to compare two armors in case they need to evaluate armors with similar statistics. I created this chart by generating two new pivot tables from the combined table, and I also made modifications to the charts and filters to ensure proper visualization.
Armor set table (transpose)
It is the same table as the Armor Set Table, but I applied a transpose to display it vertically in case it is easier for the user to view the data this way. I also used it generate a pivot table and to generate a different chart (explained in the next section).
Armor set per stats chart
This general chart allows the user to directly compare armors by stats, making it easier to see which armor is better in relation to a specific statistic. I created this chart using a pivot table based on the transposed table.
Armor Set Per Type Charts
As the final visualization, I created these charts, which are divided by armor type and can be filtered by the desired statistic. This allows the user to determine which armor type performs best according to the selected statistic. Additionally, I added cards to display the top three armors based on the chosen statistic. I consider this visualization the most helpful, as it quickly provides a more specific and filtered answer to the user’s needs.
These charts were made possible thanks to the following procedures:
I divided the main table (the first one I created for the project) based on armor type.
Heavy armor
Medium armor
Light armor
I applied the following steps to each table:
I created a pivot table to consolidate the statistics for each armor set.
Then, I transposed that pivot table and converted it into a regular table, so I could create a new pivot table, which I used for the final chart. i had to do all these steps because i can not transpose directly a pivot table, so i had to create a new transposed table first and then create the new pivot table. I could have used the first table, but it would not be separated by armor type, so i did it to make all this process more visual.
I added a filter to each chart so the user can search by statistic type.
I placed functions below the pivot tables to calculate the maximum value and the next two, allowing me to create a top 3
Both functions are used to display the values that follow the maximum value.
The following functions are used to find the corresponding armor name for the value obtained with the MAX function and the others.
I also created these functions in case there was a tie, so the chart would display “Tied.”
Set Armor Pictures
The final Excel sheet includes images of all the armors, allowing the user to check if they like the armor’s appearance.
In conclusion, the best armor is the one that fits your character’s build, the situation you are in, and the enemy you are facing. Therefore, it is not possible to say that one armor is the best overall, but this project allows users to identify which armors are best suited for their current situation.
Page updated
Google Sites
Report abuse