Quickly sampling large number of rows from large dataframes in python
<p>I have a very large dataframe (about 1.1M rows) and I am trying to sample it.</p>

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

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

<p>Method 1 - Using pandas :</p>

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

<p>Method 2 :</p>

<p>I tried to write all the sampled lines to another csv.</p>

<pre><code>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)))
</code></pre>

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

<p>Thanks</p>

<p>It seems you may benefit from simple <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html" rel="nofollow">selection methods</a>.  We don't have your data, so here is an example of selecting a subset using a pandas <code>Index</code> object and the <code>.iloc</code>  selection method.</p>

<pre><code>import pandas as pd
import numpy as np

# Large Sample DataFrame 
df = pd.DataFrame(np.random.randint(0,100,size=(1000000, 4)), columns=list('ABCD'))
df.info()

# Results
&lt;class 'pandas.core.frame.DataFrame'&gt;
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


# Convert a sample list of indices to an `Index` object
indices = [1, 2, 3, 10, 20, 30, 67, 78, 900, 2176, 78776]
idxs = pd.Index(indices)   
subset = df.iloc[idxs, :]
subset

# Output
A   B   C   D
1       9  33  62  17
2      44  73  85  11
3      56  83  85  79
10      5  72   3  82
20     72  22  61   2
30     75  15  51  11
67     82  12  18   5
78     95   9  86  81
900    23  51   3   5
2176   30  89  67  26
78776  54  88  56  17
</code></pre>

<p>In your case, try this:</p>

<pre><code>df = pd.read_csv("data.csv", index_col = 0).reset_index()
idx = pd.Index(sample_index_array)               # assuming a list
sample = df.iloc[idx, :]
</code></pre>

<p>The <a href="https://stackoverflow.com/questions/28757389/loc-vs-iloc-vs-ix-vs-at-vs-iat"><code>.iat</code> and <code>.at</code> methods</a> are even faster, but require scalar indices.  </p>

python pandas dataframe bigdata sampling