ENH: Add 'inplace' parameter to DataFrame.append()

[darindillon]

DataFrame.append() ought to have a "inplace=True" parameter to allow modifying the existing dataframe rather than copying it. This would be a big performance gain for large dataframes.

[wesm]

It actually wouldn't because new arrays still have to be allocated and the data copied over

[darindillon]

Hmm, interesting. Well, it would be convenient to have the parameter anyway, just to simplify code (even if there's no performance boost)

[jreback]

i often append to really big tables on disk (using HDFStore)

http://pandas.pydata.org/pandas-docs/stable/io.html#storing-in-table-format

[knowitnothing]

Isn't it possible to pre-alloc a larger-than-initially-needed DataFrame (possibly via a parameter) and make short appends efficient ? It would be nice to combine that with resizes that go beyond the imediate needs, reducing reallocations. This should be all obvious, and since I never touched Pandas code I guess there is some impeding reason for not doing that ?

The case I'm thinking about is that of data coming in real-time, and then one appends a DataFrame with a single entry to a larger one.

[jreback]

can you give an example of how you are using this (and include some parameters that would 'simulate' what you are doing?)

as an aside, a way of possibly mitigate this is to create new frames every so often (depends on your frequency of updates), then concat them together in one fell swoop (so you are appending to only a very small frame)

[knowitnothing]

I'm not using Pandas for that case I mentioned, but I'm considering it. I guess by "an example" you mean an extended version of that last phrase I included in the previous comment ?

So here is the extended example: the program receives live data from a given exchange. Let us restrict that to "trade" data, i.e. if a sell order or a buy order is filled in a given a exchange, the program receives a message telling that a buy/sell order was filled at a given price and a given volume. There might be additional details, but they are irrelevant here. So, suppose this exchange is just starting and the first trade on it just happened. Create a DataFrame for it. Now a new trade happened, append the just received to the earlier DataFrame. And so on. It is very interesting to use Pandas to resample this DataFrame up-to-the-last update so we can apply different analysis on it, in real time. It is also very interesting that the DataFrame can be stored in HDF5, while not a Pandas feature, it provides an easy way to do so. It might be the case that appending data to HDF5 is fast enough for this situation, and Pandas can retrieve the appended-DataFrame from the storage fast enough too. I have no benchmark data for this, by the way.

[jreback]

appending to HDF5 will be very easy to do here, to save a record of what you are doing, and you will be able to read from that HDF5 (in the same process and sequentially), e.g. you write, then read, and do your processing.

Doing this in separate processes is problematic; there is no 'locking' of the HDF5 file per se.

This is still allocating memory for the entire read back

There is nothing conceptually wrong with appending to an existing frame, it has to allocate new memory, but unless you are dealing with REALLY big frames, this shouldn't be a problem

In [1]: df = DataFrame(randn(100000,2),columns=list('AB'))

In [2]: df2 = DataFrame(randn(10,2),columns=list('AB'))

In [3]: %timeit df.append(df2)
1000 loops, best of 3: 431 us per loop

I suspect your bottleneck will not be this at all, but the actual operations you want to do on the frame

In [7]: df3 = df.append(df2)

In [8]: %timeit df3.mean()
100 loops, best of 3: 3.04 ms per loop

Write your program and profile it

my favorite saying: premature optimization is the root of all evil

[knowitnothing]

The dataframes can get big, but I guess it depends on what you mean by big. I have this data stored in another format taking ~5 million rows right now, "importing" it to a DataFrame is a one-time-heavy process but that is fine. I'm worried about reallocing 5 mil + 1, 5 mil + 1 + 1, for each append.

If the implementation takes O(n) for something that could be amortized to O(1) then this could become a bottleneck (or maybe already is for some given application, which then moved on to something else).

[jreback]

your are much better off doing a marginal calculation anyhow

if u are adding 1 point to 5m then it doesn't affect the stats of the 5m
so I would just calc the stats u need, write it to hdf for storage and later retrieval and do your calc
should be much more efficient

[knowitnothing]

Thus my earlier point: "... It might be the case that appending data to HDF5 is fast enough for this situation ...". I would actually continuously store new data in HDF5 by appending to what I currently have. And then I would use a subset of this stored DataFrame to do the analysis.

The possible advantage of not using HDF5 is that it we could guarantee that all the data is in memory, otherwise we have to trust on HDF5 being good/fast enough.

[jreback]

Here's a way to preallocate
create the frame bigger than you need (e.g. the existing + the expected)

fill in rows, increment your indexer (realloc if you run out of space)
calc your function that selects <= the indexer
repeat

In [7]: df = DataFrame(index=range(5),columns=list('AB'))

In [8]: df.iloc[0] = Series(dict(A = 10, B = 5))

In [9]: df.iloc[1] = Series(dict(A = 11, B = 6))

In [10]: def f(x,indexer):
   ....:     return x.iloc[0:indexer]*2
   ....: 

In [11]: f(df,2)
Out[11]: 
    A   B
0  20  10
1  22  12

In [12]: df.iloc[2] = Series(dict(A = 12, B = 7))

In [13]: f(df,3)
Out[13]: 
    A   B
0  20  10
1  22  12
2  24  14

[jreback]

you can do a combination of all of these approaches, you know your data and your workflow best

[knowitnothing]

The problem with your prealloc example is that you know the index values, I don't know them beforehand. Can you set index to NaN and later modify it without incurring more than constant time ? Thinking about this.. I guess I could use timestamp_{i-1} + 1 nanosecond for the prealloc. But I would still need to update the index when inserting actual data. Is that possible ? It would mostly solve the initial suggestion.

[jreback]

use the index like I did, add your 'index' as another column (which can be nan, then fill in as u fill the rows), then

func(df.iloc[0:indexer].set_index('my_index'))

[knowitnothing]

I will properly evaluate these suggestions, thank you :)