how to open csv in python?

Question

I have a dataset in following format.

row_num;locale;day_of_week;hour_of_day;agent_id;entry_page;path_id_set;traffic_type;session_durantion;hits
"988681;L6;Monday;17;1;2111;""31672;0"";6;7037;\N" "988680;L2;Thursday;22;10;2113;""31965;0"";2;49;14" "988679;L4;Saturday;21;2;2100;""0;78464"";1;1892;14" "988678;L3;Saturday;19;8;2113;51462;6;0;1;\N"

I want it to be in following format :

row_num locale day_of_week hour_of_day agent_id entry_page path_id_set traffic_type session_durantion hits
988681 L6 Monday 17 1 2111 31672 0 6 7037 N
988680 L2 Thursday 22 10 2113 31965 0 2 49 14
988679 L4 Saturday 21 2 2100 0 78464 1 1892 14
988678 L3 Saturday 19 8 2113 51462 6 0 1 N

I tried with the following code :

import pandas as pd

df = pd.read_csv("C:\Users\Rahhy\Desktop\trivago.csv", delimiter = ";")

But I am getting a error as :

SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape

Answer 1

Using replace():

with open("data_test.csv", "r") as fileObj:
    contents = fileObj.read().replace(';',' ').replace('\\', '').replace('"', '')
print(contents)

OUTPUT:

row_num locale day_of_week hour_of_day agent_id entry_page path_id_set traffic_type session_durantion hits
988681 L6 Monday 17 1 2111 31672 0 6 7037 N 988680 L2 Thursday 22 10 2113 31965 0 2 49 14 988679 L4 Saturday 21 2 2100 0 78464 1 1892 14 988678 L3 Saturday 19 8 2113 51462 6 0 1 N

EDIT:

You can open a file, read its content, replace the unwanted chars. write the new contents to the file and then read it through pd.read_csv:

with open("data_test.csv", "r") as fileObj:
    contents = fileObj.read().replace(';',' ').replace('\\', '').replace('"', '')
# print(contents)

with open("data_test.csv", "w+") as fileObj2:
    fileObj2.write(contents)

import pandas as pd
df = pd.read_csv(r"data_test.csv", index_col=False)
print(df)

OUTPUT:

row_num locale day_of_week hour_of_day agent_id entry_page path_id_set traffic_type session_durantion hits
988681 L6 Monday 17 1 2111 31672 0 6 7037 N 988680 L2 Thursday 22 10 2113 31965 0 2 49 14 988679 L4 Saturday 21 2 2100 0 78464 1 1892 14 988678 L3 Saturday 19 8 2113 51462 6 0 1 N