Author name: cndro

Blogs

How to Normalize Data Point Axis in Tableau

 In this article, I will show you how to normalize data point axis in Tableau. Recently, I got a request to build a Panel chart of all the measures in a data set and I came up with something below (I built this with a sample superstore);  You can see above that the discount and Quantity line chart looks flat and this was because they share the same axis with Units and DV that have pretty big axes. This is not too good because it is very difficult to see trends in both Discount and Quantity.  Request: Plot each measure to clearly show their trend.  Proposed Solution: I came up with the mathematical solution below;   Pivot all the measure fields, this is done to have all the measures plotted in one panel  Pick up each data point per date (in my case monthly)  Pick up the Maximum value of all the Measure field  Sum the measure field per pivot field (Measure name)   Mathematical expression;   Normalize X = (Xi * MAX (Xi-n)) /∑X (i-n)t  Where Xi == b  MAX (Xi-n) == c  ∑X (i-n)t ==d  Note that I came up with this formula and it is not a standard formula for Normalizing data  Tableau Desktop  I will not go into the details of how to create a panel chart in Tableau. This is how my data looks like after pivoting;  Pick each data point tagged ‘Data point’ with the formula below;    { FIXED [Pivot Field Names],DATETRUNC(‘month’,[Weekly Start Date]):SUM([Pivot Field Values])}  2. Find Maximum value from all the data point, tag this ‘Max’  {MAX( { FIXED [Pivot Field Names],DATETRUNC(‘month’,[Weekly Start Date]):SUM([Pivot Field Values])})}  3. Estimate sum Pivot value per Pivot Name, tagged ‘Sum of Metric’;    { FIXED [Pivot Field Names]:SUM([Pivot Field Values])}  4. Finally, compute the Normalized value for each data point using the formula provided above;                 ([Data point] * [maximum])/[sum per metrics]    Below is how the panel chart looks after normalizing both the Discount and Quantity line trend. Check out the dashboard on Tableau Public. Link to the dashboard  https://public.tableau.com/app/profile/kayode.ajewole7777/viz/PanelChart_16390261051060/AllModel?publish=yes

Blogs

Python API Tutorials| Getting Started with API’s

API is the acronym for Application Programming Interface, which is a software middleman that allows two applications to interact with each other. Each time we use an application like Facebook, send an instant message or check the weather on your phone and any other apps, you’re using an API.  Another example of an API is when you use an application on your mobile phone. The application connects to the Internet and sends data to a particular server. The server will redeem that data, demystify it, and send it back to your phone. The application then interprets that data and presents you with the information you need in a readable way .In this article, you will learn the basics of Python API and the step-by-step process to get started with APIs. How to Get Started with Python APIs To use an API, you have two options. The first option is to authenticate and the second option is not to authenticate and it depends on the kind of API you’re working with. We will demonstrate with two different examples. For us to get started with getting data or pulling data via the API, we will use a particular library in Python known as the Requests Library. This library is a standard package for making HTTP Requests in Python. There are other libraries for making HTTP Requests such as httplib, urllib, httplib2, treq. We can install Requests using the command; pip install requests conda install requests We also have different standard HTTP Methods;GET; to request data from the server. POST; to submit data to be processed to the server. PUT: Update an API resource DELETE: Delete an API resource A request is also made up of different parts: The method – these are the methods which we just discussed now and theyare the GET, POST, PUT and DELETE The Endpoint – when sending a request to the server, we have an endpoint known as the URL which we used for communicating to that server. The headers – The headers in a request are used to provide information about the request context so the server can know how to tailor our response such as the content type = application/JSON, authentication keys, and so on. The data/body – The data/body is usually used in both POST and PUT, with this, we can parse in the credentials we want to use to authenticate with the server. We must also not forget the status code returned after we sent our request. This status code helps in knowing if our request was successful or not. Examples of these different status codes are; 200(Ok), 201(Created), 404(Not found), 401(Unauthorized), 500(Internal Server Error) etc.   Getting the Data We’ll demo this with two different options we mentioned earlier which is getting data with Authentication or not, we will use these two APIs to implement this: IPify – a free API that allows you to get your current IP address(No Authentication) Google Calendar – An API used for tracking events and more(With Authentication) No Authentication We will use Ipify API with our Requests library to get data. With this API, we will observe that we don’t need any credentials to perform authentication before we can use the API.  The reason for this is that the API is an open one which makes it not require authentication. Sample API Request: #import the request library import requests #we then use the GET methods along with the requests library to retrieve data from the ipify server data= requests.get(‘https://api.ipify.org?format=json’) #we now convert our response data to json json_response=data.json() #we also extract our values we want it to return back to us. my_ip=json_response[‘ip’] #Then print our result print(my_ip) With Authentication To implement this, we will use the Google Calendar API. To get started, we need to create a new project under the google developer account by using this URL https://console.cloud.google.com and then set our details under Oauth Consent screen and Credentials tab. After you have completed the whole setup, we will download the credentials.json from the console page and use that to authenticate with the server. We will write a code to retrieve our calendar events after we have gained access to the API. Sample code #import the libraries needed from __future__ import print_function import datetime import os.path from googleapiclient.discovery import build from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request from google.oauth2.credentials import Credentials # If modifying these scopes, delete the file token.json. SCOPES = [‘https://www.googleapis.com/auth/calendar.readonly’,’https://www.googleapis.com/auth/calendar.readonly’,’https://www.googleapis.com/auth/calendar’,’https://www.googleapis.com/auth/calendar.events.readonly’,’https://www.googleapis.com/auth/calendar.events’] #we create an empty list all_events=[] #we create a function main which has all our functionality def main(): creds=None #this if statement checks if we have a token.json file in our working path if os.path.exists(‘token.json’): #when token.json exist, it execute function to recognize/authorize this user creds = Credentials.from_authorized_user_file(‘token.json’, SCOPES) #this is another if statement which checks if we don’t have the credentials or the credentials has expired or not valid if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) #this else is to perform an authentication with the server using our credentials.json file and the SCOPES we specified else: flow = InstalledAppFlow.from_client_secrets_file(‘credentials.json’, SCOPES) creds= flow.run_local_server(port=0) #After authenticating, it writes out our result which contains an access token and other details which we can use often in our project with open(‘token.json’, ‘w’) as token: token.write(creds.to_json()) #we then read in the token file which has the access token and use this with the build method to access our calendar events service = build(‘calendar’, ‘v3’, credentials=creds) now = datetime.datetime.utcnow().isoformat() + ‘Z’ #we use the service method to check our events using our primary calendarId events = service.events().list(calendarId=’primary’, timeMin=now, singleEvents=True, orderBy=’startTime’).execute() all_events.append(events) print(all_events) if __name__ == ‘__main__’: main()

Uncategorized

Data Extraction from Wix API

The Wix Data API allows you to work with your database collections using code and gives you more capabilities than what is available in the Editor alone. You can use this API to work with your site’s registered users. This API lets you get information about the current user, prompt a user to log in, and can log out the current user. The Wix-users API can be used in your site’s front-end code. Here is the list of the processes we would follow in this article to get data from the Wix API: Create an application under the Wix developer center Set up permission and derive the token, authorization, code, access token, and refresh token Get the data you need   Create an application under the Wix Developer center Step 1: To get started with the API, we need to first go to the website https://dev.wix.com/ and create a new application via the developer center for our Wix account. Afterward, we would set our Redirect URL & App URL on the OAuth tab. Step 2: The next step is to add new permission under the permissions tab, where we select the kind of permission we need from the different categories (Wix Stores, Pricing Plans, Marketing, Wix Events, Contacts & Members, etc.). Any of these permissions we set, actually indicates the type of data we are interested in. Step 3: After this has been done, we will test our app. It will prompt us to install the app and redirect us to the App URL we defined. From here we will generate a token code under the URL params returned. We’ll then proceed to authenticate by using the token code we obtained alongside other credentials available.   For Authentication In getting the authorization code and access token, we can use the Flask framework in form of a web application to generate the details we need. We’ll use the authentication URL endpoint with the token code, appId, and Redirect URL as a connection string. When the flask app has been ran, it will generate our authorization code.     from flask import Flask,redirect,url_for     import config import requests app = Flask(__name__) app.config.from_pyfile(“config.py”, silent=True) import json @app.route(‘/callback’) def oauth_callback(): redirect_url = ‘https://cndro.com/’ auth_url = (‘https://www.wix.com/installer/install?’ + ‘token=’ +app.config[“TOKEN_CODE”]+ ‘&appId=’ +app.config[“APP_ID”]+ ‘&redirectUrl=’ + redirect_url) return redirect(auth_url)   Generate Access Token and Refresh TokenTo generate the Access token and Refresh token, we’ll use the temporary authorization code returned, together with the URL endpoint and other credentials to request an access token and a refresh token.   @app.route(‘/access’) def wixAccessToken(): the_url = ‘ https://www.wix.com/oauth/access’ response = requests.post(the_url, headers = { ‘Content-Type’: ‘application/json’ }, data = { ‘grant_type’ : ‘authorization_code’, “client_id”: app.config[“APP_ID”], “client_secret”: app.config[“APP_SECRET”], “code”: app.config[“AUTH_CODE”] }) response_data = json.loads(response.text) return response_data   We must note that this access token expires in 5 minutes, because of this we must generate a new access token from the refresh token obtained. We’ll use the same URL endpoint we used earlier and also indicate our grant type as ‘refresh_token’.   @app.route(‘/refresh’) def NewAccessToken(): the_url = ‘ https://www.wix.com/oauth/access’ response = requests.post(the_url, headers = { ‘Content-Type’: ‘application/json’ }, data = { ‘grant_type’ : ‘refresh_token’, “client_id”: app.config[“APP_ID”], “client_secret”: app.config[“APP_SECRET”], “refresh_token”: app.config[“REFRESH_TOKEN”] }) response_data = json.loads(response.text) return response_data if __name__ == ‘__main__’: app.run(host=’localhost’,port=5000)   We can get data from any of the permissions categories we added by using its URL endpoint and with our Access token. You can as well use other HTTP Methods apart from GET such as POST, GET, PATCH, DELETE with any of the endpoints you’re interested in working with. An example code to get data from the Contact & Members Endpoint def getListContact(): the_url = ‘ https://www.wixapis.com/contacts/v4/contacts ? paging.limit=1000&paging.offset=1000’ response = requests.get(the_url, headers = { ‘Authorization’: wixAccessToken() }) response_data = json.loads(response.text) return response_data print(getListContact())   Follow the steps above and you will have your data extracted from Wix API. If you enjoyed this post, share it with your friends and colleagues. Connect with us on Twitter, Instagram, and YouTube. Thanks and see you next time

Uncategorized

How to Connect to Azure Blob from Alteryx

Azure blob storage is a Microsoft Azure feature that allows users to store a large amount of unstructured data. Azure blob storage can be used as a Delta Lake where you keep all the data that belongs to your organization. Blob is an acronym and it stands for Binary Large Object which includes images and multimedia files. In this article, I will show you how to connect to Azure Blob storage from Alteryx. Firstly, let’s connect with the files in the Azure container from Alteryx designer but before we can do that, we need to generate a Shared Access Signature (SAS) code for each file we would like to connect to. We are going to use wix.csv data as sample data. We are going to follow these steps below; Go to https://portal.azure.com and search for Azure Storage. Create a container (I named it alteryxtestcsvfiles) on the Blob section. Click on the three ellipses to the right of the window.     2. Click on Generate SAS     3. Adjust the expiry date to a future date   4. Click on Generate SAS token and URL     Successfully followed the four steps? Good job! Now use the SAS Code you generated to connect to the data from Alteryx download tool. With the SAS Code, we can now communicate with the resources privately. . . https://samplestorageyoucreated.blob.core.windows.net/alteryxtestcsvfiles/yourdata.csv?sp=r&st=2021-07-01T18:29:53Z&se=2021-07-06T02:29:53Z&spr=https&sv=2020-02-10&sr=b&sig=SsCdhhH%2FZG6qgeCEyAzvsuj0aoIZrS2qyhwyPenlsrk%3D The SAS code is split into two parts, we have Field1 and Field2. Field1 = https://samplestorageyoucreated.blob.core.windows.net/alteryxtestcsvfiles/yourdata.csv Field2 = sp=r&st=2021–07–01T18:29:53Z&se=2021–07–06T02:29:53Z&spr=https&sv=2020–02–10&sr=b&sig=SsCdhhH%2FZG6qgeCEyAzvsuj0aoIZrS2qyhwyPenlsrk%3D The view below shows the complete Alteryx workflow to get our CSV data, transform the data and use the browse tool to show the CSV data.       The Azure Blob Storage URL and the query string serve as the input to the workflow. Use the input tool as shown in the screenshot below.     Now we will concentrate on the Alteryx download tool, the configuration interface. I brought the Alteryx Download tool into the workflow. Take a look at the configuration window for the basic settings of the Download tool.     The Headers window has Accept: text/plain, and also has the field1 selected as the field to take care of the response body.     The screenshot below shows using the Payload Configuration tab to get and use the query string for authentication.   The Text to Columns tools (the first and the Second) help to get the text data into their table equivalence.     Use the Select Tool to pick the Fields needed for the Dataframe by deselecting unwanted fields in the configuration window.       I use the Dynamic Renaming tool to pick up the field name of a dataset from the first row of the data.       We have successfully connected to the CSV data in Azure Blob with Alteryx with the above steps. If you enjoyed this article, share it with your friends and colleagues!    

Uncategorized

Convert CSV to XML in Alteryx

Extensible Markup Language(XML) is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable. XML uses a series of simple tags to describe data and these tags can be created in an easier way. CSV is a simple file format used to store tabular data such as a database or spreadsheet. A CSV file stores data in a structure that can be read and written by a wide range of several spreadsheet software packages. In this article, I’ll walk you through how to convert CSV to XML in Alteryx. Let’s get right in. We will use some tools in Alteryx to achieve our aim. The first tool we will be using in this conversion is the input tool which shows an input file. This is where we will pass in our sample data. The second tool is the Record ID which will be used to number the data sequentially. We can call this an index. The third tool is the Transpose which we will use to change the data orientation from vertical to horizontal. Here is the result of this phase:   Add regex to remove spaces in the Name column using the Formula tool. After then, use the select tool to pick the fields and rename the datatype from string to integer. We will use the formula tool again to perform two different calculations for the name tag. Afterward, add both name tags and re-index using the Record ID tool. Use the text input tool to add xml initiation line such as this; <?xml version=”1.0″ encoding=”UTF-8″?>,and it will be passed to the data. The union tool will then be parsed in to connect different data coming from the workflow. The summation tool coming from the Record ID tool will be used to group the data using the name column.       The Append Field tool as shown below will be used to append some of the fields i.e. ColumnNumber to each of the fields(XmlLine, RecordId, Level). The other tools used include the Multi-Row Formula tool, the formula tool, and then parsing the join, union tool and summarizing them to obtain our XML file.       We’ll then use the output tool in viewing our results.  

Uncategorized

Connect CSV Data in S3 Bucket with Athena Database

Introduction Athena is a serverless query service that allows you to run SQL queries on your data stored in S3. It can easily query large datasets (or Data Lakes), whether they are structured, unstructured, or semi-structured data. A database in Athena is a logical grouping for tables you create in it. In this short guide, you will learn how to connect to CSV data in S3 bucket with Athena database. Let’s dive right in. The AWS glue crawler will help to catalog the table and give it the right schema. The steps are highlighted below.  Step 1: Open the Athena database from your AWS console and toggle to the database you want to create the table on. Click on Create table.   Step 2: Click on “from AWS Glue Crawler”. The “from S3 bucket data” is the manual process where you will enter the data columns manually. After you have clicked on the from AWS Glue Crawler you will be transferred to the AWS Glue platform for the crawler definition. Step 3: Input the name of the crawler and press the Next button. Step 4: Leave every entry at the default and enter the S3 bucket directory where the CSV file is located. Ensure you add a forward slash after the directory definition. Step 5: Choose “No” for Add another data store. Step 6: On the IAM role section, make sure you create a new role for the crawler and press the “Next” button. Step 7: After you have created the crawler, locate the name of the crawler. Select the crawler and click on run Crawler. The crawler will then create a table from the CSV file in the S3 bucket.   NOTE: A comma (,) at any point in a row of a CSV in the s3 bucket will surely disrupt the Athena data table, so make sure you replace any comma symbol in all rows of the CSV files. As a culture, I like to replace comma symbols with spaces. In this way, Athena will not return a bad table and will not split up any rows using the comma. Below is the original data from EXCEL.   The picture below shows the CSV file after being ingested from the S3 bucket to Athena Table. As you can see the data was returned as unstructured data in the Athena database. Resolution: Replace comma (,) symbols in all rows of the CSV files, then bring the data back into the S3 bucket. Then this will be perfectly provision by Athena Database because of the absence of comma symbols. That’s pretty much it. Follow the step-by-step guide to connect your CSV data in S3 to Athena database. Share your thoughts in the comment box below.

Scroll to Top