Converting data stored in Excel (grid/matrix) to a raster?

Question

I have some grid data that is stored in a xls file. The rows are latitude and the columns are longitude values in decimal degrees (see image).

What would be a quick and efficient way to convert these data to a raster with 2° grid cells? I am using ArcMap 10.2 but also have access/experience with R or QGIS.

Answer 1

There's no need for any fancy tools here at all. Just a decent text-editor (notepad will do if necessary) will let you turn it into a ASCII text file in a few easy steps.

Process

1. Export your data as a CSV file (Excel/LibreOffice). (If you can export it with space as the delimiter, skip step 4). This gives me for my subset of 6 squares:

lat\lon,100,102,104 0,0.345,0.297,0.331 2,0.426,0.433,0.502

2. Now, replace the top row of the file with the below.:

ncols 3 nrows 2 xllcorner 100.0 yllcorner 0.0 cellsize 2.0 NODATA_value -9999

3. You will need to change ncols to be the number of columns. Similarly change nrows to the number of data rows.

4. If you exported with commas as the delimiter (as in my example) use search and replace to replace all comma's with spaces.

Your final file should look like this:

ncols 3 nrows 2 xllcorner 100.0 yllcorner 0.0 cellsize 2.0 NODATA_value -9999 0.345 0.297 0.331 0.426 0.433 0.502

5. Save it as My_raster.asc (or whatever) and load into your GIS of choice.

Output