Re: [Therion] Statistics on surveys of a network
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 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 : >> >> MS Outlook blocks attachments with extension .py >> Perhaps you can repost after changing the extension to .txt? >> Thanks >> Bruce >> >> >> -Original Message- >> From: Therion On Behalf Of Philippe Vernant >> Sent: Wednesday, 10 May 2023 20:54 >> To: List for Therion users >> 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@speleo.sk >> https://mailman.speleo.sk/listinfo/therion > ___ > Therion mailing list > Therion@speleo.sk > https://mailman.speleo.sk/listinfo/therion ___ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
Re: [Therion] Statistics on surveys of a network
On 10/05/2023 10:00, Tarquin Wilton-Jones via Therion wrote: >> 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. > > Very nice! > Thanks for sharing. One thing I do notice, is that like Therion's own PDF surveying length stats, the script does not give any length for surface legs, so surface surveyors are not given any credit. https://github.com/therion/therion/issues/132 I think this is a bug in Therion's SQL output: create table CENTRELINE (ID integer, SURVEY_ID integer, TITLE varchar(60), TOPO_DATE date, EXPLO_DATE date, LENGTH real, SURFACE_LENGTH real, DUPLICATE_LENGTH real); ... insert into CENTRELINE values (4147, 4146, NULL, NULL, NULL, 0.00, 0.00, 0.00); All of my surface surveys have 0 length. The python script has the same issue though, in that it only shows "length", and ignores surface surveys. ___ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
Re: [Therion] Statistics on surveys of a network
#!/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 : > > MS Outlook blocks attachments with extension .py > Perhaps you can repost after changing the extension to .txt? > Thanks > Bruce > > > -Original Message- > From: Therion On Behalf Of Philippe Vernant > Sent: Wednesday, 10 May 2023 20:54 > To: List for Therion users > 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@speleo.sk > https://mailman.speleo.sk/listinfo/therion ___ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
[Therion] Statistics on surveys of a network
MS Outlook blocks attachments with extension .py Perhaps you can repost after changing the extension to .txt? Thanks Bruce -Original Message- From: Therion On Behalf Of Philippe Vernant Sent: Wednesday, 10 May 2023 20:54 To: List for Therion users 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@speleo.sk https://mailman.speleo.sk/listinfo/therion
Re: [Therion] Statistics on surveys of a network
> 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. Very nice! Thanks for sharing. ___ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
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 #!/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() > On 3 May 2023, at 23:44, Andrew Atkinson wrote: > > > > On 03/05/2023 21:40, Tarquin Wilton-Jones via Therion wrote: >> However, I don't know what it will output if you ask it to export a >> database. Probably nothing useful. > > As fr as I remember it was very useful, I wrote a web page that queried it, > this was over 10 years ago, the page is still there but it appears that I > have removed the database at some point > > https://wotcc.org.uk/Cheddar/ > > > > The therion book p66 gives the structure. For things like who did what, I > think each leg has the data of who was on the team doing what. With the right > sql query you should be able to find out most things recorded. > > Andrew > ___ > Therion mailing list > Therion@speleo.sk > https://mailman.speleo.sk/listinfo/therion ___ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion