Created
May 2, 2024 13:21
-
-
Save ProfAndreaPollini/6e5380764e042d400e9c833e3821a6fc to your computer and use it in GitHub Desktop.
test
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# %% | |
import sqlite3 as sq | |
import pandas as pd | |
conn = sq.connect('dati.sqlite') | |
conn.row_factory = sq.Row | |
c = conn.cursor() | |
rs = c.execute('SELECT COUNT(*) FROM misurazioni') | |
rs = rs.fetchone() | |
print('Numero di misurazioni:', rs[0]) | |
# %% | |
import datetime | |
from datetime import datetime | |
from tqdm import tqdm | |
c = conn.cursor() | |
rs = c.execute("""SELECT DISTINCT substr(orario,0,8) as year FROM misurazioni""") | |
rs = rs.fetchall() | |
years = [x["year"] for x in rs] | |
# print([x["year"] for x in rs]) | |
# p = pd.read_sql("""SELECT DISTINCT substr(orario,0,5) as year FROM misurazioni order by orario DESC LIMIT 10""",conn) | |
# p | |
for year in tqdm(years): | |
#for month in range(1,13): | |
c = conn.cursor() | |
rs = c.execute(f"SELECT * FROM misurazioni where substr(orario,0,8) = '{year}'") | |
df = [] | |
misurazioni = rs.fetchall() | |
if len(misurazioni) == 0: | |
continue | |
for misurazione in tqdm(misurazioni): | |
# misurazione["orario"] = | |
misurazione = dict(misurazione) | |
valvole = [misurazione[v] for v in ["valvola_1", "valvola_2", "valvola_3", "valvola_4","valvola_5", "valvola_6"]] | |
vin=0 | |
try: | |
vin = valvole.index(1)+1 | |
except: | |
pass | |
vout=0 | |
try: | |
vout = valvole.index(2)+1 | |
except: | |
pass | |
for v in ["valvola_1", "valvola_2", "valvola_3", "valvola_4","valvola_5", "valvola_6"]: | |
del misurazione[v] | |
misurazione["vin"] = vin | |
misurazione["vout"] = vout | |
#print(valvole) | |
#convert misurazione["orario"] to datetime | |
orario = datetime.strptime(misurazione["orario"].strip(), '%Y-%m-%d %H:%M:%S') | |
misurazione["orario"] = orario | |
id_misurazione = misurazione["id"] | |
#print('misurazione:', orario) | |
c = conn.cursor() | |
rs = c.execute('SELECT * FROM misurazioni_sensori JOIN sensori ON (misurazioni_sensori.sensore = sensori.id) WHERE id_misurazione = ?', (id_misurazione,)) | |
sensori_aggiuntivi = {} | |
for row in rs.fetchall(): | |
# print(row["descrizione"], row["valore"]) | |
sensori_aggiuntivi[row["descrizione"]] = row["valore"] | |
# misurazione_completa = {**dict(misurazioni),**dict(sensori_aggiuntivi)} | |
dict_misurazioni = dict(misurazione) | sensori_aggiuntivi | |
df.append(dict_misurazioni) | |
df = pd.DataFrame(df) | |
#df.to_json("misurazioni.json", orient="records") | |
#df.to_hdf(f"misurazioni_{year}.hdf", index=False) | |
df.to_csv(f"__misurazioni_{year}.csv", index=False) | |
# %% | |
import os | |
def get_files(path): | |
for file in os.listdir(path): | |
if os.path.isfile(os.path.join(path, file)): | |
yield file | |
print(sorted([x for x in get_files(".") if x.startswith("__")])) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment