Using Python for Data Analysis

Reading Data From CSV Files

You can obtain your data in a variety of file formats. One of the most common is the comma-separated values (CSV) file. This is a text file that separates each piece of data with commas. The first row is usually a header row that defines the file’s content, with the subsequent rows containing the actual data. CSV files have been in use for several years and remain popular because several data storage programs use them.

Because james_bond_data.csv is a text file, you can open it in any text editor. The screenshot below shows it opened in Notepad:

As you can see, a CSV file isn’t a pleasant read. Fortunately, you rarely need to read them in their raw form.

When you need to analyze data, Python’s pandas library is a popular option. To install pandas in a Jupyter Notebook, add a new code cell and type !python -m pip install pandas. When you run the cell, you’ll install the library. If you’re working in the command line, then you use the same command, only without the exclamation point (!).

With pandas installed, you can now use it to read your data file into a pandas DataFrame. The code below will do this for you:

In [1]: import pandas as pd
   ...:
   ...: james_bond_data = pd.read_csv("james_bond_data.csv").convert_dtypes()

Firstly, you import the pandas library into your program. It’s standard practice to alias pandas as pd for code to use as a reference. Next, you use the read_csv() function to read your data file into a DataFrame named james_bond_data. This will not only read your file but also take care of sorting out the headings from the data and indexing each record.

While using pd.read_csv() alone will work, you can also use .convert_dtypes(). This good practice allows pandas to optimize the data types that it uses in the DataFrame.

Suppose your CSV file contained a column of integers with missing values. By default, these will be assigned the numpy.NaN floating-point constant. This forces pandas to assign the column a float64 data type. Any integers in the column are then cast as floats for consistency.

These floating-point values could cause other undesirable floats to appear in the results of subsequent calculations. Similarly, if the original numbers were, for example, ages, then having them cast into floats probably wouldn’t be what you want.

Your use of .convert_dtypes() means that columns will be assigned one of the extension data types. Any integer columns, which were of type int, will now become the new Int64 type. This occurs because pandas.NA represents the original missing values and can be read as an Int64. Similarly, text columns become string types, rather than the more generic object. Incidentally, floats become the new Float64 extension type, with a capital F.

After creating the DataFrame, you then decide to take a quick look at it to make sure the read has worked as you expected it to. A quick way to do this is to use .head(). This function will display the first five records for you by default, but you can customize .head() to display any number you like by passing an integer to it. Here, you decide to view the default five records:

In [2]: james_bond_data.head()
Out[2]:
           Release                  Movie          Bond  Bond_Car_MFG  \
0       June, 1962                 Dr. No  Sean Connery       Sunbeam
1     August, 1963  From Russia with Love  Sean Connery       Bentley
2        May, 1964             Goldfinger  Sean Connery  Aston Martin
3  September, 1965            Thunderball  Sean Connery  Aston Martin
4   November, 1967    You Only Live Twice  Sean Connery        Toyota

          US_Gross        World_Gross  Budget ($ 000s)  Film_Length  \
0   $16,067,035.00     $59,567,035.00        $1,000.00     110 mins
1   $24,800,000.00     $78,900,000.00        $2,000.00     115 mins
2   $51,100,000.00    $124,900,000.00        $3,000.00     110 mins
3   $63,600,000.00    $141,200,000.00        $9,000.00     130 mins
4   $43,100,000.00    $111,600,000.00        $9,500.00     117 mins

   Avg_User_IMDB  Avg_User_Rtn_Tom  Martinis  Kills_Bond
0            7.3               7.7         2           4
1            7.5               8.0         0          11
2            7.8               8.4         1           9
3            7.0               6.8         0          20
4            6.9               6.3         1          21

[5 rows x 12 columns]

You now have a pandas DataFrame containing the records along with their headings and a numerical index on the left-hand side. If you’re using a Jupyter Notebook, then the output will look like this:

As you can see, the Jupyter Notebook output is even more readable. However, both are much better than the CSV file that you started with.

Reading Data From Other Sources

Although CSV is a popular data file format, it isn’t particularly good. Lack of format standardization means that some CSV files contain multiple header and footer rows, while others contain neither. Also, the lack of a defined date format and the use of different separator and delimiter characters within and between data can cause issues when you read it.

Fortunately, pandas allows you to read many other formats, like JSON and Excel. It also provides web-scraping capabilities to allow you to read tables from websites. One particularly interesting and relatively new format is the column-oriented Apache Parquet file format used for handling bulk data. Parquet files are also cost-effective when working with cloud storage systems because of their compression ability.

Although having the ability to read basic CSV files is sufficient for this analysis, the downloads section provides some alternative file formats containing the same data as james_bond_data.csv. Each file is named james_bond_data, with a file-specific extension. Why not see if you can figure out how to read each of them into a DataFrame in the same way as you did with your CSV file?

If you want an additional challenge, then try scraping the Books, by publication sequence, table from Wikipedia. If you succeed, then you’ll have gained some valuable knowledge, and M will be very pleased with you.

For solutions to these challenges, expand the following collapsible sections:

In [1]: import pandas as pd
   ...:
   ...: james_bond_data = pd.read_json("james_bond_data.json").convert_dtypes()

In [1]: import pandas as pd
   ...:
   ...: james_bond_data = pd.read_excel("james_bond_data.xlsx").convert_dtypes()

In [1]: import pandas as pd
   ...:
   ...: james_bond_data = pd.read_parquet(
   ...:     "james_bond_data.parquet"
   ...: ).convert_dtypes()

In [1]: import pandas as pd
   ...:
   ...: james_bond_tables = pd.read_html(
   ...:     "https://en.wikipedia.org/wiki/List_of_James_Bond_novels_and_short_stories"
   ...: )
   ...: james_bond_data = james_bond_tables[1].convert_dtypes()

Now that you have your data, you might think it’s time to dive deep into it and start your analysis. While this is tempting, you can’t do it just yet. This is because your data might not yet be analyzable. In the next step, you’ll fix this.

Creating Meaningful Column Names

When you extract data from some systems, the column names may not be as meaningful as you’d like. It’s good practice to make sure the columns in your DataFrame are sensibly named. To keep them readable within code, you should adopt the Python variable-naming convention of using all lowercase characters, with multiple words being separated by underscores. This forces your analysis code to use these names and makes it more readable as a result.

To rename the columns in a DataFrame, you use .rename(). You pass it a Python dictionary whose keys are the original column names and whose values are the replacement names:

In [3]: new_column_names = {
   ...:     "Release": "release_date",
   ...:     "Movie": "movie_title",
   ...:     "Bond": "bond_actor",
   ...:     "Bond_Car_MFG": "car_manufacturer",
   ...:     "US_Gross": "income_usa",
   ...:     "World_Gross": "income_world",
   ...:     "Budget ($ 000s)": "movie_budget",
   ...:     "Film_Length": "film_length",
   ...:     "Avg_User_IMDB": "imdb",
   ...:     "Avg_User_Rtn_Tom": "rotten_tomatoes",
   ...:     "Martinis": "martinis_consumed",
   ...:     "Kills_Bond": "bond_kills",
   ...: }
   ...:
   ...: data = james_bond_data.rename(columns=new_column_names)

In the code above, you’ve replaced each of the column names with something more Pythonic. This returns a fresh DataFrame that’s referenced using the data variable, not the original DataFrame referenced by james_bond_data. The data DataFrame is the one that you’ll work with from this point forward.

As with all stages in data cleansing, it’s important to test that your code has worked as you expect:

In [4]: data.columns
Out[4]:
Index(['release_date', 'movie_title', 'bond_actor', 'car_manufacturer',
       'income_usa', 'income_world', 'movie_budget', 'film_length',
       'imdb', 'rotten_tomatoes', 'martinis_consumed', 'bond_kills'],
      dtype='object')

To quickly view the column labels in your DataFrame, you use the DataFrame’s .columns property. As you can see, you’ve successfully renamed the columns. You’re now ready to move on and cleanse the actual data itself.

Dealing With Missing Data

As a starting point, you can quickly check to see if anything is missing within your data. The DataFrame’s .info() method allows you to quickly do this:

In [5]: data.info()
Out[5]:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 27 entries, 0 to 26
Data columns (total 12 columns):
 #   Column                  Non-Null Count  Dtype
---  ------                  --------------  -----
 0   release_date            27 non-null     string
 1   movie_title             27 non-null     string
 2   bond_actor              27 non-null     string
 3   car_manufacturer        27 non-null     string
 4   income_usa              27 non-null     string
 5   income_world            27 non-null     string
 6   movie_budget            27 non-null     string
 7   film_length             27 non-null     string
 8   imdb                    26 non-null     Float64
 9   rotten_tomatoes         26 non-null     Float64
 10  martinis_consumed       27 non-null     Int64
 11  bond_kills              27 non-null     Int64
dtypes: Float64(2), Int64(2), string(8)
memory usage: 2.8 KB

When this method runs, you see a very concise summary of the DataFrame. The .info() method has revealed that there’s missing data. The RangeIndex line near the top of the output tells you that there have been twenty-seven rows of data read into the DataFrame. However, the imdb and rotten_tomatoes columns contain only twenty-six non-null values each. Each of these columns has one piece of missing data.

You may also have noticed that some data columns have incorrect data types. To begin with, you’ll concentrate on fixing missing data. You’ll deal with the data type issues afterward.

Before you can fix these columns, you need to see them. The code below will reveal them to you:

In [6]: data.loc[data.isna().any(axis="columns")]
Out[6]:
   release_date           movie_title   bond_actor car_manufacturer  \
10  April, 1977  The Spy Who Loved Me  Roger Moore            Lotus

         income_usa       income_world  movie_budget  film_length  \
10   $46,800,000.00    $185,400,000.00    $14,000.00     125 mins

    imdb  rotten_tomatoes  martinis_consumed  bond_kills
10  <NA>             <NA>                  1          31

[1 rows x 12 columns]

To find rows with missing data, you can make use of the DataFrame’s .isna() method. This will analyze the data DataFrame and return a second, identically sized Boolean DataFrame that contains either True or False values, depending on whether or not the corresponding values in the data DataFrame contain <NA> or not.

Once you have this second Boolean DataFrame, you then use its .any(axis="columns") method to return a pandas Series that will contain True where rows in the second DataFrame have a True value, and False if they don’t. The True values in this Series indicate rows containing missing data, while the False values indicate where there’s no missing data.

At this point, you have a Boolean Series of values. To see the rows themselves, you can make use of the DataFrame’s .loc property. Although you usually use .loc to access subsets of rows and columns by their labels, you can also pass it your Boolean Series and get back a DataFrame containing only those rows corresponding to the True entries in the Series. These are the rows with missing data.

If you put all of this together, then you get data.loc[data.isna().any(axis="columns")]. As you can see, the output displays only one row that contains both <NA> values.

When you first saw only one row appear, you might have felt a bit shaken, but now you’re not stirred because you understand why.

To fix these errors, you need to update the data DataFrame. As you learned earlier, you’ll build up all changes temporarily in a DataFrame referenced by the data variable then write them to disk when they are all complete. You’ll now add some code to fix those <NA> values that you’ve discovered.

After doing some research, you find out the missing values are 7.1 and 6.8, respectively. The code below will update each missing value correctly:

 1In [7]: data = james_bond_data.rename(columns=new_column_names).combine_first(
 2   ...:     pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
 3   ...:)

Here, you’ve chosen to define a DataFrame using a Python dictionary. The keys of the dictionary define its column headings, while its values define the data. Each value consists of a nested dictionary. The keys of this nested dictionary provide the row index, while the values provide the updates. The DataFrame looks like this:

In [8]: pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
Out[8]:
    imdb  rotten_tomatoes
10   7.1              6.8

Then when you call .combine_first() and pass it this DataFrame, the two missing values in the imdb and rotten_tomatoes columns in row 10 are replaced by 7.1 and 6.8, respectively. Remember, you haven’t updated the original james_bond_data DataFrame. You’ve only changed the DataFrame referenced by the data variable.

You must now test your efforts. Go ahead and run data[data.isna().any(axis="columns")] to make sure no rows are returned. You should see an empty DataFrame.

Now you’ll fix the invalid data types. Without this, numerical analysis of your data is meaningless, if not impossible. To begin with, you’ll fix the currency columns.

Handling Financial Columns

The data.info() code that you ran earlier also revealed to you a subtler issue. The income_usa, income_world, movie_budget, and film_length columns all have data types of string. However, these should all be numeric types because strings are of little use for calculations. Similarly, the release column, which contains the release date, is also a string. This should be a date type.

First of all, you need to take a look at some of the data in each of the columns to learn what the problem is:

In [9]: data[
   ...:     ["income_usa", "income_world", "movie_budget", "film_length"]
   ...: ].head()
Out[9]:
        income_usa     income_world  movie_budget  film_length
0   $16,067,035.00   $59,567,035.00     $1,000.00     110 mins
1   $24,800,000.00   $78,900,000.00     $2,000.00     115 mins
2   $51,100,000.00  $124,900,000.00     $3,000.00     110 mins
3   $63,600,000.00  $141,200,000.00     $9,000.00     130 mins
4   $43,100,000.00  $111,600,000.00     $9,500.00     117 mins

To access multiple columns, you pass a list of column names into the DataFrame’s [] operator. Although you could also use data.loc[], using data[] alone is cleaner. Either option will return a DataFrame containing all the data from those columns. To keep things manageable, you use the .head() method to restrict the output to the first five records.

As you can see, the three financial columns each have dollar signs and comma separators, while the film_length column contains "mins". You’ll need to remove all of this to use the remaining numbers in the analysis. These additional characters are why the data types are being misinterpreted as strings.

Although you could replace the $ sign in the entire DataFrame, this may remove it in places where you don’t want to. It’s safer if you remove it one column at a time. To do this, you can make excellent use of the .assign() method of a DataFrame. This can either add a new column to a DataFrame, or replace existing columns with updated values.

As a starting point, suppose you wanted to replace the $ symbols in the income_usa column of the data DataFrame that you’re creating. The additional code in lines 6 through 12 achieves this:

 1In [10]: data = (
 2   ...:     james_bond_data.rename(columns=new_column_names)
 3   ...:     .combine_first(
 4   ...:         pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
 5   ...:     )
 6   ...:     .assign(
 7   ...:         income_usa=lambda data: (
 8   ...:             data["income_usa"]
 9   ...:             .replace("[$,]", "", regex=True)
10   ...:             .astype("Float64")
11   ...:         ),
12   ...:     )
13   ...: )

To correct the income_usa column, you define its new data as a pandas Series and pass it into the data DataFrame’s .assign() method. This method will then either overwrite an existing column with a new Series or create a new column containing it. You define the name of the column to be updated or created as a named parameter that references the new data Series. In this case, you’ll pass a parameter named income_usa.

It’s best to create the new Series using a lambda function. The lambda function used in this example accepts the data DataFrame as its argument and then uses the .replace() method to remove the $ and comma separators from each value in the income_usa column. Finally, it converts the remaining digits, which are currently of type string, to Float64.

To actually remove the $ symbol and commas, you pass the regular expression [$,] into .replace(). By enclosing both characters in [], you’re specifying that you want to remove all instances of both. Then you define their replacements as "". You also set the regex parameter to True to allow [$,] to be interpreted as a regular expression.

The result of the lambda function is a Series with no $ or comma separators. You then assign this Series to the variable income_usa. This causes the .assign() method to overwrite the existing income_usa column’s data with the cleansed updates.

Take another look at the above code, and you’ll see how this all fits together. You pass .assign() a parameter named income_usa, which references a lambda function that calculates a Series containing the updated content. You assign the Series that the lambda produces to a parameter named income_usa, which tells .assign() to update the existing income_usa column with the new values.

Now go ahead and run this code to remove the offending characters from the income_usa column. Don’t forget to test your work and verify that you’ve made the replacements. Also, remember to verify that the data type of income_usa is indeed Float64.

Of course, it isn’t only the income_usa column that you need to work on. You also need to do the same with the income_world and movie_budget columns. You can also achieve this using the same .assign() method. You can use it to create and assign as many columns as you like. You simply pass them in as separate named arguments.

Why not go ahead and see if you can write the code that removes the same two characters from the income_world and movie_budget columns? As before, don’t forget to verify that your code has worked as you expect, but remember to check the correct columns!

Once you’ve tried your hand at resolving the remaining issues with these columns, you can reveal the solution below:

 1In [11]: data = (
 2   ...:     james_bond_data.rename(columns=new_column_names)
 3   ...:     .combine_first(
 4   ...:         pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
 5   ...:     )
 6   ...:     .assign(
 7   ...:         income_usa=lambda data: (
 8   ...:             data["income_usa"]
 9   ...:             .replace("[$,]", "", regex=True)
10   ...:             .astype("Float64")
11   ...:         ),
12   ...:         income_world=lambda data: (
13   ...:             data["income_world"]
14   ...:             .replace("[$,]", "", regex=True)
15   ...:             .astype("Float64")
16   ...:         ),
17   ...:         movie_budget=lambda data: (
18   ...:             data["movie_budget"]
19   ...:             .replace("[$,]", "", regex=True)
20   ...:             .astype("Float64")
21   ...:         ),
22   ...:     )
23   ...: )

With the currency colum data type now corrected, you can fix the remaining invalid types.

Correcting Invalid Data Types

Next, you must remove the "mins" string from the film length values, then convert the column to the integer type. This will allow you to analyze the values. To remove the offending "mins" text, you decide to use pandas’ .str.removesuffix() Series method. This allows you to remove the string passed to it from the right-hand side of the film_length column. You can then use .astype("Int64") to take care of the data type.

Using the above information, go ahead and see if you can update the film_length column using a lambda, and add it in as another parameter to the .assign() method.

You can reveal the solution below:

 1In [12]: data = (
 2   ...:     james_bond_data.rename(columns=new_column_names)
 3   ...:     .combine_first(
 4   ...:         pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
 5   ...:     )
 6   ...:     .assign(
 7   ...:         income_usa=lambda data: (
 8   ...:             data["income_usa"]
 9   ...:             .replace("[$,]", "", regex=True)
10   ...:             .astype("Float64")
11   ...:         ),
12   ...:         income_world=lambda data: (
13   ...:             data["income_world"]
14   ...:             .replace("[$,]", "", regex=True)
15   ...:             .astype("Float64")
16   ...:         ),
17   ...:         movie_budget=lambda data: (
18   ...:             data["movie_budget"]
19   ...:             .replace("[$,]", "", regex=True)
20   ...:             .astype("Float64")
21   ...:         ),
22   ...:         film_length=lambda data: (
23   ...:             data["film_length"]
24   ...:             .str.removesuffix("mins")
25   ...:             .astype("Int64")
26   ...:         ),
27   ...:     )
28   ...: )

In addition to the problems with the financial data, you also noticed that the release_date column was being treated as a string. To convert its data into datetime format, you can use pd.to_datetime().

To use to_datetime(), you pass the Series data["release_date"] into it, not forgetting to specify a format string to allow the date values to be interpreted correctly. Each date here is of the form June, 1962, so in your code, you use %B followed by a comma and space to denote the position of the month names, then %Y to denote the four-digit years.

You also take the opportunity to create a new column in your DataFrame named release_year for storing the year portion of your updated data["release_date"] column data. The code to access this value is data["release_date"].dt.year. You figure that having each year separate may be useful for future analysis and even perhaps a future DataFrame index.

Using the above information, go ahead and see if you can update the release_date column to the correct type, and also create a new release_year column containing the year that each movie came out. As before, you can achieve both with .assign() and lambdas, and again as before, remember to test your efforts.

You can reveal the solution below:

 1In [13]: data = (
 2   ...:     james_bond_data.rename(columns=new_column_names)
 3   ...:     .combine_first(
 4   ...:         pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
 5   ...:     )
 6   ...:     .assign(
 7   ...:         income_usa=lambda data: (
 8   ...:             data["income_usa"]
 9   ...:             .replace("[$,]", "", regex=True)
10   ...:             .astype("Float64")
11   ...:         ),
12   ...:         income_world=lambda data: (
13   ...:             data["income_world"]
14   ...:             .replace("[$,]", "", regex=True)
15   ...:             .astype("Float64")
16   ...:         ),
17   ...:         movie_budget=lambda data: (
18   ...:             data["movie_budget"]
19   ...:             .replace("[$,]", "", regex=True)
20   ...:             .astype("Float64")
21   ...:         ),
22   ...:         film_length=lambda data: (
23   ...:             data["film_length"]
24   ...:             .str.removesuffix("mins")
25   ...:             .astype("Int64")
26   ...:         ),
27   ...:         release_date=lambda data: pd.to_datetime(
28   ...:             data["release_date"], format="%B, %Y"
29   ...:         ),
30   ...:         release_year=lambda data: (
31   ...:             data["release_date"]
32   ...:             .dt.year
33   ...:             .astype("Int64")
34   ...:         ),
35   ...:     )
36   ...: )

Now that you’ve resolved these initial issues, you rerun data.info() to verify that you’ve fixed all of your initial concerns:

 1In [14]: data.info()
 2Out[14]:
 3<class 'pandas.core.frame.DataFrame'>
 4Index: 27 entries, 0 to 26
 5Data columns (total 13 columns):
 6 #   Column                  Non-Null Count  Dtype
 7---  ------                  --------------  -----
 8 0   bond_actor              27 non-null     string
 9 1   bond_kills              27 non-null     Int64
10 2   car_manufacturer        27 non-null     string
11 3   film_length             27 non-null     Int64
12 4   income_usa              27 non-null     Float64
13 5   income_world            27 non-null     Float64
14 6   imdb                    27 non-null     Float64
15 7   martinis_consumed       27 non-null     Int64
16 8   movie_budget            27 non-null     Float64
17 9   movie_title             27 non-null     string
18 10  release_date            27 non-null     datetime64[ns]
19 11  rotten_tomatoes         27 non-null     Float64
20 12  release_year            27 non-null     Int64
21dtypes: Float64(5), Int64(3), datetime64[ns](1), string(3)
22memory usage: 2.8 KB

As you can see, the original twenty-seven entries now all have data in them. The release_date column has a datetime64 format, and the three earnings and film_length columns all have numeric types. There’s even a new release_year column in your DataFrame as well. Of course, this check wasn’t really necessary because, like all good secret agents, you already checked your code as you wrote it.

You may also have noticed that the column order has changed. This has happened as a result of your earlier use of combine_first(). In this analysis, the column order doesn’t matter because you never need to display the DataFrame. If necessary, you can specify a column order by using square brackets, as in data[["column_1", ...]].

At this point, you’ve made sure nothing is missing from your data and that it’s all of the correct type. Next you turn your attention to the actual data itself.

Fixing Inconsistencies in Data

While updating the movie_budget column label earlier, you may have noticed that its numbers appear small compared to the other financial columns. The reason is that its data is in thousands, whereas the other columns are actual figures. You decide to do something about this because it could cause problems if you compared this data with the other financial columns that you worked on.

You might be tempted to write another lambda and pass it into .assign() using the movie_budget parameter. Unfortunately, this won’t work because you can’t use the same parameter twice in the same function. You could revisit the movie_budget parameter and add functionality to multiply its result by 1000, or you could create yet another column based on the movie_budget column values. Alternatively, you could create a separate .assign() call.

Each of these options would work, but multiplying the existing values is probably the simplest. Go ahead and see if you can multiply the results of your earlier movie_budget lambda by 1000.

You can reveal the solution below:

 1In [15]: data = (
 2   ...:     james_bond_data.rename(columns=new_column_names)
 3   ...:     .combine_first(
 4   ...:         pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
 5   ...:     )
 6   ...:     .assign(
 7   ...:         income_usa=lambda data: (
 8   ...:             data["income_usa"]
 9   ...:             .replace("[$,]", "", regex=True)
10   ...:             .astype("Float64")
11   ...:         ),
12   ...:         income_world=lambda data: (
13   ...:             data["income_world"]
14   ...:             .replace("[$,]", "", regex=True)
15   ...:             .astype("Float64")
16   ...:         ),
17   ...:         movie_budget=lambda data: (
18   ...:             data["movie_budget"]
19   ...:             .replace("[$,]", "", regex=True)
20   ...:             .astype("Float64")
21   ...:             * 1000
22   ...:         ),
23   ...:         film_length=lambda data: (
24   ...:             data["film_length"]
25   ...:             .str.removesuffix("mins")
26   ...:             .astype("Int64")
27   ...:         ),
28   ...:         release_date=lambda data: pd.to_datetime(
29   ...:             data["release_date"], format="%B, %Y"
30   ...:         ),
31   ...:         release_year=lambda data: (
32   ...:             data["release_date"]
33   ...:             .dt.year
34   ...:             .astype("Int64")
35   ...:         ),
36   ...:     )
37   ...: )

Now that you’ve sorted out some formatting issues, it’s time for you to move on and do some other checks.

Correcting Spelling Errors

One of the most difficult data cleansing tasks is checking for typos because they can appear anywhere. As a consequence, you’ll often not encounter them until late in your analysis and, indeed, may never notice them at all.

In this exercise, you’ll look for typos in the names of the actors who played Bond and in the car manufacturers’ names. This is relatively straightforward to do because both of these columns contain data items from a finite set of allowable values:

 1In [16]: data["bond_actor"].value_counts()
 2Out[16]:
 3bond_actor
 4Roger Moore       7
 5Sean Connery      5
 6Daniel Craig      5
 7Pierce Brosnan    4
 8Timothy Dalton    3
 9George Lazenby    1
10Shawn Connery     1
11Roger MOORE       1
12Name: count, dtype: Int64

The .value_counts() method allows you to quickly obtain a count of each element within a pandas Series. Here you use it to help you find possible typos in the bond_actor column. As you can see, one instance of Sean Connery and one of Roger Moore contain typos.

To fix these with string replacement, you use the .str.replace() method of a pandas data Series. In its simplest form, you only need to pass it the original string and the string that you want to replace it with. In this case, you can replace both typos at the same time by chaining two calls to .str.replace().

Using the above information, go ahead and see if you can correct the typos in the bond_actor column. As before, you can achieve this with a lambda.

You can reveal the solution below:

 1In [17]: data = (
 2   ...:     james_bond_data.rename(columns=new_column_names)
 3   ...:     .combine_first(
 4   ...:         pd.DataFrame({"imdb": {10: 7.1}, "rotten_tomatoes": {10: 6.8}})
 5   ...:     )
 6   ...:     .assign(
 7   ...:         income_usa=lambda data: (
 8   ...:             data["income_usa"]
 9   ...:             .replace("[$,]", "", regex=True)
10   ...:             .astype("Float64")
11   ...:         ),
12   ...:         income_world=lambda data: (
13   ...:             data["income_world"]
14   ...:             .replace("[$,]", "", regex=True)
15   ...:             .astype("Float64")
16   ...:         ),
17   ...:         movie_budget=lambda data: (
18   ...:             data["movie_budget"]
19   ...:             .replace("[$,]", "", regex=True)
20   ...:             .astype("Float64")
21   ...:             * 1000
22   ...:         ),
23   ...:         film_length=lambda data: (
24   ...:             data["film_length"]
25   ...:             .str.removesuffix("mins")
26   ...:             .astype("Int64")
27   ...:         ),
28   ...: