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
Re: [Therion] Statistics on surveys of a network
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
Re: [Therion] Statistics on surveys of a network
Hi Philippe, > Is there a way to export the statistics of a network with the following > information: > -date of each surveys, > -names of the team members for each survey > -length surveyed for each survey export cave-list -surveys on -location on -o cavelist.html export survey-list -o surveylist.html The best it can give is the lengths. cave-list gives you: Title Length Depth ExploredX Y Altitude For each "cave" (surveys that have survey ... -entrance 2), and for each survey above that. survey-list gives you: Title Length Depth ExploredApprox. Duplicate Surface Shots Stations That is; it gives you statistics of the survey data, not the team. The team members are printed on the PDF (if you enable the right options), but only a total. It does not give you per-survey. However, I don't know what it will output if you ask it to export a database. Probably nothing useful. Unless there is some magical hidden option I don't know about. Cheers, Tarquin ___ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
Re: [Therion] Statistics on surveys of a network
I believe there is no direct way, but you can export to SQL then interrogate that. Andrew -- Sent with K-9 Mail On 3 May 2023 20:33:20 BST, Philippe Vernant wrote: >Hi guys, > >I’ve been reading the fucking manual, but no way to find it. Is there a way to >export the statistics of a network with the following information: >-date of each surveys, >-names of the team members for each survey >-length surveyed for each survey > >Thanks, >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
Hi guys, I’ve been reading the fucking manual, but no way to find it. Is there a way to export the statistics of a network with the following information: -date of each surveys, -names of the team members for each survey -length surveyed for each survey Thanks, Phil ___ Therion mailing list Therion@speleo.sk https://mailman.speleo.sk/listinfo/therion
