Finding missing sequential data in Numbers...

Question

Level 1

0 points

Finding missing sequential data in Numbers...

I'm analysing a lot of data which is currently in Excel spreadsheets, although I plan to convert to Numbers (I've got Numbers 09, version 2.1 (436)), and need to find omitted hours and add them to the spreadsheets.

For example, the hourly time column should be sequential: 0000, 0100, 0200, 0300, 0400... up to 2300 for each day, but some days are missing certain hours so the hourly column for a particular day may be: 0000, 0100, 0300, 0400 - so 0200 is missing. There is no pattern to the missing hours and some days are missing a number of hours.

As I have approximately 1.4 million rows of data that I am analysing, it is not feasible to check each row manually and then insert rows manually where hours are missing. I am analysing differences in the data on an hourly basis so it is crucial that I find the missing hours and insert rows so the missing hours can be added and the hour column runs sequentially - the rest of the data on the row would be left blank so that the application does not calculate differences in the data for a period greater than 1 hour.

If it is possible to do a search for the missing sequential hours (and add missing rows), obviously the application must know that the sequence should only run from 0000 to 2300 each day...

Hope this makes sense - if not let me know.

James

MacBook Pro, Mac OS X (10.6.8)

Posted on Jul 26, 2011 8:22 AM

Reply

Answer 1

Jul 26, 2011 8:54 AM in response to James1234567

PERL is your friend. Excel and Numbers do not sound like the write tools for this job (definielty not Numbers for this amount of data)..

Just my opinion.

To check the pattern you could create a formula in a new column that confirms that row[n] is the same as mod((row[n-1] + 100), 2400)

Even then this will be a pain because there are so many rows. Applescript may also solve this

Message was edited by: Wayne Contello to fix spelling

Reply

Answer 2

Jul 26, 2011 8:54 AM in response to Wayne Contello

=IF(A2=MOD(A1+100, 2400), "", "MISSING")

Reply

Answer 3

Jul 26, 2011 9:09 AM in response to James1234567

James,

It's the 1.4 million figure that worries me. Numbers just isn't good at large amounts of data - it becomes very slow.

Unless you're going to be processing this data a little bit at a time, you'd be better off in a capable database app.

Jerry

Reply

Answer 4

James1234567 Author

Level 1

0 points

Jul 26, 2011 10:00 AM in response to Wayne Contello

Thanks Wayne & Jerry.

Wayne, that's exactly what I was after to find the missing data - I assume it's beyond the scope of Numbers to then add a row with the correct hour where there is missing data? If so, that's a great help anyway as I can now find all the mising rows easily and just insert the rows manually.

Jerry, the 1.4 million rows is the total in 16 spreadsheets from 16 sites so all the data isn't combined and it's not too slow. I'm actually using SPSS to analyse most of the data once I've got it sorted, but didn't want to confuse matters by mentioning that initially!

Thanks again both of you.

James

Reply

Answer 5

KOENIG Yvan

Level 8

41,819 points

Jul 26, 2011 10:30 AM in response to Jerrold Green1

Not only it will be slow but it's unable to treat such tables.

No more than 65535 rows !

Yvan KOENIG (VALLAURIS, France) mardi 26 juillet 2011 19:30:22

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.0

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Reply

Answer 6

Jul 26, 2011 11:48 AM in response to James1234567

James1234567 wrote:

Thanks Wayne & Jerry.

...I assume it's beyond the scope of Numbers to then add a row with the correct hour where there is missing data?

Yes, but an effectve alternate might be to create a table with rows for all the time values and use LOOKUP functions to bring in the matching entries from hours that are present in the data.

Jerry

Reply

Answer 7

James1234567 Author

Level 1

0 points

Jul 27, 2011 3:34 AM in response to Jerrold Green1

Thanks Jerry.

I've been experimenting with the Lookup Function, but am having trouble with it... Would you mind giving an example of what to type in the first cell to bring in the matching entries? Also, would be be best to create new worksheets within the spreadsheets to bring in the matching entries or is it okay to use the same worksheets?

Many thanks.

James

Reply