In [1]:
import os
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
% matplotlib inline
In [2]:
# pandas options
pd.options.display.max_rows = 6
In [3]:
inFile = r'.\Groundwater-Levels\B24F0047001_0.csv'
df = pd.read_csv(inFile, skiprows=10, parse_dates=[2], index_col =2)
In [4]:
df
Out[4]:
In [5]:
df['STAND (MV)'].plot()
Out[5]:
In [104]:
In [ ]:
In [6]:
correct_cols = ['Locatie', 'Filternummer','Peildatum', 'Stand (cm t.o.v. MP)',
'Stand (cm t.o.v. MV)', 'Stand (cm t.o.v. NAP)', 'Bijzonderheid',
'Opmerking', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10']
In [7]:
inFolder = r'.\Groundwater-Levels'
#os.listdir(inFolder)
In [8]:
def read_header(file):
i=0
loc=0
with open(file) as f:
for line in f:
if line[:7]=='Locatie':
loc = loc + 1
if loc==1:
metarow = i
if loc==2:
#print('over')
#print(i)
skiprows = i
break
i=i+1
return metarow,skiprows
In [9]:
def get_xy(file,metarow):
with open(file) as f:
lines = f.readlines()
line = lines[metarow+1].split(',')
return [int(x) for x in line[3:5]]
In [10]:
df_list = []
for file in os.listdir(inFolder):
wellFile,ext = os.path.splitext(file)
if wellFile[-2:] == '_1':
print(file,end=', ')
try:
# read header
metarow,skiprows = read_header(inFolder + '\\' +file)
(x,y) = get_xy(inFolder + '\\' +file,metarow)
# read data
df = pd.read_csv(inFolder + '\\' +file, skiprows=skiprows,index_col=False, parse_dates=[2],dayfirst=True)
# check if the right columns were imported
ll = [i for i, j in zip(df.columns.tolist(), correct_cols) if i != j ]
if len(ll)==0:
# it's ok
df['x'] = x
df['y'] = y
df_list.append(df)
print('ok')
else:
print('wrong columns')
except:
print('error')
# merge all the dataframes
all_dfs = pd.concat(df_list)
In [11]:
# set index
all_dfs.set_index(['Locatie','Filternummer','Peildatum'],inplace=True)
In [12]:
all_dfs
Out[12]:
In [13]:
# list the well numbers
all_dfs.index.get_level_values(0).unique()
Out[13]:
In [14]:
# select only the interesting columns
all_depths = all_dfs[['Stand (cm t.o.v. NAP)','x','y']]
all_depths = all_depths.rename(columns={'Stand (cm t.o.v. NAP)':'depth'})
In [15]:
# some global stats
all_depths.describe().T
Out[15]:
In [16]:
# table with average depths
all_depths.mean(level=[0,1]).style
Out[16]:
In [17]:
# plot on a map
means = all_depths.mean(level=[0,1])
means.plot.scatter(x='x',y='y',c='depth', cmap='plasma', s=50, figsize=(10,10))
Out[17]:
In [ ]: