Pandas data processing example display: global listed company data collection

I currently have a piece of data on Forbes’ 2016 Global 2000 List of Listed Companies, but the original data is not standardized and needs to be processed before further use.

This article introduces the use of pandas for data organization through practical examples.

As usual, let me first talk about my operating environment, as follows:

• windows 7, 64-bit

• python 3.5

• pandas 0.19.2 version

After getting the original data, let’s first take a look at the data and think about what we need data results.

Below is the raw data:

In this article, we need the following preliminary results for future continued use.

You can see that in the original data, the data related to the enterprise ("Sales", "Profits", "Assets", "Market_value") are currently Not a numeric type that can be used for calculations.

The original content contains currency symbols "$", "-", strings composed of pure letters, and other information that we consider abnormal. What's more, the units for these data are not consistent. They are represented by "B" (Billion, one billion) and "M" (Million, one million) respectively. Unit unification is required before subsequent calculations.

1 Processing method Method-1

The first processing idea that comes to mind is to split the data information into billions ('B') and millions ('M'), respectively. processed and finally merged together. The process is as follows.

• Load the data and add the column name

import pandas as pd

df_2016 = pd.read_csv('data_2016.csv', encoding='gbk',header=None)# 更新列名df_2016.columns = ['Year', 'Rank', 'Company_cn','Company_en',                   'Country_en', 'Sales', 'Profits', 'Assets', 'Market_value']

print('the shape of DataFrame: ', df_2016.shape)
print(df_2016.dtypes)
df_2016.head(3)

• Get the unit in billions ('B') Data

# 数据单位为 B的数据（Billion，十亿）df_2016_b = df_2016[df_2016['Sales'].str.endswith('B')]
print(df_2016_b.shape)
df_2016_b

• Get data in millions ('M')

# 数据单位为 M的数据（Million，百万）df_2016_m = df_2016[df_2016['Sales'].str.endswith('M')]
print(df_2016_m.shape)
df_2016_m

This method is easy to understand It is relatively simple, but the operation will be more cumbersome, especially if there are many columns of data to be processed, it will take a lot of time.

I won’t describe further processing here. Of course, you can try this method.

The following is a slightly simpler method.

2 Processing method Method-2

2.1 Loading data

The first step is to load data, which is the same as Method-1.

Let’s process the 'Sales' column

2.2 Replace relevant abnormal characters

First, replace the relevant abnormal characters, including the dollar currency symbol '$', the alphabetical string 'undefined', and 'B'. Here, we want to uniformly organize the data units into billions, so 'B' can be directly replaced. And 'M' requires more processing steps.

2.3 Processing data related to 'M'

Processing data containing millions of "M" units, that is, data ending with "M", the idea is as follows:

(1) Set the search condition mask;

(2) Replace the string "M" with an empty value

（3）用pd.to_numeric()转换为数字

（4）除以1000，转换为十亿美元，与其他行的数据一致

上面两个步骤相关的代码如下：

# 替换美元符号df_2016['Sales'] = df_2016['Sales'].str.replace('$','')# # 查看异常值，均为字母（“undefined”）# df_2016[df_2016['Sales'].str.isalpha()]# 替换异常值“undefined”为空白# df_2016['Sales'] = df_2016['Sales'].str.replace('undefined','')df_2016['Sales'] = df_2016['Sales'].str.replace('^[A-Za-z]+$','')# 替换符号十亿美元“B”为空白，数字本身代表的就是十亿美元为单位df_2016['Sales'] = df_2016['Sales'].str.replace('B','')# 处理含有百万“M”为单位的数据，即以“M”结尾的数据# 思路：# （1）设定查找条件mask；# （2）替换字符串“M”为空值# （3）用pd.to_numeric()转换为数字# （4）除以1000，转换为十亿美元，与其他行的数据一致mask = df_2016['Sales'].str.endswith('M')
df_2016.loc[mask, 'Sales'] = pd.to_numeric(df_2016.loc[mask, 'Sales'].str.replace('M', ''))/1000df_2016['Sales'] = pd.to_numeric(df_2016['Sales'])
print('the shape of DataFrame: ', df_2016.shape)
print(df_2016.dtypes)
df_2016.head(3)

用同样类似的方法处理其他列

可以看到，这个方法比第一种方法还是要方便很多。当然，这个方法针对DataFrame的每列数据都要进行相关的操作，如果列数多了，也还是比较繁琐的。

有没有更方便一点的方法呢。 答案是有的。

插播一条硬广：技术文章转发太多。文章来自微信公众号“Python数据之道”（ID：PyDataRoad）。

 

3 处理方法 Method-3

在Method-2的基础上，将处理方法写成更通用的数据处理函数，根据数据的结构，拓展更多的适用性，则可以比较方便的处理相关数据。

3.1 加载数据

第一步还是加载数据，跟Method-1是一样的。

3.2 编写数据处理的自定义函数

参考Method-2的处理过程，编写数据处理的自定义函数’pro_col’，并在Method-2的基础上拓展其他替换功能，使之适用于这四列数据（“Sales”，“Profits”，“Assets”，“Market_value”）。

函数编写的代码如下：

def pro_col(df, col):   # 替换相关字符串，如有更多的替换情形，可以自行添加df[col] = df[col].str.replace('$','')
    df[col] = df[col].str.replace('^[A-Za-z]+$','')
    df[col] = df[col].str.replace('B','')# 注意这里是'-$'，即以'-'结尾，而不是'-'，因为有负数df[col] = df[col].str.replace('-$','')
    df[col] = df[col].str.replace(',','')# 处理含有百万“M”为单位的数据，即以“M”结尾的数据# 思路：# （1）设定查找条件mask；# （2）替换字符串“M”为空值# （3）用pd.to_numeric()转换为数字# （4）除以1000，转换为十亿美元，与其他行的数据一致mask = df[col].str.endswith('M')
    df.loc[mask, col] = pd.to_numeric(df.loc[mask, col].str.replace('M',''))/1000# 将字符型的数字转换为数字类型df[col] = pd.to_numeric(df[col])return df

3.3 将自定义函数进行应用

针对DataFrame的每列，应用该自定义函数，进行数据处理，得到需要的结果。

pro_col(df_2016, 'Sales')
pro_col(df_2016, 'Profits')
pro_col(df_2016, 'Assets')
pro_col(df_2016, 'Market_value')

print('the shape of DataFrame: ', df_2016.shape)
print(df_2016.dtypes)
df_2016.head()

当然，如果DataFrame的列数特别多，可以用for循环，这样代码更简洁。代码如下：

cols = ['Sales', 'Profits', 'Assets', 'Market_value']for col in cols:
    pro_col(df_2016, col)

print('the shape of DataFrame: ', df_2016.shape)
print(df_2016.dtypes)
df_2016.head()

最终处理后，获得的数据结果如下：

The above is the detailed content of Pandas data processing example display: global listed company data collection. For more information, please follow other related articles on the PHP Chinese website!