<jupyter_start><jupyter_text>Data BootCamp - Final Project (File 01) : - <NAME> (<EMAIL>)- <NAME>     (<EMAIL>)   Analysis of the Size of Residential Apartments in South Korea   1. Background. The purpose of this project is to analyze the statistics regarding the size(sq.meter) of apartments in South Korea using Python. For a detailed explanation of the South Korean Apartment market, visit http://www.yourultimateapartment.com/korean-apartment-size. 2. Project Content & Data Explanation This Project is consist of two files : File  01 : Data Analysis / File 02 : Data Collection. First file(file01) contains our data analysis, and the 2nd file contins the process of our data collection. Unlike the United States (ex.Pluto), South Korea does not provide the city/urban area data in the neat form. Therefore, as part of our project, we had to crawl public websites and gather the relevant data source. South Korea consists of 10 provinces & 8 metropolitan cities.  (See image A below). Most apartments are concentrated in Metro Cities and Kyungi, a Province Area near Seoul. Even though there are apartments in other provinces, because the amount of data is reletively small, we decided to leave it out of our analysis.We used sqlite3(SQL) to collect database as we want to try various packages. However, we used excel as a data format for the analysis due to its familiarity and convenience.  ====================================================================================================================== 2-1 : Image - City and Province of South Korea           8 Metropolitan CitiesSeoul (1), Busan (2), Daegu (3), Incheon (4), Gwangju (5), Daejeon (6), Ulsan(7), Saejong            Suburban province containing majority of Apartments in South Korea:Kyungi  (A)===================================================================================================================== 2-2 :Data Location & Other SourcesData location- Korean Apartment(Residential Property) Management Info System : http://www.k-apt.go.kr- South Korea Ministry of Land and Infrastructure and Transportation : http://rt.molit.go.krStudy Material Location, and Origin of Idea, Codes and Idioms- NYU DATABOOTCAMP : http://databootcamp.nyuecon.com/- Coursera : https://www.coursera.org/learn/python-network-data/- Sqlite3 http://www.tutorialspoint.com/sqlite/sqlite_python.htm- Openwings : http://goodvc.tistory.com/category/%EB%82%98%EC%9D%98%20%EB%8D%B0%EC%9D%B4%ED%84%B0%20%EB%B6%84%EC%84%9D- Code academy : https://www.codecademy.com/ko/courses/python-advanced-ko/0/1?curriculum_id=53b5e0d8fed2a81590000003- Haeshik : http://nbviewer.ipython.org/gist/hyeshik/cf9f3d7686e07eedbfda?revision=6All Right reserved. Source, Data, Coding Method to orginal locaiton of link mentioned above.<jupyter_code># import packages 
import pandas as pd                   # data management
import matplotlib.pyplot as plt       # graphics 
import matplotlib as mpl              # graphics parameters
import numpy as np                    # numerical calculations 
%matplotlib inline

# Checking Python version 
import datetime as dt 
import sys
print('Today is', dt.date.today())
print('What version of Python are we running? \n', sys.version, sep='') 

# import collected data (Excel Format)
path = 'C:\\Users\\Dongjin\\Desktop\\Database-Translated2.xlsx'
Apartment_data = pd.read_excel(path)
Apartment_data.shape<jupyter_output><empty_output><jupyter_text>2-3 Data DictionaryThe original data set has 20 objects initially. However, during our data cleansing phase, we narrowed down to 17 objects for our analysis. Therefore, it only contains 17 columns. The following is the data dictionary of our dataset.<jupyter_code>path2 = 'C:\\Users\\Dongjin\\Desktop\\dictionary.xlsx'
dictionary = pd.read_excel(path2)
dictionary
# Copying Data and preserve original data
# checking the size of the data
apt_datacopy = Apartment_data
apt_datacopy.shape<jupyter_output><empty_output><jupyter_text>As mentioned in the dictionary above, in the excel sheet there are 4 columns which counted number of units based on the floor-size categorized as follows.    KAPTDA_CNT1 = 135㎡ <     KAPTDA_CNT2 = 85㎡~135㎡    KAPTDA_CNT3 = 60㎡~85㎡    KAPTDA_CNT4 = < 60㎡<jupyter_code># Renaming the Column name in the dataframe
apt_datacopy.rename(columns={'KAPTDA_CNT4': 'less_than_60sqm','KAPTDA_CNT3': '60sqm-85sqm'\
                       ,'KAPTDA_CNT2': '85sqm-135sqm','KAPTDA_CNT1': '135sqm<'}, inplace=True)
# Insert the column Total which counts the entire number of unit apartments
apt_datacopy['Total'] = apt_datacopy['less_than_60sqm'] +  apt_datacopy['60sqm-85sqm'] +  apt_datacopy['85sqm-135sqm'] +  apt_datacopy['135sqm<']
# Confirm the copied dataframe
apt_datacopy.head(1)<jupyter_output><empty_output><jupyter_text>3. Analysis 3-1. Annual Growth in Apartment Units<jupyter_code># Since the data does not recognize it as a string, we converted them to date
apt_datacopy['Dates'] = pd.to_datetime(apt_datacopy['KAPT_USEDATE'],format="%m/%d/%Y")
# Now we decide to gather them by year, and plot them with the relative size.
Annualgrowth = apt_datacopy.groupby(apt_datacopy['Dates'].map(lambda x: x.year))
Annualgrowth.count()['Total'].plot(kind='bar',title='Annual Growth in Apartment Units',color = 'b',figsize=(10,5))
plt.show()<jupyter_output><empty_output><jupyter_text>3-2. Large Apartment RatioAccording to the Korean Government, residential properties larger than 85㎡  (914 sq ft) are considered "large residential property" for tax purpose. We decide to figure out the number of such large apartments in our data set and its ratio to the entire market.Large Apartment Ratio  = (Number of Large Residential Property)/(Number of Entire Residential Property)<jupyter_code>apt_datacopy['Small(Total)'] = apt_datacopy['less_than_60sqm']+apt_datacopy['60sqm-85sqm']
apt_datacopy['Large(Total)'] = apt_datacopy['85sqm-135sqm']+apt_datacopy['135sqm<']
Chart1 = apt_datacopy[['less_than_60sqm','60sqm-85sqm','85sqm-135sqm','135sqm<']].sum()
Chart2 = apt_datacopy[['Small(Total)','Large(Total)']].sum()

fig, axes = plt.subplots(nrows=1, ncols=2, figsize=(12,6))
axes[0].set_title('Apartment Ratio per square meter')
Chart1.plot(kind='pie', figsize=(6,6),colors=('y','g','b','r'),autopct='%.2f%%', subplots=True , ax=axes[0])
axes[1].set_title('Ratio : Small & Large')
Chart2.plot(kind='pie', figsize=(6,6),colors=('r','b'), autopct='%.2f%%', subplots=True , ax=axes[1])
plt.show()<jupyter_output><empty_output><jupyter_text>3-3. Large Apartment in Each Individual CityNow lets find out the number and ratio of large apartments in each city.<jupyter_code>grouped = apt_datacopy.groupby(['City'])
Chart3 = grouped.agg({'Small(Total)':np.sum,'Large(Total)':np.sum, 'Total':np.sum } , as_index=False ).sort(['Total'])
Chart3['Small(Ratio)'] = Chart3['Small(Total)'] /  Chart3['Total']
Chart3['Large(Ratio)'] = Chart3['Large(Total)'] /  Chart3['Total']
Chart3[['Small(Total)','Large(Total)']].plot(kind='barh', stacked=True, color =('y','r'), title='',figsize=(12,6))<jupyter_output><empty_output><jupyter_text>Since Seoul is the capital and nearly half of the population resides there, the amount of apartment is far exceeds any other city. But what about the ratio? Let's find out.<jupyter_code>Chart3[['Small(Ratio)','Large(Ratio)']].sort('Large(Ratio)').plot(kind='barh', stacked=True, color =('y','r'),figsize=(12,6))<jupyter_output>C:\Anaconda3\lib\site-packages\ipykernel\__main__.py:1: FutureWarning: sort(columns=....) is deprecated, use sort_values(by=.....)
  if __name__ == '__main__':<jupyter_text>3-4. Factors Correlated to the Large Apartment RatioWe tested correlation of two factors: city budget and average GDP of the city toward large apartment ratio.<jupyter_code># City Budget (Unit : 10,000 Won )
list = '''Seoul 342,751 144,328 50,993 2,231 50,362 18,603 18,182 9,560 48,492 80.0 81.2
Busan 122,222 34,647 12,207 10,658 37,213 6,932 4,639 4,746 11,180 51.4 66.6
Daegu 87,347 22,474 6,545 10,663 28,021 5,289 2,830 2,709 8,816 46.1 67.6
Incheon 113,513 30,741 19,157 5,658 28,964 6,534 3,474 8,436 10,549 62.6 71.8
Kwangju 51,727 12,174 4,115 6,247 18,373 3,470 1,946 1,516 3,886 41.9 62.5
Daejeon 50,277 13,466 4,092 4,683 15,145 4,429 1,722 1,055 5,685 49.4 66.6
Ulsan 42,370 14,002 4,300 1,888 10,054 2,285 1,799 850 7,192 61.4 69.4
Saejong 7,606 2,534 845 1,540 1,616 - - 95 976 47.6 77.3
Kyungi 102,961 132,107 51,285 22,502 102,446 14,558 17,132 7,907 55,024 61.2 71.5
Kangwon 108,044 13,129 7,398 33,219 38,622 3,698 1,229 1,346 9,403 22.2 68.3'''.replace(',','').split()
dim2 = np.reshape(list,(10,12))
budget = pd.DataFrame(dim2[:,1], index=dim2[:,0], columns=['Budget']).astype(float)
# GDP of each city (Unit : 10,000 Won )
list = """ Seoul Busan Daegu Incheon Kwangju Daejeon Ulsan Kyungi Kangwon Saejong 
1,752 1,505 1,431 1,380 1,405 1,489 1,031 1,429 1,288 1,339
""".replace(",","").split()

gdp = pd.DataFrame(list[10:], index=list[0:10], columns=['GDP']).astype(float)
# Combine Large Apartment ratio , City Budget and GDP
Combined = Chart3.join(budget).join(gdp).dropna()
Combined = Combined[['Large(Ratio)','Budget','GDP']].sort(['GDP'])
# Table and Graph
print(Combined)
Combined.plot(kind='bar',subplots=True, figsize=(8,10))
# Define and modify the function correlation

def plot_correlation( ds, x, y, c, ylim=(0,500000) ):
    plt.xlim(.35,.65)
    plt.ylim(ylim[0],ylim[1])
    plt.scatter(ds[x], ds[y], alpha=0.6, s=50, c=c) 
    for abc, row in ds.iterrows():
        plt.text(row[x], row[y],abc )
    plt.xlabel(x)
    plt.ylabel(y)
    # Correlation 
    trend_variable = np.poly1d(np.polyfit(ds[x], ds[y], 1))
    trendx = np.linspace(0.3, 0.62, 4)
    plt.plot(trendx, trend_variable(trendx), color=c) 
    r = sp.stats.pearsonr(ds[x],ds[y])
    plt.text(trendx[3], trend_variable(trendx[3]),'r={:.3f}'.format(r[0]), color = c )
    plt.tight_layout()
import scipy as sp
from scipy import stats  
# Budget and GDP Relationship with Big Apartment

plot01 = plt.figure(figsize=(10,5))
plt.subplot(1, 2, 1)
plot_correlation( Combined,'Large(Ratio)' , 'Budget', 'b' )
plot02 = plt.figure(figsize=(10,5))
plt.subplot(1, 2, 1)
plot_correlation( Combined, 'Large(Ratio)', 'GDP', 'b' , ylim=(1200,2000))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1563661255622922, 'fraction_numerical': 0.0785253275963231, 'mean_word_length': 5.5098663271801405, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 36, 'https://': 2, 'lorem ipsum': 0, 'www.': 5, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '131558', 'license': 'MIT', 'n_tokens_mistral': 4111, 'n_tokens_neox': 3472, 'n_words': 989, 'path': 'MBA_S16/Kim-Lee - Kor_Apt01.ipynb', 'repo_name': 'NYUDataBootcamp/Projects', 'size': '10262'}