LoginSignup
1
1

More than 3 years have passed since last update.

@toshikawa(川俣 利久)

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

Last updated at Posted at 2021-01-14

仙台中央卸売市場のデータがようやくcsvで提供されるようになった。

でも

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
エクセルファイルをそのままCSVに出力されたと思われるひどいもの・・・

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

正規化

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使えるか微妙だったので作り直してみた。

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:

仙台市オープンデータポータルにも載っていないし、気にしていないんだろうな〜と思ったり。

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

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1