エクセルをそのままcsvにしたファイルを正規化する。

[仙台中央卸売市場](http://www.city.sendai.jp/chuo-kanri/kurashi/shizen/nogyo/nosanbutsu/oroshiuri/index.html)のデータがようやくcsvで提供されるようになった。

でも

```text:seika.csv
仙台市中央卸売市場日報（青果部）,,,,,,,,,,,,,,,,,,,,,,,,,,,,
令和3年1月13日 （水）  天気(晴れ),,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,仙台市中央卸売市場,,,,,,,,,,,,,,,,,
販売量,（１）野菜,310 ｔ,,,,,,,,,ＴＥＬ　（０２２）２３２−８１２１〜２,,,,,,,,,,,,,,,,,
,（２）果実,94 ｔ,,,,,,,,,ＦＡＸ　（０２２）２３２−８１４４,,,,,,,,,,,,,,,,,
総販売量,404 ,ｔ,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
主要品目別相場表（１）野菜,,,,,,,,,,,,,,,,,,,,,,,,,,,,
品名,産地,単位,,高値,中値,安値,品名,産地,単位,,高値,中値,安値,,,,,,,,,,,,,,,
だいこん　L,千葉,10,Kg,"1,728","1,512","1,296",トマト　M,熊本,4,Kg,"1,404",−     ,"1,296",,,,,,,,,,,,,,,
だいこん　L,神奈川,10,Kg,"1,728",−     ,"1,296",ミニトマト　M,千葉,200,g,140,−     ,108,,,,,,,,,,,,,,,
かぶ　3L,千葉,1,束,−     ,194,−     ,ミニトマト　M,熊本,3,Kg,"1,512",−     ,"1,296",,,,,,,,,,,,,,,
かぶ　LL,千葉,1,束,216,173,86,ピーマン　M,茨城,150,g,108,97,92,,,,,,,,,,,,,,,
にんじん　M,茨城,10,Kg,"1,944","1,836","1,728",ピ−マン　M,高知,150,g,108,97,86,,,,,,,,,,,,,,,
にんじん　M,千葉,10,Kg,"1,944","1,728","1,512",さやえんどう  A,鹿児島,1,Kg,"1,944",−     ,"1,620",,,,,,,,,,,,,,,
ごぼう　M,青森,4,Kg,"1,620","1,404",756,さつまいも　L,茨城,5,Kg,−     ,"1,512",−     ,,,,,,,,,,,,,,,
はくさい,茨城,15,Kg,756,432,432,さつまいも　L,千葉,5,Kg,−     ,"1,512",−     ,,,,,,,,,,,,,,,
みずな,宮城,150,g,−     ,86,−     ,ばれいしょ　L,北海道,10,Kg,"1,728",−     ,"1,620",,,,,,,,,,,,,,,
みずな,茨城,200,g,−     ,86,−     ,メークイン　L,北海道,10,Kg,−     ,"2,376",−     ,,,,,,,,,,,,,,,
小松菜,宮城,250,g,108,86,86,里いも　LL,新潟,5,Kg,"3,240","1,620",540,,,,,,,,,,,,,,,
キャベツ　L,千葉,10,Kg,−     ,"1,296",−     ,長いも　LL,青森,10,Kg,"3,240","2,700","2,160",,,,,,,,,,,,,,,
キャベツ　L,愛知,10,Kg,"1,404","1,296","1,080",たまねぎ　L,北海道,20,Kg,"1,728",−     ,"1,620",,,,,,,,,,,,,,,
ほうれん草,宮城,200,g,162,119,97,生しいたけ,宮城,100,g,162,130,108,,,,,,,,,,,,,,,
小ねぎ,宮城,100,g,130,108,97,なめこ　S,宮城,100,g,54,−     ,43,,,,,,,,,,,,,,,
長ねぎ　L,宮城,5,Kg,"2,700","2,700","2,484",なめこ　S,山形,100,g,−     ,65,−     ,,,,,,,,,,,,,,,
長ねぎ　L,埼玉,5,Kg,"4,320","3,780","3,456",えのき,宮城,100,g,−     ,38,−     ,,,,,,,,,,,,,,,
曲がりねぎ,宮城,4,Kg,"1,620",−     ,"1,404",えのき,宮城,200,g,77,77,65,,,,,,,,,,,,,,,
あさつき,福島,4,Kg,−     ,"2,160",−     ,えのき,新潟,200,g,70,−     ,65,,,,,,,,,,,,,,,
糸みつば,宮城,60,g,−     ,130,−     ,ぶなしめじ,宮城,100,g,97,−     ,86,,,,,,,,,,,,,,,
糸みつば,宮城,100,g,108,−     ,76,ぶなしめじ,新潟,170,g,130,−     ,108,,,,,,,,,,,,,,,
しゅんぎく　M,宮城,150,g,162,162,140,鶏卵  AM,岩手,10,Kg,"1,728",−     ,"1,620",,,,,,,,,,,,,,,
せり　M,宮城,100,g,216,162,162,,,,,,,,,,,,,,,,,,,,,,
にら　L,福島,100,g,108,108,86,,,,,,,,,,,,,,,,,,,,,,
にら　M,高知,100,g,−     ,130,−     ,,,,,,,,,,,,,,,,,,,,,,
セルリ　ＬＬ,愛知,10,Kg,"2,376",−     ,"2,160",,,,,,,,,,,,,,,,,,,,,,
レタス　L,香川,10,Kg,"3,348",−     ,"3,240",,,,,,,,,,,,,,,,,,,,,,
チンゲンサイ,宮城,250,g,108,65,65,,,,,,,,,,,,,,,,,,,,,,
ゆきな,宮城,200,g,−     ,76,−     ,,,,,,,,,,,,,,,,,,,,,,
ゆきな,宮城,250,g,97,−     ,76,,,,,,,,,,,,,,,,,,,,,,
きゅうり　M,高知,5,Kg,"2,160","2,160","1,944",,,,,,,,,,,,,,,,,,,,,,
きゅうり　S,宮崎,5,Kg,"2,376",−     ,"2,160",,,,,,,,,,,,,,,,,,,,,,
かぼちゃ　７玉,メキシコ,10,Kg,"2,160",−     ,"1,728",,,,,,,,,,,,,,,,,,,,,,
なす　M,高知,5,Kg,"2,916","2,160","1,944",,,,,,,,,,,,,,,,,,,,,,
トマト　M,宮城,4,Kg,"1,296",−     ,"1,080",,,,,,,,,,,,,,,,,,,,,,
（２）果実,,,,,,,市況,,,,,,,,,,,,,,,,,,,,,
品名,産地,単位,,高値,中値,安値,野菜,,,,,,,,,,,,,,,,,,,,,
レモン　140玉,アメリカ,17,Kg,"7,020",−     ,"6,480",野菜総販売数量201ｔ減,,,,,,,,,,,,,,,,,,,,,
ｸﾞﾚ-ﾌﾟﾌﾙｰﾂ   36玉,アメリカ,17,Kg,"3,780",−     ,"3,240",  せり　M,,,小幅安,,,,,,,,,,,,,,,,,,
デコポン　18玉,和歌山,5,Kg,"3,564","3,240","3,024",  さやえんどう  A,,,１〜２割安,,,,,,,,,,,,,,,,,,
もういっこ,宮城,270,g,432,378,356,,,,,,,,,,,,,,,,,,,,,,
とちおとめ,宮城,270,g,410,410,378,,,,,,,,,,,,,,,,,,,,,,
アールスメロン　6玉,高知,9,Kg,−     ,"9,720",−     ,,,,,,,,,,,,,,,,,,,,,,
バナナ,フｲリピン,13,Kg,"3,024",−     ,"2,700",,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,  他全般的に保合,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,果実,,,,,,,,,,,,,,,,,,,,,
,,,,,,,果実総販売数量62ｔ減,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,  全般的に保合,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
```

とこの![エクセルpdf.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/b50cfc3f-b164-f279-2eb9-6e86d3d3f742.png)
エクセルファイルをそのままCSVに出力されたと思われるひどいもの・・・

なにもできなのでとりあえず正規化してみた。

#正規化
```python:processing_csv.py
import re, datetime
import pandas as pd
# from IPython.display import display

url='http://kei008220.webcrow.jp/seika.csv'

r=pd.read_csv(url,encoding='Shift-JIS', header=None)
#pd.set_option('display.max_rows', 100)

#確認
r=r.dropna(how="all", axis=1)
#display(r)



#見た目で入れていく
hyoudai=r.iloc[0,0] #表題
nichiji=r.iloc[1,0]   #日時　令和3年1月13日 （水） 天気(晴れ)	をあとで分離
sijou=r.iloc[2,11]   #市場名
hanbairyo_vegetable=r.iloc[3,2] #野菜販売量
hanbairyo_fruit=r.iloc[3,2] #果物販売量、単位(t)はあとで分離したい
hanbairyo_full=r.iloc[5,1]+r.iloc[5,2] #販売量、単位(t)もとりあえずくっつけとく

hinshu_kugiri=r[r[0].str.contains('（\d）', na=False)].index #主要品目別相場表（１）と（２）の行[8,45]
vegetable=r.iloc[hinshu_kugiri[0]+1:hinshu_kugiri[1],:]
fruit=r.iloc[hinshu_kugiri[1]+1:,:].dropna(how="all")

#野菜の表を作成
veg1=vegetable.iloc[:,:7]
veg2=vegetable.iloc[:,7:]
veg2.columns=[0,1,2,3,4,5,6]
veg=pd.concat([veg1,veg2[1:]],ignore_index=True).dropna(how="all") #横に２列になっていたのを、縦に結合。そのさいindexを付け替えている。
veg[2]=veg[2].str.cat(veg[3], na_rep='')  #単位が2列になっていたのでくっつける
veg=veg.drop(columns=3,axis=1)  #くっつけたあと、不要な列を削除
veg.columns=veg.iloc[0,:]  #列名を指定
veg=veg.iloc[1:,:] #列名に使った行を削除
veg['種類']='野菜'  #野菜だけじゃないので列を追加 
#display(veg)

#果物の表を作成
fru=fruit.iloc[:,:7]
fru=fru.dropna(how="all", axis=0)
fru.iloc[:,2]=fru.iloc[:,2].str.cat(fru.iloc[:,3], na_rep='')
fru=fru.drop(columns=3,axis=1)
fru.columns=fru.iloc[0,:]
fru=fru.iloc[1:,:]
fru['種類']='果物'

#display(fru)

#結合
seika=pd.concat([veg,fru], ignore_index=True)
for i in ['高値','中値','安値']:
  seika[i]=seika[i].str.replace(',','')

seika['日付']=nichiji.split()[0]
seika['天気']=re.search(r'(?<=天気.)[^\)]+',nichiji).group()

#令和→西暦

wareki=re.search(r'(\d+)\D+(\d+)\D+(\d+)',nichiji.split()[0]).groups()
year=str(2000+int(wareki[0])+18)
month=wareki[1]
day=wareki[2]
#print(f'{year}/{month}/{day}')
#日付変換
seika['date']=datetime.datetime.strptime(year+'/'+month+'/'+day,'%Y/%m/%d')

#csv出力
filename="seika_"+year+month+day+".csv"
header=['product_name','area','unit','high_price','middle_price','low_price','category','wareki','weather','date']
seika.to_csv(filename,header=header,index=None,encoding='UTF-8')
```

##結果

index|product_name|area|unit|high_price|middle_price|low_price|category|wareki|weather|date
--:|:-:|:-:|:-:|--:|--:|--:|--:|:-:|:-:|:-:|
0|だいこん　L|千葉|10Kg|1728|1512|1296|野菜|令和3年1月13日|晴れ|2021-01-13
1|だいこん　L|神奈川|10Kg|1728|−|1296|野菜|令和3年1月13日|晴れ|2021-01-13
2|かぶ　3L|千葉|1束|−|194|−|野菜|令和3年1月13日|晴れ|2021-01-13
3|かぶ　LL|千葉|1束|216|173|86|野菜|令和3年1月13日|晴れ|2021-01-13
4|にんじん　M|茨城|10Kg|1944|1836|1728|野菜|令和3年1月13日|晴れ|2021-01-13
...|...|...|...|...|...|...|...|...|...|...
59|デコポン　18玉|和歌山|5Kg|3564|3240|3024|果物|令和3年1月13日|晴れ|2021-01-13
60|もういっこ|宮城|270g|432|378|356|果物|令和3年1月13日|晴れ|2021-01-13
61|とちおとめ|宮城|270g|410|410|378|果物|令和3年1月13日|晴れ|2021-01-13
62|アールスメロン　6玉|高知|9Kg|−|9720|−|果物|令和3年1月13日|晴れ|2021-01-13
63|バナナ|フｲリピン|13Kg|3024|−|2700|果物|令和3年1月13日|晴れ|2021-01-13

#解説
- 基本的に力技
- セルがマージされているところは左端の列になってくれるので、一旦列名を番号で読み込み
- データーフレームを`iloc`で指定して、各項目を読み込み
- `dropna()`で行が全て`NaN`になっているところを削除
- データフレームを縦に結合する時は`concat()`
    - 列名を合わせた方がいいので、`columns=[]`で変更している。
- colaboratoryでやっていることもあって、和暦変換する`japanera`が使えなかったので`re.search()`で数字を取得して、西暦等に変換
    - 令和から西暦は_+18_とのこと。
- csvに出力する時、Splunkに取り込むことを考えてヘッダーを英文に変更した。
    - これで`INDEXED_EXTRACTIONS=csv`が動いてくれるはず・・・

#pandasからの脱却
GCPやlambdaもpandas使えるか微妙だったので作り直してみた。

```python:no_pandas.py
import re, datetime
import urllib.request

# 仙台市中央市場　野菜果実のデータ
url='http://kei008220.webcrow.jp/seika.csv'

#　データ取り込み、文字コードがShift-jisなのでデコード

req = urllib.request.Request(url)
with urllib.request.urlopen(req) as response:
   file = response.read().decode('cp932')

#全角スペース置換
lst=file.replace('　',' ')
#金額正規化
lst2=re.sub(r"\"(\d+),(\d+)\"", r"\1\2", lst)
#最後の空白セルを正規表現で削除
lst2=re.sub(r',{15}\r\n','\r\n',lst2)
#行で改行し、csvで配列化
r = [i.split(",") for i in lst2.splitlines()]  
#最後にたくさんの空白があるので削除するつもりだったが正規表現で削除したので[:15]を除いた
l =[ r[i] for i in range(len(r)) if max(r[i])]
#行を数えて、表示
rows=sum(1 for _ in l)
#for i in range(rows):
#    print(f'行{i}: {l[i]}')

#　データ項目の抽出
wareki=l[1][0].split()[0]
weather=re.search(r'(?<=天気.)[^\)]+',l[1][0]).group()

#令和→西暦
wareki_tmp=re.search(r'(\d+)\D+(\d+)\D+(\d+)',wareki).groups()
year=str(2000+int(wareki_tmp[0])+18)
month=wareki_tmp[1].zfill(2)
day=wareki_tmp[2]
#print(f'{year}/{month}/{day}')
#日付変換
date_tmp=datetime.datetime.strptime(year+'/'+month+'/'+day,'%Y/%m/%d')
date=datetime.datetime.strftime(date_tmp,"%Y-%m-%d")

#　野菜と果物のテーブル　
# searchで文字列を検索して行番号を出している。
vegitable_row=''
furit_row=''
for i,v in [[i,re.search('野菜',v[0])]  for i,v in enumerate(l)]:
    if v:
        vegitable_row=i
for i,v in [[i,re.search('果実',v[0])]  for i,v in enumerate(l)]:
    if v:
        fruit_row=i
vegitable_lst=[]
# スライスの仕方は確認したらこれで大丈夫だった。
for i in range(vegitable_row+2,fruit_row):
    vegitable_lst.append(l[i][:7])
for i in range(vegitable_row+2,fruit_row):
    vegitable_lst.append(l[i][:8])
fruit_lst=[]
for i in range(fruit_row+2,rows):
    if max(l[i][:7]):
        fruit_lst.append(l[i][:7])

# ヘッダーも含め再構成
for i,v in enumerate(vegitable_lst):
    vegitable_lst[i]=[v[0],v[1],v[2]+v[3],v[4],v[5],v[6],'野菜',wareki,weather,date,'\r\n']
for i,v in enumerate(fruit_lst):
    fruit_lst[i]=[v[0],v[1],v[2]+v[3],v[4],v[5],v[6],'果実',wareki,weather,date,'\r\n']
header=[['product_name'],['area'],['unit'],['high_price'],['middle_price'],['low_price'],['category'],['wareki'],['weather'],['date'],['\r\n']]
csvfile_lst=header+vegitable_lst+fruit_lst
# できたリストの行数を確認
csv_rows=sum(1 for _ in csvfile_lst)
#　リストを展開したのち１行のテキストファイルに変換
csvtmp=[]
for i in range(csv_rows):
    csvtmp.append(','.join(csvfile_lst[i]))
csvfile=','.join(i for i in csvtmp)
# 余計な「,」等を削除
csvfile=csvfile.replace(',\r\n,','\r\n').replace('－     ','－').replace(',\r\n','')

#書き込み
filename='seika_'+date+'.csv'

with open(filename, 'w', encoding='utf-8') as w:
    w.write(csvfile)
```

colaboratoryで確認。
CSVを扱っているはずなのに、`import csv`していない:sweat:

##解説
- `file = response.read().decode('cp932')`した段階で`file`は１行の文字列になっている。それもあって`csv.reader()`が使い物にならなかった。
    - 素直にファイル出力すればそんなことはないと思われます。
-　テキストならということでできる正規化は`re.sub()`で実施
-　`splitlines()`でリスト化したら、なんか形が見えてきた。
-　あとは力技
-　最後の出力も一つの文字列をそのまま書き込んでいる


#まとめ
もともとpdfしかなかったので、CSVをあげてくれるだけでも進歩したと思いたい:sweat:

[仙台市オープンデータポータル](http://www.city.sendai.jp/joho-kikaku/shise/security/kokai/dataportal.html)にも載っていないし、気にしていないんだろうな〜と思ったり。

ここまで加工してしまえば、可視化はなんでも大丈夫だと思います。