<jupyter_start><jupyter_text>Python for Data Analysis introductionMaster of Advanced Studies in Digital Forensics & Cyber InvestigationData Analytics and Visualization for Digital Forensics(c) <NAME>, 2021This exercise starts with examples of most common data analysis tasks with Python, from the features of Python itself to using modules like Pandas with a few exercises that you can try to accomplish yourself.  Part 1 Answers<jupyter_code># initialize dataframes to start where we left of with the instructions
#
import numpy as np
import pandas as pd
df = pd.read_excel('DirPrint_Filelist - clean.xlsx')
df
df = df.set_index("LastMod")<jupyter_output><empty_output><jupyter_text>1 Use a regular expression to select office files based on file extension<jupyter_code># here is a simple example using a regular expression to find all rows with xls in the Ext colum
df[df.Ext.str.match(r'(xls|doc|xlsx|docx|ppt|pptx)')==True]<jupyter_output><empty_output><jupyter_text>2 Remove duplicate entries in df based on the md5 value.<jupyter_code>df.shape
df_dedup = df.drop_duplicates('Md5', keep='last')
df_dedup.shape<jupyter_output><empty_output><jupyter_text>3 For a given file, find if it has duplicates and list the folders where duplicates are located<jupyter_code>df.duplicated('Md5')
# Apparently record 255826 is duplicated. We can print it's Md5 value using the df.loc attribute
#
df.loc[255827].Md5
# We can select all duplicates with a filter
#
df[df.Md5 == '8280BBD8640ECF3826460903760D9774']
df[df.Size==0]<jupyter_output><empty_output><jupyter_text>4 Create a pivot table showing number of files per month based on data last modified<jupyter_code>df.pivot_table(index=[df.index.year,df.index.month],values='Size',aggfunc=['count','sum'])<jupyter_output><empty_output><jupyter_text>5 Create a pivot table listing number of files vs file extension<jupyter_code>df.pivot_table(index='Ext',aggfunc=['count'])
# Alternative count like this:

df.groupby('Ext').count()
# or ....
df['Ext'].value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09470068694798822, 'fraction_numerical': 0.02649656526005888, 'mean_word_length': 7.02755905511811, 'pattern_counts': {'":': 0, '<': 26, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '394259', 'license': 'Apache-2.0', 'n_tokens_mistral': 692, 'n_tokens_neox': 645, 'n_words': 206, 'path': 'Part_1_Exercise_Answers.ipynb', 'repo_name': 'HansHenseler/masdav', 'size': '2045'}