[Therion] Statistics on surveys of a network

[Philippe Vernant]

Thanks Martin, you’ve been the fastest !

Bruce, this the script, lots of room for improvement.

Cheers,
Phil




> On 10 May 2023, at 13:29, Martin Sluka via Therion <therion at speleo.sk> wrote:
> 
> 
> #!/usr/bin/env python3
> # -*- coding: utf-8 -*-
> 
> # grab data to generate summary for surveys of a cave network
> 
> import sqlite3
> import pandas as pd
> import numpy as np
> 
> conn = sqlite3.connect(':memory:')
> conn.executescript(open('cave.sql').read())
> stats = open('survey_stats.dat','w')
> 
> # sql to panda
> surv = pd.read_sql_query('select ID, NAME from SURVEY', conn)
> surv = surv[surv.NAME  != ''] # drop empty survey names
> ctli = pd.read_sql_query('select ID, SURVEY_ID, TOPO_DATE, LENGTH from CENTRELINE', conn)
> ctli = ctli.dropna() # drop empty date centerlines
> topo = pd.read_sql_query('select PERSON_ID, CENTRELINE_ID from TOPO', conn)
> team = pd.read_sql_query('select ID, NAME, SURNAME from PERSON', conn)
> team = team.drop_duplicates()
> team = team.to_numpy()
> 
> #iterate over surveys
> for index, s in surv.iterrows():
>    print(s.NAME)
>    stats.write('%s\n'%(s.NAME))
> 
>    for index, c in ctli.iterrows():
>    	if (c.SURVEY_ID == s.ID) :
>    		print('      date   ',c.TOPO_DATE)
>    		stats.write('      date   %s\n'%(c.TOPO_DATE))
>    		print('      length ',c.LENGTH)
>    		stats.write('      length %s\n'%(c.LENGTH))
>    		for index, t in topo.iterrows():
>    			if (t.CENTRELINE_ID == c.ID) :
>    				name = team[team[:,0] == t.PERSON_ID]
>    				print ('                 {} {}'.format(name[0,1],name[0,2]))
>    				stats.write('                 {} {}\n'.format(name[0,1],name[0,2]))
> 
> stats.close()
> 
> Odesláno z iPhonu
> 
>> 10. 5. 2023 v 12:05, Bruce Mutton <bruce at tomo.co.nz>:
>> 
>> MS Outlook blocks attachments with extension .py
>> Perhaps you can repost after changing the extension to .txt?
>> Thanks
>> Bruce
>> 
>> 
>> -----Original Message-----
>> From: Therion <therion-bounces at speleo.sk> On Behalf Of Philippe Vernant
>> Sent: Wednesday, 10 May 2023 20:54
>> To: List for Therion users <therion at speleo.sk>
>> Subject: Re: [Therion] Statistics on surveys of a network
>> 
>> Hi guys,
>> 
>> Thanks for the answers. I wrote a python script. It is far from being
>> elegant but it does the job. I attach it to this message in case it can be
>> helpful to some of you.
>> 
>> Cheers,
>> Phil
>> 
>> 
>> 
>> _______________________________________________
>> Therion mailing list
>> Therion at speleo.sk
>> https://mailman.speleo.sk/listinfo/therion
> _______________________________________________
> Therion mailing list
> Therion at speleo.sk
> https://mailman.speleo.sk/listinfo/therion