有没有一种方法可以加快以下Pandas循环的速度？

Question

有没有一种方法可以加快以下Pandas循环的速度？

7

我的数据框包含1000万行！在分组后，剩下约900万个子框架需要循环。

代码如下：

data = read.csv('big.csv')
for id, new_df in data.groupby(level=0): # look at mini df and do some analysis
    # some code for each of the small data frames

这是非常低效的，代码已经运行了10多个小时。

有没有办法加速？

完整代码：

d = pd.DataFrame() # new df to populate
print 'Start of the loop'
for id, new_df in data.groupby(level=0):
    c = [new_df.iloc[i:] for i in range(len(new_df.index))]
    x = pd.concat(c, keys=new_df.index).reset_index(level=(2,3), drop=True).reset_index()
    x = x.set_index(['level_0','level_1', x.groupby(['level_0','level_1']).cumcount()])
    d = pd.concat([d, x])

获取数据：

data = pd.read_csv('https://raw.githubusercontent.com/skiler07/data/master/so_data.csv', index_col=0).set_index(['id','date'])

注意：

大多数id只有一个日期，这表示仅有一次访问。对于有多次访问的id，我希望将它们结构化为三维格式，例如将所有访问存储在三个维度中的第二个维度中。输出结果为(id，visits，features)

- GRS

1

这个问题的答案取决于“# some code for each of the small data frames”。您是否有一个正在执行的示例计算，以及一些样本数据，以便我们可以进行测试/基准测试？ - jpp

1

@jpp 我会进行编辑。 - GRS

难题，但如果可能的话，dask 应该能够帮助解决。 - jezrael

@PatrickArtner 这是一个带有每个ID时间戳的2D框架。我想将其转换为带有时间戳的3D数组。 - GRS

为什么不将数据拆分为多个文件，然后逐个读取。这样你应该能够利用多个核心的特性。 - digitalnomd

显示剩余4条评论

3个回答

2

我相信您的特征工程方式可以更好地完成，但我将坚持回答您的问题。

在Python中，遍历字典比遍历数据框快得多。

这是我如何处理一个巨大的pandas数据框（约100,000,000行）的方法：

# reset the Dataframe index to get level 0 back as a column in your dataset
df = data.reset_index()  # the index will be (id, date)

# split the DataFrame based on id
# and store the splits as Dataframes in a dictionary using id as key
d = dict(tuple(df.groupby('id')))

# iterate over the Dictionary and process the values
for key, value in d.items():

    pass  # each value is a Dataframe


# concat the values and get the original (processed) Dataframe back  
df2 = pd.concat(d.values(), ignore_index=True)

- Abdulrahman Bres

0

修改了 @Stephen 的代码

def make_3d(dataset):

    def make_3d_lines(a_df):
        a_df['depth'] = 0 # sets all depth from (1 to n) to 0
        depth = 1 # initiate from 1, so that the first loop is correct
        prev = None
        accum = [] # accumulates blocks of data belonging to given user
        for row in a_df.values.tolist(): # for each row in our dataset
            row[0] = 0 # NOT SURE
            key = row[1] # this is the id of the row
            if key == prev: # if this rows id matches previous row's id, append together 
                depth += 1 
                accum.append(row)
            else: # else if this id is new, previous block is completed -> process it
                if depth == 0: # previous id appeared only once -> get that row from accum
                    yield accum[0] # also remember that depth = 0
                else: # process the block and emit each row
                    depth = 0
                    to_emit = [] # prepare to emit the list
                    for i in range(len(accum)): # for each unique day in the accumulated list
                        date = accum[i][2] # define date to be the first date it sees
                        for j, r in enumerate(accum[i:]):
                            to_emit.append(list(r))
                            to_emit[-1][0] = j # define the depth
                            to_emit[-1][2] = date # define the 
                    for r in to_emit[0:]:
                        yield r
                accum = [row]
            prev = key

    df_data = dataset.reset_index()
    df_data.columns = ['depth'] + list(df_data.columns)[1:]

    new_df = pd.DataFrame(
        make_3d_lines(df_data.sort_values('id date'.split(), ascending=[True,False])),
        columns=df_data.columns
    ).astype(dtype=df_data.dtypes.to_dict())

    return new_df.set_index('id date'.split())

测试：

t = pd.DataFrame(data={'id':[1,1,1,1,2,2,3,3,4,5], 'date':[20180311,20180310,20180210,20170505,20180312,20180311,20180312,20180311,20170501,20180304], 'feature':[10,20,45,1,14,15,20,20,13,11],'result':[1,1,0,0,0,0,1,0,1,1]})
t = t.reindex(columns=['id','date','feature','result'])
print t 
              id     date      feature      result
0              1  20180311          10           1
1              1  20180310          20           1
2              1  20180210          45           0
3              1  20170505           1           0
4              2  20180312          14           0
5              2  20180311          15           0
6              3  20180312          20           1
7              3  20180311          20           0
8              4  20170501          13           1
9              5  20180304          11           1

输出

                        depth     feature      result
id            date                                   
1             20180311      0          10           1
              20180311      1          20           1
              20180311      2          45           0
              20180311      3           1           0
              20180310      0          20           1
              20180310      1          45           0
              20180310      2           1           0
              20180210      0          45           0
              20180210      1           1           0
              20170505      0           1           0
2             20180312      0          14           0
              20180312      1          15           0
              20180311      0          15           0
3             20180312      0          20           1
              20180312      1          20           0
              20180311      0          20           0
4             20170501      0          13           1

- GRS

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Stephen Rauch · Accepted Answer

以下是一种加速的方法。在处理行的代码中直接添加需要的新行，可避免不断构建小型数据框所带来的开销。在我的机器上，您示例的10万行数据运行时间只需几秒钟。而您使用样本数据的10,000行的代码则需要> 100秒。这似乎代表了几个数量级的提高。

代码：

def make_3d(csv_filename):

    def make_3d_lines(a_df):
        a_df['depth'] = 0
        depth = 0
        prev = None
        accum = []
        for row in a_df.values.tolist():
            row[0] = 0
            key = row[1]
            if key == prev:
                depth += 1
                accum.append(row)
            else:
                if depth == 0:
                    yield row
                else:
                    depth = 0
                    to_emit = []
                    for i in range(len(accum)):
                        date = accum[i][2]
                        for j, r in enumerate(accum[i:]):
                            to_emit.append(list(r))
                            to_emit[-1][0] = j
                            to_emit[-1][2] = date
                    for r in to_emit[1:]:
                        yield r
                accum = [row]
            prev = key

    df_data = pd.read_csv('big-data.csv')
    df_data.columns = ['depth'] + list(df_data.columns)[1:]

    new_df = pd.DataFrame(
        make_3d_lines(df_data.sort_values('id date'.split())),
        columns=df_data.columns
    ).astype(dtype=df_data.dtypes.to_dict())

    return new_df.set_index('id date'.split())

测试代码：

start_time = time.time()
df = make_3d('big-data.csv')
print(time.time() - start_time)

df = df.drop(columns=['feature%d' % i for i in range(3, 25)])
print(df[df['depth'] != 0].head(10))

结果：

1.7390995025634766

                          depth  feature0  feature1  feature2
id              date                                         
207555809644681 20180104      1   0.03125  0.038623  0.008130
247833985674646 20180106      1   0.03125  0.004378  0.004065
252945024181083 20180107      1   0.03125  0.062836  0.065041
                20180107      2   0.00000  0.001870  0.008130
                20180109      1   0.00000  0.001870  0.008130
329567241731951 20180117      1   0.00000  0.041952  0.004065
                20180117      2   0.03125  0.003101  0.004065
                20180117      3   0.00000  0.030780  0.004065
                20180118      1   0.03125  0.003101  0.004065
                20180118      2   0.00000  0.030780  0.004065