Hello!! this Friday project was quite intense. Each of us were given a different task involving APIs&Web scrapping, in my case I had to use the OpenWeatherMap API (https://openweathermap.org/price#weather).
In this post I will guide you through the steps I followed to get the data using Alteryx.
First thing worth noting is that, in order to get the API key, you have to create an account. Also, in the free version there are limited options, in my case I used "Historical weather 5 days"
The structure of the API call is shown is the image below and involves 3 parameters and the API key.
As the Date is required in UNIX time I used this website to convert the dates in that format: https://www.unixtimestamp.com/
At this point I decided the purpose of my project: To compare the weather in last 5 days of both London and Córdoba (My hometown, in the south of Spain ☀) so I needed the latitude and longitude of both locations for my API call.
This is my flow in Alteryx:
Both flows are identical, one flow for each city, and then I joined them with the union to have them together. So for simplicity I will just explain one stream.
- Download data and parse Json:
At this stage I divided this into two streams: on the one hand the first rows with latitude longitude, timezone and current data, on the other hand data that contains "hourly", which is the hourly data from the date specified in the API call.
2. Hourly stream data:
- Split to columns: 3 columns using a dot as a separator.
- Rename fields with the Select Tool: Json_name_2 as Row ID and Json_name_3 as Field Name. (This will be handy for the cross tab configuration).
- Use the formula tool to fix any mismatch or error in the data.
- Cross tab: to have each column as a different field.
- Use a formula tool to convert dt (the field that contains the date, which is in Unix) to a normal Date format.
- Use a select to rename fields and change data types.
This is the final data from this stream:
3. Location data.
- Sample tool to have the first two rows (latitude and longitude).
- Cross tab to have the values in the same row (we need this configuration in order to create the spatial points).
- Create spatial points.
- Create the field city with the name of the location.
4. Join hourly data and location data
the common field in each of the stream is the URL and Day (see parsed data image in the step 1)
- Join data using the common fields.
5. Repeat this steps for different locations and union them using the union tool.
6. Output the data and create a Dashboard.
I was running out time so i was not able to create something very cool but this is what I managed to do in the time we were given:
I hope you find this useful and don't hesitate to contact me via Twitter or Linkedin if you have any question :))
