This tutorial explain how to open the CSV result data from PlantEye in Excel. Although excel is definitely not the right tool to properly analyze such amounts of data, it can be a good way of getting a first hands on feeling for the data. This tutorial will show you how to read the file, add some helpful columns and finally how to load and visualize it using a Pivot table.
Opening the file
If you have a result file on your computer that is stored as a csv simply open it in Excel. Often, the CSV format is not directly recognized by excel which can result in a spreadsheet that looks like this
All data is squeezed in the first column because Excel didn't know how to properly parse the file. Often the character that is used to separate the Character Separated Values (CSV) depend on the operating system and also on the language and regional setting. To split the result into multiple columns you can use the Text to columns function that you can find in the Data Tab of Excel. A more detailed explanation for this can be found here. The result should look like this:
Additional timestamp column
It can happen that the Measurement timestamp in Column A is not properly detected by Excel and it shows in a format like 20170426T151937. In this case you can try an easy workaround to restore the detailed time information from that string. Simply append a new column at the end and name it Timestamp Corrected. In the first data row of this column enter the formula
It should transform the string in Column A into a date that can be read by Excel. When the formula works, auto complete it into the entire column by double clicking the small square in the bottom right corner of the cell. By default Excel will most likely hide the time of this new timestamp and only show the date. You can change that by applying a user defined format.
Creating a unique Label
As can be seen in the data above each of my measurements timestamps contains 12 plants, divided into 4 X-sectors and 3 Y-sectors. It can be helpful when using Pivot Tables to have the X and Y sector in a common unique Label for each plant. to create such a label simply add a new column and name it Label. Go into the first data row and enter the formula
This will put together the values of Column B and C and split them using a "-". When the formula works, auto complete it into the entire column by double clicking the small square in the bottom right corner of the cell. Your result should somehow like this.
Creating a Pivot Table
With the data edited like this we are ready to create a pivot table. Simply select all columns of your table, go to the Insert Tab of Excel and click on the first button PivotTable. Insert the Pivot Table on a new table. You should end up with a new table with your pivot table on the left and the fields of the Pivot table on the right.
To generate a first plot from the pivot table go to the fields on the right hand side of your screen. You can see the new two columns we created Timestamp corrected and Label at the bottom of the list. Drag the timestamp corrected into the Rows of your pivot table field list at the bottom right corner of the screen. You will see that directly the data is sown the the table on the left. Excel will group time values by the date. If you click on the + in front of the date it will expand the data into hourly values.
To get a first feeling for the data simply a value like Height in the Value field of the pivot table. By default excel will no summarize the values by their amount per timestamp. To change that click on the value you dropped in and go to value settings and summarize this value by Average. The result will be a pivot table that shows the average height for all sectors split by the time.
Creating a first Chart
When you have achieved this you can simply create a PivotChart. Simply click on the pivot table and go to the analysis tab. On the right hand side click on the PivotChart button and select the line chart. The result could look something like this:
Splitting by Label
The chart above represents the data for all plants in the file. We further want to split this now by the label column we created. To do that, simply drag and drop the Label in the pivot field settings to the Legend (column) field. You will see how your plot from before will be changed into a multi-line plot with one line for each label or plant.
With the plus and minus buttons on the bottom right side of the chart you can change the resolution of the time and display individual values per hour or summarize them by day.