[Solved] How do I reduce columns to say, every 5th row only
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
[Solved] How do I reduce columns to say, every 5th row only
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
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
Last edited by Hagar Delest on Tue Nov 26, 2019 4:22 pm, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OOo 4.0.1 on Ms Windows 7
Re: How do I reduce columns to say, every 5th row only.
Fill a helper column with some marker content, for example
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:
Then you will able to filter the data by the result of the formula, what will be 0,1, 2, 3, 4 periodically.
Code: Select all
0
0
0
0
1
0
0
0
0
1
You can use constant values or a formula for the marker:
Code: Select all
=MOD(ROW();5)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: How do I reduce columns to say, every 5th row only.
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
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
OOo 4.0.1 on Ms Windows 7
Re: How do I reduce columns to say, every 5th row only.
Please upload your sample file here. I will modify it based on my tips, and I will send it back.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: How do I reduce columns to say, every 5th row only.
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
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
- Attachments
-
- SQM sample.ods
- (29.87 KiB) Downloaded 117 times
OOo 4.0.1 on Ms Windows 7
Re: How do I reduce columns to say, every 5th row only.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How do I reduce columns to say, every 5th row only.
What exactly are you doing?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
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 .
LibreOffice 7.3.7. 2; Ubuntu 22.04
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: How do I reduce columns to say, every 5th row only.
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
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
OOo 4.0.1 on Ms Windows 7
Re: How do I reduce columns to say, every 5th row only.
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.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
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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: [Solved] How do I reduce columns to say, every 5th row o
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
Martin
OOo 4.0.1 on Ms Windows 7
Re: [Solved] How do I reduce columns to say, every 5th row o
BASIC ????
Next it will be Fortran.
If is only a once a month exercise then Tibor's method seems fine 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.
Next it will be Fortran.
If is only a once a month exercise then Tibor's method seems fine 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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: [Solved] How do I reduce columns to say, every 5th row o
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
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
- Attachments
-
- SQM Qbasic.zip
- (9.06 KiB) Downloaded 109 times
OOo 4.0.1 on Ms Windows 7
Re: [Solved] How do I reduce columns to say, every 5th row o
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.
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.
LibreOffice 7.3.7. 2; Ubuntu 22.04