Subset variable data in one .csv based on values from same variable in a second .csv

1 次查看（过去 30 天）

Louise Wilson 2020-4-20

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/519456-subset-variable-data-in-one-csv-based-on-values-from-same-variable-in-a-second-csv

评论： Peter Perkins 2020-5-13

I have a large .csv file (~7GB) where the first column is filled with datetimes, and the remaining columns filled with data corresponding to that datetime.

e.g.:

20191025241533, 65.2, 35.2, 75.4

20200122061522, 32.2, 74.5, 61.3

20190411075120, 54.1, 87.0, 22.1

I then have a second smaller .csv which contains a list of datetimes which are of interest to me.

e.g.

20191025241533

I would like to subset the first large .csv based on the datetime values in the second, so I would only keep rows with datetimes present in the second .csv.

Secondly, I would like to use the datetime values in the second smaller .csv to extract files (where the filename is datetime) from a larger folder of files into a separate folder which contains only those files that have the same filename (datetime) as those recorded in the smaller .csv.

Thanks for your help!

11 个评论
显示 9更早的评论隐藏 9更早的评论

Peter Perkins 2020-5-5

There are several different things going on. You need to solve one at a time.

Again, csvread is not going to help. Don't use it.

The variable editor will not display such a large (7GB) table. That doesn't matter; just use head, or tail, or row subscripting to convince yourself that you have read the data correctly.

Which, it turns out, you haven't. Apparently, the first row of your file contains 0:N. You need to skip that row using HeaderRows.

It's not clear what's really in your files. You descibe the large one as having things like 20191025241533 in the first column, yet in one of your screenshots, it seems that you have (or have converted to) datenums. Don't use datenums. Another screenshot of the large file indicates that you've successfully read the file and have datetimes in your table, that's good. Another screenshot suggests that your small file contains text timestamps?. You seem to have converted those to datetime, that's good, and maybe that's what you did for the large file too. At any rate, it looks like you have a large table and a small table, each of which has a datetime variable.

So, are you not done except for using ismember? If your timestamps match exactly, that's all you need to do:

i = ismember(largeTable.Time,smallTable.Time);
shortenedTable = largeTable(i,:);

If they don't match exactly, then you need to use withtol:

wt = withtol(smallTable.Time,someTolerance);
shortenedTable = largeTable(wt,:);

I don't know what the tolerance would be. Your screenshots indicate that your timestamps are to a resolution of seconds; that suggests that you don't need a tolerance, and should be using ismember.

Louise Wilson 2020-5-6

Hi Peter,

Thank you. Yep, I have used withtol as it seems there is a slight difference in the datetimes.

I will use readtable from now on and I'm using head() to preview.

The large .csv is a spreadsheet which contains frequency values in the first row (1-7200Hz) and datenums in the first column. These datenums once converted to datetime correspond to the filenames of different .wav files e.g.yymmddHHMMss.wav

I wrote these files using datenum as the time vector as this is what a previous colleague had done and I've been building on their code. These spreadsheet is the output of an acoustic analysis which takes days to write so I'm afraid I can't go back and re-do them all as it would be so time consuming. So I have to work with this datenum in the first column. I converted the datenum to datetime and then changed the table to a timetable.

The small file contains the .wav file names in the first column and these are in text form as you say but correspond to datetimes.

Why do you advise not to use datenum?

I had used withtol and a tolderance of 5 seconds which got me a believable amount of matches. Using ismember I get a much smaller number of matches so it looks like there is a bit of a difference, something I'll investigate as I'd expect them to be the same.

Louise Wilson 2020-5-6

I have just realised that the reason they don't match is because when I convert from datenum to datetime, the timing is off by 1 second. Is there a way to fix that?

Peter Perkins 2020-5-13

They are almost certainly not off by 1 sec, but rather off by a small negative amount, and the display is off. Add .SSS to the3 display format and you will see. Because datenum counts in "days", they incur round-off for almost every value that is not a whole day, or at some "nice" whole hour. It's that round-off that is causing this. datetime tries to account for that, but it can't possibly be perfect.

So ... this is one of the reasons why not to use datenums. datetime has been around since R2014b. It's a better choice. If you have no control over the file, and you know the time stamps are to a resolution of whole seconds, use dateshift to round your datetimes.

请先登录，再进行评论。

请先登录，再回答此问题。