Filter and Merging many data files after edit in R?
<p>I have thousand of files in separate folder. I need to bind them all together. I know how to read all these files using <code>list.files</code> function in <code>R</code>. But this problem is little complicated. Before binding all files into single file in R, I need to edit each every file. Here is sample data of 1st file: </p>

<pre><code>Data &lt;- read.table(file=FileList[1], header=FALSE, sep=",")[,c(1,2,6)]
head(Data)
         V1       V2      V6
  20MICRONS 20110701   47.40
 3IINFOTECH 20110701   47.30
    3MINDIA 20110701 4184.75
     A2ZMES 20110701  253.15
  AANJANEYA 20110701  368.45
 AARTIDRUGS 20110701  134.35
</code></pre>

<p>In this data, <code>V1</code> is company name, <code>V2</code> is date (first four digits represent year, then next two represent month and last two digits show day) and <code>V6</code> is price. Date is same in entire column <code>V2</code> because each file contain data for single day only. So, I want first to convert my data into following format:</p>

<pre><code>Date     20MICRONS 3IINFOTECH 3MINDIA  A2ZMES  AANJANEYA AARTIDRUGS 
20110701 47.40     47.30      4184.75  253.15  368.45    134.35
</code></pre>

<p>Then I want to merge all files into single data files by company name.  So, I want final output must be liked this: </p>

<pre><code>Date     20MICRONS 3IINFOTECH 3MINDIA  A2ZMES  AANJANEYA AARTIDRUGS 
20110701 47.40     47.30      4184.75  253.15  368.45    134.35
20110702 48.95     50.23      4101.36  256.32  370.48    136.95
</code></pre>

<p>where 2nd row represent data from 2nd files (first row is column name). </p>

<p>The another problem is that the number of rows in each file may not be the same. It might be possible that one file one contain 1000 rows, while file 2 might contain 1002. It may be possible that file 3 contain data for "Wipro" ( company name) which is not included in previous two files. So, now final output must be looked like this: </p>

<pre><code>Date     20MICRONS 3IINFOTECH 3MINDIA  A2ZMES  AANJANEYA AARTIDRUGS Wipro
20110701 47.40     47.30      4184.75  253.15  368.45    134.35     NA
20110702 48.95     50.23      4101.36  256.32  370.48    136.95     NA
20110703 50        52         4000.32  250.32  372.36    152.32     326
</code></pre>