PythonとPostgreSQLによる幸福の追求

世界はほとんど完璧でありません、そして、同じことは現実のデータのために行きます.PostgreSQLデータベースのデータをクリーンアップして解析に使えるようにする方法を見つけてください.

情報を創造することはどんな創造的なプロセスのようです.これは、(この場合は、データソース)の材料を見つけると起動し、データを結合し、混乱を洗浄、継続して起動します.あなたが何かを美しく構築し、共有するための新たな洞察力を得て終了するまでに-そして、あなたは再びすべてを開始します.
情報の創造の中で最も広く使われている言語の1つは、データの科学者、エンジニア、およびデータのための既存のライブラリのその偉大な生態系のためのアナリストとアナリストによって愛されるPythonです.
このブログ記事はそのようなライブラリの3つを探ります.pandas , ddlgenerator and psycopg2 . これらの3つは、データセットをきれいにし、PostgreSQLデータベースにプッシュすることを可能にします.そこでは、データを後で問い合わせて、巨大な様々な会社の数字に公開することができます.
今日、目的は単純です:我々は幸せデータセットを探索し、世界のどこで我々はうれしい生活を持って移動する必要がありますを見つけるしよう!

0 .データセットを取得する

前述のように、データセットを見つけることから始めましょう.Kaggle データサイエンスコミュニティで広く使われているウェブサイトであり、チャレンジ、競合、学習に使用されるデータセットを提供しています.彼らはこの素晴らしいデータセットを持っているWorld Happiness これはダウンロードするだけのログインが必要です.それは5つのCSVファイル、1年に1つ、いくつかの他の指標と一緒に様々な国の幸福ランキングを記載しています.
フォルダを作成しましょうhappiness とサブフォルダdata ファイルを保存する

mkdir -p happiness/data
cd happiness

次に、データをダウンロードKaggle そして、5つのCVSをhappiness/data フォルダ.

1 .データをきれいにする

頻繁に起こるように、ソースデータセットは常にきちんとしていてきれいではありません.これは私たちの幸福データセットにも有効です.
ときにファイルを閲覧することができます2015.csv and 2016.csv 列名とデータの同じ形式を共有します.The 2017.csv はRegion 列がなく、いくつかの他の名前と順序で並べ替えGenerosity and Trust を交換する).ファイルにも同様です2018.csv and 2019.csv どこで我々はもう信頼区間を見つけることができません.また、年はファイル名だけで、列には含まれません.
我々の掃除努力を始める前に、それは分離をつくる良い練習ですvirtual environment たびに、新しいPythonプロジェクトを起動します.これにより、デフォルトのPython環境が矛盾するライブラリでオーバーロードされません.仮想環境を作成し、次のようにします.

python -m venv python_pg
source python_pg/bin/activate

ファイルは、データの一貫したビューを生成するためにいくつかの作業を必要とするので、我々のPythonスーパーヒーローマスクを置きましょう.インストールするpandas 次のコードを端末で実行することで、データ操作と解析を支援するライブラリです.

pip install pandas

これでファイル名を作成できますprepare.py 我々の中でhappiness フォルダ内のいくつかのPythonコードを含む起動します.私たちは、以前にインストールされたライブラリをインポートすることから始めます os and glob デフォルトでインストールされ、それぞれのシステムとパス名のパターン展開との相互作用を可能にします.

import pandas as pd
import glob, os

次に、ターゲットのデータファイル名で使用するカラム名を定義しますall_years_df データセットを一度に保存します.次の行をprepare.py ファイル

column_names = ['survey_yr', 'country', 'overall_rank',
                'score', 'gdp', 'generosity', 'freedom',
                'social_support', 'life_exp', 'gov_trust']

all_years_df = pd.DataFrame()

今ではカオスに秩序をもたらす時間であり、すべてのCSVファイルを解析するdata フォルダ.次のコードを追加しますprepare.py ファイル

files = glob.glob('data/*.csv')

for fp in files:
    # Handling file names as new column
    file_year = os.path.basename(fp).split('.')[0]
    file_content = pd.read_csv(fp).assign(Year=int(file_year)-2000)

    # Picking the right column for each file based on the year
    uniformed_columns = find_columns(file_content, file_year)

    # Conforming column names and appending the parsed year data to all_years_df
    uniformed_columns.columns = column_names
    all_years_df = pd.concat([all_years_df, uniformed_columns])

all_years_df.to_csv('results.csv', index=False)

The glob 関数は、happiness/data 私たちがループするフォルダ.それぞれのファイルに対して、os.path.basename , ドットに名前を分割する. ) そして、最初の部分を取る、年間情報のみを抽出します.The file_content 行はパンダを使うread_csv ファイルデータを読み込み、新しい列を読み込む機能Year (assign 関数の年の最後の2桁int(file_year)-2000 ).
次に、私たちはfind_columns 関数は、すぐに定義されます.ソースファイルから正しい列を選択するには、年によって異なります.
この後、uniformed_columns データの1年だけを含んでいるデータFramrameall_years_df プロセスの最後にデータセット全体を格納するDataFrame.最後に、結果をCSVファイルに保存しますresults.csv にhappiness フォルダ.

正しいカラムの選択

行方不明の部分はfind_columns 関数の定義では、列名と順序を統合します.上記のように、年によっては、ソースファイルから正しい列を選択する必要があります.次のコードを追加しますprepare.py ファイルの直後にimport ステートメントは、様々な年のトリックを実行する

def find_columns(file_content, file_year):
    if file_year in ['2015', '2016']:
        uniformed_columns = file_content[[
            'Year', 'Country', 'Happiness Rank', 'Happiness Score',
            'Economy (GDP per Capita)', 'Generosity', 'Freedom', 'Family',
            'Health (Life Expectancy)', 'Trust (Government Corruption)'
            ]]

    elif file_year in ['2017']:
        uniformed_columns = file_content[[
            'Year', 'Country', 'Happiness.Rank', 'Happiness.Score',
            'Economy..GDP.per.Capita.', 'Generosity', 'Freedom', 'Family',
            'Health..Life.Expectancy.', 'Trust..Government.Corruption.'
            ]]

    else:
        uniformed_columns = file_content[[
            'Year', 'Country or region', 'Overall rank', 'Score',
            'GDP per capita', 'Generosity', 'Freedom to make life choices',
            'Social support', 'Healthy life expectancy',
            'Perceptions of corruption'
            ]]

    return uniformed_columns

この関数は、各年の正しいカラムを選択し、結果のデータファイルを返します.
今はパイプラインが動いていることを確認する時です.端末がhappiness フォルダーを実行します.

python prepare.py

ではファイルをチェックするresults.csv にhappiness コードが動作したことを確認するフォルダ.結果は次のようになります.

2 . PostgreSQLインスタンスを作成する

番目のステップは、PostgreSQLのテーブルにデータを格納することです.しかし、保存する前に、PGデータベースが必要ですAiven CLI 我々のターミナルで

avn service create demo-pg    \
  -t pg                       \
  --cloud google-europe-west3 \
  -p hobbyist

上記のコマンドは、demo-pg on google-europe-west3 を使うhobbyist 計画すべてのPostgreSQLの計画を確認することができますin our pricing page . それに接続するには、我々は必要がありますservice uri 情報を含むhost , port , とデフォルトavnadmin 利用者password . 以下のようにして取得できます:

avn service get demo-pg --format '{service_uri}'

今すぐに、いくつかの分のリラックスしながら、インスタンスは、次の呼び出しでは、端末の準備を待っている間

avn service wait demo-pg

PostgreSQLへデータをプッシュする

サービスが実行されると、Pythonに戻って、データプッシュを準備します.
私たちはddlgenerator PostgreSQLテーブルを作成するSQLステートメントを自動的に生成するライブラリpsycopg2 データベースと対話する.端末に次の行をインストールすることができます.

pip install psycopg2 ddlgenerator

今すぐ新しいpush.py ファイルをインポートし、データベースステートメントに接続します.次のコードに置き換えます<SERVICE_URI_OUTPUT> パラメータavn service get 上記のコマンド

import psycopg2

conn = psycopg2.connect('<SERVICE_URI_OUTPUT>')

接続が確立されます.これでテーブルを作成し、データをプッシュできます.ddlgenerator CSVファイルからテーブルDDLとInsertステートメントを作成するための素晴らしい機能を提供します.次のコードを追加できますpush.py ファイル

from ddlgenerator.ddlgenerator import Table

table = Table('results.csv', table_name='HAPPINESS')
sql = table.sql('postgresql', inserts=True)

インポート後Table クラスからddlgenerator , このコードは、results.csv ファイル名、ターゲット名HAPPINESS . 最後の行はCREATE PostgreSQLのためのテーブル定義SQLです.inserts=True ). SQL文を出力する場合は、次のようになります.

DROP TABLE IF EXISTS happiness;

CREATE TABLE happiness (
    survey_yr INTEGER NOT NULL,
    country VARCHAR(24) NOT NULL,
    overall_rank INTEGER NOT NULL,
    score DECIMAL(15, 14) NOT NULL,
    gdp DECIMAL(17, 16) NOT NULL,
    generosity DECIMAL(16, 16) NOT NULL,
    freedom DECIMAL(16, 16) NOT NULL,
    social_support DECIMAL(16, 15) NOT NULL,
    life_exp DECIMAL(17, 16) NOT NULL,
    gov_trust DECIMAL(16, 16)
);

INSERT INTO happiness (survey_yr, country, overall_rank, score, gdp, generosity, freedom, social_support, life_exp, gov_trust) VALUES (19, 'Finland', 1, 7.769, 1.34, 0.153, 0.596, 1.587, 0.986, 0.393);
INSERT INTO happiness (survey_yr, country, overall_rank, score, gdp, generosity, freedom, social_support, life_exp, gov_trust) VALUES (19, 'Denmark', 2, 7.6, 1.383, 0.252, 0.592, 1.573, 0.996, 0.41);
...

SQL文をデータベースに対して実行するには、次のコードをpush.py ファイル

cur = conn.cursor()
cur.execute(sql)
conn.commit()
cur.close()
conn.close()

カーソルを作成し、SQLステートメントを実行し、データベースへの変更をコミットし、カーソルと接続の両方を閉じました.今すぐコードを実行する.同じ端末をhappiness フォルダを実行します.

python push.py

congrats、データは現在PostgreSQLで読み込まれます.

PostgreSQLでデータを問い合わせる

PostgreSQLでデータが正しく設定されているか確認しましょうhappiness 表.新しいPythonファイルを作るquery.py 次のコンテンツを使用してservice uri ):

import psycopg2

conn = psycopg2.connect('<SERVICE_URI_OUTPUT>')

cur = conn.cursor()
cur.execute('''
SELECT SURVEY_YR, COUNTRY, OVERALL_RANK
from HAPPINESS
WHERE OVERALL_RANK <= 3
ORDER BY SURVEY_YR, OVERALL_RANK
''')

print(cur.fetchall())
cur.close()
conn.close()

としてpush.py ファイルは、PostgreSQLへの接続を作成し、どの国が様々な年にわたって上位3つの位置にあるかを確認するクエリを実行します.結果は次のようになります.

[
(15, 'Switzerland', 1),   (15, 'Iceland', 2),       (15, 'Denmark', 3),
(16, 'Denmark', 1),       (16, 'Switzerland', 2),   (16, 'Iceland', 3),
(17, 'Norway', 1),        (17, 'Denmark', 2),       (17, 'Iceland', 3),
(18, 'Finland', 1),       (18, 'Norway', 2),        (18, 'Denmark', 3),
(19, 'Finland', 1),       (19, 'Denmark', 2),       (19, 'Norway', 3)
]

北ヨーロッパ諸国のようなルックスは、生きる絶好の場所です!どのようにaivenの故郷の特別な通知を取る.Finland , 過去2年間でトップにされています.
現在、PostgreSQLのリレーショナルテーブルで利用可能なデータは、すべての主要な解析とレポートツールによって照らされ、公開され、より広い聴衆によってアクセス可能になります.

ラッピング

正しいデータセットを見つけることは、ちょうど始まりです:掃除をすること、そして、データを出版することは情報をつくります.Pythonは、この地域で大きなフィット感とデータコミュニティで広く採用されています.The pandas , ddlgenerator and psycopg2 ブログに記載されているライブラリは、簡単なデータ処理を可能にし、PostgreSQLにプッシュします.これは、データ発見や企業報告のための固体ベースになります.
この地域の情報はいくつかあります.

Aiven for PostgreSQL : ここでAivenの提供に関するすべての情報を見つけることができます

Aiven PostgreSQL supported extensions : 拡張モジュールをどこで閲覧できるか

pandas , ddlgenerator and psycopg2 : 使用するライブラリについてのドキュメントを見つける