# -*- coding: utf-8 -*-
"""
Script for Importing data from MySQL database and cleaning

"""
import os
import pymysql
import pandas as pd
from bs4 import BeautifulSoup

# Changing directory
os.chdir("")

# Running the file containing MySQL information
execfile("connection_config.py")

# Connecting to MySQL
connection = pymysql.connect(host=hostname, user=usr, password=pwd, db=db, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

# Fetching data from the database
with connection.cursor() as cursor:
    sql = "SELECT * FROM answers" 
    cursor.execute(sql)
    result = cursor.fetchall()

# Closing connection
connection.close()

# Saving the data as a dataframe
data = pd.DataFrame(result)

# Saving the data to an excel file
#data.to_excel("answers.xlsx")

# Importing data from the excel file
#data = pd.read_excel("answers.xlsx")

## Data cleaning
body = data["body"]

# Checking for html tag
bool(BeautifulSoup(body[2], "html.parser").find())

# Cleaning html
## 

# Cleaning escape characters
body_new = body.str.replace("[\r\n\t]", "")
body_new = body_new.str.replace("[\\\\{2}]", " ")
body_new = body_new.str.replace("&rsquo;", "'")

data["body"] = body_new

@@ -161,16 +161,32 @@
 ulSoup%0A%0A
+## Getting Data%0A
 # Changi
@@ -858,16 +858,17 @@
 xlsx%22)%0A%0A
+%0A
 ## Data 
@@ -876,20 +876,28 @@
 leaning%0A
+data%5B%22
 body
+%22%5D
  = data%5B
@@ -907,105 +907,210 @@
 dy%22%5D
-%0A%0A# Checking for html tag%0Abool(BeautifulSoup(body%5B2%5D, %22html.parser%22).find())%0A%0A# Cleaning html%0A## 
+.fillna(%22%22)%0A%0A# Cleaning html%0Anrow = data.shape%5B0%5D%0Abody = list()%0A%0Afor i in range(0, nrow):%0A    body.append(BeautifulSoup(data%5B%22body%22%5D%5Bi%5D, %22html%22))%0A    body%5Bi%5D = body%5Bi%5D.get_text()%0A%0Abody = pd.Series(body)
 %0A%0A# 
@@ -1234,55 +1234,26 @@
  %22)%0A
-body_new = body_new.str.replace(%22&rsquo;%22, %22'%22)
+%0A# Fixing unicode%0A
 %0A%0Ada

Add html cleaning option