Organizing a Large Column of Data

Question

I have collected 10 repeated lines of magnetometer data that have been imported into an excel spreadsheet. Every row is a different data point with the first column being position, the second and third columns being sensor readings (top and bottom respectively), and the fourth column being the line number that the data in that row belong to. Data were imported as "raw data."

Here is the spreadsheet: https://drive.google.com/open?id=0B4OUmLXw4ZJ7cDlYRUJmeEVzeVU

What is the easiest way to organize these data by their respective line?

What I've done is created a list with 10 spots for each line of data. I had to look through the data in excel to see where to part the columns for each line.... For example, for columns 1 and 2:

topsensor = {rawdata[[All, {1, 2}]][[1 ;; 1457]], 
rawdata[[All, {1, 2}]][[1458 ;; 3045]], 
rawdata[[All, {1, 2}]][[3046 ;; 4579]], 
rawdata[[All, {1, 2}]][[4580 ;; 6007]], 
rawdata[[All, {1, 2}]][[6008 ;; 7579]], 
rawdata[[All, {1, 2}]][[7580 ;; 9198]], 
rawdata[[All, {1, 2}]][[9199 ;; 10819]], 
rawdata[[All, {1, 2}]][[10820 ;; 12336]], 
rawdata[[All, {1, 2}]][[12337 ;; 13849]], 
rawdata[[All, {1, 2}]][[13850 ;; 15509]]};

which gives me a list that looks like {{line09 data},{line08 data},.....,{line00 data}}.

I thought of trying a nested for loop that runs through the data and adds every row with a "0" to the first part in the list, every row with a "1" to the second part in the list, etc...

Answer 1

Import your data discarding the first row (i.e. the headers):

data = Import["C:\\path\\to\\data.xlsx"][[1, 2 ;;]];

Gather the data by the value in the last column, i.e. the line in your case:

chopped = GatherBy[data, Last];

Use of GatherBy is the key step that saves you a lot of time over finding the chopping points by hand.

You can see that the chopping points are in accordance with what you found already:

Accumulate[Length /@ chopped]
(* Out: {1457, 3045, 4579, 6007, 7579, 9198, 10819, 12336, 13849, 15509} *)

Now, chopped is made of 10 groups of data, each of various length, one per line:

Dimensions /@ chopped
(* Out: {{1457, 4}, {1588, 4}, {1534, 4}, {1428, 4}, {1572, 4}, 
        {1619, 4}, {1621, 4}, {1517, 4}, {1513, 4}, {1660, 4}} *)

In each group, each row has the structure in the original data set, i.e. Y, top reading, bottom reading, line:

chopped[[1, 1]]

(* Out: {199.933, 47766.3, 47954., 9.} *)

So for instance grouped bottom readings could be obtained by:

bottom = chopped[[All, All, {1, 3}]];

And you can plot them as a function of Y:

ListLinePlot[bottom, PlotRange -> All, PlotLegends -> Range[9, 0, -1]]

And similarly you can manipulate all the other readings.