<jupyter_start><jupyter_text>Data Analyst Hit Feature<jupyter_code>import numpy as np
import pandas as pd
import seaborn as sns
import json
from pandas.io.json import json_normalize
# Path data
df_file = pd.ExcelFile(r"C:\Users\jalerse\Documents\DataAnalys-Pivot\Data Pivot Party 19012021.xlsx")
df_file.sheet_names<jupyter_output><empty_output><jupyter_text>Data Cleansing<jupyter_code># Data loading
df = pd.read_excel(df_file, sheet_name='uc_2020_12')
df = df.iloc[:,1:] # hilangin kolom tidak terpakai(kosong)
# df.drop(['DateTime', 'Message Request'], inplace=True, axis=1)
df.head()
df['Intent'] = df['Intent'].str.replace('usr.ask_','')
df['Message Request'] = df['Message Request'].apply(lambda msg_request: (json.loads(msg_request)['msg'][0]['text']))
df.head()
df['Message Request'].apply(json.loads)
# json_normalize(df['Message Request'][0])
# pd.json_normalize(json.loads(df['Message Request'][0])
# pd.json_normalize(df['Message Request'].apply(json.loads))

df['Message Request'].apply(lambda msg_request: (json.loads(msg_request)['msg'][0]['text']))

# p = json.loads(text)
# p['msg'][index]['text']
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17682926829268292, 'fraction_numerical': 0.017421602787456445, 'mean_word_length': 8.655462184873949, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '296773', 'license': 'MIT', 'n_tokens_mistral': 432, 'n_tokens_neox': 428, 'n_words': 72, 'path': 'Data-Analyst-hit.ipynb', 'repo_name': 'ropok/explore-jupyter3', 'size': '1148'}