pythonrexcelcsvgis
How to convert gridded csv temperature data (by lat/long) to a raster map?
I've downloaded an average temperature change dataset formatted like this but with lat/long range across the entire US: original csv
I'm trying to convert it into a raster that I can visualize in a python or R map, and all methods I've seen require the lat, long and z fields to be tabular like this: ideal table
Is there a way to do this with the current "grid" format or do I need to transform it into a table? If the latter how can I do that in Excel or python/R?
Tried transposing data in Excel first, at a loss for other methods
Solution
Please include sample code/sample data when you ask a question here. Your data set pictured in the PNG was small enough, so I recreated it:
+----------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| Lat/Long | -179.5 | -179 | -178.5 | -178 | -177.5 | -177 | -176.5 | -176 | -175.5 | -175 |
+----------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| 18.5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 19 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 19.5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 20 | 0 | 1.524704 | 1.489677 | 1.488556 | 1.485161 | 0 | 0 | 0 | 0 | 0 |
| 20.5 | 0 | 1.484848 | 1.484863 | 1.484833 | 1.484802 | 1.516785 | 1.554611 | 1.5672 | 1.567184 | 0 |
| 21 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 21.5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 22 | 0 | 0 | 0 | 0 | 0 | 0 | 1.586227 | 0 | 0 | 0 |
| 23 | 0 | 0 | 2.718926 | 2.743782 | 2.74353 | 0 | 1.64222 | 1.661705 | 1.720245 | 1.755074 |
| 23.5 | 0 | 0 | 0 | 3.006203 | 3.005981 | 0 | 0 | 0 | 0 | 1.808762 |
+----------+--------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
A problem like this would be better solved/dealt with in Numpy/Python, but if you want to do it in Excel, here are the steps I took to arrive at the end result posted below. I am assuming you are using Excel 365 on a Windows 10 PC. I am also assuming that you need help with the data set, not with the raster map itself.
- The first problem you have is that there are blanks in your data where zeros should be. I have no idea how big this table is - probably really big - so if you want to do this in Excel, select the first cell in A1, then the last cell in column K while holding the SHIFT key. Click CTRL + H, which brings up the "Find & Replace" dialog. Replace all of the spaces with "0".
- Format your data as a table in Excel by clicking within the range, and then on the Home tab "Format as Table" in the "Styles" group. The style you pick does not matter. I named the table "Original" (select a cell in the table, then click on the "Table Design" tab which appears in the top right; change the table name in the ribbon under "Properties" on the left).
- Click on the "Data" tab while the table is still selected, then select "From Table/Range" in the "Get & Transform Data". This will open Power Pivot. Since you don't want to output this table again from the query, click on the arrow (NOT the button) next to "Close & Load" on the ribbon under "Close" and pick "Close & load to". This brings up a dialog box. Select "Only Create Connection" and then click "OK". If you accidentally hit the button itself, it will create a table on a new worksheet that is identical to the one you started with. You can delete the sheet later, which will convert the output of the query to a connection.
- In the data tab, click on "Queries & Connections" in the "Queries & Connections" group. This brings up a sidebar on the right. Double-click the query you just created, which gets you back to Power Query:
- I duplicated the original query, because we want to manipulate it further (right-click on the query in the left pane, then select "Duplicate"). Name the query something specific. I picked "Unpivoted".
- Select the first column that contains the Latitude values. Then click on the arrow next to "Unpivot Columns" on the "Transform" tab and select "Unpivot Other Columns":
- As a final step, I renamed the resulting columns "Latitude", "Longitude" and "Temperature", then clicked "Close & Load" to put the table onto its own worksheet.
Here is the resulting data set:
+----------+-----------+-------------+
| Latitude | Longitude | Temperature |
+----------+-----------+-------------+
| 18.5 | -179.5 | 0 |
| 18.5 | -179 | 0 |
| 18.5 | -178.5 | 0 |
| 18.5 | -178 | 0 |
| 18.5 | -177.5 | 0 |
| 18.5 | -177 | 0 |
| 18.5 | -176.5 | 0 |
| 18.5 | -176 | 0 |
| 18.5 | -175.5 | 0 |
| 18.5 | -175 | 0 |
| 19 | -179.5 | 0 |
| 19 | -179 | 0 |
| 19 | -178.5 | 0 |
| 19 | -178 | 0 |
| 19 | -177.5 | 0 |
| 19 | -177 | 0 |
| 19 | -176.5 | 0 |
| 19 | -176 | 0 |
| 19 | -175.5 | 0 |
| 19 | -175 | 0 |
| 19.5 | -179.5 | 0 |
| 19.5 | -179 | 0 |
| 19.5 | -178.5 | 0 |
| 19.5 | -178 | 0 |
| 19.5 | -177.5 | 0 |
| 19.5 | -177 | 0 |
| 19.5 | -176.5 | 0 |
| 19.5 | -176 | 0 |
| 19.5 | -175.5 | 0 |
| 19.5 | -175 | 0 |
| 20 | -179.5 | 0 |
| 20 | -179 | 1.524704 |
| 20 | -178.5 | 1.489677 |
| 20 | -178 | 1.488556 |
| 20 | -177.5 | 1.485161 |
| 20 | -177 | 0 |
| 20 | -176.5 | 0 |
| 20 | -176 | 0 |
| 20 | -175.5 | 0 |
| 20 | -175 | 0 |
| 20.5 | -179.5 | 0 |
| 20.5 | -179 | 1.484848 |
| 20.5 | -178.5 | 1.484863 |
| 20.5 | -178 | 1.484833 |
| 20.5 | -177.5 | 1.484802 |
| 20.5 | -177 | 1.516785 |
| 20.5 | -176.5 | 1.554611 |
| 20.5 | -176 | 1.5672 |
| 20.5 | -175.5 | 1.567184 |
| 20.5 | -175 | 0 |
| 21 | -179.5 | 0 |
| 21 | -179 | 0 |
| 21 | -178.5 | 0 |
| 21 | -178 | 0 |
| 21 | -177.5 | 0 |
| 21 | -177 | 0 |
| 21 | -176.5 | 0 |
| 21 | -176 | 0 |
| 21 | -175.5 | 0 |
| 21 | -175 | 0 |
| 21.5 | -179.5 | 0 |
| 21.5 | -179 | 0 |
| 21.5 | -178.5 | 0 |
| 21.5 | -178 | 0 |
| 21.5 | -177.5 | 0 |
| 21.5 | -177 | 0 |
| 21.5 | -176.5 | 0 |
| 21.5 | -176 | 0 |
| 21.5 | -175.5 | 0 |
| 21.5 | -175 | 0 |
| 22 | -179.5 | 0 |
| 22 | -179 | 0 |
| 22 | -178.5 | 0 |
| 22 | -178 | 0 |
| 22 | -177.5 | 0 |
| 22 | -177 | 0 |
| 22 | -176.5 | 1.586227 |
| 22 | -176 | 0 |
| 22 | -175.5 | 0 |
| 22 | -175 | 0 |
| 23 | -179.5 | 0 |
| 23 | -179 | 0 |
| 23 | -178.5 | 2.718926 |
| 23 | -178 | 2.743782 |
| 23 | -177.5 | 2.74353 |
| 23 | -177 | 0 |
| 23 | -176.5 | 1.64222 |
| 23 | -176 | 1.661705 |
| 23 | -175.5 | 1.720245 |
| 23 | -175 | 1.755074 |
| 23.5 | -179.5 | 0 |
| 23.5 | -179 | 0 |
| 23.5 | -178.5 | 0 |
| 23.5 | -178 | 3.006203 |
| 23.5 | -177.5 | 3.005981 |
| 23.5 | -177 | 0 |
| 23.5 | -176.5 | 0 |
| 23.5 | -176 | 0 |
| 23.5 | -175.5 | 0 |
| 23.5 | -175 | 1.808762 |
+----------+-----------+-------------+
And this is the underlying M code:
let
Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Lat/Long", type number}, {"-179.5", type any}, {"-179", type number}, {"-178.5", type number}, {"-178", type number}, {"-177.5", type number}, {"-177", type number}, {"-176.5", type number}, {"-176", type number}, {"-175.5", type number}, {"-175", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Lat/Long"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Lat/Long", "Latitude"}, {"Attribute", "Longitude"}, {"Value", "Temperature"}})
in
#"Renamed Columns"
I hope this is what you are looking for. Please click the check box by this answer to accept it if this solved your problem.
- Webscraping Roblox
- Remove the mandatory field label 'This field is required.' and fix the bug with 'clean_email'
- How to plot a Probability Density Function in Python?
- How large is a fresh install of Python?
- Appending new elements into an empty list
- Simple way to measure cell execution time in ipython notebook
- PyAudio working, but spits out error messages each time
- Reportlab show page number and page count IF there is more than one page in a document
- How to read SharePoint Online (Office365) Excel files into Python specifically pandas with Work or School Account?
- How to set a column which suffix name is based on a value in another column
- Debugging Python C++ extension from Visual Studio Code on Linux
- How can I get all users on Google admin_sdk?
- csv.Error: iterator should return strings, not bytes
- How to check if an object has an attribute?
- How to use selenium with proxy auth in headless mode?
- Is there a way to exit a pytest test and continue to the next one?
- Returning the lowest index for the first non whitespace character in a string in Python
- Formatting exceptions as Python does
- Prime factorization using list comprehension in Python
- Why does the power spectrum E(k) of my velocity field follow 𝑘 ^(−(n−1)) instead of 𝑘^(−n)?
- How to merge dataframes over multiple columns and split rows?
- How to create a Sympy IndexedBase using a custom subclass of Symbol?
- Removing dynamically an element from a list
- Returning boolean if set is empty
- Can variables be decorated?
- Fast(est) exponentiation of numpy 3D matrix
- Removing an element from a list based on a condition
- Printing elements of dictionary line by line
- Matplotlib does not display the hatch of a patch in a legend
- Python win32com - Class not registered error