import pandas as pd
cd c:/users/rsignell/downloads
c:\users\rsignell\downloads
df = pd.read_csv('SS_data_edited.txt',delim_whitespace=True,
index_col='sample_dt',parse_dates=True,skiprows=[1],
usecols=['sample_dt','agency_cd','site_no','body_part_id'])
df.head()
agency_cd | site_no | body_part_id | |
---|---|---|---|
sample_dt | |||
1995-01-26 | USGS | 9010500 | 1 |
1995-02-23 | USGS | 9010500 | 1 |
1995-03-17 | USGS | 9010500 | 0 |
1995-04-07 | USGS | 9010500 | 5 |
1995-06-07 | USGS | 9010500 | 26 |
stations=df['site_no'].unique()
print stations
[9010500 9033300]
def read_flowfile(infile):
df = pd.read_csv(infile,delim_whitespace=True,index_col='sample_dt',
parse_dates=True,skiprows=28,
names=('agency_cd','site_no','sample_dt','Q','Qcd'),compression='gzip')
return df
for station in stations:
infile = '0%d.rdb.gz' % station
outfile = '%d_sed.csv' % station
print 'reading %s ' % infile
leftDF = read_flowfile(infile)
rightDF = df[df['site_no']==station]
newDF = pd.merge(leftDF,rightDF,on=['agency_cd','site_no'],
left_index=True,right_index=True,how='outer')
newDF.to_csv(outfile)
print 'writing %s ' % outfile
reading 09010500.rdb.gz writing 9010500_sed.csv reading 09033300.rdb.gz writing 9033300_sed.csv
newDF.head(20)
agency_cd | site_no | Q | Qcd | body_part_id | |
---|---|---|---|---|---|
sample_dt | |||||
1998-08-20 | USGS | 9033300 | NaN | NaN | 5 |
1998-09-03 | USGS | 9033300 | NaN | NaN | 6 |
1998-10-01 | USGS | 9033300 | 36 | A | NaN |
1998-10-02 | USGS | 9033300 | 57 | A | NaN |
1998-10-03 | USGS | 9033300 | 49 | A | NaN |
1998-10-04 | USGS | 9033300 | 62 | A | NaN |
1998-10-05 | USGS | 9033300 | 49 | A | NaN |
1998-10-06 | USGS | 9033300 | 43 | A | NaN |
1998-10-07 | USGS | 9033300 | 42 | A | NaN |
1998-10-08 | USGS | 9033300 | 46 | A | 5 |
1998-10-09 | USGS | 9033300 | 46 | A | NaN |
1998-10-10 | USGS | 9033300 | 43 | A | NaN |
1998-10-11 | USGS | 9033300 | 41 | A | NaN |
1998-10-12 | USGS | 9033300 | 39 | A | NaN |
1998-10-13 | USGS | 9033300 | 38 | A | NaN |
1998-10-14 | USGS | 9033300 | 40 | A | NaN |
1998-10-15 | USGS | 9033300 | 38 | A | NaN |
1998-10-16 | USGS | 9033300 | 37 | A | NaN |
1998-10-17 | USGS | 9033300 | 42 | A | NaN |
1998-10-18 | USGS | 9033300 | 41 | A | NaN |