Lotus Base
< Prev
Blog

2020-01-08

【卒論】PHP（Laravel）+ MySQL で貿易統計データ処理【UN Comtrade API】

あけましておめでとうございます。

年末年始でなんとか卒論（経済）を終わらせました！！！テーマは「ベトナムにおける自由貿易協定と貿易構造の変化」についてでした。ざっくり言えば貿易統計データ分析です。（学士論文のため内容は簡素です…）

ベトナムの貿易統計は、一応ベトナム統計総局 (https://www.gso.gov.vn) にあるのですが、まあこれがざっくりとしたデータしか掲載されておらず、分析の根幹となる「相手国別の輸出・輸入における商品の内訳」についてのデータが全く取れなかったので、結局 UN Comtrade（国連商品取引データベース）の API を使って大量のデータ処理をするしか方法がありませんでした。

大量といっても1万件程度で大したことはないのですが、人力でやるには面倒な分量だったので、データの取得と集計処理をPHP（Laravel）＆ MySQL で自動化しました。今回はそのメモ書きです。

PHPじゃなくてPythonのほうがいいんじゃないの？

うるせ～～～～～～そんなことわかってる！！！絶対こういうデータ処理はPythonとかRのほうが向いてると思います。（グラフの出力まで言語のライブラリで手軽にできるため）

自分はWebプログラミングばかりでPythonのデータ処理系ライブラリはほとんど触れたことがないので、わざわざ新しく学習するよりも、使い慣れてるPHPで「データ取得＆整形 → CSV出力 → Excelでグラフ生成」するのが効率よさそうだったのでそうしちゃいました。おまけにPHPのLaravelベースです。一体誰に役立つ記事なんだこれは・・・

やること

取りたいデータ
  • 対象国: ベトナム
  • 期間: 2000-2017年
  • 相手国: 中国・韓国・米国、それぞれの輸出額と輸入額
  • 商品分類: HSコード2桁（およそ100分類）

HSコードについては下記のサイトが見やすくまとまっています。 https://www.toishi.info/hscode/

上記データを取得・集計・整形し、CSV出力します。

モデルとマイグレーションファイルの生成

  • 取引データをモデルにしてデータの保存・集計を Laravel + MySQL でできるようにします。
  • 取引額は20億を超えることがあるので金額カラムの型は bigint を指定しましょう。

terminal

php artisan make:model Trade -m

Trade.php

class Trade extends Model
{
    protected $guarded = ['id'];
}

create_trades_table.php

class CreateTradesTable extends Migration
{
    public function up()
    {
        Schema::create('trades', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->integer('year');
            $table->string('type');      // import || export
            $table->bigInteger('value'); // 取引金額がでかいので bigInteger にする
            $table->string('country');
            $table->integer('code');     // 商品分類コード
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('trades');
    }
}

データ取得の自動化

  • Laravel の Artisan コマンドで行います。
  • Base URI は http://comtrade.un.org/api/get です。これにパラメータを付与します。
  • API の仕様はドキュメントにまとまっています。
  • 貿易相手国と対象年がひとつずつしか指定できないため、これらをループさせ自動取得させています。

terminal

php artisan make:command GetTradeData

GetTradeData.php

class GetTradeData extends Command
{
    protected $signature = 'thesis:getdata';
    protected $description = 'Get trade date from UN Comtrade';

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $vietnamCountryId = 704;
        $yearFrom  = 2000;
        $yearTo    = 2017;
        // 貿易相手国
        $countries = [
            156 => 'China',
            842 => 'USA',
            410 => 'Korea'
        ];

        $this->line('Process start');
        $this->line('***');

        for($y = $yearFrom; $y <= $yearTo; $y++) {
            foreach($countries as $countryId => $countryName) {
                $this->line("Fetching data... [$countryName $y]");
                // ちゃんとやるなら下記でエラーハンドリング（今回は省略）
                $res = json_decode(file_get_contents(
                    "https://comtrade.un.org/api/get?r=${vietnamCountryId}&px=HS&ps=${y}&p=${countryId}&cc=AG2&max=25000&fmt=json&type=C&freq=A&head=M"
                ));
                foreach($res->dataset as $d) {
                    \App\Trade::create([
                        'year' => $d->yr,
                        'type' => mb_strtolower($d->rgDesc), // import || export
                        'value' => $d->TradeValue,
                        'country' => $d->pt3ISO, // CHN || USA || KOR (英字3文字での国名)
                        'code' => $d->cmdCode,
                    ]);
                }
                $this->info("Data saved! [$countryName $y]");
                sleep(1); // APIを叩きすぎないように休止させる
            }
        }

        $this->line('***');
        $this->line('Process finish!');
    }
}

terminal

php artisan thesis:getdata

データの整形とCSV出力

米中韓それぞれの輸出と輸入について、商品を

  • 軽工業 (39-71, 94-96)
  • 一次産品 (1-27)
  • 化学 (28-38)
  • 機械機器 (84-93)
  • 鉄鋼 (72-83)
  • その他 (97-99)

の６分類に分け、その合計額を年ごとにまとめてCSVに吐き出させます。（カッコ内は該当するHSコードです ）

例）対中輸出の出力結果（単位は100万ドル）

データの取得時と同じく Artisan コマンドで行います。

terminal

php artisan make:command GenerateCsv

class GenerateCsv extends Command
{
    protected $signature = 'thesis:gencsv';
    protected $description = 'Generate CSV from trade data';

    public function __construct()
    {
        parent::__construct();
    }

    public function handle()
    {
        $years = [];
        for($y = 2000; $y <= 2017; $y++) {
            $years[] = $y;
        }

        $m = 1000000;

        // CSV の出力パターン一覧
        $csv_set = [
            ["CHN", "export"],
            ["CHN", "import"],

            ["USA", "export"],
            ["USA", "import"],

            ["KOR", "export"],
            ["KOR", "import"],
        ];

        foreach ($csv_set as $c) {
            $country = $c[0];
            $type = $c[1];

            // CSVの元となる配列データ
            $res = [array_merge(['year'], $years)];

            // 軽工業のみ商品コードの範囲が2つに渡るので、別で処理
            $manu = ['manu'];
            foreach($years as $y) {
                $manu[] = $this->getSrc($country,$type)
                    ->where('year', $y)
                    ->where(function($q) {
                        $q->whereRaw('code BETWEEN 39 and 71')
                        ->orWhereRaw('code BETWEEN 94 and 96');
                    })
                    ->sum("value") / $m;
            }
            $res[] = $manu;

            // 各分類の商品コードの名称と範囲
            $industries = [
                [
                    'name' => 'primary', // 一次産品
                    'code' => [1,27]
                ],
                [
                    'name' => 'chemical', // 化学
                    'code' => [28,38]
                ],
                [
                    'name' => 'machine', // 機械機器
                    'code' => [84,93]
                ],
                [
                    'name' => 'metal', // 鉄鋼・非鉄金属
                    'code' => [72,83]
                ],
                [
                    'name' => 'other', // その他
                    'code' => [97,99]
                ]
            ];
            foreach($industries as $i) {
                $line = [$i['name']];
                foreach($years as $y) {
                    $line[] = $this->getSrc($country,$type)
                        ->where('year', $y)
                        ->whereBetween('code', $i['code'])
                        ->sum("value") / $m; // 金額がでかすぎるので単位を100万USDにそろえる
                }
                $res[] = $line;
            }
            
            // CSVを書き込み、出力
            $fp = fopen("${country}_${type}.csv", 'w');
            foreach ($res as $line) {
                fputcsv($fp, $line);
            }
            fclose($fp);
        }
    }

    private function getSrc($country, $type)
    {
        return \App\Trade::where('country', $country)->where('type', $type);
    }
}

terminal

php artisan thesis:gencsv

これで３か国との輸出入それぞれ、年度と商品分類ごとにまとめた取引額がCSVで出力できました！！

結論（備考）

なお、論文では上記に加えて、さらに詳しくベトナムの機械・電子機器類の輸出入の内訳についても集計を行いました。（コードは省略します）

下図は対中輸出の機械機器類の内訳です。

電子機器類の伸びがやべ～～～～～～～～なんと2015から2017年のたった2年間で3.5倍にまで拡大してました。サムスンのスマホ部品（半導体）工場が本格稼働したのがかなり強く影響してるみたいです。

このおかげで近年のベトナムの貿易収支は黒字なのですが、結局外資依存で国内の裾野産業がまだまだ未発達・中間財も外国製品に頼っているのが現状なのでもっと技能移転と国内産業育成を進めましょう、みたいな結論になってます。おつかれさまでした。

< Prev
Back to Top