Data Cleaning and Analysis
Budget: $30 – $250 AUD
Hi, I need some help with Excel. I have a file called FantasticFilms.xlsx that contains information on around 510 movies. The file opens with a memo sheet, but the actual raw data is stored in a sheet named “FantasticFilms_Dirty.” Please do not make any changes directly to this sheet. Instead, make a copy of it and name the copy “Clean Data.” All of the work you carry out should be done in the Clean Data sheet so that the original data always remains preserved.
In the Clean Data sheet, carefully check the dataset and begin by removing any duplicate rows so that every film only appears once. If you come across missing or blank cells, replace them with the word “Unknown” so it is clear that information was missing rather than simply left empty. Next, standardise all of the text categories so they are written consistently. For example, ensure that the genre column uses the same format for every entry and that all genres appear in Title Case, such as “Drama,” “Comedy,” and “Action.” You should also check that the MPAA Rating field, actor names, and countries are written consistently and not repeated in different formats.
After standardising text fields, make sure that all numeric columns are properly formatted as numbers. This applies to columns such as Budget, Gross, Runtime, Rating, and Rating Count. If any of these columns are currently stored as text, convert them to numeric data. You should also check that the Release Date column is written in a single consistent date format, for example day-month-year, so that all entries match.
Once you have completed the cleaning, create a new sheet named “Client Memo.” In this sheet, please write out a short record of exactly what you did during the cleaning process. Use dot points to describe each step clearly, for example “Removed duplicate films,” “Replaced missing genres with Unknown,” “Standardised genres to Title Case,” and “Converted Gross values to numbers.” This memo is simply a record of the cleaning decisions you made.
After the data has been cleaned, create a pivot table using the Clean Data sheet. The pivot table should show the Total Box Office Revenue grouped by Genre. Place the Genre field in the rows and set the Gross values to be summed in the values area. Save this pivot table in its own sheet named “Pivot Table.”
From this pivot table, create a pivot chart to make the data easier to read visually. Use a Column Chart as the format, although a bar chart is acceptable if the layout looks clearer. Add a descriptive chart title, for example “Total Box Office Revenue by Genre,” and make sure the axis labels are included so that the chart is easy to interpret. Save this chart in a separate sheet named “Pivot Chart.”
When the work is complete, the Excel workbook must contain exactly five sheets arranged in this order: Client Memo, FantasticFilms_Dirty, Clean Data, Pivot Table, and Pivot Chart. The sheets must be named exactly as written so that they are easy to identify.
In the Clean Data sheet, carefully check the dataset and begin by removing any duplicate rows so that every film only appears once. If you come across missing or blank cells, replace them with the word “Unknown” so it is clear that information was missing rather than simply left empty. Next, standardise all of the text categories so they are written consistently. For example, ensure that the genre column uses the same format for every entry and that all genres appear in Title Case, such as “Drama,” “Comedy,” and “Action.” You should also check that the MPAA Rating field, actor names, and countries are written consistently and not repeated in different formats.
After standardising text fields, make sure that all numeric columns are properly formatted as numbers. This applies to columns such as Budget, Gross, Runtime, Rating, and Rating Count. If any of these columns are currently stored as text, convert them to numeric data. You should also check that the Release Date column is written in a single consistent date format, for example day-month-year, so that all entries match.
Once you have completed the cleaning, create a new sheet named “Client Memo.” In this sheet, please write out a short record of exactly what you did during the cleaning process. Use dot points to describe each step clearly, for example “Removed duplicate films,” “Replaced missing genres with Unknown,” “Standardised genres to Title Case,” and “Converted Gross values to numbers.” This memo is simply a record of the cleaning decisions you made.
After the data has been cleaned, create a pivot table using the Clean Data sheet. The pivot table should show the Total Box Office Revenue grouped by Genre. Place the Genre field in the rows and set the Gross values to be summed in the values area. Save this pivot table in its own sheet named “Pivot Table.”
From this pivot table, create a pivot chart to make the data easier to read visually. Use a Column Chart as the format, although a bar chart is acceptable if the layout looks clearer. Add a descriptive chart title, for example “Total Box Office Revenue by Genre,” and make sure the axis labels are included so that the chart is easy to interpret. Save this chart in a separate sheet named “Pivot Chart.”
When the work is complete, the Excel workbook must contain exactly five sheets arranged in this order: Client Memo, FantasticFilms_Dirty, Clean Data, Pivot Table, and Pivot Chart. The sheets must be named exactly as written so that they are easy to identify.