script to merge water quality and discharge data from two USGS gages for the X2CM project

merge.py

# -*- coding: utf-8 -*-
"""
script to merge water quality and discharge data from two USGS gages for 
the X2CM project

@author: saraceno
Created on Wed Jun 14 14:51:19 2017

"""
import pandas as pd

def readfile(filename, index_col, site_name):
    """
    function to read in a csv file pulled from NWIS web
    INPUTS: 
        index_col, integer, column that contains a datetime 
        filename, string,  csv data filename
        site name, string, name of site to prepend to data columns after merge
    OUTPUT:
        Datetime indexed pandas dataframe
    """
    df = pd.read_csv(filename, index_col=index_col, delimiter=',')
    #convert index to a pandas datetime index
    df.index = pd.to_datetime(df.index)
    #make index unique for tracking
    df.index.name= site_name
    #remove duplicate entries to allow later merging
    df = df.loc[~df.index.duplicated(keep='first')]
    #drop the Unnamed column
    if u'Unnamed: 0' in df.columns:
        df.drop([u'Unnamed: 0', ], inplace=True, axis=1)
    #rename the columns as to be unique for later merging
    df.rename(columns=lambda x: site_name + '_' + x, inplace=True)
    return df.copy()

#define file names
qw_fname = r'DEC.csv'
q_fname = r'RIOQ.csv'
#read in the data into pandas dataframe
qw = readfile(qw_fname, 5, 'DEC')
q = readfile(q_fname, 2, 'RIO')
#merge the dataframes on datetime index
merged = pd.concat([q, qw], axis=1)
#output the merged data
merged.to_csv('DEC_RIO_merged.csv')