<jupyter_start><jupyter_text>Topic by LDA<jupyter_code># Open in pandas the table with the topic LDA
df_lda = pd.read_csv(csv_path / "topic_lda.csv") #Make sure to update this data
df_lda.head()
df_nmf = pd.read_table(csv_path / "topic_classification.txt",sep=',') #Make sure to update this data
df_nmf.head()
filepath_2 = Path.cwd().parent / "data" / "Approval_Index.xlsx"
approval=pd.read_excel(filepath_2, usecols=['DATE','APPROVAL INDEX'])
approval.head()
approval=approval.rename(columns={'DATE':'date','APPROVAL INDEX':'approval_index'})
approval.head()
df_lda['date'] = df_lda['date'].astype('datetime64[ns]')
df_lda['year']=df_lda['date'].dt.year
df_lda["topic_1_num"] = df_lda["main_topic_1"].str.replace("Topic ", "")

merged_lda=pd.merge(df_lda,approval,on='date',how='left')
import datetime
merged_lda.loc[merged_lda.approval_index.isna(),'date']= merged_lda.date+pd.Timedelta("2 days")
merged_lda=pd.merge(merged_lda.drop(['approval_index'],axis=1),approval,on='date',how='left')
merged_lda.head()

merged_lda.loc[merged_lda.approval_index.isna(),['date','state','country','approval_index']]
merged_lda['approval_index'].astype('Int64')


cross_topic_lda=pd.DataFrame(merged_lda.groupby(['main_topic_1']).main_topic_2.value_counts().reset_index(name='count'))
sns.relplot(x="main_topic_1", y="main_topic_2", size="count",sizes=(70, 700), alpha=.5,height=7, data=cross_topic_lda)
most_freq_topic=[22,20,8,1,4,9,15,14,0,3]##.loc[df_nmf['main_topic_1_index']].isin(most_freq_topic)
cross_topic_nmf=pd.DataFrame(df_nmf.groupby(['main_topic_1_index']).main_topic_2_index.value_counts().reset_index(name='count'))
order_2 = ['1','2','3','4','5','6','7','8','9','10','11','12','13', '14','15','16','17','18','19','20', '21','22','23','24','25','none']
sns.relplot(x="main_topic_1_index", y="main_topic_2_index", size="count",sizes=(50, 500), alpha=.5,height=10, data=cross_topic_nmf,row_order=order_2)
n_approval = pd.crosstab(index=merged_lda['main_topic_1'], columns=merged_lda['year']) 
n_approval
sns.set(rc={'figure.figsize':(20,8.27)})
n_approval.T.plot.bar(stacked=True)
avg_approval = pd.pivot_table(merged_lda, values='approval_index', columns='year',index=['main_topic_1'],aggfunc=np.mean,margins=True)
avg_approval=avg_approval.iloc[: , :-2].transpose()
sns.set(rc={'figure.figsize':(20,8.27)})
sns.lineplot(data=avg_approval)
state_approval = pd.pivot_table(merged_lda.loc[merged_lda['country']=='USA'], values='approval_index', columns='main_topic_1',index=['state'],aggfunc=np.mean,margins=True)
state_approval

freq_topic_state= pd.crosstab(index=merged_lda['state'], columns=merged_lda['main_topic_1']) 
merged_lda.loc[(merged_lda['state']=='no_state')& (merged_lda['country']=='USA')]
states = merged_lda.groupby(['state','main_topic_1'])['main_topic_1'].agg({'count'})
mask = states.groupby(level=0).agg('idxmax')
df_count = states.loc[mask['count']]
df_count = df_count.reset_index()
df_count["topic_1_num"] = df_count["main_topic_1"].str.replace("Topic ", "")
print("\nOutput\n{}".format(df_count))

freq_topic_nmf= pd.crosstab(index=df_nmf['main_topic_1_index'], columns='count').sort_values(by='count')
freq_topic_nmf
most_freq_topic=[22,20,8,1,4,9,15,14,0,3,7,18]<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup