Convert *.csv file in DB with repeat second header several times

user_15343649 0
Viewed 45

I have *.csv file that looks:

# time;P_O2, atm;P_He, atm;Flow O2, l/min;Flow He, l/min;FiO2 Env, %;FiO2 sens2, %;P mask, cm H2O;Tmask, gradC;Tnagr, gradC;V, ml;f, 1/min;Tzad, gradC;FiO2 zad, %;Flags;
# POWERON 01.11.2018 15:02:29
1;39;33;0;2;0;0;255;135;135;0;0;0;25;83886592;0
5;39;33;0;2;0;0;255;135;135;0;0;0;25;83886624;0
26;0;0;0;8;529;0;255;135;135;0;0;0;25;83886592;0
72;0;0;0;8;598;0;248;135;135;0;0;0;25;83886085;0
# POWERON 01.11.2018 15:04:02
1;0;0;0;7;0;0;255;135;135;0;0;0;25;83886592;0
2;0;0;0;7;113;0;255;135;135;0;0;0;25;83886085;0
# POWERON 01.11.2018 15:04:48
1;0;0;0;6;0;0;255;135;135;0;0;0;25;83886592;0
2;0;0;0;6;115;0;255;135;135;0;0;0;25;83886085;0

So, I try convert it to DB and make this one:

import sqlite3
import pandas as pd

conn = sqlite3.connect('mydb.db')

stud_data = pd.read_csv(r'Log/20181101.LOG', sep=';', engine='python')
stud_data.to_sql('interation', conn, if_exists='replace', index=False)
cur = conn.cursor()
for row in cur.execute('SELECT * FROM interation'):
    print(row)
conn.close()

but my result is:

('# POWERON 01.11.2018 15:02:29', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None)
('1', 39.0, 33.0, 0.0, 2.0, 0.0, 0.0, 255.0, 135.0, 135.0, 0.0, 0.0, 0.0, 25.0, 83886592.0, 0.0)
('5', 39.0, 33.0, 0.0, 2.0, 0.0, 0.0, 255.0, 135.0, 135.0, 0.0, 0.0, 0.0, 25.0, 83886624.0, 0.0)
('26', 0.0, 0.0, 0.0, 8.0, 529.0, 0.0, 255.0, 135.0, 135.0, 0.0, 0.0, 0.0, 25.0, 83886592.0, 0.0)
('72', 0.0, 0.0, 0.0, 8.0, 598.0, 0.0, 248.0, 135.0, 135.0, 0.0, 0.0, 0.0, 25.0, 83886085.0, 0.0)
('# POWERON 01.11.2018 15:04:02', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None)
('1', 0.0, 0.0, 0.0, 7.0, 0.0, 0.0, 255.0, 135.0, 135.0, 0.0, 0.0, 0.0, 25.0, 83886592.0, 0.0)
('2', 0.0, 0.0, 0.0, 7.0, 113.0, 0.0, 255.0, 135.0, 135.0, 0.0, 0.0, 0.0, 25.0, 83886085.0, 0.0)
('# POWERON 01.11.2018 15:04:48', None, None, None, None, None, None, None, None, None, None, None, None, None, None, None)
('1', 0.0, 0.0, 0.0, 6.0, 0.0, 0.0, 255.0, 135.0, 135.0, 0.0, 0.0, 0.0, 25.0, 83886592.0, 0.0)

I need value (date) in 'POWERON' insert second columns each other iteration like this:

POWERON;                 time; P_O2, atm; P_He, atm; ....

01.11.2018 15:02:29      1;    39;        33;   ...

01.11.2018 15:02:29      5;    39;        33;   ...

How I may do it simple? May be with pd.read_csv (some option?) or I must use some iteration on DB? It's my first question, sorry for any mistakes design errors.

pandas csv python
1 Answers

Use pd.read_csv is not sufficient to extract the datetime in comments. You have to parse the csvfile yourself. Furthermore, the number of column of header and data are unbalanced.

import sqlite3
import io

def load_csvfile(filepath):
    with open(filepath) as csvfile:
        data = io.StringIO()
        line = csvfile.readline()
        line = f"POWERON;{line.rsplit(';', 1)[0]}\n"
        data.writelines([line])
        for line in csvfile.readlines():
            if line.startswith('# POWERON'):
                dt = line[10:].strip()
            else:
                line = f"{dt};{line.rsplit(';', 1)[0]}\n"
                data.writelines([line])
    data.seek(0)
    return pd.read_csv(data, sep=';', parse_dates=['POWERON'])

conn = sqlite3.connect('mydb.db')

stud_data = load_csvfile(r'Log/20181101.LOG')
stud_data.to_sql('interation', conn, if_exists='replace', index=False)
cur = conn.cursor()
for row in cur.execute('SELECT * FROM interation'):
    print(row)
conn.close()

('2018-01-11 15:02:29', 1, 39, 33, 0, 2, 0, 0, 255, 135, 135, 0, 0, 0, 25, 83886592)
('2018-01-11 15:02:29', 5, 39, 33, 0, 2, 0, 0, 255, 135, 135, 0, 0, 0, 25, 83886624)
('2018-01-11 15:02:29', 26, 0, 0, 0, 8, 529, 0, 255, 135, 135, 0, 0, 0, 25, 83886592)
('2018-01-11 15:02:29', 72, 0, 0, 0, 8, 598, 0, 248, 135, 135, 0, 0, 0, 25, 83886085)
('2018-01-11 15:04:02', 1, 0, 0, 0, 7, 0, 0, 255, 135, 135, 0, 0, 0, 25, 83886592)
('2018-01-11 15:04:02', 2, 0, 0, 0, 7, 113, 0, 255, 135, 135, 0, 0, 0, 25, 83886085)
('2018-01-11 15:04:48', 1, 0, 0, 0, 6, 0, 0, 255, 135, 135, 0, 0, 0, 25, 83886592)
('2018-01-11 15:04:48', 2, 0, 0, 0, 6, 115, 0, 255, 135, 135, 0, 0, 0, 25, 83886085)

Note: you will receive an UserWarning about the spaces in column names.

user_152399510
Related