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

Discuss the spreadsheet application

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

Postby marty-0750 » Thu Nov 14, 2019 7:52 am

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

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

Postby Zizi64 » Thu Nov 14, 2019 8:00 am

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8517
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby marty-0750 » Sun Nov 17, 2019 10:00 am

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

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

Postby Zizi64 » Sun Nov 17, 2019 12:14 pm

Please upload your sample file here. I will modify it based on my tips, and I will send it back.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8517
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby marty-0750 » Mon Nov 25, 2019 6:47 am

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 8 times
OOo 4.0.1 on Ms Windows XP
marty-0750
 
Posts: 20
Joined: Tue Feb 17, 2009 5:01 am

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

Postby Zizi64 » Mon Nov 25, 2019 11:45 am

SQM sample_Zizi64.ods
(28.25 KiB) Downloaded 4 times
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8517
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby jrkrideau » Tue Nov 26, 2019 2:25 am

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: .
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3715
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

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

Postby marty-0750 » Tue Nov 26, 2019 2:16 pm

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

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

Postby jrkrideau » Tue Nov 26, 2019 5:34 pm

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.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3715
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

Postby marty-0750 » Wed Nov 27, 2019 4:57 am

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

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

Postby jrkrideau » Wed Nov 27, 2019 5:11 pm

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.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3715
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

Postby marty-0750 » Thu Nov 28, 2019 6:46 am

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 3 times
OOo 4.0.1 on Ms Windows XP
marty-0750
 
Posts: 20
Joined: Tue Feb 17, 2009 5:01 am

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

Postby jrkrideau » Thu Nov 28, 2019 3:47 pm

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.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3715
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada


Return to Calc

Who is online

Users browsing this forum: baro, Google [Bot], MSN [Bot] and 19 guests