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

Discuss the spreadsheet application
Locked
marty-0750
Posts: 28
Joined: Tue Feb 17, 2009 5:01 am

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

Post 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
Last edited by Hagar Delest on Tue Nov 26, 2019 4:22 pm, edited 1 time in total.
Reason: tagged solved
OOo 4.0.1 on Ms Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

Fill a helper column with some marker content, for example

Code: Select all

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

=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.
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.
marty-0750
Posts: 28
Joined: Tue Feb 17, 2009 5:01 am

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

Post 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
OOo 4.0.1 on Ms Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

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.
marty-0750
Posts: 28
Joined: Tue Feb 17, 2009 5:01 am

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

Post 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
Attachments
SQM sample.ods
(29.87 KiB) Downloaded 117 times
OOo 4.0.1 on Ms Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

SQM sample_Zizi64.ods
(28.25 KiB) Downloaded 111 times
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.
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

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

Post 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: .
LibreOffice 7.3.7. 2; Ubuntu 22.04
marty-0750
Posts: 28
Joined: Tue Feb 17, 2009 5:01 am

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

Post 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
OOo 4.0.1 on Ms Windows 7
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

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

Post 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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
marty-0750
Posts: 28
Joined: Tue Feb 17, 2009 5:01 am

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

Post 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
OOo 4.0.1 on Ms Windows 7
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

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

Post by jrkrideau »

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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
marty-0750
Posts: 28
Joined: Tue Feb 17, 2009 5:01 am

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

Post 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
Attachments
SQM Qbasic.zip
(9.06 KiB) Downloaded 109 times
OOo 4.0.1 on Ms Windows 7
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

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

Post 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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Locked