en:lesson9

# Lesson 09. Data Analysis

## Data Analysis with Excel

Open the resulting data file with Excel. Press Ctrl-O and chose the folder containing the file. By default the Excel will not open txt form files, but when you enter * at the enter form and press Enter there will be displayed all files. Choose the data file and press Open.

This will start the import wizard. Press Next. On the first page specify the data form “with dividers”.

On the second page specify that column divider is the space character.

Make sure that in the window with example of the data presentation all columns are divided by vertical lines, than press Next.

Column’s data form must be leaved as “general”. Press “Finished”, the data will be written into Excel sheet.

Evidently, the data is divided by empty lines. To delete the empty lines select the first column.

Press F5, then press “Select” at the displayed window.

Check the box of “empty cells” and press ОК.

There will be selected all empty cells from the first column.

Now delete the lines where the empty cells are selected. Press.

Now the data are ready for analysis and diagrams’ plotting.

Please note! The first lines contain identical data because the OrbiCraft began rotation with delay of 5 seconds. Insert into document the point diagram with smooth curves and press the button for choosing data.

Press the button for adding data.

Do not enter the name of the row. Click “Х values” window and choose all cells from the first column by clicking at column’s header.

Then click “Y values”, delete “{1}” characters to leave equal mark only, and then choose the whole second column with data by clicking on the column header.

At the data source choose window there will be displayed the first data row.

Press ОК to close data choose window. Put the cursor to the top right corner of the second data column and drag it right to seize all data.

Select the columns from В to I.

Now the diagram will include all data collected from solar sensors. Please note the “outbreaks” at the diagram; it is measuring errors that are perfectly visible at the diagram.

Put the cursor at the “outbreak” and find the line with erroneous data in the table.

Manually change the incorrect values, changing them to the averages of the adjusting values.

The diagram plotted by the cleared data must be approximately like the one below:

Please note! The first lines contain identical data; it is evident from the diagram.

## Data preparation

All decimal values in our table are pinned down to the left borders of the cells; it means that Excel interprets them as text data. For sure - the Excel uses for decimal numbers not a dot but comma character that divides the integer from its decimal part. We must replace all dots to commas in our file. To do so press Ctrl-H and enter in the upper window the dot mark, and in the lower window enter the comma mark.

Scroll down the table and memorize the last value of the measured angle.

Delete all data at the start of the table up to this angle.

Now the data will start and end from approximately the same angle; hence the OrbiCraft performed one revolution around its radial axis. Taking into account that magnetometer measures angles with some error, its readings is inadvisable base for positioning by solar sensors. Since OrbiCraft during data collection rotated equably we can calculate the precision angles. Count the overall number of the data lines (I personally counted 441 lines).

This number may be some less or some more depending on OrbiCraft rotation speed. Since the circle usually divided to 360 degrees, the angle value at every line of our table must differ from previous value for 360/441= 0,816327 degree. At the upper cell of the К column (К1 cell) copy the angle value from J1 cell. At К2 cell enter this formula:

Double-click lower-right corner of the К2 cell, the formula will be copied to all К column cells.

Now all angle values must be normalized to the range from -180 to 180 degrees. Find the line where angle value is more than 180 degree; change the formula in this line.

Formulas in the other cells must not be changed. Now all angle values in the table are in range from от -180 to 180.

For visual estimation of magnetometer error plot the diagram by outer right columns with angle values.

The diagram clearly shows that even the calibrated magnetometer is in error during angle measurement. Now we must delete all redundant data from the table. Copy К column and insert the data as values to L column. Now delete the J and K columns, also the first А column, with values of measurement time. Although in Excel the integer part divided from decimal by comma, the Python uses for this a dot. Using replace function (Ctrl-H) replaces all commas to dots in the file.

Now transfer all processed data back to Notepad++. Select all data in Excel and press Ctrl-C. Open Notepad++, create new project and insert the data using Ctrl-V.

Data inserted are divided by tabulation marks and line hyphen characters, but we need the division by Python language rules – division by comma and space characters. Press Ctrl-H, enter \t to the upper window, enter comma with space character to the lower window. Press “Replace all” or “Replace in all open documents”.

Now we will replace the line hyphen characters in similar manner. Press Ctrl-H, enter \r\n to the upper window, enter comma with space character to the lower window. Press “Replace all” or “Replace in all open documents”.

Now all data are listed as one line. We will create the array based on this line. Save the data file in .txt form.