Pandas: Data Preparation with Vectorized Strings vs Lambda Functions

What will we cover in this tutorial?

Understand the challenge

Most of the time when you read data into a pandas DataFrame it need to be prepared.

To be more concrete, let’s look at an example. Let’s consider we want to look at the List of largest companies by revenue on Wikipedia.

From wikipedia.org

You can read an inspect the data by the following code by using a DataFrame from the pandas library.

import pandas as pd

pd.set_option('display.max_rows', 300)
pd.set_option('display.max_columns', 10)
pd.set_option('display.width', 1000)


url = 'https://en.wikipedia.org/wiki/List_of_largest_companies_by_revenue'
tables = pd.read_html(url)

table = tables[0]

print(table)
print(table.dtypes)

Notice that we use pd.set_option calls to get the full view. If you are new to read_html from the pandas library, we recommend you read this tutorial.

The top of the output will be as follows.

    Rank                        Name                     Industry Revenue(USD millions) Profit(USD millions)  Employees                       Country   Ref
0      1                     Walmart                       Retail              $514,405               $6,670    2200000                 United States   [5]
1      2               Sinopec Group                  Oil and gas              $414,650               $5,845     619151                         China   [6]
2      3           Royal Dutch Shell                  Oil and gas              $396,556              $23,352      81000  Netherlands / United Kingdom   [7]
3      4    China National Petroleum                  Oil and gas              $392,976               $2,270    1382401                         China   [8]
4      5                  State Grid                  Electricity              $387,056               $8,174     917717                         China   [9]
5      6                Saudi Aramco                  Oil and gas              $355,905             $110,974      76418                  Saudi Arabia  [10]
6      7                          BP                  Oil and gas              $303,738               $9,383      73000                United Kingdom  [11]
7      8                  ExxonMobil                  Oil and gas              $290,212              $20,840      71000                 United States  [12]
8      9                  Volkswagen                   Automotive              $278,341              $14,332     664496                       Germany  [13]

And the last lines.

Rank                      int64
Name                     object
Industry                 object
Revenue(USD millions)    object
Profit(USD millions)     object
Employees                 int64
Country                  object
Ref                      object
dtype: object

Where we see interesting information about what data types each column has. Not surprisingly, the Revenue and Profit columns are of type object (which are strings in this case).

Hence, if we want to sum up values, we need to transform them to floats. This is a bit tricky, as the output shows above. An example is $6,670, where there are two issues to transform them to floats. First, there is a dollars ($) sign in the beginning. Second, there is comma (,) in the number, which a simple cast to float does not handle.

Now let’s deal with them in each their method.

Method 1: Using pandas DataFrame/Series vectorized string functions

Vectorization with pandas data structures is the process of executing operations on entire data structure. This is handy, as the alternative would be to make a loop-function.

Also, the pandas has many string functions available for vectorization as you can see in the documentation.

First of, we can access the string object by using the .str, then we can apply the string function. In our case, we will use the substring with square brackets to remove the dollar sign.

index_r = 'Revenue(USD millions)'
table[index_r] = table[index_r].str[1:]

Which will give the following output.

    Rank                        Name                     Industry Revenue(USD millions) Profit(USD millions)  Employees                       Country   Ref
0      1                     Walmart                       Retail               514,405               $6,670    2200000                 United States   [5]
1      2               Sinopec Group                  Oil and gas               414,650               $5,845     619151                         China   [6]
2      3           Royal Dutch Shell                  Oil and gas               396,556              $23,352      81000  Netherlands / United Kingdom   [7]
3      4    China National Petroleum                  Oil and gas               392,976               $2,270    1382401                         China   [8]
4      5                  State Grid                  Electricity               387,056               $8,174     917717                         China   [9]
5      6                Saudi Aramco                  Oil and gas               355,905             $110,974      76418                  Saudi Arabia  [10]
6      7                          BP                  Oil and gas               303,738               $9,383      73000                United Kingdom  [11]
7      8                  ExxonMobil                  Oil and gas               290,212              $20,840      71000                 United States  [12]
8      9                  Volkswagen                   Automotive               278,341              $14,332     664496                       Germany  [13]

Then we need to remove the comma (,). This can be done by using replace.

index_r = 'Revenue(USD millions)'
table[index_r] = table[index_r].str[1:] .str.replace(',', '')

Which will result in the following output.

    Rank                        Name                     Industry Revenue(USD millions) Profit(USD millions)  Employees                       Country   Ref
0      1                     Walmart                       Retail                514405               $6,670    2200000                 United States   [5]
1      2               Sinopec Group                  Oil and gas                414650               $5,845     619151                         China   [6]
2      3           Royal Dutch Shell                  Oil and gas                396556              $23,352      81000  Netherlands / United Kingdom   [7]
3      4    China National Petroleum                  Oil and gas                392976               $2,270    1382401                         China   [8]
4      5                  State Grid                  Electricity                387056               $8,174     917717                         China   [9]
5      6                Saudi Aramco                  Oil and gas                355905             $110,974      76418                  Saudi Arabia  [10]
6      7                          BP                  Oil and gas                303738               $9,383      73000                United Kingdom  [11]
7      8                  ExxonMobil                  Oil and gas                290212              $20,840      71000                 United States  [12]
8      9                  Volkswagen                   Automotive                278341              $14,332     664496                       Germany  [13]

Finally, we need to convert the string to a float.

index_r = 'Revenue(USD millions)'
table[index_r] = table[index_r].str[1:] .str.replace(',', '').astype(float)

Which does not change the printed output, but the type of the column.

Nice and easy, to prepare the data in one line. Notice, that you could chose to make it in multiple lines. It is a matter of taste.

Method 2: Using pandas DataFrame lambda function

Another way to prepare data is by using a lambda function. If you are new to lambda functions, we recommend you read this tutorial.

Here you can do it row by row and apply your defined lambda function.

The next column has the same challenge as the first one. So let’s apply it on that.

In this case, we cannot use the substring with square brackets like in the case above, as some figures are negative and contain that minus sign before the dollar sign. But using the replace call will do fine.

index_p = 'Profit(USD millions)'
table[index_p] = table.apply(lambda row: row[index_p].replace('$', ''), axis=1)

Which would result in the following output.

    Rank                        Name                     Industry  Revenue(USD millions) Profit(USD millions)  Employees                       Country   Ref
0      1                     Walmart                       Retail               514405.0                6,670    2200000                 United States   [5]
1      2               Sinopec Group                  Oil and gas               414650.0                5,845     619151                         China   [6]
2      3           Royal Dutch Shell                  Oil and gas               396556.0               23,352      81000  Netherlands / United Kingdom   [7]
3      4    China National Petroleum                  Oil and gas               392976.0                2,270    1382401                         China   [8]
4      5                  State Grid                  Electricity               387056.0                8,174     917717                         China   [9]
5      6                Saudi Aramco                  Oil and gas               355905.0              110,974      76418                  Saudi Arabia  [10]
6      7                          BP                  Oil and gas               303738.0                9,383      73000                United Kingdom  [11]
7      8                  ExxonMobil                  Oil and gas               290212.0               20,840      71000                 United States  [12]
8      9                  Volkswagen                   Automotive               278341.0               14,332     664496                       Germany  [13]

Then we do the same to remove the comma (,).

index_p = 'Profit(USD millions)'
table[index_p] = table.apply(lambda row: row[index_p].replace('$', '').replace(',', ''), axis=1)

Which result in the following output.

    Rank                        Name                     Industry  Revenue(USD millions) Profit(USD millions)  Employees                       Country   Ref
0      1                     Walmart                       Retail               514405.0                 6670    2200000                 United States   [5]
1      2               Sinopec Group                  Oil and gas               414650.0                 5845     619151                         China   [6]
2      3           Royal Dutch Shell                  Oil and gas               396556.0                23352      81000  Netherlands / United Kingdom   [7]
3      4    China National Petroleum                  Oil and gas               392976.0                 2270    1382401                         China   [8]
4      5                  State Grid                  Electricity               387056.0                 8174     917717                         China   [9]
5      6                Saudi Aramco                  Oil and gas               355905.0               110974      76418                  Saudi Arabia  [10]
6      7                          BP                  Oil and gas               303738.0                 9383      73000                United Kingdom  [11]
7      8                  ExxonMobil                  Oil and gas               290212.0                20840      71000                 United States  [12]
8      9                  Volkswagen                   Automotive               278341.0                14332     664496                       Germany  [13]

Finally, we will do the same for casting it to a float.

index_p = 'Profit(USD millions)'
table[index_p] = table.apply(lambda row: float(row[index_p].replace('$', '').replace(',', '')), axis=1)

Which will produce the same output.

Comparing the two methods

To be honest, it is a matter of taste in this case. When things can be achieved by simple string manipulation calls that are available through the vectorized calls, there is nothing to gain by lambda functions.

The strength of lambda functions is the flexibility. You can actually do anything function in there, which is a big strength. The vectorized functions are limited to simple operations, which covers a lot of use cases.

Putting it all together

Well, now we came so far, let’s put it all together and get some nice data. Sum it up and print it sorted out and make a horizontal bar plot.

import pandas as pd
import matplotlib.pyplot as plt

pd.set_option('display.max_rows', 300)
pd.set_option('display.max_columns', 10)
pd.set_option('display.width', 1000)


url = 'https://en.wikipedia.org/wiki/List_of_largest_companies_by_revenue'
tables = pd.read_html(url)

table = tables[0]

index_r = 'Revenue(USD millions)'
table[index_r] = table[index_r].str[1:] .str.replace(',', '').astype(float)

index_p = 'Profit(USD millions)'
table[index_p] = table.apply(lambda row: float(row[index_p].replace('$', '').replace(',', '')), axis=1)

table = table.drop(['Rank'], axis=1)


print(table.groupby('Country').sum().sort_values([index_r, index_p], ascending=False))

table.groupby('Industry').sum().sort_values([index_r, index_p], ascending=False).plot.barh()
plt.show()

The output graph.

Resulting output graph.

And the output from the program.

                              Revenue(USD millions)  Profit(USD millions)  Employees
Country                                                                             
United States                             4169049.0              243970.0    6585076
China                                     2263521.0              182539.0    5316321
Germany                                    602635.0               31693.0    1105639
Japan                                      561157.0               27814.0     670636
Netherlands / United Kingdom               396556.0               23352.0      81000
Saudi Arabia                               355905.0              110974.0      76418
France                                     309684.0               13971.0     208525
United Kingdom                             303738.0                9383.0      73000
Russia                                     250447.0               33062.0     568600
South Korea                                221579.0               39895.0     221579
Switzerland                                219754.0                3408.0      85504
Singapore                                  180744.0                 849.0       4316
Taiwan                                     175617.0                4281.0     667680
Netherlands                                175009.0                1589.0     314790