I have a large script that saves data to a postgres database. Tell me how can I get rid of [‘ and ‘]. I need the data in the storage to be saved without these characters
JavaScript
x
29
29
1
total.to_csv("C:/Users/.../total_type19.csv", index = False, sep =';')
2
3
conn5 = psycopg2.connect(dbname='', user='',
4
password='', host='', port = '')
5
cursor5 = conn5.cursor()
6
7
8
with open("C:/Users/.../total_type19.csv", "r",encoding = 'utf-8') as file:
9
reader = csv.reader(file, delimiter = ";")
10
header = next(reader )
11
# print(f"HEADER {header}")
12
counter = 1
13
for row in reader:
14
# print(row)
15
test_id =row[0]
16
test_id = ''.join([i for i in test_id if i.isdigit()])
17
if test_id == '':
18
counter +=1
19
test_id = counter
20
else:
21
test_id = int(test_id)
22
# print(test_id)
23
cursor5.execute(
24
'INSERT INTO interaction_fillword (test_id, data_size, data_words_selection, data_matrix, data_colors, data_answers) VALUES(%s,%s, %s, %s, %s, %s)',
25
(test_id, row[1], row[2], row[3], row[4], row[5]))
26
27
conn5.commit()
28
29
This is how the data looks in the database
JavaScript
1
5
1
id test_id data_size data_words_selection data_matrix data_colors data_answers
2
0 224 312229 ['12'] ['["v","b","c","c","a","h","i","e","r","s","f"... ['[{from:[3,4],to:[7,4],color:2},{from:[3,6],t ['"#ff0000","#00fe00","#0000ff","#d2ea9a","#40... ['"place","cartable","gomme","bureau","bibliot...
3
1 225 400606 ['12'] ['["v","b","c","c","a","h","i","e","r","s","f"... ['[{from:[3,4],to:[7,4],color:2},{from:[3,6],t ['"#ff0000","#00fe00","#0000ff","#d2ea9a","#40... ['"place","cartable","gomme","bureau","bibliot...
4
2 226 410051 ['12'] ['["v","b","c","c","a","h","i","e","r","s","f"... ['[{from:[3,4],to:[7,4],color:2},{from:[3,6],t ['"#ff0000","#00fe00","#0000ff","#d2ea9a","#40... ['"place","cartable","gomme","bureau","bibliot...
5
Tell me how to get rid of these characters, perhaps the problem occurs after parsing when writing to a dataframe. It is necessary to get rid of the first [‘ and ‘], the following brackets must be left Help please
this is an example of how i would like the data to look like
JavaScript
1
5
1
id test_id data_size data_words_selection data_matrix data_colors data_answers
2
0 224 312229 12 ["v","b","c","c","a","h","i","e","r","s","f" [{from:[3,4],to:[7,4],color:2},{from:[3,6],t "#ff0000","#00fe00","#0000ff","#d2ea9a","#40... "place","cartable","gomme","bureau","bibliot
3
1 225 400606 12 ["v","b","c","c","a","h","i","e","r","s","f" [{from:[3,4],to:[7,4],color:2},{from:[3,6],t "#ff0000","#00fe00","#0000ff","#d2ea9a","#40... "place","cartable","gomme","bureau","bibliot
4
2 226 410051 12 ["v","b","c","c","a","h","i","e","r","s","f" [{from:[3,4],to:[7,4],color:2},{from:[3,6],t "#ff0000","#00fe00","#0000ff","#d2ea9a","#40... "place","cartable","gomme","bureau","bibliot
5
Replace doesn’t help.
Advertisement
Answer
Try map with specific column and add lstrip and rstrip in lambada
JavaScript
1
2
1
df[col] = df[col].map(lambda x: str(x).lstrip("['").rstrip("']")).astype(float)
2
Hope this will work for you.