Using Python Pandas for Log Analysis
- 2019-03-07 01:28 AM
In this quick article, I'll walk you through how to use the Pandas library for Python in order to analyze a CSV log file for offload 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.
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.
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.
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!
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/
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)
☞ Complete Python: Go from zero to hero in Python
☞ Learn Python 3 Programming for Beginners
☞ An A-Z of useful Python tricks
☞ Learning Python: From Zero to Hero
☞ Automated Machine Learning on the Cloud in Python
☞ Python Tutorial for Beginners (2019) - Learn Python for Machine Learning and Web Development
☞ Complete Python Bootcamp: Go from zero to hero in Python 3
☞ Machine Learning A-Z™: Hands-On Python & R In Data Science
☞ Complete Python Masterclass
☞ Python and Django Full Stack Web Developer Bootcamp
Originally published by Akshay Ranganath at https://dzone.com