找出给定数据集中每列中缺少值的百分比

import pandas as pd
df = pd.read_csv('https://query.data.world/s/Hfu_PsEuD1Z_yJHmGaxWTxvkz7W_b0')
percent= 100*(len(df.loc[:,df.isnull().sum(axis=0)>=1 ].index) / len(df.index))
print(round(percent,2))

输入为 https://query.data.world/s/Hfu_PsEuD1Z_yJHmGaxWTxvkz7W_b0

输出应该是

Ord_id                 0.00
Prod_id                0.00
Ship_id                0.00
Cust_id                0.00
Sales                  0.24
Discount               0.65
Order_Quantity         0.65
Profit                 0.65
Shipping_Cost          0.65
Product_Base_Margin    1.30
dtype: float64
138116 次浏览
小开
最佳答案

这个怎么样? 我觉得我以前在这里发现过类似的东西，但我现在没看到..。

percent_missing = df.isnull().sum() * 100 / len(df)
missing_value_df = pd.DataFrame({'column_name': df.columns,
'percent_missing': percent_missing})

如果您希望对丢失的百分比进行排序，请按照以下步骤执行:

missing_value_df.sort_values('percent_missing', inplace=True)

正如注释中提到的，你也可以仅仅通过我上面代码中的第一行来完成，例如:

percent_missing = df.isnull().sum() * 100 / len(df)
小开

更新让我们使用 mean和 isnull:

df.isnull().mean() * 100

产出:

Ord_id                 0.000000
Prod_id                0.000000
Ship_id                0.000000
Cust_id                0.000000
Sales                  0.238124
Discount               0.654840
Order_Quantity         0.654840
Profit                 0.654840
Shipping_Cost          0.654840
Product_Base_Margin    1.297774
dtype: float64

国际职业联合会:

df.isnull().sum() / df.shape[0] * 100.00

产出:

Ord_id                 0.000000
Prod_id                0.000000
Ship_id                0.000000
Cust_id                0.000000
Sales                  0.238124
Discount               0.654840
Order_Quantity         0.654840
Profit                 0.654840
Shipping_Cost          0.654840
Product_Base_Margin    1.297774
dtype: float64
小开

涵盖所有 失踪了值并围绕结果:

((df.isnull() | df.isna()).sum() * 100 / df.index.size).round(2)

输出:

Out[556]:
Ord_id                 0.00
Prod_id                0.00
Ship_id                0.00
Cust_id                0.00
Sales                  0.24
Discount               0.65
Order_Quantity         0.65
Profit                 0.65
Shipping_Cost          0.65
Product_Base_Margin    1.30
dtype: float64
小开 
import numpy as np
import pandas as pd


raw_data = {'first_name': ['Jason', np.nan, 'Tina', 'Jake', 'Amy'],
'last_name': ['Miller', np.nan, np.nan, 'Milner', 'Cooze'],
'age': [22, np.nan, 23, 24, 25],
'sex': ['m', np.nan, 'f', 'm', 'f'],
'Test1_Score': [4, np.nan, 0, 0, 0],
'Test2_Score': [25, np.nan, np.nan, 0, 0]}
results = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'sex', 'Test1_Score', 'Test2_Score'])




results


first_name last_name   age  sex  Test1_Score  Test2_Score
0      Jason    Miller  22.0    m          4.0         25.0
1        NaN       NaN   NaN  NaN          NaN          NaN
2       Tina       NaN  23.0    f          0.0          NaN
3       Jake    Milner  24.0    m          0.0          0.0
4        Amy     Cooze  25.0    f          0.0          0.0

您可以使用以下函数，它将为您提供在 Dataframe 的输出

  • 零价值
  • 缺失的价值观
  • 占总值的百分比
  • 总零缺失值
  • % 总零缺失值
  • 资料类别

只需复制粘贴下面的函数，并通过传递您的熊猫数据框调用它

def missing_zero_values_table(df):
zero_val = (df == 0.00).astype(int).sum(axis=0)
mis_val = df.isnull().sum()
mis_val_percent = 100 * df.isnull().sum() / len(df)
mz_table = pd.concat([zero_val, mis_val, mis_val_percent], axis=1)
mz_table = mz_table.rename(
columns = {0 : 'Zero Values', 1 : 'Missing Values', 2 : '% of Total Values'})
mz_table['Total Zero Missing Values'] = mz_table['Zero Values'] + mz_table['Missing Values']
mz_table['% Total Zero Missing Values'] = 100 * mz_table['Total Zero Missing Values'] / len(df)
mz_table['Data Type'] = df.dtypes
mz_table = mz_table[
mz_table.iloc[:,1] != 0].sort_values(
'% of Total Values', ascending=False).round(1)
print ("Your selected dataframe has " + str(df.shape[1]) + " columns and " + str(df.shape[0]) + " Rows.\n"
"There are " + str(mz_table.shape[0]) +
" columns that have missing values.")
#         mz_table.to_excel('D:/sampledata/missing_and_zero_values.xlsx', freeze_panes=(1,0), index = False)
return mz_table


missing_zero_values_table(results)

输出

Your selected dataframe has 6 columns and 5 Rows.
There are 6 columns that have missing values.


Zero Values  Missing Values  % of Total Values  Total Zero Missing Values  % Total Zero Missing Values Data Type
last_name              0               2               40.0                          2                         40.0    object
Test2_Score            2               2               40.0                          4                         80.0   float64
first_name             0               1               20.0                          1                         20.0    object
age                    0               1               20.0                          1                         20.0   float64
sex                    0               1               20.0                          1                         20.0    object
Test1_Score            3               1               20.0                          4                         80.0   float64

如果希望保持简单，那么可以使用以下函数获取% 中缺少的值

def missing(dff):
print (round((dff.isnull().sum() * 100/ len(dff)),2).sort_values(ascending=False))




missing(results)


Test2_Score    40.0
last_name      40.0
Test1_Score    20.0
sex            20.0
age            20.0
first_name     20.0
dtype: float64
小开

你要找的解决办法是:

round(df.isnull().mean()*100,2)

这将四舍五入的百分比高达小数点后2位

另一种方法是

round((df.isnull().sum()*100)/len(df),2)

但是这并不像使用 mean ()那样有效。

小开

如果有多个数据框，下面的函数将用百分比计算每列缺失值的数目

def miss_data(df):
x = ['column_name','missing_data', 'missing_in_percentage']
missing_data = pd.DataFrame(columns=x)
columns = df.columns
for col in columns:
icolumn_name = col
imissing_data = df[col].isnull().sum()
imissing_in_percentage = (df[col].isnull().sum()/df[col].shape[0])*100


missing_data.loc[len(missing_data)] = [icolumn_name, imissing_data, imissing_in_percentage]
print(missing_data)
小开

通过以下代码，可以从每个列获得相应的百分比值。只需将 train _ data 的名称切换为 df，以防万一。

输入:

In [1]:


all_data_na = (train_data.isnull().sum() / len(train_data)) * 100
all_data_na = all_data_na.drop(all_data_na[all_data_na == 0].index).sort_values(ascending=False)[:30]
missing_data = pd.DataFrame({'Missing Ratio' :all_data_na})
missing_data.head(20)

产出:

Out[1]:
Missing Ratio
left_eyebrow_outer_end_x       68.435239
left_eyebrow_outer_end_y       68.435239
right_eyebrow_outer_end_y      68.279189
right_eyebrow_outer_end_x      68.279189
left_eye_outer_corner_x        67.839410
left_eye_outer_corner_y        67.839410
right_eye_inner_corner_x       67.825223
right_eye_inner_corner_y       67.825223
right_eye_outer_corner_x       67.825223
right_eye_outer_corner_y       67.825223
mouth_left_corner_y            67.811037
mouth_left_corner_x            67.811037
left_eyebrow_inner_end_x       67.796851
left_eyebrow_inner_end_y       67.796851
right_eyebrow_inner_end_y      67.796851
mouth_right_corner_x           67.796851
mouth_right_corner_y           67.796851
right_eyebrow_inner_end_x      67.796851
left_eye_inner_corner_x        67.782664
left_eye_inner_corner_y        67.782664
小开

对我来说，我是这样做的:

def missing_percent(df):
# Total missing values
mis_val = df.isnull().sum()
        

# Percentage of missing values
mis_percent = 100 * df.isnull().sum() / len(df)
        

# Make a table with the results
mis_table = pd.concat([mis_val, mis_percent], axis=1)
        

# Rename the columns
mis_columns = mis_table.rename(
columns = {0 : 'Missing Values', 1 : 'Percent of Total Values'})
        

# Sort the table by percentage of missing descending
mis_columns = mis_columns[
mis_columns.iloc[:,1] != 0].sort_values(
'Percent of Total Values', ascending=False).round(2)
        

# Print some summary information
print ("Your selected dataframe has " + str(df.shape[1]) + " columns.\n"
"There are " + str(mis_columns.shape[0]) +
" columns that have missing values.")
        

# Return the dataframe with missing information
return mis_columns
小开

我们来分析一下你的要求

  1. 你想要失去价值的百分比
  2. 它应该按升序排序，并将值四舍五入为2浮点数

说明:

  1. Dhr [ fill _ protocol ] . isnull () . sum ()-从列方面给出缺失值的总数
  2. Form [0]-给出行的总数
  3. (dhr [ fill _ ols ] . isnull () . sum ()/dhr.form [0])-给出一个以百分比作为值、列名作为索引的序列
  4. 因为输出是一个序列，您可以根据值进行舍入和排序

密码:

(dhr[fill_cols].isnull().sum()/dhr.shape[0]).round(2).sort_values()

参考文献: Rel = “ nofollow noReferrer”> round

小开

单线解法单线解法

df.isnull().mean().round(4).mul(100).sort_values(ascending=False)
小开 
import numpy as np


import pandas as pd


df = pd.read_csv('https://query.data.world/s/Hfu_PsEuD1Z_yJHmGaxWTxvkz7W_b0')


df.loc[np.isnan(df['Product_Base_Margin']),['Product_Base_Margin']]=df['Product_Base_Margin'].mean()


print(round(100*(df.isnull().sum()/len(df.index)), 2))
小开

试试这个方法


import pandas as pd
df = pd.read_csv('https://query.data.world/s/Hfu_PsEuD1Z_yJHmGaxWTxvkz7W_b0')
print(round(100*(df.isnull().sum()/len(df.index)),2))
小开

我找到的最佳解决方案是-(只显示缺少的列)

missing_values = [feature for feature in df.columns if df[feature].isnull().sum() > 1]


for feature in missing_values:
print(f"{feature} {np.round(df[feature].isnull().mean(), 4)}% missing values")
小开 
import pandas as pd
df = pd.read_csv('https://query.data.world/s/Hfu_PsEuD1Z_yJHmGaxWTxvkz7W_b0')
df.isna().sum()


Output:


Ord_id                   0
Prod_id                  0
Ship_id                  0
Cust_id                  0
Sales                   20
Discount                55
Order_Quantity          55
Profit                  55
Shipping_Cost           55
Product_Base_Margin    109
dtype: int64

df.shape


Output: (8399, 10)

# for share [0; 1] of nan in each column


df.isna().sum() / df.shape[0]


Output:


Ord_id                0.0000
Prod_id               0.0000
Ship_id               0.0000
Cust_id               0.0000
Sales                 0.0024  # (20  / 8399)
Discount              0.0065  # (55  / 8399)
Order_Quantity        0.0065  # (55  / 8399)
Profit                0.0065  # (55  / 8399)
Shipping_Cost         0.0065  # (55  / 8399)
Product_Base_Margin   0.0130  # (109 / 8399)
dtype: float64

# for percent [0; 100] of nan in each column


df.isna().sum() / (df.shape[0] / 100)


Output:


Ord_id                0.0000
Prod_id               0.0000
Ship_id               0.0000
Cust_id               0.0000
Sales                 0.2381  # (20  / (8399 / 100))
Discount              0.6548  # (55  / (8399 / 100))
Order_Quantity        0.6548  # (55  / (8399 / 100))
Profit                0.6548  # (55  / (8399 / 100))
Shipping_Cost         0.6548  # (55  / (8399 / 100))
Product_Base_Margin   1.2978  # (109 / (8399 / 100))
dtype: float64

# for share [0; 1] of nan in dataframe


df.isna().sum() / (df.shape[0] * df.shape[1])


Output:


Ord_id                0.0000
Prod_id               0.0000
Ship_id               0.0000
Cust_id               0.0000
Sales                 0.0002  # (20  / (8399 * 10))
Discount              0.0007  # (55  / (8399 * 10))
Order_Quantity        0.0007  # (55  / (8399 * 10))
Profit                0.0007  # (55  / (8399 * 10))
Shipping_Cost         0.0007  # (55  / (8399 * 10))
Product_Base_Margin   0.0013  # (109 / (8399 * 10))
dtype: float64

# for percent [0; 100] of nan in dataframe


df.isna().sum() / ((df.shape[0] * df.shape[1]) / 100)


Output:


Ord_id                0.0000
Prod_id               0.0000
Ship_id               0.0000
Cust_id               0.0000
Sales                 0.0238  # (20  / ((8399 * 10) / 100))
Discount              0.0655  # (55  / ((8399 * 10) / 100))
Order_Quantity        0.0655  # (55  / ((8399 * 10) / 100))
Profit                0.0655  # (55  / ((8399 * 10) / 100))
Shipping_Cost         0.0655  # (55  / ((8399 * 10) / 100))
Product_Base_Margin   0.1298  # (109 / ((8399 * 10) / 100))
dtype: float64
小开

一句俏皮话

我想知道没有人利用 大小和数量的优势? 这似乎是最短(也可能是最快)的方式做到这一点。

df.apply(lambda x: 1-(x.count()/x.size))

结果:

Ord_id                 0.000000
Prod_id                0.000000
Ship_id                0.000000
Cust_id                0.000000
Sales                  0.002381
Discount               0.006548
Order_Quantity         0.006548
Profit                 0.006548
Shipping_Cost          0.006548
Product_Base_Margin    0.012978
dtype: float64

如果你发现任何原因为什么这不是一个好的方式，请评论


提交答案