在python中从大型数据帧中快速采样大量行 [英] Quickly sampling large number of rows from large dataframes in python

问题描述

我有一个非常大的数据框(大约110万行)，我正在尝试对其进行采样.

I have a very large dataframe (about 1.1M rows) and I am trying to sample it.

我有一个要从整个数据框中选择的索引列表(大约70,000个索引).

I have a list of indexes (about 70,000 indexes) that I want to select from the entire dataframe.

这是我到目前为止尝试过的方法，但是所有这些方法都花费了太多时间:

This is what Ive tried so far but all these methods are taking way too much time:

方法1-使用熊猫:

sample = pandas.read_csv("data.csv", index_col = 0).reset_index()
sample = sample[sample['Id'].isin(sample_index_array)]

方法2:

我试图将所有采样行写入另一个csv.

I tried to write all the sampled lines to another csv.

f = open("data.csv",'r')

out  = open("sampled_date.csv", 'w')
out.write(f.readline())

while 1:
    total += 1
    line = f.readline().strip()

    if line =='':
        break
    arr = line.split(",")

    if (int(arr[0]) in sample_index_array):
        out.write(",".join(e for e in (line)))

有人可以建议一种更好的方法吗?还是我可以对其进行修改以使其更快?

Can anyone please suggest a better method? Or how I can modify this to make it faster?

谢谢

推荐答案

我们没有您的数据，因此这里有两个选项的示例:

We don't have your data, so here is an example with two options:

1. 阅读后:使用熊猫Index对象通过.iloc skiprows参数

1. after reading: use a pandas Index object to select a subset via the .iloc selection method
2. while reading: a predicate with the skiprows parameter

给予

索引的集合和(大)样本DataFrame写入test.csv:

A collection of indices and a (large) sample DataFrame written to test.csv:

import pandas as pd
import numpy as np


indices = [1, 2, 3, 10, 20, 30, 67, 78, 900, 2176, 78776]

df = pd.DataFrame(np.random.randint(0, 100, size=(1000000, 4)), columns=list("ABCD"))
df.to_csv("test.csv", header=False)
df.info()

输出

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1000000 entries, 0 to 999999
Data columns (total 4 columns):
A    1000000 non-null int32
B    1000000 non-null int32
C    1000000 non-null int32
D    1000000 non-null int32
dtypes: int32(4)
memory usage: 15.3 MB

代码

选项1-阅读后

将索引的样本列表转换为Index对象，并对加载的DataFrame进行切片:

Convert a sample list of indices to an Index object and slice the loaded DataFrame:

idxs = pd.Index(indices)   
subset = df.iloc[idxs, :]
print(subset)

.iat和.at方法甚至更快，但是需要标量索引.

The .iat and .at methods are even faster, but require scalar indices.

选项2-阅读时(推荐)

我们可以编写一个谓词，以在读取文件时保留选定的索引(效率更高):

We can write a predicate that keeps selected indices as the file is being read (more efficient):

pred = lambda x: x not in indices
data = pd.read_csv("test.csv", skiprows=pred, index_col=0, names="ABCD")
print(data)

另请参阅导致扩展skiprows 的问题.

See also the issue that led to extending skiprows.

结果

后面的选项产生相同的输出:

The same output is produced from the latter options:

        A   B   C   D
1      74  95  28   4
2      87   3  49  94
3      53  54  34  97
10     58  41  48  15
20     86  20  92  11
30     36  59  22   5
67     49  23  86  63
78     98  63  60  75
900    26  11  71  85
2176   12  73  58  91
78776  42  30  97  96

这篇关于在python中从大型数据帧中快速采样大量行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！