# Import the pandas module
import pandas as pd

# Set the url as a variable; this is the URL we generated above
theURL = 'https://waterdata.usgs.gov/nc/nwis/water_use?format=rdb&rdb_compression=value&wu_area=County&wu_year=ALL&wu_county=ALL&wu_category=IN&wu_county_nms=--ALL%2BCounties--&wu_category_nms=Industrial'

# Read in the data as a pandas data frame and display the first 5 rows
# -Note we need to specify that it's a tab delimited file and uses '#' to indicated commments
dfNWIS = pd.read_csv(theURL, delimiter='\t', comment='#')
dfNWIS.head()

"""
Explanation: 01. Grabbing static text files with Pandas
While once much more common, a number of agencies still provide access to data as raw text files served up on a web server. Some examples are USGS Water Use Data, and NWIS Stream Flow data. If you've ever used these data in your projects, you know it's fairly easy to manage: just download the link to the file (if a download link is provided), or justcopy and paste. 
But if you have a lot of files to download, or if, like the stream gage data, you need to download it in real-time for your reseach, you can write a script to do this. Turns out it's note even that hard, thanks to the pandas package. 
This notebook demonstrates how pandas can read a text file from the web just as easily as a text file located on our local machine. We just substitute the data's web address (i.e., its URL) where we'd put the filename in using the read_csv function. It's that easy, but it only works if the data being served remotely is a raw text file...
Here we examine the process for grabbing 2010 water use data for North Carolina hosted on a USGS server...
First, for argument's sake, let's see how this data set would be nabbed manually:
* Navigate to https://waterdata.usgs.gov/nwis/wu
* In the upper-righthand side, select North Carolina from the dropdown
* Select State Data
* For retrieval criteria, choose:
 * Year --All Years--
 * Area Type: County, --All Counties--
 * Category: Intustrial
* Hit Submit
* For Output Format, select Tab-separated data
* When the data appear, the URL in the browser is what we want; it will always link to these data, and we can use pandas to pull the data we want using that URL.
End of explanation
"""


#Drop the first row, and again show the first 5 rows of data...
dfNWIS.drop(0,axis='rows',inplace=True)
dfNWIS.head()

"""
Explanation: So there's one catch: the second line of the dataframe is not the data we want, but rather a listing of the field type and width. Pandas offers two ways around this. First, we could just drop the first row...
End of explanation
"""


#Create a list of numbers 0 thru 49, recalling Python lists are zero-indexed...
rowsToSkip = range(49) 
#Append '51' to the list
rowsToSkip.append(50)

#Use the read_csv function as before, but skip the rows we want to skip
dfNWIS = pd.read_csv(theURL, delimiter='\t', skiprows=rowsToSkip)
dfNWIS.head()

"""
Explanation: Another way around this is to invoke the skip rows option when reading the CSV. If you look at the file we are importing, you see that the first 49 rows are comments, then comes our header row, and then the field type row that we don't want. So we want to skip rows 1 thru 49 and also line 51. If we create a list of these row numbers, we can pass that to the skip_rows parameter...
End of explanation
"""


dfNWIS.to_csv("NCWaterData.csv")

"""
Explanation: Now that we have this as a pandas data frame, we can analyze it here, or we can simply save a copy to our local machine. For the latter, pandas' to_csv() function works quite easily.
End of explanation
"""


