title: Pandas append perfomance concat/append using "larger" DataFrames
body: <p>The problem: I have data stored in csv file with the following columns data/id/value. I have 15 files each containing around 10-20mio rows. Each csv file covers a distinct period so the time indexes are non overlapping, but the columns are (new ids enter from time to time, old ones disappear).  What I originally did was running the script without the pivot call, but then I run into memory issues on my local machine (only 8GB). Since there is lots of redundancy in each file, pivot seemd at first a nice way out (roughly 2/3 less data) but now perfomance kicks in. If I run the following script the concat function will run "forever" (I always interrupted manually so far after some time (2h>)). Concat/append seem to have limitations in terms of size (I have roughly 10000-20000 columns), or do I miss something here? Any suggestions?</p>
  
  <pre><code>import pandas as pd
  path = 'D:\\'
  data = pd.DataFrame()
  #loop through list of raw file names
  for file in raw_files:
      data_tmp = pd.read_csv(path + file, engine='c',
                             compression='gzip',
                             low_memory=False,
                             usecols=['date', 'Value', 'ID'])
      data_tmp = data_tmp.pivot(index='date', columns='ID',
                                values='Value')
  
      data = pd.concat([data,data_tmp])
      del data_tmp
  </code></pre>
  
  <p>EDIT I:To clarify, each csv file has about 10-20mio rows and three columns, after pivot is applied this reduces to about 2000 rows but leads to 10000 columns.</p>
  
  <p>I can solve the memory issue by simply splitting the full-set of ids into subsets and run the needed calculations based on each subset as they are independent for each id. I know it makes me reload the same files n-times, where n is the number of subsets used, but this is still reasonable fast. I still wonder why append is not performing.</p>
  
  <p>EDIT II: I have tried to recreate the file structure with a simulation, which is as close as possible to the actual data structure. I hope it is clear, I didn't spend to much time minimizing simulation-time, but it runs reasonable fast on my machine.</p>
  
  <pre><code>import string
  import random
  import pandas as pd
  import numpy as np
  import math
  
  # Settings :-------------------------------
  num_ids = 20000
  start_ids = 4000
  num_files = 10
  id_interval = int((num_ids-start_ids)/num_files)
  len_ids = 9
  start_date = '1960-01-01'
  end_date = '2014-12-31'
  run_to_file = 2
  # ------------------------------------------
  
  # Simulation column IDs
  id_list = []
  # ensure unique elements are of size &gt;num_ids
  for x in range(num_ids + round(num_ids*0.1)):
      id_list.append(''.join(
          random.choice(string.ascii_uppercase + string.digits) for _
          in range(len_ids)))
  id_list = set(id_list)
  id_list = list(id_list)[:num_ids]
  
  time_index = pd.bdate_range(start_date,end_date,freq='D')
  chunk_size =  math.ceil(len(time_index)/num_files)
  
  data = []
  #  Simulate files
  for file in range(0, run_to_file):
      tmp_time = time_index[file * chunk_size:(file + 1) * chunk_size]
      # TODO not all cases cover, make sure ints are obtained
      tmp_ids = id_list[file * id_interval:
          start_ids + (file + 1) * id_interval]
  
      tmp_data = pd.DataFrame(np.random.standard_normal(
          (len(tmp_time), len(tmp_ids))), index=tmp_time,
          columns=tmp_ids)
  
      tmp_file = tmp_data.stack().sortlevel(1).reset_index()
      # final simulated data structure of the parsed csv file
      tmp_file = tmp_file.rename(columns={'level_0': 'Date', 'level_1':
                                          'ID', 0: 'Value'})
  
      # comment/uncomment if pivot takes place on aggregate level or not
      tmp_file = tmp_file.pivot(index='Date', columns='ID',
                                values='Value')
      data.append(tmp_file)
  
  data = pd.concat(data)
  # comment/uncomment if pivot takes place on aggregate level or not
  # data = data.pivot(index='Date', columns='ID', values='Value')
  </code></pre>