Using Python Pandas for Log Analysis

In this short tutorial, I would like to walk through the use of Python Pandas to analyze a CSV log file for offload analysis. This is a typical use case that I face at Akamai.

Background

Python Pandas is a library that provides data science capabilities to Python. Using this library, you can use data structures like DataFrames. This data structure allows you to model the data like an in-memory database. By doing so, you will get query-like capabilities over the data set.

Use Case

Suppose we have a URL report from taken from either the Akamai Edge server logs or the Akamai Portal report. In this case, I am using the Akamai Portal report. In this workflow, I am trying to find the top URLs that have a volume offload less than 50%. I've attached the code at the end. I am going to walk through the code line-by-line. Here are the column names within the CSV file for reference.

Offloaded Hits,Origin Hits,Origin OK Volume (MB),Origin Error Volume (MB)

Initialize the Library

The first step is to initialize the Pandas library. In almost all the references, this library is imported as pd. We'll follow the same convention.

import pandas as pd

Read the CSV as a DataFrame

The next step is to read the whole CSV file into a DataFrame. Note that this function to read CSV data also has options to ignore leading rows, trailing rows, handling missing values, and a lot more. I am not using these options for now.

urls_df = pd.read_csv('urls_report.csv')

Pandas automatically detects the right data formats for the columns. So the URL is treated as a string and all the other values are considered floating point values.

Compute Volume Offload

The default URL report does not have a column for Offload by Volume. So we need to compute this new column.

urls_df['Volume Offload'] = (urls_df['OK Volume']*100) / (urls_df[

We are using the columns named OK Volume and Origin OK Volumn (MB) to arrive at the percent offloads.

Filter the Data

At this point, we need to have the entire data set with the offload percentage computed. Since we are interested in URLs that have a low offload, we add two filters:

• Consider the rows having a volume offload of less than 50% and it should have at least some traffic (we don't want rows that have zero traffic).
• We will also remove some known patterns. This is based on the customer context but essentially indicates URLs that can never be cached.

Sort Data

At this point, we have the right set of URLs but they are unsorted. We need the rows to be sorted by URLs that have the most volume and least offload. We can achieve this sorting by columns using the sort command.

low_offload_urls.sort_values(by=['OK Volume','Volume Offload'],inplace

Print the Data

For simplicity, I am just listing the URLs. We can export the result to CSV or Excel as well.

First, we project the URL (i.e., extract just one column) from the dataframe. We then list the URLs with a simple for loop as the projection results in an array.

for each_url in low_offload_urls['URL']:
print (each_url)

I hope you found this useful and get inspired to pick up Pandas for your analytics as well!

References

I was able to pick up Pandas after going through an excellent course on Coursera titled Introduction to Data Science in Python. During this course, I realized that Pandas has excellent documentation.

• Pandas Documentation: http://pandas.pydata.org/pandas-docs/stable/

Full Code

import pandas as pd

urls_df = pd.read_csv('urls_report.csv')

#now convert to right types
urls_df['Volume Offload'] = (urls_df['OK Volume']*100) / (urls_df['OK Volume'] + urls_df['Origin OK Volume (MB)'])

low_offload_urls = urls_df[(urls_df['OK Volume'] > 0) & (urls_df['Volume Offload']<50.0)]
low_offload_urls = low_offload_urls[(~low_offload_urls.URL.str.contains("some-pattern.net")) & (~low_offload_urls.URL.str.contains("stateful-apis")) ]

low_offload_urls.sort_values(by=['OK Volume','Volume Offload'],inplace=True, ascending=['True','False'])

for each_url in low_offload_urls['URL']:
print (each_url)