Applying formatting functions
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, whilst others are in uppercase. To unify these, we're creating a new column titled key_standardized. We'll use the upper function to convert all key data to uppercase. This not only cleans our data set, but also prepares it for more accurate analysis. Next, we'll explore the importance of rounding functions through practical application. We'll create two new columns, round down, and round nearest 10. In our first column, we'll use the round down function on our danceability column with the digit zero to return a whole number. Then we can use the round function to approximate the value to our nearest 10 by setting the number of digits to minus one, 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 the year, month, and day functions respectively, enriches our analytical options. To see this in action, we can create a new column called release year, and apply the year function, which takes the argument for serial number. All dates in Excel are stored as serial numbers, so we can select release date 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 release date reconstructed 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'll trim down strings in a new mode short column with the left function to capture the essence of the mode information in just 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!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.