Join csv normalized by Python pandas to make it easier to check

Processed to make it easier to check by joining the normalized csv

Overview

RDBMS is basically normalized and stores data.

For example In a transaction Date, product code, sales quantity To the master Product code, product name, product classification etc And so on.

If it is stored in RDBMS and you have an environment to access it You can check the contents by combining them in one shot with the SQL SELECT statement.

However, I only have the CSV for each table For some reason, it was necessary to check or confirm the contents. Is there a situation? I wonder if there is not much w

For that reason, assuming that you have a normalized CSV (that is, a CSV output of the RDBMS table as it is for each table) Here is a sample that combines them to generate easy-to-read Excel.

Usage environment

Python 3.8.1 Visual Studio Code 1.41.1

Test data preparation

Python3でcsv処理あれこれテスト As with the test data, CSV of the list of listed companies Obtain and use the one distributed by Mr. Kabusapo. https://kabusapo.com/stock-data/stock-list/

I dare to normalize this (although not so much) Stock code, number of shares constituting one unit Brand code, brand name Divide into two CSVs.

csv_join_pre.py

#csv_join data preparation

import pandas as pd

def main():
    #Body processing
    stoc_value_table = pd.read_csv(".\\CSV\\stocklist.csv",encoding="utf-8", dtype=str )

    #CSV, split output
    stoc_value_table.to_csv(".\\CSV\\tangen.csv",encoding="utf-8",columns=['Stock code', 'Number of shares constituting one unit'],header=True, index=False)
    stoc_value_table.to_csv(".\\CSV\\name_master.csv",encoding="utf-8",columns=['Stock code', 'trading name'],header=True, index=False)
    

if __name__ == '__main__': main()

Source code

As shown below, join and finally output Excel for confirmation

pandas_csv_join_sample.py

#Processed to make it easier to check by joining the normalized csv

import pandas as pd


def main():
    #Body processing
    #open csv data(Master and transaction)
    brand_master = pd.read_csv(".\\CSV\\name_master.csv",encoding="utf-8", dtype=str)
    tangen_tran = pd.read_csv(".\\CSV\\tangen.csv",encoding="utf-8", dtype=str)

    #Left join with the brand code.
    join_brand = pd.merge(tangen_tran, brand_master, how="left", on="Stock code")
    
    #2 Sample to join another table with one key
    #vew_brand = pd.merge(join_brand, cat_master, how="left", left_on=['A','B'], right_on=['C','D'])

    #Excel output
    join_brand.to_excel(".\\CSV\\vew_brand.xlsx",columns=['Stock code', 'trading name', 'Number of shares constituting one unit'])
    

if __name__ == '__main__': main()