yskma’s blog

日々の作業の備忘録@株式会社ビズオーシャン

ネストのあるjsonデータをpandasでフラットに扱う

ネストの深いデータ

簡単にデータを取り込んで扱えるpandasだが、下記のようにネストが深いと勝手が悪い。

nested.json
{
    col1:{row1:1,row2:x,row3:{id:1,age:20,data:{a:1,b:2}}},
    col2:{row1:2,row2:y,row3:{id:2,age:23,data:{a:3,b:4}}}
}

そのままとりこむとこんな感じ

import pandas as pd

f = open(filepath, 'r')
data = json.load(f)
df = pd.DataFrame(data)
col1 col2
row1 1 2
row2 x y
row3 {id:1,age:20,data:{a:1,b:2}} {id:2,age:23,data:{a:3,b:4}}

データの転置

列ごとに1件とするデータだとわかる。そのままだと扱いづらいので転置する。

df_t = df.T
row1 row2 row3
col1 1 x {'age': '20', 'data': {'b': '2', 'a': '1'}, 'id': '1'}
col2 2 y {'age': '23', 'data': {'b': '4', 'a': '3'}, 'id': '2'}

json_normalizeでフラットにする

あとはrow3の要素をそれぞれ取り出して一覧したい。
そんなときはjson_normalizeを利用すると便利。

import pandas as pd
from pandas.io.json import json_normalize

def extract_data(d):
    result = json_normalize(d)
    result = result.iloc[0, :]  # seriesに変換
    return result  # seriesで返すこと

f = open(filepath, 'r')
data = json.load(f)
df = pd.DataFrame(data)
df_t = df.T
df_flatten = pd.concat([df_t, df_t.row3.apply(extract_data)], axis=1)

データフレームに入れるとrow3が辞書型の文字列として取り込まれるので、
作成した関数内でjson_normalizeを使ってデータフレームに変換。
そのままだと元のdf_tに返せないのでseriesに変換して渡す。
pd.concatで元のdf_tとrow3のデータから作ったデータフレームをくっつける。

これでめでたく一つのフラットなデータフレームとして扱える。

row1 row2 row3 age data.a data.b id
col1 1 x {'age': '20', 'data': {'b': '2', 'a': '1'}, 'i... 20 1 2 1
col2 2 y {'age': '23', 'data': {'b': '4', 'a': '3'}, 'i... 23 3 4 2

まだ試していないけれど、もっとネストが深くても同じ要領で操作を繰り返せばなんとかなりそう。

fortigate cliからの管理者追加

fortigate cliからのユーザー追加

現在使用しているUTMのfortigateだが、GUIからだとなぜか管理者追加ができなかったので、CLIから操作する。
参考:Fortinet Docs Library - FortiOS 6.0 CLI Reference

config, edit, setと階層構造になっているので、順番に入力して下層に移動していく。
※実際にconfigファイルを見てみると階層でインデントされているのでわかりやすいと思う。
ちなみに"?"を叩けばコマンドの候補が表示されるので困ったら都度押して調べる。

1.config xxx で移動(今回はsystem admin)
2.新規に追加したいので edit "foo"
3.set bar で各種設定をアカウントに付与していく。
4. endを入力して設定の追加が完了する。
showコマンドやGUIで設定ファイルをみると、下記のように記述が増えていることが確認できる。

config system admin
    edit "youadminnamehere"
      set accprofile "super_admin"   for access users profile 
      set vdom "root"
      set password "pickastrongpassword"
end

gspreadで指定行にまとめて書き込み

googleスプレッドシートにコードからアクセスして指定した行を編集したい。

(設定についてはあちこちで説明されているので割愛)

今回はgspreadを使ってみる。
Referenceを参考にいろいろためす
https://gspread.readthedocs.io/en/latest/

import gspread

scope = ['https://spreadsheets.google.com/feeds']
doc_id = 'xxxxx'
path = os.path.expanduser("./credentials.json")
credentials = ServiceAccountCredentials.from_json_keyfile_name(path, scope)
client = gspread.authorize(credentials)
gfile = client.open_by_key(doc_id)
worksheet = gfile.sheet1

上記で作業対象のシートを指定した後、
更新用のデータを用意してfor文で回してそれぞれをセルごとに更新してもどうにかなるけど、

insert_data = {
1:AAA
3:CCC
}

for k, v in insert_data.items():
    worksheet.update_cell(1, k, v)

これだとセル更新のたびスプレッドシートにアクセスするため更新するセルが多いと完了までに時間がかかる。
そこで

# 100行目の列1~21を取得
cell_list = worksheet.range(100, 1, 100, 21)
# 取得したcell_listを編集
for k, v in insert_data.items():
    cell_list[k - 1].value = v
# 編集したcell_listをシートに反映
worksheet.update_cells(cell_list) 

update_cellsを使うと行単位でまとめて更新できる 。

スプレッドシート上の関数の扱い

シート上に下記のようなデータがあった時、

A1 B1 C1
aaa =A1

そのままだとスプレッドシートに予め入れてあったB1の関数から値しか取得できず(文字列aaaを取得)、行の更新時に文字列aaaとして上書きされるのでB1の式が消えてしまう。

A1 B1 C1
AAA aaa CCC

 
これを避けるには、
データ取得時にvalue_render_option='FORMULA'を指定する。

worksheet.row_values(300, value_render_option='FORMULA')

これでB1のデータとして'=A1'が取得できる。

また、更新時に下記value_input_option='USER_ENTERED'を指定

worksheet.update_cells(cell_list, value_input_option='USER_ENTERED')

これで関数をそのままシートに戻せる。

# cell_list取得
cell_list = worksheet.range(100, 1, 100, 21)

# シート上の対象列のデータを取得
target_row = worksheet.row_values(100, value_render_option='FORMULA')

# シートから取得したデータを編集
for k, v in insert_data.items():
    target_row[k - 1] = v

# cell_listに編集したデータを格納
for i, data in enumerate(target_row):
cell_list[i].value = data

# 更新
worksheet.update_cells(cell_list, value_input_option='USER_ENTERED')
A1 B1 C1
AAA =A1 CCC

うまくいった。

bq loadで正規表現が使えないのでxargsでなんとかしてみる

大量にあるcsvファイルから特定の複数ファイルを対象にコマンドライン上からbig queryにインポートしたい。
bq loadと正規表現でどうにかなるかと思ったが、bq loadはワイルドカードしか使えなかった。正規表現を含めるとエラーになる。
下記ならOK。

bq --location=US load --source_format=CSV --schema=./myschema.json gs://mybucket/dir1/1¥*.csv bqdb.mytable

アスタリスクの前にエスケープ文字を入れないと認識してくれない

100.csv, 101.csv, 102.csv ... 1000.csv, 1001.csv ... 3000.csv

gs上にこんなファイル群があったとして、
1000.csvから1999.csvまでインポートしたい。
ワイルドカードだけだと1*.csvのように指定すると100.csv~199.csvまでも対象になってしまう。
 
そこで、gsutilから対象ファイルをegrepでフィルターしてxargsでbq loadを実行してみる。

gsutil ls gs://mybucket/dir1/\* | egrep '/1[0-9]{3}\.' | xargs -L1 bq --location=US load --source_format=CSV --schema=./myschema.json bqdb.mytable

うまくいった。
ただしbqのjobが大量につくられるのでファイルが多いと大変に時間がかかってしまう。

結局、対象となるファイルをワイルドカードであらかじめ全部big queryにインポートしてから、
bq上でインポートしたデータにクエリー発行して抽出。その結果を別テーブルとして新たに作り直した方がずっと早かった。

備考、というかわかったこと

google strageからbig queryへのインポート時にワイルドカードを使うと、同数のファイルを指定して個別にインポートする時に比べて処理に要する時間が格段に短くなる。

jedi-vimの補完がpandasだと遅い問題

プラグイン管理にdein.vimを利用してjedi-vimを入れて使っているのだが、pandasの自動補完が遅くて辛かったので設定を変更。

 

import pandas as pd

pd.

上記のようにピリオドを入力したときに自動で補完が働くのだが、候補が出てくるのが妙に遅く、ピリオドを打つたびに入力できない状態で待たされて手が止まってしまう。これをどうにかしたい。
あとついでに候補選択時に説明ウィンドウが表示されるのも煩わしいので消したい。

devidhalter/jedi-vimのREADMEを参考にして
dein-vimの設定ファイルdein_lazy.tomlを適当にいじる。
github.com

 
変更前。ただインストールしただけの状態。

dein_lazy.toml

[[plugins]]
repo = 'devidhalter/jedi-vim'
ob_ft = 'python'

変更後
勝手に1番目の候補を入力しないようにする設定もあったのであわせて追加した。

dein_lazy.toml

[[plugins]]
repo = 'devidhalter/jedi-vim'
ob_ft = 'python'
hook_add = '''
    # .入力時に補完候補を表示しない
    let g:jedi#popup_on_dot = 0
    # 勝手に最初の候補を入力しない
    let g:jedi#popup_select_first = 0
    # docstringは表示しない
    autocmd FileType python setlocal completeopt-=preview
'''

あとは必要な時だけCtrl+スペースで補完候補を呼び出すようにする。
ピリオド入力後に数文字入れてから補完すれば、候補が絞られるからか待ち時間が短縮されていた。

pandasでの正規表現の否定

pandasを利用中、下図のようなデータに対してid列の文字列が混ざっている行だけを削除したい場面があった。

id color
1 red
2 orange
3test blue
4 yellow

3行目のidにtestの文字が混ざってしまっている。

contains()で正規表現の否定の記述

これをcontains()でやろうとして少し嵌った。vim上でよくやる[^a-zA-Z]が使えなかったからだ。

df = df[df['id'].str.contains('[^a-zA-Z]', na=False)]
これだとうまくいかない
df = df[~df['id'].str.contains('[a-zA-Z]', na=False)]
これならOK
match()で否定先読み

調べたところ、正規表現を使いたいならmatch()で否定先読みを使うほうがいいかもしれない。

df = df[df['id'].str.match('(?![a-zA-Z])', na=False)]

あと行を除外するためにdrop()でやろうとするとかえって面倒だった。指定したインデックスを削除するものだが、そのインデックスを作成するのが面倒。
指定した条件を除外する。ではなく、指定した除外条件以外を抽出する。と考えたほうが楽。