# How to construct a matrix composed of a subset of column of multiple .cvs files

Suppose I have a lot of .cvs files in a directory. All have the same structure and the first column is a column of dates --- their length is not necessarily equal. For instance, say I have two files the first looking like this

 Date,Open,High,Low,Close,Volume,Adj Close
2016-07-21,37.63,37.84,37.055,37.435,902000,37.435
2016-07-20,37.885,38.015,37.225,37.62,723500,37.62
2016-07-19,37.45,38.59,37.15,37.535,1054900,37.535
2016-07-18,37.48,37.77,37.365,37.50,852700,37.50
2016-07-15,37.00,37.565,36.85,37.425,2232700,37.425
2016-07-14,38.95,39.255,38.45,38.60,1486600,38.60
2016-07-13,39.00,39.555,38.78,38.87,2474600,38.87
2016-07-12,37.09,38.31,37.005,37.61,1001100,37.61
2016-07-11,36.37,37.255,36.15,37.00,1362800,37.00
2016-07-08,34.80,36.325,34.68,35.945,979200,35.945
2016-07-07,34.025,35.195,33.945,34.875,1140600,34.875
2016-07-06,34.35,34.525,33.32,33.79,1263300,33.79


The second like that

Date,Open,High,Low,Close,Volume,Adj Close
2016-07-18,95.90,96.83,95.17,95.87,550400,95.87
2016-07-15,94.50,95.98,94.37,95.89,840700,95.89
2016-07-14,94.08,95.37,93.90,94.81,888800,94.81
2016-07-13,93.04,93.94,93.04,93.60,736100,93.60
2016-07-12,92.31,93.85,92.02,93.37,720900,93.37
2016-07-11,90.49,92.25,90.48,92.17,797700,92.17
2016-07-08,89.63,91.14,89.26,89.87,1185000,89.87
2016-07-07,89.92,90.61,88.90,89.96,1106700,89.96
2016-07-06,92.00,92.47,89.37,89.60,1800800,89.60
2016-07-05,93.96,94.24,92.27,93.50,1172200,93.50
2016-07-04,95.19,95.27,94.01,94.47,582300,94.47
2016-07-01,94.59,95.37,93.81,94.68,971000,94.68
2016-06-30,92.54,94.10,92.09,94.06,1206800,94.06
2016-06-29,91.04,93.03,90.77,93.03,1036900,93.03
2016-06-28,90.16,91.31,89.94,90.08,1383500,90.08
2016-06-27,90.40,91.55,88.25,88.71,2344900,88.71
2016-06-24,90.00,92.83,89.15,90.87,3553700,90.87
2016-06-23,95.00,96.65,94.68,96.19,1202900,96.19


In fact I have more than 40 files with for some more that 4000 lines.

I would like to 1) import all those files or only the 2) construct a new matrix composed of the same column taken in each file eliminating all the line which have not the same first element --- take the least common subset.

This comes from the fact that in finance, one must clear the data before to take average and construct the variance-covariance matrix from the wished columns.

• I know how to load each file and eliminate each line one by one. I dont know how to make this automatic – cyrille.piatecki Oct 4 '16 at 20:38
• ...and where is the code for "load(ing) each file and eliminat(ing) each line one by one"? – J. M.'s technical difficulties Oct 4 '16 at 20:45

Is this what you want?

files = FileNames["*.csv", NotebookDirectory[]]

imp = Drop[#, 1] & @ Import[#] & /@ files

lis = Table[imp[[i, All, 1]], {i, 1, Length@imp}]

dc = Intersection[##] & @@ lis

pos = Sort[#, Last] & @ (Flatten[#, 1] & @ (Position[imp, #] & /@ dc))[[All, 1 ;; 2]]

out = imp[[##]] & @@@ pos


• Sory dor a so late comment. Here you keep the singletons. I want to eliminate all dates that are not in the intersection. – cyrille.piatecki Oct 5 '16 at 20:10
• @cyrille.piatecki So you want to keep the intersection? See edit. – corey979 Oct 5 '16 at 20:30
• Yes. I imagine easily that I can do it by a syntax of the type : if x belongs to the list out, drop it from the list toto. I will try this but if you can show me the way it will be very nice of you. – cyrille.piatecki Oct 6 '16 at 3:15