How to Visualize ADP Data in Python with pandas

The rich ecosystem of Python modules lets you get to work quickly and integrate your systems more effectively. With the CData Python Connector for ADP, the pandas & Matplotlib modules, and the SQLAlchemy toolkit, you can build ADP-connected Python applications and scripts for visualizing ADP data. This article shows how to use the pandas, SQLAlchemy, and Matplotlib built-in functions to connect to ADP data, execute queries, and visualize the results.

With built-in optimized data processing, the CData Python Connector offers unmatched performance for interacting with live ADP data in Python. When you issue complex SQL queries from ADP, the driver pushes supported SQL operations, like filters and aggregations, directly to ADP and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations).

Connecting to ADP Data

Connecting to ADP data looks just like connecting to any relational data source. Create a connection string using the required connection properties. For this article, you will pass the connection string as a parameter to the create_engine function.

Connect to ADP by specifying the following properties:

• SSLClientCert: Set this to the certificate provided during registration.
• SSLClientCertPassword: Set this to the password of the certificate.
• UseUAT: The connector makes requests to the production environment by default. If using a developer account, set UseUAT = true.
• RowScanDepth: The maximum number of rows to scan for the custom fields columns available in the table. The default value will be set to 100. Setting a high value may decrease performance.

The connector uses OAuth to authenticate with ADP. OAuth requires the authenticating user to interact with ADP using the browser. For more information, refer to the OAuth section in the Help documentation.

Follow the procedure below to install the required modules and start accessing ADP through Python objects.

Install Required Modules

Use the pip utility to install the pandas & Matplotlib modules and the SQLAlchemy toolkit:

pip install pandas
pip install matplotlib
pip install sqlalchemy

Be sure to import the module with the following:

import pandas
import matplotlib.pyplot as plt
from sqlalchemy import create_engine

Visualize ADP Data in Python

You can now connect with a connection string. Use the create_engine function to create an Engine for working with ADP data.

engine = create_engine("adp:///?OAuthClientId=YourClientId&OAuthClientSecret=YourClientSecret&SSLClientCert='c:\cert.pfx'&SSLClientCertPassword='admin@123'InitiateOAuth=GETANDREFRESH&OAuthSettingsLocation=/PATH/TO/OAuthSettings.txt")

Execute SQL to ADP

Use the read_sql function from pandas to execute any SQL statement and store the resultset in a DataFrame.

df = pandas.read_sql("SELECT AssociateOID, WorkerID FROM Workers WHERE AssociateOID = 'G3349PZGBADQY8H8'", engine)

Visualize ADP Data

With the query results stored in a DataFrame, use the plot function to build a chart to display the ADP data. The show method displays the chart in a new window.

df.plot(kind="bar", x="AssociateOID", y="WorkerID")
plt.show()

Free Trial & More Information

Download a free, 30-day trial of the CData Python Connector for ADP to start building Python apps and scripts with connectivity to ADP data. Reach out to our Support Team if you have any questions.

Full Source Code

import pandas
import matplotlib.pyplot as plt
from sqlalchemy import create_engin

engine = create_engine("adp:///?OAuthClientId=YourClientId&OAuthClientSecret=YourClientSecret&SSLClientCert='c:\cert.pfx'&SSLClientCertPassword='admin@123'InitiateOAuth=GETANDREFRESH&OAuthSettingsLocation=/PATH/TO/OAuthSettings.txt")
df = pandas.read_sql("SELECT AssociateOID, WorkerID FROM Workers WHERE AssociateOID = 'G3349PZGBADQY8H8'", engine)

df.plot(kind="bar", x="AssociateOID", y="WorkerID")
plt.show()