Skip to content

Instantly share code, notes, and snippets.

@OneGneissGuy
Last active January 4, 2018 20:52
Show Gist options
  • Save OneGneissGuy/e7c8d29404ec87ce866b7678eaf802d2 to your computer and use it in GitHub Desktop.
Save OneGneissGuy/e7c8d29404ec87ce866b7678eaf802d2 to your computer and use it in GitHub Desktop.
script to merge water quality and discharge data from two USGS gages for the X2CM project
# -*- 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')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment