import os
import os.path
import datetime
import pandas as pd
# Download CSV files
def download_csv(filenames):
for filename in filenames:
print("download {filename}...".format(filename=filename))
os.system("curl http://www.city.sendai.jp/kikaku/seisaku/toukei/jinkou/kakusai/{filename} -o data/{filename}".format(filename=filename))
print("Done!")
# download_filenames = ["h2004.xls", "h2104.xls", "h2204.xls" , "h2304.xls","h2404.xls", "h2504.xls", "h2604.xls"]
download_filenames = ["h2004.xls"]
download_csv(download_filenames)
download h2004.xls... Done!
def generate_df(filepath):
print("{datetime} {filepath}".format(datetime=datetime.datetime.now(), filepath=filepath))
filename = os.path.basename(filepath)
xls = pd.ExcelFile(filepath)
# 全ての人口統計を入れるデータフレーム
df_all = pd.DataFrame(columns=['year', 'month', 'ward', 'town', 'gender', 'age', 'head_count'])
print " Sheet -> ",
for sheet_index_num, sheet_name in enumerate(xls.sheet_names):
# 『合計』のシートは除く
if u'合計' in sheet_name or u'計' in sheet_name or u'総数' in sheet_name:
continue
print sheet_name,
df_xls= xls.parse(xls.sheet_names[sheet_index_num], skiprows=3)
heisei_year = filename[1:3]
df_xls['year'] = int(heisei_year) + 1988
df_xls['month'] = int(filename[3:5])
# ward と town をクレンジングして追加
df_xls['ward'] = df_xls.iloc[:,0].fillna(method='ffill').apply(lambda x : x.strip())
df_xls['town'] = df_xls.iloc[:,1].fillna(method='ffill').apply(lambda x : x.strip())
# 『町名』列にある不要な『合計』行を削除
df_xls = df_xls[(df_xls['town'] != u'合計') & (df_xls['town'] != u'計') & (df_xls['town'] != u'総数') ]
# 性別を振り分け
gender = None
if u'男' in sheet_name:
gender = '男'
if u'女' in sheet_name:
gender = '男'
df_xls['gender'] = gender
df_xls.head()
# 年齢毎に処理
for age in range(0, 101):
df_xls['age'] = age
df_xls['head_count'] = df_xls.iloc[:, age + 3].apply(lambda x: x if isinstance(x, int) else None).fillna(method='ffill')
df_all= pd.concat([df_all, df_xls.iloc[:, 104:]])
print("Done!")
return df_all
def get_df_all_demographic(filenames, dirpath = 'data/'):
df = pd.DataFrame()
dirpath = 'data/'
print("Convert {num} files...".format(num=len(filenames)))
for filename in filenames:
filepath = dirpath + filename
df = pd.concat([df, generate_df(filepath)])
return df
# filenames = ["h2004.xls", "h2104.xls", "h2204.xls" , "h2304.xls","h2404.xls", "h2504.xls", "h2604.xls"]
# NOTE: h20~h23のcsvのデータ形式が異なるので除外
filenames = ["h2404.xls", "h2504.xls", "h2604.xls"]
# データ読み込み
df_demographic = get_df_all_demographic(filenames)
df_demographic.info()
df_demographic
Convert 3 files... 2014-06-25 17:06:56.678466 data/h2404.xls Sheet -> 青葉区(男) 青葉区(女) 宮城野区(男) 宮城野区(女) 若林区(男) 若林区(女) 太白区(男) 太白区(女) 泉区(男) 泉区(女) Done! 2014-06-25 17:07:24.323563 data/h2504.xls Sheet -> 青葉区(男) 青葉区(女) 宮城野区(男) 宮城野区(女) 若林区(男) 若林区(女) 太白区(男) 太白区(女) 泉区(男) 泉区(女) Done! 2014-06-25 17:08:01.308072 data/h2604.xls Sheet -> 青葉区(男) 青葉区(女) 宮城野区(男) 宮城野区(女) 若林区(男) 若林区(女) 太白区(男) 太白区(女) 泉区(男) 泉区(女) Done! <class 'pandas.core.frame.DataFrame'> Int64Index: 567620 entries, 0 to 172 Data columns (total 7 columns): year 567620 non-null float64 month 567620 non-null float64 ward 567620 non-null object town 567620 non-null object gender 567620 non-null object age 567620 non-null float64 head_count 567620 non-null float64 dtypes: float64(4), object(3)
year | month | ward | town | gender | age | head_count | |
---|---|---|---|---|---|---|---|
0 | 2012 | 4 | 青葉区 | 青葉町 | 男 | 0 | 2 |
1 | 2012 | 4 | 青葉区 | 赤坂1丁目 | 男 | 0 | 2 |
2 | 2012 | 4 | 青葉区 | 赤坂2丁目 | 男 | 0 | 5 |
3 | 2012 | 4 | 青葉区 | 赤坂3丁目 | 男 | 0 | 1 |
4 | 2012 | 4 | 青葉区 | あけぼの町 | 男 | 0 | 6 |
5 | 2012 | 4 | 青葉区 | 旭ケ丘1丁目 | 男 | 0 | 4 |
6 | 2012 | 4 | 青葉区 | 旭ケ丘2丁目 | 男 | 0 | 10 |
7 | 2012 | 4 | 青葉区 | 旭ケ丘3丁目 | 男 | 0 | 5 |
8 | 2012 | 4 | 青葉区 | 旭ケ丘4丁目 | 男 | 0 | 4 |
9 | 2012 | 4 | 青葉区 | 愛子中央1丁目 | 男 | 0 | 1 |
10 | 2012 | 4 | 青葉区 | 愛子中央2丁目 | 男 | 0 | 3 |
11 | 2012 | 4 | 青葉区 | 愛子中央3丁目 | 男 | 0 | 3 |
12 | 2012 | 4 | 青葉区 | 愛子中央4丁目 | 男 | 0 | 0 |
13 | 2012 | 4 | 青葉区 | 愛子中央5丁目 | 男 | 0 | 5 |
14 | 2012 | 4 | 青葉区 | 愛子中央6丁目 | 男 | 0 | 0 |
15 | 2012 | 4 | 青葉区 | 愛子東1丁目 | 男 | 0 | 5 |
16 | 2012 | 4 | 青葉区 | 愛子東2丁目 | 男 | 0 | 1 |
17 | 2012 | 4 | 青葉区 | 愛子東3丁目 | 男 | 0 | 9 |
18 | 2012 | 4 | 青葉区 | 愛子東4丁目 | 男 | 0 | 8 |
19 | 2012 | 4 | 青葉区 | 愛子東5丁目 | 男 | 0 | 9 |
20 | 2012 | 4 | 青葉区 | 愛子東6丁目 | 男 | 0 | 3 |
21 | 2012 | 4 | 青葉区 | 荒巻 | 男 | 0 | 7 |
22 | 2012 | 4 | 青葉区 | 荒巻神明町 | 男 | 0 | 7 |
23 | 2012 | 4 | 青葉区 | 荒巻中央 | 男 | 0 | 7 |
24 | 2012 | 4 | 青葉区 | 荒巻本沢1丁目 | 男 | 0 | 3 |
25 | 2012 | 4 | 青葉区 | 荒巻本沢2丁目 | 男 | 0 | 1 |
26 | 2012 | 4 | 青葉区 | 荒巻本沢3丁目 | 男 | 0 | 4 |
27 | 2012 | 4 | 青葉区 | 一番町1丁目 | 男 | 0 | 11 |
28 | 2012 | 4 | 青葉区 | 一番町2丁目 | 男 | 0 | 0 |
29 | 2012 | 4 | 青葉区 | 一番町3丁目 | 男 | 0 | 0 |
... | ... | ... | ... | ... | ... | ... | ... |
143 | 2014 | 4 | 泉区 | 南中山2丁目 | 男 | 100 | 0 |
144 | 2014 | 4 | 泉区 | 南中山3丁目 | 男 | 100 | 0 |
145 | 2014 | 4 | 泉区 | 南中山4丁目 | 男 | 100 | 0 |
146 | 2014 | 4 | 泉区 | 南中山5丁目 | 男 | 100 | 0 |
147 | 2014 | 4 | 泉区 | 南中山6丁目 | 男 | 100 | 0 |
148 | 2014 | 4 | 泉区 | 紫山1丁目 | 男 | 100 | 0 |
149 | 2014 | 4 | 泉区 | 紫山2丁目 | 男 | 100 | 0 |
150 | 2014 | 4 | 泉区 | 紫山3丁目 | 男 | 100 | 0 |
151 | 2014 | 4 | 泉区 | 紫山4丁目 | 男 | 100 | 0 |
152 | 2014 | 4 | 泉区 | 紫山5丁目 | 男 | 100 | 0 |
153 | 2014 | 4 | 泉区 | 八乙女1丁目 | 男 | 100 | 0 |
154 | 2014 | 4 | 泉区 | 八乙女2丁目 | 男 | 100 | 0 |
155 | 2014 | 4 | 泉区 | 八乙女3丁目 | 男 | 100 | 1 |
156 | 2014 | 4 | 泉区 | 八乙女4丁目 | 男 | 100 | 0 |
157 | 2014 | 4 | 泉区 | 八乙女中央1丁目 | 男 | 100 | 0 |
158 | 2014 | 4 | 泉区 | 八乙女中央2丁目 | 男 | 100 | 0 |
159 | 2014 | 4 | 泉区 | 八乙女中央3丁目 | 男 | 100 | 0 |
160 | 2014 | 4 | 泉区 | 八乙女中央4丁目 | 男 | 100 | 0 |
161 | 2014 | 4 | 泉区 | 八乙女中央5丁目 | 男 | 100 | 0 |
162 | 2014 | 4 | 泉区 | 館1丁目 | 男 | 100 | 0 |
163 | 2014 | 4 | 泉区 | 館2丁目 | 男 | 100 | 0 |
164 | 2014 | 4 | 泉区 | 館3丁目 | 男 | 100 | 0 |
165 | 2014 | 4 | 泉区 | 館4丁目 | 男 | 100 | 0 |
166 | 2014 | 4 | 泉区 | 館5丁目 | 男 | 100 | 0 |
167 | 2014 | 4 | 泉区 | 館6丁目 @2 | 男 | 100 | 0 |
168 | 2014 | 4 | 泉区 | 館7丁目 *2 | 男 | 100 | 0 |
169 | 2014 | 4 | 泉区 | 山の寺1丁目 | 男 | 100 | 1 |
170 | 2014 | 4 | 泉区 | 山の寺2丁目 | 男 | 100 | 0 |
171 | 2014 | 4 | 泉区 | 山の寺3丁目 | 男 | 100 | 0 |
172 | 2014 | 4 | 泉区 | 友愛町 | 男 | 100 | 0 |
567620 rows × 7 columns
# 読み込んだデータをCSVに書き出し
df_demographic.to_csv('data/all_demographic.csv', index=False, encoding='UTF-8')
%ls -l data/all_demographic.csv
-rw-r--r-- 4 cortyuming staff 28724549 Jun 25 17:09 data/all_demographic.csv
# 2014年4月の仙台の総人口
df_demographic[df_demographic.year == 2014].head_count.sum()
1065377.0
df_work = pd.DataFrame(columns=['year', 'group', 'ward', 'head_count'], index=range(len(df_demographic.ward.unique())))
df_work
year | group | ward | head_count | |
---|---|---|---|---|
0 | NaN | NaN | NaN | NaN |
1 | NaN | NaN | NaN | NaN |
2 | NaN | NaN | NaN | NaN |
3 | NaN | NaN | NaN | NaN |
4 | NaN | NaN | NaN | NaN |
latest_year = int(df_demographic.year.unique().max())
df_work['ward'] = df_demographic[df_demographic.year == latest_year].groupby(['ward'])['head_count'].sum().index
df_work['group'] = u'中学生'
df_work
year | group | ward | head_count | |
---|---|---|---|---|
0 | NaN | 中学生 | 太白区 | NaN |
1 | NaN | 中学生 | 宮城野区 | NaN |
2 | NaN | 中学生 | 泉区 | NaN |
3 | NaN | 中学生 | 若林区 | NaN |
4 | NaN | 中学生 | 青葉区 | NaN |
# 最新年から10年間
df = pd.DataFrame()
for i in range(10):
df_work.year = latest_year + i
df_work.head_count = df_demographic[(df_demographic.year == latest_year) & (12 - i <= df_demographic.age) & (df_demographic.age <= 12 + 2 - i)].groupby(['ward'])['head_count'].sum().values
df = pd.concat([df, df_work])
df
year | group | ward | head_count | |
---|---|---|---|---|
0 | 2014 | 中学生 | 太白区 | 6367 |
1 | 2014 | 中学生 | 宮城野区 | 5097 |
2 | 2014 | 中学生 | 泉区 | 6696 |
3 | 2014 | 中学生 | 若林区 | 3508 |
4 | 2014 | 中学生 | 青葉区 | 7443 |
0 | 2015 | 中学生 | 太白区 | 6344 |
1 | 2015 | 中学生 | 宮城野区 | 5122 |
2 | 2015 | 中学生 | 泉区 | 6505 |
3 | 2015 | 中学生 | 若林区 | 3427 |
4 | 2015 | 中学生 | 青葉区 | 7440 |
0 | 2016 | 中学生 | 太白区 | 6336 |
1 | 2016 | 中学生 | 宮城野区 | 5102 |
2 | 2016 | 中学生 | 泉区 | 6422 |
3 | 2016 | 中学生 | 若林区 | 3372 |
4 | 2016 | 中学生 | 青葉区 | 7335 |
0 | 2017 | 中学生 | 太白区 | 6142 |
1 | 2017 | 中学生 | 宮城野区 | 5075 |
2 | 2017 | 中学生 | 泉区 | 6198 |
3 | 2017 | 中学生 | 若林区 | 3264 |
4 | 2017 | 中学生 | 青葉区 | 7223 |
0 | 2018 | 中学生 | 太白区 | 5982 |
1 | 2018 | 中学生 | 宮城野区 | 5019 |
2 | 2018 | 中学生 | 泉区 | 6085 |
3 | 2018 | 中学生 | 若林区 | 3160 |
4 | 2018 | 中学生 | 青葉区 | 6956 |
0 | 2019 | 中学生 | 太白区 | 5878 |
1 | 2019 | 中学生 | 宮城野区 | 4955 |
2 | 2019 | 中学生 | 泉区 | 5917 |
3 | 2019 | 中学生 | 若林区 | 3160 |
4 | 2019 | 中学生 | 青葉区 | 7002 |
0 | 2020 | 中学生 | 太白区 | 5971 |
1 | 2020 | 中学生 | 宮城野区 | 5056 |
2 | 2020 | 中学生 | 泉区 | 5879 |
3 | 2020 | 中学生 | 若林区 | 3261 |
4 | 2020 | 中学生 | 青葉区 | 7109 |
0 | 2021 | 中学生 | 太白区 | 6104 |
1 | 2021 | 中学生 | 宮城野区 | 5270 |
2 | 2021 | 中学生 | 泉区 | 5916 |
3 | 2021 | 中学生 | 若林区 | 3476 |
4 | 2021 | 中学生 | 青葉区 | 7251 |
0 | 2022 | 中学生 | 太白区 | 6169 |
1 | 2022 | 中学生 | 宮城野区 | 5480 |
2 | 2022 | 中学生 | 泉区 | 5821 |
3 | 2022 | 中学生 | 若林区 | 3464 |
4 | 2022 | 中学生 | 青葉区 | 7189 |
0 | 2023 | 中学生 | 太白区 | 6134 |
1 | 2023 | 中学生 | 宮城野区 | 5653 |
2 | 2023 | 中学生 | 泉区 | 5762 |
3 | 2023 | 中学生 | 若林区 | 3487 |
4 | 2023 | 中学生 | 青葉区 | 7186 |
df_pivot = pd.pivot_table(df, values='head_count', index='year', columns='ward', aggfunc=sum, fill_value=0)
df_pivot
ward | 太白区 | 宮城野区 | 泉区 | 若林区 | 青葉区 |
---|---|---|---|---|---|
year | |||||
2014 | 6367 | 5097 | 6696 | 3508 | 7443 |
2015 | 6344 | 5122 | 6505 | 3427 | 7440 |
2016 | 6336 | 5102 | 6422 | 3372 | 7335 |
2017 | 6142 | 5075 | 6198 | 3264 | 7223 |
2018 | 5982 | 5019 | 6085 | 3160 | 6956 |
2019 | 5878 | 4955 | 5917 | 3160 | 7002 |
2020 | 5971 | 5056 | 5879 | 3261 | 7109 |
2021 | 6104 | 5270 | 5916 | 3476 | 7251 |
2022 | 6169 | 5480 | 5821 | 3464 | 7189 |
2023 | 6134 | 5653 | 5762 | 3487 | 7186 |
df_pivot.plot()
prop_mac_jp = {'fname' : r'/Library/Fonts/Osaka.ttf'}
plt.legend(prop=prop_mac_jp, loc='lower left')
<matplotlib.legend.Legend at 0x1067a5890>