1. Applying formatting functions
In this video, we'll be working on a fascinating new data set, the most popular songs on Spotify. To get started, make sure you have the popular Spotify songs starter file open, which you'll find in the workbooks folder under the demo subfolder. This data set features a mix of data types, from track names and artist names to release dates, stream counts, keys, and detailed audio metrics.
First up, let's address data consistency within the key column. You'll notice some keys are in lowercase while others are in uppercase. To unify these, we're creating a new column titled key underscore standardized, where we'll use the upper function to convert all key data to uppercase.
This not only cleans our dataset but also prepares it for more accurate analyses. Next, we'll explore the importance of rounding functions through practical application. We'll create two new columns, roundDown and roundNearest10. In our first column, we'll use the roundDown function on our danceability column with the digit 0 to return a whole number.
Then, we can use the round function to approximate the value to the nearest ten by setting the number of digits to minus 1, which can be very useful when grouping data points into certain buckets. Understanding these functions allows for precision control over your data's granularity.
Despite having a comprehensive release date column, breaking this down into release year, release month, and release day columns using year, month, and day functions, respectively, enriches our analytical options. To see this in action, we can create a new column called ReleaseYear and apply the year function, which takes the argument for SerialNumber.
All dates in Excel are stored as serial numbers, so we can select the ReleaseDate column as our argument. We can now repeat this for Release, Month, and Day. Moreover, reconstructing a date from these components offers a deep dive into data manipulation within Excel by creating a ReleaseDateReconstructed column.
We'll employ the date function, combining year, month, and day into a single coherent date, illustrating the versatility of Excel's date functions.
Lastly, we're trimming down strings in a new mode short column, with the LEFT function, to capture the essence of the mode information in just the first three characters.
This exercise in string manipulation reveals Excel's capacity to handle and transform textual data efficiently. Having explored these diverse functions, from data cleaning to complex calculations and string manipulation, you're now equipped to apply these techniques in various contexts. Ready to put your skills to the test?
2. Let's practice!