Replace all the backslash '\' with frontslash '/' during Data Cleansing activity in Python before importing the data in MySQL

user_16992660 0
Viewed 182

I have a DataFrame of type: pandas.core.frame.DataFrame, imported from CSV file which has a column with links having elements as shown below

sno link
A C:\Users\Documents\SQL\Python Codes\
B D:/Users/Documents\Pendrive\Example/
C D:/Users\Documents/Pendrive/Example\
D D:/Users\Documents/Pendrive/Example

I am trying to export this data in CSV and load in MySQL.

There's a mix of front and back slash in my links. Currently the rows with backslash - '\' present at the end, i.e., Row 'A' and 'C' are creating issue while importing in SQL. Row 'B' and 'D' has no problem.

I am performing my pre-process in Python where I am trying to eradicate this error.

How can I replace all the '\' with '/' in my entire DataFrame.

pandas backslash python
2 Answers

Method 1: Using df.applymap

You can use the df.applymap method with a lambda replace function.

df = df.applymap(lambda x: x.replace("\\", "/"))

Method 2: Using df.replace

You can also use df.replace with regular expression.

df = df.replace(r"\\", "/", regex=True)
user_169364150

I created a dictionary to avoid the unicode error for certain cases, and then finally apply lambda function.

Output in Column 'link1'

import pandas as pd

newdf = pd.DataFrame([
    ["A","C:\\Users\\Documents\\SQL\\Python Codes\\"],
    ["B","D:/Users/Documents\\Pendrive\\Example/"],
    ["C","D:/Users\\Documents/Pendrive/Example\\"],
    ["D","D:/Users\\Documents/Pendrive/Example"]],
    columns=['sno','link'])

newdf
sno link
0 A C:\Users\Documents\SQL\Python Codes\
1 B D:/Users/Documents\Pendrive\Example/
2 C D:/Users\Documents/Pendrive/Example\
3 D D:/Users\Documents/Pendrive/Example 
comp_dict = dict()

comp_dict['\x07'] = '\\a'
comp_dict['\x08'] = '\\b'
comp_dict['\x0c'] = '\\f'
comp_dict['\x0b'] = '\\v'

def check_replace(str_data,find,replace,comp_dict):
    for k,v in zip(comp_dict.keys(),comp_dict.values()):
        str_data = str_data.replace(k,v)
    print(str_data)
    return str_data.replace(find,replace)


link = newdf['link']

newdf['link1']= link.map(lambda x: check_replace(x,"\\","/",comp_dict))

newdf
sno link link1
0 A C:\Users\Documents\SQL\Python Codes\ C:/Users/Documents/SQL/PythonCodes/
1 B D:/Users/Documents\Pendrive\Example/ D:/Users/Documents/Pendrive/Example/
2 C D:/Users\Documents/Pendrive/Example\ D:/Users/Documents/Pendrive/Example/
3 D D:/Users\Documents/Pendrive/Example D:/Users/Documents/Pendrive/Example
user_169926600
Related