# Write/read efficiently dataframe objects into memory or disk [closed]

i'm running a for loop that loops over all the rows of a pandas dataframe, then it calculates the euclidean distance from one point at a time to all the other points in the dataframe, then it pass the following point, and do the same thing, and so on.

The thing is that i need to store the value counts of the distances to plot a histogram later, i'm storing this in another pandas dataframe. The problem is that as the second dataframe gets bigger, i will run out of memory at the some time. Not to mention that also as the dataframe size grows, repeating this same loop will get slower, since it will be heavier and harder to handle in memory.

Here's some toy data to reproduce the original problem:

import pandas as pd

xx = []
yy = []
tt = []

for i in progressbar(range(1,655556)):
xx.append(i)
yy.append(i)
tt.append(i)

df = pd.DataFrame()
df['xx'] = xx
df['yy'] = yy
df['tt'] = tt
df['xx'] = df['xx'].apply(lambda x : float(x))
df['yy'] = df['yy'].apply(lambda x : float(x))
df['tt'] = df['tt'].apply(lambda x : float(x))
df


This is the original piece of code i was using:

counts = pd.DataFrame()

for index, row in df.iterrows():

dist = pd.Series(np.sqrt((row.xx - df.xx)**2 + (row.yy - df.yy)**2 + (row.tt - df.tt)**2))
counter = pd.Series(dist.value_counts( sort = True)).reset_index().rename(columns = {'index': 'values', 0:'counts'})
counts = counts.append(counter)


The original df has a shape of (695556, 3) so the expected result should be a dataframe of shape (695556**3, 2) containing all the distance values from all the 3 vectors, and their counts. The problem is that this is impossible to fit into my 16gb ram.

So i was trying this instead:

for index, row in df.iterrows():
counts = pd.DataFrame()
dist = pd.Series(np.sqrt((row.xx - df.xx)**2 + (row.yy - combination.yy)**2 + (row.tt - df.tt)**2))
counter = pd.Series(dist.value_counts( sort = True)).reset_index().rename(columns = {'index': 'values', 0:'counts'})
counts = counts.append(counter)
counts.to_csv('counts/count_' + str(index) + '.csv')
del counts


In this version, instead of just storing the counts dataframe into memory, i'm writting a csv for each loop. The idea is to put it all together later, once it finishes. This code works faster than the first one, since the time for each loop won't increment as the dataframe grows in size. Although, it still being slow, since it has to write a csv each time. Not to say it will be even slower when i will have to read all of those csv's into a single dataframe.

Can anyone show me how i could optimize this code to achieve these same results but in faster and more memory efficient way?? I'm also open to other implementations, like, spark, dask, or whatever way to achieve the same result: a dataframe containing the value counts for all the distances but that could be more or less handy in terms of time and memory.

Thank you very much in advance

• Hi, comparatively to StackOverflow we do not accept stub code, also, please give a little more context to your data otherwise it's hard to understand what your code is supposed to do. – IEatBagels Mar 13 '19 at 14:44
• Hi, thank you for the suggestions. What more do you need to know? I think it's clearly explained i9n the question. My code calculates all the distances between a point and all the rest of points in the df, and then goes to the next point and repeat the same operation. My issue is my code being extremely slow and memory consuming, and i would like to know if there's a possible way to optimize it in terms of speed and memory – Miguel 2488 Mar 13 '19 at 15:21

1. better to use dataframe.apply in vector operation for enhancing performance.
2. del dataframe is not working as you think, check this
3. saved reference first and use pandas.concat after looping
4. size of expected result should be (n ** 2, 2) where df.shape = (n, 3)
5. Optional: Use different datatype like np.float16 or np.float32 to trade memory size with decimal accuracy
import gc

def calc_dist(row):
return np.sqrt((row ** 2).sum())

temp = []
for _, row in df.iterrows():
new_df = df - row # recenter
dist = new_df.apply(calc_dist, 1)
counts = dist.value_counts(sort = True).reset_index()
counts.columns = ["distance", "count"]
del new_df, dist
temp.append(counts)
gc.collect()
final = pd.concat(temp, ignore_index=True).groupby("distance").sum()
key = 0.0
final.loc[key] = final.loc[key] - n

• Hi hks014. Thank you for your answer!! After trying your code suggestion i could see that it's way slower than mine. It takes 3 minutes per iteration. It's huge, my code doesn't even take a second per iteration. Are you sure this is the way to go?? I'll edit the question to include some toy data to reproduce the problem – Miguel 2488 Mar 13 '19 at 8:45