Page 1 of 1

[Solved] How do I reduce columns to say, every 5th row only

PostPosted: Thu Nov 14, 2019 7:52 am
by marty-0750
I have this selection of data for every one minute of time. But I want to eliminate every four rows so that that I have data for only every 5 minutes as example on right. There are thousands of rows to process.

2019/10/01 12:00:39 AM 18.89----------- 2019/10/01 12:00:39 AM 18.89
2019/10/01 12:01:39 AM 18.89----------- 2019/10/01 12:05:39 AM 18.89
2019/10/01 12:02:39 AM 18.89----------- 2019/10/01 12:10:39 AM 18.9
2019/10/01 12:03:39 AM 18.9------------ 2019/10/01 12:15:39 AM 18.9
2019/10/01 12:04:39 AM 18.89
2019/10/01 12:05:39 AM 18.89
2019/10/01 12:06:39 AM 18.9
2019/10/01 12:07:39 AM 18.9
2019/10/01 12:08:39 AM 18.89
2019/10/01 12:09:39 AM 18.9
2019/10/01 12:10:39 AM 18.9
2019/10/01 12:11:39 AM 18.9
2019/10/01 12:12:39 AM 18.91
2019/10/01 12:13:39 AM 18.9
2019/10/01 12:14:39 AM 18.9
2019/10/01 12:15:39 AM 18.9
2019/10/01 12:16:39 AM 18.9
2019/10/01 12:17:39 AM 18.9


Martin

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Thu Nov 14, 2019 8:00 am
by Zizi64
Fill a helper column with some marker content, for example
Code: Select all   Expand viewCollapse view
0
0
0
0
1
0
0
0
0
1

And then use a Filter in your spreadsheet. Copy the filtered content onto an another sheet.

You can use constant values or a formula for the marker:
Code: Select all   Expand viewCollapse view
=MOD(ROW();5)

Then you will able to filter the data by the result of the formula, what will be 0,1, 2, 3, 4 periodically.

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Sun Nov 17, 2019 10:00 am
by marty-0750
Tibor

I think i may need a more explicit instructions. I don't know how to use =MOD(ROW();5). I put A1 in "ROW" but then what do I do?

Martin

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Sun Nov 17, 2019 12:14 pm
by Zizi64
Please upload your sample file here. I will modify it based on my tips, and I will send it back.

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Mon Nov 25, 2019 6:47 am
by marty-0750
Hi Zizi64

Here is a sample. Will appreciate if you can show step by step what I need to do.

I have 36 of sets of data in separate files like this to process each with 20,000 lines of data. Is there also a way to auto process files as a batch to save tedium?

Martin

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Mon Nov 25, 2019 11:45 am
by Zizi64
SQM sample_Zizi64.ods
(28.25 KiB) Downloaded 7 times

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Tue Nov 26, 2019 2:25 am
by jrkrideau
marty-0750 wrote:Hi Zizi64

I have 36 of sets of data in separate files like this to process each with 20,000 lines of data. Is there also a way to auto process files as a batch to save tedium?

Martin


What exactly are you doing?

Are all the files in exactly the same format?
Are file names in a consistent pattern, something like dat1.ods, dat2.ods and so on?
Are the files originally in .ods or perhaps .csv format.
Is each file being analysed individually or will you be doing some analyses on larger data sets?
Are you likely to be doing this sort of thing regularly?


If, at the moment' you have 36 files containing 720,000 data points, you might want to consider another tool rather than a spread sheet. Perhaps some of our data base gurus could comment on using one?

I can think of one or two ways to auto process the files but it would mean using completely different soft ware and a wee bit of a learning curve :twisted: .

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Tue Nov 26, 2019 2:16 pm
by marty-0750
Zizi64

Ah now i get it. It tried it and it works

thank you so much


And Jrkrideau

This is an ongoing project. Data is acquired every night 365 days a year. A file will contain 1 month of data. So it seems its not that onerous to process each file given the method described by Zizi64 does not take much time. Thank you for responding.

Martin

Re: How do I reduce columns to say, every 5th row only.

PostPosted: Tue Nov 26, 2019 5:34 pm
by jrkrideau
marty-0750 wrote:Zizi64

Ah now i get it. It tried it and it works

thank you so much


And Jrkrideau

This is an ongoing project. Data is acquired every night 365 days a year. A file will contain 1 month of data. So it seems its not that onerous to process each file given the method described by Zizi64 does not take much time. Thank you for responding.

Martin


You should definitely not be using spreadsheets. Tibor"s (Zizi64"s) method is excellent for a one-off project but for such an on-going project you really need to do a lot of automation and, almost certainly store the raw data in a database for future manipulation. This should reduce workload and improve data integrity.

From the SQM Reader Pro 2.2.0.0 manual, it looks like you can get nice clean .csv output that should be fairly easy to input into a data base.

Re: [Solved] How do I reduce columns to say, every 5th row o

PostPosted: Wed Nov 27, 2019 4:57 am
by marty-0750
As an exercise tried a short Power Basic program (yep I still do old fashion BASIC programming using Vdos window) to read lines in csv version and grab only those with 5 minute intervals and output to a file. So maybe I could expand the program to process several files as a batch. Tibor's method is still practical in that future monthly files only need one-off end-of-month task and can be done within the sheet.

Martin

Re: [Solved] How do I reduce columns to say, every 5th row o

PostPosted: Wed Nov 27, 2019 5:11 pm
by jrkrideau
BASIC ????
Next it will be Fortran.

If is only a once a month exercise then Tibor's method seems fine [I misread this] Still,as an old data wrangler, it seems that one should generalize more. It is amazing how maddening it can be to get a mess of data that one needs to standardize before analysis.

Re: [Solved] How do I reduce columns to say, every 5th row o

PostPosted: Thu Nov 28, 2019 6:46 am
by marty-0750
Waste time on learning new coding when BASIC that I know well does the trick?! Nah don't think so.

Attach is the solution. 24 lines of QBasic code that extracts every 5-minute line. A sample file of a 1000 odd lines for you want to check it.

The input sample is called SQM2019.OUT because it was first merge and trimmed with another BASIC program I wrote.

QBASIC processed 9 months of about 170,000 lines into 5-minute intervals in 53 seconds. Power Basic did it 28 seconds! If I was running in native Dos it would just few seconds for each.

Problem solved I'd say.

Thank you for nudging me to a better alternative solution. Much appreciated.

Martin

Re: [Solved] How do I reduce columns to say, every 5th row o

PostPosted: Thu Nov 28, 2019 3:47 pm
by jrkrideau
Ah, very nice.

I KNEW there had to to a better way. I just had not realized that BASIC would be so efficient. I have not done anything in it in many years.