ネストのある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上でインポートしたデータにクエリー発行して抽出。その結果を別テーブルとして新たに作り直した方がずっと早かった。
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の文字が混ざってしまっている。