<jupyter_start><jupyter_text>최종 결과 취합<jupyter_code>import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import os

# row, columns 길게 조회할 수 있게 옵션 걸어두기
pd.options.display.max_info_columns =200
pd.options.display.max_columns = 200
pd.options.display.max_info_rows =999
pd.options.display.max_rows = 999

# import warnings
# warnings.filterwarnings(action='ignore')
# 한글글꼴
import warnings
import platform
from matplotlib import font_manager, rc

path = "c:/Windows/Fonts/malgun.ttfn"
if platform.system() == 'Darwin':
    font_name = 'AppleGothic'
    rc('font', family='AppleGothic')
elif platform.system() == 'Windows':
    font_name = font_manager.FontProperties(fname=path).get_name()
    rc('font', family=font_name)
else:
    font_name = font_manager.FontProperties(fname="/usr/share/fonts/nanumfont/NanumGothic.ttf")
    rc('font', family="NanumGothic")

# warnings.simplefilter(action='ignore')<jupyter_output><empty_output><jupyter_text>submission 형태 19년도 실제값 불러오기<jupyter_code>df = pd.read_csv('/Users/ppangppang/Desktop/ssac/Main_project/data/4.체감안전도_통계(2017_2019).csv')
df
df_total = df[df['large_category'].str.contains('경찰서별')&df['large_category'].str.contains('2019')]
df_total.index = range(len(df_total))
df_total
for i in range(len(df_total)):
    df_total['medium_category'][i] = df_total['medium_category'][i][:-1] + '경찰서'
df_total
for i in range(len(df_total)):
    if df_total['medium_category'][i] == '경기남부수원중부경찰서':
        df_total['medium_category'][i] = '수원중부경찰서'
    elif df_total['medium_category'][i] == '경기남부수원남부경찰서':
        df_total['medium_category'][i] = '수원남부경찰서'        
    elif df_total['medium_category'][i] == '경기남부수원서부경찰서':
        df_total['medium_category'][i] = '수원서부경찰서'                
    elif df_total['medium_category'][i] == '경남창원중부경찰서':
        df_total['medium_category'][i] = '창원중부경찰서'    
    elif df_total['medium_category'][i] == '경남창원서부경찰서':
        df_total['medium_category'][i] = '창원서부경찰서'    
    elif df_total['medium_category'][i] == '경남마산중부경찰서':
        df_total['medium_category'][i] = '마산중부경찰서'            
    elif df_total['medium_category'][i] == '경남마산동부경찰서':
        df_total['medium_category'][i] = '마산동부경찰서'                    
    elif df_total['medium_category'][i] == '경남진주경찰서':
        df_total['medium_category'][i] = '진주경찰서'            
    elif df_total['medium_category'][i] == '경남진해경찰서':
        df_total['medium_category'][i] = '진해경찰서'  
    elif df_total['medium_category'][i] == '충남세종경찰서':
        df_total['medium_category'][i] = '세종경찰서'  
df_total['year'] = 0
for i in range(len(df_total)):
    if i <=40 : 
        df_total['year'][i] = '2019(상반기)'
    else : 
        df_total['year'][i] = '2019(하반기)'
df_total
df_total.drop(columns=['large_category'],inplace=True)
df_total.rename(columns={'tot_f_safety':'종합체감안전도','dept_safety':'분야별안전도','crime_safety':'범죄안전도','medium_category':'jur_stn',' rob_satety_q1':'q1','mur_safety_q2':'q2','ta_safety_q3':'q3','raw_odder_q4':'q4','overall_q5':'q5'},inplace=True)
df_total = df_total[['jur_stn','year','종합체감안전도', '분야별안전도','범죄안전도','q1','q2','q3','q4','q5' ]]
df_total
df_total.to_csv('/Users/ppangppang/Desktop/ssac/Main_project/processed_data(slack)/2019체감안전도_실제값.csv',index=False)<jupyter_output><empty_output><jupyter_text>예측값 모으기 - 범죄안전도 = (q1 + q2)/2- 분야별안전도  = (범죄안전도 * 0.343) + (q3 * 0.305) + (q4 * 0.352)- 종합체감안전도 = (q5 * 0.3) + (분야별안전도 * 0.7)<jupyter_code>df_pred = pd.read_csv('/Users/ppangppang/Desktop/ssac/Main_project/data/base_df.csv')
df_pred = df_pred[df_pred.year.str.contains('2019')]
df_pred.index = range(len(df_pred))
df_pred
df_pred[['종합체감안전도','분야별안전도','범죄안전도','q1','q2','q3','q4','q5']] = 0.0
df_pred
df_pred.to_csv('/Users/ppangppang/Desktop/ssac/Main_project/processed_data(slack)/2019체감안전도_예측값.csv',index=False)<jupyter_output><empty_output><jupyter_text>전체 MAE 계산<jupyter_code>from sklearn.metrics import mean_absolute_error
real = df_total.sort_values(['jur_stn','year'])
real.index = range(len(real))
real
# 예측값 불러오기
merge_pred = pd.read_csv('/Users/ppangppang/Downloads/19년_체감_test.csv')
merge_pred
test = pred.copy()
test = pd.merge(test,merge_pred,how='left',on=('jur_stn','year'))
test
test.columns
test = test[['jur_stn', 'year', '종합체감안전도', '분야별안전도', '범죄안전도', 'q1_y', 'q2_y', 'q3_y', 'q4_y',
       'q5_y']]
test.rename(columns={'q1_y':'q1','q2_y':'q2','q3_y':'q3','q4_y':'q4','q5_y':'q5'},inplace=True)
test
# 범죄안전도 계산
test['범죄안전도'] = (test['q1'] + test['q2'])/2

# 분야별안전도 계산
test['분야별안전도'] = (test['범죄안전도']*0.343) + (test['q3'] * 0.305) + (test['q4'] * 0.352)

# 종합체감안전도 계산
test['종합체감안전도'] = (test['q5'] * 0.3) + (test['분야별안전도'] * 0.7)
test
pred = test
pred = df_pred.sort_values(['jur_stn','year'])
pred.index = range(len(pred))
pred
test1 = real[real.columns[2:]]
test2 = pred[pred.columns[2:]]
mean_absolute_error(test1,test2)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1882183908045977, 'fraction_numerical': 0.028735632183908046, 'mean_word_length': 5.693681318681318, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '306546', 'license': 'MIT', 'n_tokens_mistral': 2398, 'n_tokens_neox': 2648, 'n_words': 260, 'path': '3.Model_code/0.Final_calculate_MAE.ipynb', 'repo_name': 'Joripje/Main_project_police', 'size': '4872'}