Coordinates Mis-matched when graphing points from spreadsheet

AstroZack shared this question 8 months ago
Answered

I have a problem with points graphing incorrectly from the spreadsheet. It is not keeping the correct points together because there are empty cells.


Notice the way the data is entered in column C. I want column A to be the x-coordinate and then column C to be the y-coordinate, but there are a bunch of empty cells in column C because of how the data is recorded. No matter what I do, when I go to create and graph a list points, the program is taking the first available value in column A as the x-coord and then the first non-empty cell in column C as the y-coord.


So for example, instead of graphing the point as (A22, C22), the point being created is (A6, C22). How can I prevent this mismatch?


How can I set up a list of points where it ignores incomplete sets of point (ex. {A6, C6} through {A21, C21}) and starts graphing at {A22, C22}?

Comments (12)

photo
1

Work with a matrix created as a copy from the spreadsheet.

m1 = KeepIf(Length(RemoveUndefined(L)) > 0, L, Sequence(Sequence(Cell(X, Y), X, 1, 6), Y, 6, 115))

All dependend objects and lists then refer exclusively to SpreadsheetCopy (directly or indirectly)

.

example: you need all defined (not == ?) Europa-Data:

LstEuropa = KeepIf(IsDefined(Element(L, 1)) ∧ IsDefined(Element(L, 3)), L, m1)

photo
1

I'm sorry, I just can't quite follow that and I've been working on it for two hours.


What is L? I don't have a variable L currently defined. Is that the list of ordered pairs (EuropaPoints, for example) or is it the list of data points in the column I'm working in (Ex. ListEuropa - defined as C6:C115)?


Since my data first begins in row 6 and could theoretically run to row 115, would that means that I'd go:

Sequence (Sequence(Cell(X,Y),X,6,115),Y6,115))


I'm testing this is much as I can but Geogebra keeps crashing/freezing...

photo
1

L is a local variable (2. parameters with valididity only inside the KeepIf() command)

See the manual for KeepIf( <Condition>, <Variable>, <List> )

In this context, L represents one row in the matrix whose content is passed to the first parameter (<condition>)

only rows of the matrix that meet the condition are taken over.

---------------------------------------

See also the manual for Sequence( <Expression>, <Variable>, <Start Value>, <End Value> )

this is one of the moste important commands of GGB.

Note: in your last post you write: Sequence (Sequence(Cell(X,Y),X,6,115),Y6,115))

This does not correspond at all to what I have written and inevitably leads to a crash.

----------------------------------------

The two following lines are pushed together vertically and form one line = 1 object in the algebra view.

Sequence(................................................ , Y, 6, 115)

..................Sequence(Cell(X, Y), X, 1, 6)

Are two nested sequence commands

The upper/outer command has a local variable named Y. This is incremented from 6 to 115. With each step the first parameter (left of Y) is executed. Y corresponds to the row number.

The lower/inner command is also a sequence. It has the local variable with the name X. It is incremented starting at 1 up to 6. With each increment the first parameter of the inner sequence (left of X) is executed. This corresponds to a column within a row with Cell(<columne X>,<row Y>)

The result is a matrix. This is a list that contains lists { {...}, {...}, ......., {...} }

--------------------------------------

All the above is difficult to understand at first. But it is worthwhile to learn how to work with lists, because they can be used to write much shorter applications.

photo
1

(unsolicited) AddON

Maybe you need a list (matrix) only with date and Europa. Then use the following.

LstEuropaSingle = Sequence({LstEuropa(n, 1), LstEuropa(n, 3)}, n, 1, Length(LstEuropa))

meaning:

the local variable "n" is incremented from 1 to the length (number of rows) of LstEuropa.

With each increment a list is formed ({....} in the first parameter. This list corresponds to one row.

This list contains two elements:

  • LstEuropa(n, 1) --> The element from LstEuropa with the row n and the column 1
  • LstEuropa(n, 3) --> The element from LstEuropa with the row n and the column 3

The result is a matrix with 2 colons

------------------------------------------------

alternative: create a List with points:

LstEuropaPoints = Sequence((LstEuropa(n, 1), LstEuropa(n, 3)), n, 1, Length(LstEuropa))

only the curly brackets are changed to round brackets.

-----------------------------------------------

Note: sooner or later you will have to master these commands.

I believe that all other methods require manual post-processing, depending on the constellation of inputs.

A possible alternative is that you prepare the data in Excel and import them cleaned into GGB. Then you would not have to change anything in the ggb program.

photo
1

Thank you, Rami!


It is taking me a while to get the hang of lists. I'm still not all the way there, but will keep working. Monday morning I hope to spend some more time working on the solution you gave here. Thanks again!

photo
photo
1

Why not just copy the numbers to new columns? If you can't do that then how about:


(A22:A38, C22:C38)

photo
1

Good question. Because I'm dealing with hundreds of students and need this to be as flexible as possible. Everything needs to be pre-programmed so that students don't need to do any messing with the code.

photo
1

good idea but next cell to an empty cell is matched with previous value of A22:A38

photo
photo
1

select rows of column A, then press ctrl and select the rows in column C

then right click-->create-->list of points

or


Zip((a, b), b, C22:C38, a, A22:A38)

photo
1

Please explain the complete workflow that your "hundreds of students" are doing

photo
1

I literally do have hundreds of students using this. The students are collecting their data from a planetarium software program, recording it in a spreadsheet and then copying the data into the GGB app here.


Once that is done, they click on the box for the moon they want to analyze. Their data points for that moon will then appear. If they have more than 10 data points for that moon, they will also see a sine wave appear along with a bunch of calculations and text. Students are using the sliders to match the shape of their sine wave - creating their own line of best fit. Once their data is somewhat close to fitting, an R^2 value will appear and thy can continue using the sliders to improve the R^2 value. While this is happening, the app is using the values from their sine wave to calculate the mass of Jupiter. Once the fit is as good as they can get it, students will submit several of the values provided in the calculations.


For the most part, the app is working beautifully. I have one special case which is starting to happen a lot which results in incorrectly graphed data.


The potential challenge comes when students are collecting their data. If they use the same times for the data on all of their moons then there is no problem - everything works flawlessly. However, when they do one moon and then restart for the next moon using different times it creates the situation shown in the GGB file attached above - it leaves empty cells in the spreadsheet. Because of the empty cells in the data column for the moons (ex. column C), GGB isn't graphing the ordered pairs for those columns correctly. It is taking the first available date (starting in A6) and using that as the x-coordinate instead of the date in the same row as the moon data. So, for example, instead of graphing the pair as (A22, C22) is is graphing (A6, C22).


The goal is for students to not have to do anything in the GGB app other than paste their data in and use the check boxes and slider bars as they work through the analysis.

photo
1

OK, I suggest you try something like this file

1) Students should just paste into, say, columns A and B

2) fix all the cells in column C - this enables a special behaviour so that deleting cells in A and B actually just sets them to undefined


3) RemoveUndefined(C1:C100) should then always give a "sane" list :)


@rami if you can't be polite then please just don't post

photo
© 2021 International GeoGebra Institute