title: Make my code faster - load CSVs into pandas dataframe on selected columns and merge them
body: <p>I have three CSV files in the folder ending by "1251". I want to iterate through the folder, pick these files, load them in chunks into pandas dataframes and merge them with selected columns.</p>
  
  <p>The file of 90MB is a breeze but the script takes 15 min to add the file of 700 MB (over 3 mil lines). The whole operation takes 20 min to finish - this being unacceptable. </p>
  
  <p>Is there any way to change and accelerate the procedure? I mean load CSV to pandas dataframes in chunks and merge/append/concatenate them into one file.</p>
  
  <p>This works well with small files, but it needs to be faster with big csv files. I have found many questions with good ideas, but this should work - not sure why is it so slow. 
  Any ideas how to make it faster?</p>
  
  <pre><code>import os
  import sys
  import struct
  import fileinput
  import csv
  import pandas as pd
  
  
  
  cwd = 'C:\\Users\\'
  print(cwd)
  directory = (cwd + '\\FINAL\\')
  directory2 = (cwd + '\\FINAL\\CSV')
  print(directory)
  x=pd.DataFrame()
  for file in os.listdir(directory):
      if file.endswith( "1251.csv"):
          fajl = os.path.splitext(file)[0]
          print(fajl)
          for chunk in pd.read_csv(directory + '\\' + fajl + ".csv", sep=",",error_bad_lines=False, encoding='latin-1',low_memory=False, chunksize=100000):
  
              mylist = []
              mylist.append(chunk)
              big_data = pd.concat(mylist, axis= 0)
  
  
              big_data = big_data.fillna(value='')
              selected = big_data[['SYS', 'MANDT', 'AGR_NAME', 'OBJECT', 'AUTH', 'FIELD', 'LOW', 'HIGH', 'DELETED']]
  
              x=x.append(selected)
  
              x.to_csv(directory2 + '\\' + fajl + '.csv', sep=',', index=False)
  </code></pre>