<jupyter_start><jupyter_text>Set up dataframe<jupyter_code>data = pd.read_excel('data.xlsx')

df = data[['AGENCY','COMPONENT','SUB_COMPONENT','GRADELEVEL','SUP_STATUS','Please briefly describe an example of one burdensome administrative task or process which you believe is "low value"']]
df.columns = ['AGENCY','COMPONENT','SUB_COMPONENT','GRADELEVEL','SUP_STATUS','TEXT']
df.head()
full_df = df[df['TEXT'].isnull()==False]
full_df = df[df['TEXT'].isna()==False]
full_df = df[df['COMPONENT'].isna()==False]
full_df = df[df['GRADELEVEL'].isna()==False]
full_df.dropna(subset=['TEXT'],inplace=True)
#df_ag = full_df[full_df['AGENCY']=='Department of Agriculture']

#data_ag = df_ag['TEXT'].values.tolist()
#full_df.fillna('other')
#full_df.replace(pd.isna,'other')
unique_comps = full_df['COMPONENT'].unique()
unique_agenics = full_df['AGENCY'].unique()<jupyter_output><empty_output><jupyter_text>Remove emails and newline characters<jupyter_code>'''# Remove Emails
data_ag = [re.sub('\S*@\S*\s?', '', str(sent)) for sent in data_ag]

# Remove new line characters
data_ag = [re.sub('\s+', ' ', str(sent)) for sent in data_ag]

# Remove distracting single quotes
data_ag = [re.sub("\'", "", str(sent)) for sent in data_ag]'''
# def sent_to_words(sentences):
#     for sentence in sentences:
#         yield(gensim.utils.simple_preprocess(str(sentence), deacc=True))  # deacc=True removes punctuations

#data_ag_words = list(sent_to_words(data_ag))
# print(data_ag_words[:2])<jupyter_output><empty_output><jupyter_text>Build bigram and trigrams<jupyter_code>'''# Build the bigram and trigram models
bigram = gensim.models.Phrases(data_ag_words, min_count=5, threshold=100) # higher threshold fewer phrases.
trigram = gensim.models.Phrases(bigram[data_ag_words], threshold=100)  

# Faster way to get a sentence clubbed as a trigram/bigram
bigram_mod = gensim.models.phrases.Phraser(bigram)
trigram_mod = gensim.models.phrases.Phraser(trigram)'''
# print(trigram_mod[bigram_mod[data_ag_words[12]]])<jupyter_output><empty_output><jupyter_text>Remove Stopwords, make Bigrams and lemmatize<jupyter_code># nlp = spacy.load('en', disable=['parser', 'ner'])

# from nltk.corpus import stopwords
# stop_words = stopwords.words('english')
# stop_words.extend(['from', 'subject', 're', 'edu', 'use','none'])
# # Remove Stop Words
# data_words_nostops = remove_stopwords(data_ag_words)

# # Form Bigrams
# data_words_bigrams = make_bigrams(data_words_nostops)

# # Initialize spacy 'en' model, keeping only tagger component (for efficiency)
# # python3 -m spacy download en
# nlp = spacy.load('en', disable=['parser', 'ner'])

# # Do lemmatization keeping only noun, adj, vb, adv
# data_lemmatized = lemmatization(data_words_bigrams, allowed_postags=['NOUN', 'ADJ', 'VERB', 'ADV'])


# print(data_lemmatized[:3])<jupyter_output><empty_output><jupyter_text>create dictionary and corpus<jupyter_code># Create Dictionary
# id2word = corpora.Dictionary(data_lemmatized)

# # Create Corpus
# texts = data_lemmatized

# # Term Document Frequency
# corpus = [id2word.doc2bow(text) for text in texts]
# print(corpus[:2])<jupyter_output><empty_output><jupyter_text>Find optimal number of topics<jupyter_code>#moved to utils
#model_list, coherence_values = compute_coherence_values(dictionary=id2word, corpus=corpus, texts=data_lemmatized, start=2, limit=20, step=2)

#moved to  calc

scores = {'Department of Agriculture':{
'scores':{'total':8,'manager':4,'nonmanager':8
}},
'Department of Commerce':{
'scores':{'total':6,'manager':4,'nonmanager':6
}},
'Department of Defense':{
'scores':{'total':10,'manager':16,'nonmanager':8
}},
'Department of Education':{
'scores':{'total':6,'manager':14,'nonmanager':8
}},
'Department of Energy':{
'scores':{'total':4,'manager':18,'nonmanager':6
}},
'Department of Health and Human Services':{
'scores':{'total':6,'manager':10,'nonmanager':10
}},
'Department of Homeland Security':{
'scores':{'total':6,'manager':6,'nonmanager':6
}},
'Department of Housing and Urban Development':{
'scores':{'total':4,'manager':10,'nonmanager':4
}},
'Department of Justice':{
'scores':{'total':8,'manager':12,'nonmanager':4
}},
'Department of Labor':{
'scores':{'total':8,'manager':14,'nonmanager':6
}},
'Department of State':{
'scores':{'total':14,'manager':6,'nonmanager':6
}},
'Department of the Interior':{
'scores':{'total':10,'manager':14,'nonmanager':4
}},
'Department of the Treasury':{
'scores':{'total':8,'manager':10,'nonmanager':8
}},
'Department of Transportation':{
'scores':{'total':6,'manager':16,'nonmanager':6
}},
'Department of Veterans Affairs':{
'scores':{'total':8,'manager':12,'nonmanager':6
}},
'Environmental Protection Agency':{
'scores':{'total':14,'manager':12,'nonmanager':10
}},
'General Services Administration':{
'scores':{'total':18,'manager':8,'nonmanager':6
}},
'National Aeronautics and Space Administration':{
'scores':{'total':4,'manager':18,'nonmanager':6
}},
'National Science Foundation':{
'scores':{'total':4,'manager':16,'nonmanager':12
}},
'Nuclear Regulatory Commission':{
'scores':{'total':6,'manager':16,'nonmanager':6
}},
'Office of Personnel Management':{
'scores':{'total':6,'manager':12,'nonmanager':6
}},
'Social Security Administration':{
'scores':{'total':10,'manager':6,'nonmanager':6
}},
'Small Business Administration':{
'scores':{'total':6,'manager':16,'nonmanager':6
}},
'U.S. Agency for International Development':{
'scores':{'total':4,'manager':12,'nonmanager':8
}
}}
# scores.keys()<jupyter_output><empty_output><jupyter_text>Get Topics for agency level data<jupyter_code>def get_topics_agencies(df,topic_num):
    data_2 = df['TEXT'].values.tolist()

    # Remove Emails
    data_2 = [re.sub('\S*@\S*\s?', '', str(sent)) for sent in data_2]

    # Remove new line characters
    data_2 = [re.sub('\s+', ' ', str(sent)) for sent in data_2]

    # Remove distracting single quotes
    data_2 = [re.sub("\'", "", str(sent)) for sent in data_2]

    data_words_2 = list(sent_to_words(data_2))

    # Build the bigram and trigram models
    bigram2 = gensim.models.Phrases(data_words_2, min_count=5, threshold=50) # higher threshold fewer phrases.
    trigram2 = gensim.models.Phrases(bigram2[data_words_2], threshold=50)  

    # Faster way to get a sentence clubbed as a trigram/bigram
    bigram_mod2 = gensim.models.phrases.Phraser(bigram2)
    trigram_mod2= gensim.models.phrases.Phraser(trigram2)

    # Remove Stop Words
    data_words_nostops2 = remove_stopwords(data_words_2)

    # Form Bigrams
    data_words_bigrams2 = make_bigrams(data_words_nostops2,bigram_mod2)

    # Initialize spacy 'en' model, keeping only tagger component (for efficiency)
    # python3 -m spacy download en
    nlp = spacy.load('en_core_web_sm', disable=['parser', 'ner'])

    # Do lemmatization keeping only noun, adj, vb, adv
    data_lemmatized2 = lemmatization(data_words_bigrams2, allowed_postags=['NOUN', 'ADJ', 'VERB', 'ADV'])

    # Create Dictionary
    id2word2 = corpora.Dictionary(data_lemmatized2)

    # Create Corpus
    texts2 = data_lemmatized2

    # Term Document Frequency
    corpus2 = [id2word2.doc2bow(text) for text in texts2]

    #model_list, coherence_values = compute_coherence_values(dictionary=id2word2, corpus=corpus2, texts=data_lemmatized2, start=2, limit=20, step=2)

    #print(coherence_values)
    #max_coherence_score = max(coherence_values)
    #best_num_loc = coherence_values.index(max_coherence_score)
    #best_topic_num = (coherence_values.index(max_coherence_score) + 1) *2
    #print (best_topic_num)

    #best_model = model_list[best_num_loc]

    #model_topics = best_model.show_topics(formatted=False)

    model_2 = gensim.models.ldamodel.LdaModel( corpus=corpus2, num_topics=topic_num, id2word=id2word2)

    #pprint(best_model.print_topics(num_words=8))
    #pprint(model_2.print_topics(num_words=8))

    return model_2.show_topics(num_words=8,formatted=True)

def get_topics(df):


    data_2 = df['TEXT'].values.tolist()
    if len(data_2)==0:
        return 'empty'

    # Remove Emails
    data_2 = [re.sub('\S*@\S*\s?', '', str(sent)) for sent in data_2]

    # Remove new line characters
    data_2 = [re.sub('\s+', ' ', str(sent)) for sent in data_2]

    # Remove distracting single quotes
    data_2 = [re.sub("\'", "", str(sent)) for sent in data_2]

    data_words_2 = list(sent_to_words(data_2))

    # Build the bigram and trigram models
    bigram2 = gensim.models.Phrases(data_words_2, min_count=5, threshold=50) # higher threshold fewer phrases.
    trigram2 = gensim.models.Phrases(bigram2[data_words_2], threshold=50)  

    # Faster way to get a sentence clubbed as a trigram/bigram
    bigram_mod2 = gensim.models.phrases.Phraser(bigram2)
    trigram_mod2= gensim.models.phrases.Phraser(trigram2)

    # Remove Stop Words
    data_words_nostops2 = remove_stopwords(data_words_2)

    # Form Bigrams
    data_words_bigrams2 = make_bigrams(data_words_nostops2,bigram_mod2)

    # Initialize spacy 'en' model, keeping only tagger component (for efficiency)
    # python3 -m spacy download en
    nlp = spacy.load('en_core_web_sm', disable=['parser', 'ner'])

    # Do lemmatization keeping only noun, adj, vb, adv
    data_lemmatized2 = lemmatization(data_words_bigrams2, allowed_postags=['NOUN', 'ADJ', 'VERB', 'ADV'])

    # Create Dictionary
    id2word2 = corpora.Dictionary(data_lemmatized2)

    # Create Corpus
    texts2 = data_lemmatized2

    # Term Document Frequency
    corpus2 = [id2word2.doc2bow(text) for text in texts2]
    try:
        model_list, coherence_values = compute_coherence_values(dictionary=id2word2, corpus=corpus2, texts=data_lemmatized2, start=2, limit=20,\
                   step=2,id2word=id2word2)
    except ValueError:
        return 'no data'

    #print(coherence_values)
    max_coherence_score = max(coherence_values)
    best_num_loc = coherence_values.index(max_coherence_score)
    best_topic_num = (coherence_values.index(max_coherence_score) + 1) *2
    #print (best_topic_num)

    #best_model = model_list[best_num_loc]

    #model_topics = best_model.show_topics(formatted=False)

    model_2 = gensim.models.ldamodel.LdaModel( corpus=corpus2, num_topics=best_topic_num, id2word=id2word2)

    #pprint(best_model.print_topics(num_words=8))
    #pprint(model_2.print_topics(num_words=8))

    return model_2.show_topics(num_words=8,formatted=True)
topic_dict_agency={}
key_values = ['total', 'manager', 'nonmanager']

for agency in unique_agenics:
    topic_dict_agency[agency] = get_topics_agencies(full_df[full_df['AGENCY']==agency],scores.get(agency).get('scores').get('total'))

agency_df = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in topic_dict_agency.items() ])).T
agency_df.to_csv('Agency_Topics.csv')
topic_dict_agency_mang={}
key_values = ['total', 'manager', 'nonmanager']

for agency in unique_agenics:
    topic_dict_agency_mang[agency] = get_topics_agencies(full_df[(full_df['AGENCY']==agency) & (full_df['SUP_STATUS']==1)],scores.get(agency).get('scores').get('manager'))
agency_df_mang = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in topic_dict_agency_mang.items() ])).T
agency_df_mang.to_csv('Agency_Topics_Senior_Manager.csv')
topic_dict_agency_nonmang={}
key_values = ['total', 'manager', 'nonmanager']

for agency in unique_agenics:
    topic_dict_agency_nonmang[agency] = get_topics_agencies(full_df[(full_df['AGENCY']==agency) & (full_df['SUP_STATUS']==0)],scores.get(agency).get('scores').get('nonmanager'))
agency_df_nonmang = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in topic_dict_agency_nonmang.items() ])).T
agency_df_nonmang.to_csv('Agency_Non_Manager_Topics.csv')
unique_grades = full_df['GRADELEVEL'].unique()
grade_topic_dict = {}
for grade in unique_grades:
    grade_topic_dict[grade] = get_topics(full_df[full_df['GRADELEVEL']==grade])
gs_df = pd.DataFrame(dict([ (k,pd.Series(v)) for k,v in grade_topic_dict.items() ])).T
gs_df.to_csv('GS_Topics.csv')
unique_comps_topics_mang = {}

for agency in unique_agenics:
    temp_dict = {}

    for comps in full_df[(full_df['AGENCY']==agency) & (full_df['SUP_STATUS']==1)]['COMPONENT'].unique():
        temp_dict[comps] = get_topics(full_df[(full_df['AGENCY']==agency) & (full_df['SUP_STATUS']==1)&(full_df['COMPONENT']==comps)])
    
    unique_comps_topics_mang[agency] = temp_dict

    
print(type(unique_comps_topics_mang.items()))

unique_comps_topics_mang['Department of Agriculture']
unique_comps_topics_mang['Department of Agriculture']['Trade and Foreign Agricultural Affairs']
comps_mang_df = pd.DataFrame([(k,k1,pd.Series(v1)) for k,v in unique_comps_topics_mang.items() for k1,v1 in v.items() ],columns=['Agency','Component','topics'])
comps_mang_df.head()

comps_mang_df['topics'][0]
headers_list = ['Agency','Component','topics','topic 0','topic 1','topic 2', 'topic 3','topic 4','topic 5','topic 6','topic 7', 'topic 8','topic 9']
comps_mang_df = comps_mang_df.reindex(columns = headers_list)
comps_mang_df.head()
comps_mang_df['topics'].iloc[1]
for i in range(len(comps_mang_df)):
    for t in range((10)):
        try:
            comps_mang_df['topic '+str(t)].iloc[i] = comps_mang_df['topics'].iloc[i][t][1]
        except KeyError:
            pass

comps_mang_df.to_csv('Component_SR_Manager.csv')
unique_comps_topics_nonmang = {}

for agency in unique_agenics:
    temp_dict = {}

    for comps in full_df[(full_df['AGENCY']==agency) & (full_df['SUP_STATUS']==0)]['COMPONENT'].unique():
        temp_dict[comps] = get_topics(full_df[(full_df['AGENCY']==agency) & (full_df['SUP_STATUS']==0)&(full_df['COMPONENT']==comps)])
    
    unique_comps_topics_nonmang[agency] = temp_dict
comps_nonmang_df = pd.DataFrame([(k,k1,pd.Series(v1)) for k,v in unique_comps_topics_nonmang.items() for k1,v1 in v.items() ],columns=['Agency','Component','topics'])
headers_list = ['Agency','Component','topics','topic 0','topic 1','topic 2', 'topic 3','topic 4','topic 5','topic 6','topic 7', 'topic 8','topic 9']
comps_nonmang_df = comps_nonmang_df.reindex(columns = headers_list)
for i in range(len(comps_nonmang_df)):
    for t in range((10)):
        try:
            comps_nonmang_df['topic '+str(t)].iloc[i] = comps_nonmang_df['topics'].iloc[i][t][1]
        except KeyError:
            pass

comps_nonmang_df.to_csv('Component_Non_Manager.csv')
unique_comps_topics = {}

for agency in unique_agenics:
    temp_dict = {}

    for comps in full_df[(full_df['AGENCY']==agency)]['COMPONENT'].unique():
        temp_dict[comps] = get_topics(full_df[(full_df['AGENCY']==agency)&(full_df['COMPONENT']==comps)])
    
    unique_comps_topics[agency] = temp_dict
comps_df = pd.DataFrame([(k,k1,pd.Series(v1)) for k,v in unique_comps_topics.items() for k1,v1 in v.items() ],columns=['Agency','Component','topics'])
headers_list = ['Agency','Component','topics','topic 0','topic 1','topic 2', 'topic 3','topic 4','topic 5','topic 6','topic 7', 'topic 8','topic 9']
comps_df = comps_df.reindex(columns = headers_list)
for i in range(len(comps_df)):
    for t in range((10)):
        try:
            comps_df['topic '+str(t)].iloc[i] = comps_df['topics'].iloc[i][t][1]
        except KeyError:
            pass
comps_df.to_csv('Component_Topics.csv')
f = open('GS_Topics.csv','r')

f_text = f.read()
list1=re.sub(r"[^a-zA-Z]+", ' ',f_text)
g
f.close()
g = open('Agency_Topics.csv','r')
g_text = g.read()
listg=re.sub(r"[^a-zA-Z]+", ' ',g_text)
g.close()
h = open('Component_SR_manager.csv','r')
h_text = h.read()
listh=re.sub(r"[^a-zA-Z]+", ' ',h_text)
h.close()
j = open('Component_Topics.csv','r')
j_text = j.read()
listj=re.sub(r"[^a-zA-Z]+", ' ',j_text)
j.close()
k = open('Component_Topics.csv','r')
k_text = k.read()
listk=re.sub(r"[^a-zA-Z]+", ' ',k_text)
k.close()
l = open('Agency_Topics_Senior_Manager.csv','r')
l_text = l.read()
listl=re.sub(r"[^a-zA-Z]+", ' ',l_text)
l.close()
new_word = listl+listk+listj+listh+listg+list1
text_file = open('words.txt',"w")
text_file.write(new_word)
text_file.close()
import gensim.downloader as api

dataset = api.load("text8")
dataset = [wd for wd in dataset]

from ngrams_maker import NGramsMaker

maker = NGramsMaker(dataset, 2)
print(type(maker.make_ngrams([dataset[1]])))

maker = NGramsMaker(dataset, 2)
print(maker.make_ngrams([dataset[1]]))

from topicsanalyser import TopicsAnalyser

analyser = TopicsAnalyser('data.xlsx')
# analyser = TopicsAnalyser('CSS_Hiring_Data_FedEmployee_Reason_OTHER_v1.xlsx')
num_topics = 5
groupby_cols = ['AGENCY','GRADELEVEL']

analyser.get_topics(num_topics, groupby_cols)
test_data = {
            'key0': {
                'key0_0': {
                    'key0_0_0': [(0, '000'),(1, '001')]
                },
                'key0_1': {
                    'key0_1_0': [(0, '010'),(1, '011')]
                },
                'key0_2': {
                    'key0_2_0': [(0, '020'),(1, '021')]
                }
            },
            'key1': {
                'key1_0': {
                    'key1_0_0': [(0, '100'),(1, '101')]
                },
                'key1_1': {
                    'key1_1_0': [(0, '110'),(1, '111')]
                },
                'key1_2': {
                    'key1_2_0': [(0, '120'),(1, '121')]
                }
            }
       }



test = "col1,col2,col3"
test2 = ""

test.split(',')
test2.split(',')
arrays = [
    np.array(["bar", "bar", "baz", "baz", "foo", "foo", "qux", "qux"]),
    np.array(["one", "two", "one", "two", "one", "two", "one", "two"]),
    ]

df = pd.DataFrame(np.random.randn(3, 8), index=["A", "B", "C"], columns=arrays)
df
num_topics = 5
grp_cols = ['AGENCY','COMPONENT']
# cols = [['Topics'] + grp_cols + sorted([f"Topic {i}" for i in range(1, num_topics+1)]*2),
#         ['' for i in range(len(grp_cols) + 1)] + [item for sublist in [['Coherence Score','Distribution'] for i in range(num_topics)] for item in sublist]
#         ]
cols = ['Topics'] + grp_cols + [item for sublist in [[f'Topic {i+1}', f'Coherence Score {i+1}'] for i in range(num_topics)] for item in sublist]

df = pd.DataFrame(np.random.randn(3, num_topics * 2 + 3), index=["A", "B", "C"], columns=cols)
df
grp_cols = ['AGENCY','COMPONENT']
test2 = [['' for i in range(len(grp_cols) + 1)] + ['Coherence Score','Distribution'] for i in range(5)]
test2
flat_list = [item for sublist in test2 for item in sublist]
flat_list
test3 = lambda<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1706288343558282, 'fraction_numerical': 0.0228965819456617, 'mean_word_length': 5.394746059544659, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '660783', 'license': 'CC0-1.0', 'n_tokens_mistral': 7158, 'n_tokens_neox': 6784, 'n_words': 1191, 'path': 'topicsanalyser/tests.ipynb', 'repo_name': 'kapangyu-gsa/MD-Automated-Text-Analysis', 'size': '18256'}