python - 如何高效地删除 'NaN' 值旁边的值？

Question

我正在尝试从我的数据中删除错误的值(一系列 1500 万个值，700MB)。要删除的值是“nan”值旁边的值，例如:

系列:/1/,nan,/2/,3,/4/,nan,nan,nan,/8/,9斜杠包围的数字即/1/、/2/、/4/、/8/是值，应将其删除。

问题是使用我的以下代码计算它需要太长时间:

%%time

import numpy as np
import pandas as pd

# sample data
speed = np.random.uniform(0,25,15000000)
next_speed = speed[1:]

# create a dataframe
data_dict = {'speed': speed[:-1],
            'next_speed': next_speed}

df = pd.DataFrame(data_dict)


# calculate difference between the current speed and the next speed
list_of_differences = []

for i in df.index:
    difference = df.next_speed[i]-df.speed[i]
    list_of_differences.append(difference)

df['difference'] = list_of_differences

# add 'nan' to data in form of a string. 

for i in range(len(df.difference)):
    # arbitrary condition
    if df.difference[i] < -2:
        df.difference[i] = 'nan'

#########################################
# THE TIME-INEFFICIENT LOOP

# remove wrong values before and after 'nan'.
for i in  range(len(df)):

    # check if the value is a number to skip computations of the following "if" cases
    if not(isinstance(df.difference[i], str)):
        continue

    # case 1: where there's only one 'nan' surrounded by values. 
    # Without this case the algo will miss some wrong values because 'nan' will be removed
    # Example of a series: /1/,nan,/2/,3,4,nan,nan,nan,8,9
    # A number surrounded by slashes e.g. /1/ is a value to be removed
    if df.difference[i] == 'nan' and df.difference[i-1] != 'nan' and df.difference[i+1] != 'nan':
        df.difference[i-1]= 'wrong'
        df.difference[i+1]= 'wrong'

    # case 2: where the following values are 'nan': /1/, nan, nan, 4
    # E.g.: /1/, nan,/2/,3,/4/,nan,nan,nan,8,9
    elif df.difference[i] == 'nan' and df.difference[i+1] == 'nan':
        df.difference[i-1]= 'wrong'

    # case 3: where next value is NOT 'nan'  wrong, nan,nan,4 
        # E.g.: /1/, nan,/2/,3,/4/,nan,nan,nan,/8/,9
    elif df.difference[i] == 'nan' and df.difference[i+1] != 'nan':
        df.difference[i+1]= 'wrong'

如何提高时间效率？

Answer 1

这对我来说仍然是一项正在进行的工作。我将你的虚拟数据大小减少了 100 倍，以达到我可以等待的效果。

我还在我的版本顶部添加了此代码:

 import time

current_milli_time = lambda: int(round(time.time() * 1000))

def mark(s):
    print("[{}] {}".format(current_milli_time()/1000, s))

这只是打印一个字符串，前面有一个时间标记，看看是什么花了这么长时间。

完成后，在您的'difference'中列计算，您可以用向量运算代替手动列表生成。这段代码:

df = pd.DataFrame(data_dict)

mark("Got DataFrame")

# calculate difference between the current speed and the next speed
list_of_differences = []

for i in df.index:
    difference = df.next_speed[i]-df.speed[i]
    list_of_differences.append(difference)

df['difference'] = list_of_differences
mark("difference 1")

df['difference2'] = df['next_speed'] - df['speed']
mark('difference 2')

print(df[:10])

产生此输出:

[1490943913.921] Got DataFrame
[1490943922.094] difference 1
[1490943922.096] difference 2
   next_speed      speed  difference  difference2
0   18.008314  20.182982   -2.174669    -2.174669
1   14.736095  18.008314   -3.272219    -3.272219
2    5.352993  14.736095   -9.383102    -9.383102
3    5.854199   5.352993    0.501206     0.501206
4    2.003826   5.854199   -3.850373    -3.850373
5   12.736061   2.003826   10.732236    10.732236
6    2.512623  12.736061  -10.223438   -10.223438
7   18.224716   2.512623   15.712093    15.712093
8   14.023848  18.224716   -4.200868    -4.200868
9   15.991590  14.023848    1.967741     1.967741

请注意，两个差异列是相同的，但第二个版本花费了大约 8 秒的时间。 (当您有 100 倍以上的数据时，大概需要 800 秒。)

我在“nanify”过程中做了同样的事情:

df.difference2[df.difference2 < -2] = np.nan

这里的想法是，许多二元运算符实际上生成占位符、系列或向量。并且可以将其用作索引，这样 df.difference2 < -2变成(本质上)该条件为真的位置的列表，然后您可以索引 df (整个表)或 df 的任何列，例如 df.difference2 ，使用该索引。这是慢速 python for 的快速简写循环。

更新

好吧，最后，这是一个矢量化“时间效率低的循环”的版本。我只是将整个内容粘贴到底部以进行复制。

前提是 Series.isnull() 方法返回一个 bool 系列(列)，如果内容“丢失”、“无效”或“伪造”，则该系列为 true。一般来说，这意味着 NaN ，但它也识别 Python None 等。

在 pandas 中，棘手的部分是将列向上或向下移动一位以反射(reflect)“周围”性。

也就是说，我想要另一个 bool 列，其中如果 col[n] 为 null，则 col[n-1] 为 true。这是我的“在楠之前”专栏。同样，我想要另一列，如果 col[n] 为 null，则 col[n+1] 为 true。这是我的“after a nan”专栏。

事实证明我必须把这该死的东西拆开!我必须伸手去提取底层的numpy使用 Series.values 的数组属性，这样 pandas index 将被丢弃。然后创建一个新索引，从 0 开始，一切又恢复正常。 (如果您不删除索引，列会“记住”它们的数字应该是什么。因此，即使您删除列 [0]，该列也不会向下移动。相反，它知道“我丢失了我的[0] 值，但其他人仍然在正确的位置!”)

无论如何，弄清楚了这一点，我就能够构建三列(不必要 - 它们可能是表达式的一部分)，然后将它们合并到第四列中，该列指示您想要的内容:该列是 True当该行位于 nan 之前、之上或之后时值。

missing = df.difference2.isnull()
df['is_nan'] = missing
df['before_nan'] = np.append(missing[1:].values, False)
df['after_nan'] = np.insert(missing[:-1].values, 0, False)
df['around_nan'] = df.is_nan | df.before_nan | df.after_nan

这是整个事情:

import numpy as np
import pandas as pd

import time

current_milli_time = lambda: int(round(time.time() * 1000))

def mark(s):
    print("[{}] {}".format(current_milli_time()/1000, s))

# sample data
speed = np.random.uniform(0,25,150000)
next_speed = speed[1:]

# create a dataframe
data_dict = {'speed': speed[:-1],
            'next_speed': next_speed}

df = pd.DataFrame(data_dict)

mark("Got DataFrame")

# calculate difference between the current speed and the next speed
list_of_differences = []

#for i in df.index:
    #difference = df.next_speed[i]-df.speed[i]
    #list_of_differences.append(difference)

#df['difference'] = list_of_differences
#mark("difference 1")

df['difference'] = df['next_speed'] - df['speed']
mark('difference 2')

df['difference2'] = df['next_speed'] - df['speed']

# add 'nan' to data in form of a string.

#for i in range(len(df.difference)):
    ## arbitrary condition
    #if df.difference[i] < -2:
        #df.difference[i] = 'nan'

df.difference[df.difference < -2] = np.nan
mark('nanify')

df.difference2[df.difference2 < -2] = np.nan
mark('nanify 2')

missing = df.difference2.isnull()
df['is_nan'] = missing
df['before_nan'] = np.append(missing[1:].values, False)
df['after_nan'] = np.insert(missing[:-1].values, 0, False)
df['around_nan'] = df.is_nan | df.before_nan | df.after_nan
mark('looped')

#########################################
# THE TIME-INEFFICIENT LOOP

# remove wrong values before and after 'nan'.
for i in  range(len(df)):

    # check if the value is a number to skip computations of the following "if" cases
    if not(isinstance(df.difference[i], str)):
        continue

    # case 1: where there's only one 'nan' surrounded by values.
    # Without this case the algo will miss some wrong values because 'nan' will be removed
    # Example of a series: /1/,nan,/2/,3,4,nan,nan,nan,8,9
    # A number surrounded by slashes e.g. /1/ is a value to be removed
    if df.difference[i] == 'nan' and df.difference[i-1] != 'nan' and df.difference[i+1] != 'nan':
        df.difference[i-1]= 'wrong'
        df.difference[i+1]= 'wrong'

    # case 2: where the following values are 'nan': /1/, nan, nan, 4
    # E.g.: /1/, nan,/2/,3,/4/,nan,nan,nan,8,9
    elif df.difference[i] == 'nan' and df.difference[i+1] == 'nan':
        df.difference[i-1]= 'wrong'

    # case 3: where next value is NOT 'nan'  wrong, nan,nan,4
        # E.g.: /1/, nan,/2/,3,/4/,nan,nan,nan,/8/,9
    elif df.difference[i] == 'nan' and df.difference[i+1] != 'nan':
        df.difference[i+1]= 'wrong'

mark('time-inefficient loop done')