Python groupby output

timlaw71 0
Viewed 48

I'm trying to take a spreadsheet input and display only the urls that are CMS related (wordpress|Wordpress|WordPress|Drupal|drupal|Joomla|joomla). I'm trying to get the output to be "technologies" and then the "url" associated (grouped) to those urls.

Link to data file output

Code is:

import pandas as pd
import numpy as np

dataset="ASD-example.xlsx"

term_cms = 'wordpress|Wordpress|WordPress|Drupal|drupal|Joomla|joomla'

df = pd.read_excel((dataset), sheet_name="HTTPX")

df['technology_count'] = df.groupby('technologies')['url'].transform('count')
df.drop(['timestamp', 'request', 'response-header', 'scheme', 'port', 'body-sha256','header-sha256', 'a', 'cnames', 'input', 'location', 'error', 'response-body', 'content-type','method', 'host', 'content-length', 'chain-status-codes', 'status-code', 'tls-grab', 'csp', 'vhost','websocket', 'pipeline', 'http2', 'cdn', 'response-time', 'chain', 'final-url', 'failed','favicon-mmh3', 'lines', 'words','path','webserver'],inplace=True,axis=1)
df[df['technologies'].str.contains(term_cms, na=False)]
pivot1 = pd.pivot_table(df, index=['technologies', 'url'], columns=None, fill_value=0)

print(pivot1)
pandas python
1 Answers

I cleaned your code a bit to make it more readable to get the output you want.

term_cms = ["wordpress", "drupal", "joomla"]

# remove square brackets and lowercase all names
df['technologies'] = df['technologies'].str.strip('[]')
df['technologies'] = df['technologies'].str.lower()

# include only needed technologies
mask = df['technologies'].isin(term_cms)
df = df[mask]

# groupby and count
df = df.groupby(['technologies', 'url']).size().reset_index(name='technology_count')

Output:

    technologies    URL                      technology_count
0   joomla          https://testcom123.      1
1   Wordpress       https://test.com:443     1
Ibrahim Sherif0
Related