Load csv data from a URL directly to a mysql table after clean-up

user_8692106 0
Viewed 57

I want to load the data given in the URL "https://www.treasury.gov/ofac/downloads/sdn.csv" directly into a table named sdn. The only change I want to do is to replace all the '-0- ' with '' for all the columns that have this value.

I tried to do this using pandas but the approach I have does not look clean.

import requests
import pandas as pd


sdnURL = "https://www.treasury.gov/ofac/downloads/sdn.csv"
altURL = "https://www.treasury.gov/ofac/downloads/alt.csv"
addURL = "https://www.treasury.gov/ofac/downloads/add.csv"
sdnCommentsURL = "https://www.treasury.gov/ofac/downloads/sdn_comments.csv"

sdnHeader = ["sdn_id", "sdn_name", "sdn_type", "program", "title", "call_sign", "vessel_type", "tonnage", "gross_tonnage", "vessel_flag", "vessel_owner", "remarks"]
altHeader = ["sdn_id", "alt_id", "alt_type", "alt_name", "remarks"]
addHeader = ["sdn_id", "address_id", "address", "city_state_post", "country", "remarks"]
sdnCommentsHeader = ["sdn_id", "remarks"]


sdn = pd.read_csv(sdnURL, names = sdnHeader, header = None)
alt = pd.read_csv(altURL, names = altHeader, header = None)
add = pd.read_csv(addURL, names = addHeader, header = None)
sdnComments = pd.read_csv(sdnCommentsURL, names = sdnCommentsHeader, header = None)

sdn.to_csv('sdn.csv', index = False)
alt.to_csv('alt.csv', index = False)
add.to_csv('add.csv', index = False)
sdnComments.to_csv('sdnComments.csv', index = False)

Further I plan to load csv to the mysql table. I have two concerns in my approach -

  1. I don't want to write the commands for each file.
  2. Replacing the '-0- ' from all the columns in one go

Final Edit: Thanks @Jimmar for the answer, I finally ended up writing the code like this -

import requests
import pandas as pd

files = {
         "sdn" : ["sdn_id", "sdn_name", "sdn_type", "program", "title", "call_sign", "vessel_type", "tonnage", "gross_tonnage", "vessel_flag", "vessel_owner", "remarks"],
         "alt" : ["sdn_id", "alt_id", "alt_type", "alt_name", "remarks"],
         "add" : ["sdn_id", "address_id", "address", "city_state_post", "country", "remarks"],
         "sdn_comments" : ["sdn_id", "remarks"]
        }

def fetch_csv(file, headers):
    df = pd.read_csv("https://www.treasury.gov/ofac/downloads/"+file+".csv", names=headers, header=None)
    df = df.replace('-0- ', '')
    df.to_csv(file+'.csv', index=False)

for file, headers in files.items():
    fetch_csv(file, headers)
pandas dataframe python-3.x
1 Answers

you can organize your code in this way (I'll just do for 2)

import requests
import pandas as pd

def fetch_csv(url, headers, file_name):
    df = pd.read_csv(url, names=headers, header=None)
    df = df.replace('-0- ', '')
    df.to_csv(file_name, index=False)

sources = [
    {
       "url": "https://www.treasury.gov/ofac/downloads/sdn.csv",
       "headers": ["sdn_id", "sdn_name", "sdn_type", "program", "title", "call_sign", "vessel_type", "tonnage", "gross_tonnage", "vessel_flag", "vessel_owner", "remarks"],
       "file_name": "sdn.csv"
    },
    {
       "url": "https://www.treasury.gov/ofac/downloads/alt.csv",
       "headers":  ["sdn_id", "alt_id", "alt_type", "alt_name", "remarks"],
       "file_name": "alt.csv"
    } # add the rest in the same pattern
]

for source in sources:
    fetch_csv(source['url'], source['headers'], source['file_name'])

if you need to write it to a database, then you should replace the df.to_csv line with to_sql

user_5466340
Related