notebook.community

Edit and run



In [1]:

    
import pandas as pd



In [2]:

    
cd c:/users/rsignell/downloads









    



c:\users\rsignell\downloads



In [3]:

    
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'])



In [4]:

    
df.head()









    Out[4]:






  
    
      
      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



In [5]:

    
stations=df['site_no'].unique()
print stations









    



[9010500 9033300]



In [6]:

    
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



In [7]:

    
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



In [8]:

    
newDF.head(20)









    Out[8]:






  
    
      
      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



In [8]:

	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

	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