When it comes to analysing soil data there are a myriad of tools, which can make things confusing. Most Agri-tech software products you can get will come with a level of analysis for your soil results. But for those out there without access to these, it’s simple and easy enough to do it for free. All you need is either:
- Microsoft Excel (if you already have Microsoft Office) or;
- Google Sheets (completely free, just sign up to google.com/drive or use your Gmail account if you have one).
- A hypothesis (ie know what you’re looking for)
In this post I’m going to talk about how to quickly convert PDF soil tests to a spreadsheet and format them for analysis.
Step 1. Load up your results.
Open up Microsoft Excel, or Google Sheets. I’m going to assume you’ve used a spreadsheet before, so won’t go into this in detail. However if you need some assistance, here are some great sites that dive a bit deeper into how to use them here (for excel) or here (for google sheets)
In order to get your results in PDF format, there are a couple of tools you can use to get this automatically converted to Excel (.XLS) format.
- Acrobat PDF converter (costs $, but comes with free 7 day trial)
- PDF wondershare (free, but requires download)
I found the freepdfconvert the best although it limits you to a max of 2 pages and 5 documents.
You should load up as many results as you want to compare – generally best to start with all previous soil test results from the same field or area. Once you’ve analysed one field, you should move on to the next rather than trying to do everything at once.
If you’ve converted all your results to a spreadsheet, now it’s time to combine them all and clean your data.
Step 2.
Combine+Clean your data. Cleaning is always a big one for those who work with data, as without it you end up with weird anomalies that screw up your results. The idea is to make sure all the numbers are in the correct format, as are dates, and they’re line up nicely in a row and column format.
If you’ve had to copy-paste results into excel, or used one of the programs I mentioned above, they probably look like this:
This is OK, but it’s not great for analysing data over the course of time. Instead, you’ll want to use the ‘transpose’ function to lay them out horizontally which makes them much easier to order by date. Particularly if you’re comparing results for a single field or want to chart them later on.
To do this;
1. In excel or Google sheets, highlight the two columns of data you’d like to transpose (use ctrl + click to select columns that aren’t next to one another).
2. Find an area or sheet you’d like to transpose them to.
3. In Excel, go to edit – paste and select ‘paste special’ then select transpose. In Google, right click where you’d like them and select ‘Paste Special’ and then ‘Paste Transpose’.
Your data should now be formatted like:
You’ll also need to remove all the blank spaces between cells, to make sure your columns and rows are readable by the chart. You can see in mine I’ve removed the blank spaces in between the rows to show only my results.
Next we can add an extra column on the far left to show the date this data was collected:
Dates are particularly important when it comes to displaying the chart, so make sure they are all shown (formatted) the same way. This could be – DD/MM/YY or DD/MM/YYYY, just as long as excel or google is reading the values down that column the same. Otherwise it won’t chart.
Lastly I’ve added all my other results into the table. That way I can easily chart and compare the numbers, which I’ll show how to do in the next post.
