-1
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ What is difficult about this? It should be just programming. $\endgroup$ – mikado Oct 4 '16 at 20:35
  • $\begingroup$ I know how to load each file and eliminate each line one by one. I dont know how to make this automatic $\endgroup$ – cyrille.piatecki Oct 4 '16 at 20:38
  • 1
    $\begingroup$ ...and where is the code for "load(ing) each file and eliminat(ing) each line one by one"? $\endgroup$ – J. M. will be back soon Oct 4 '16 at 20:45
0
$\begingroup$

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

enter image description here

$\endgroup$
  • $\begingroup$ Sory dor a so late comment. Here you keep the singletons. I want to eliminate all dates that are not in the intersection. $\endgroup$ – cyrille.piatecki Oct 5 '16 at 20:10
  • $\begingroup$ @cyrille.piatecki So you want to keep the intersection? See edit. $\endgroup$ – corey979 Oct 5 '16 at 20:30
  • $\begingroup$ 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. $\endgroup$ – cyrille.piatecki Oct 6 '16 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.