CODEWITHSUNDEEP
pythonpandasreplaceconcatenation
Robert Kolås
Robert Kolås

Reputation: 1

Pandas - replace a doublequote(") in a dataframe with something

I'm making a little project for my self, where I combine two csv-files with pd.concat()

As seen in the picture below, there was added double quotes to the data from each sheet i combined "data from sheet 1", "data from sheet 2" example from data set

I want to remove the double quotes from the csv-file, but when i use:

 df = df.replace('"', '', regex=True)

nothing happens.

if I use:

df = df.replace('e', 'x', regex=True)

it works fine

Here is my code:

import pandas as pd
import xlrd
import os
from pathlib import Path
import csv

#path of the file we read from
filename = os.path.join('..', 'C:\\prosjekt_lisensrapport\\201902_ModulesLicensesReport.xlsx')

sheet_names = [0,1]

dfs = pd.read_excel(filename,
                sheet_name=sheet_names)

pd.concat((df.assign(source=sheet) for sheet, df in dfs.items()), ignore_index=True)

def sheets_to_df(filename, sheet_names):
    df_dict = pd.read_excel(filename, sheetname=sheet_names)
    return pd.concat(
        (df.assign() for sheet, df in dfs.items()), axis=1, join='inner')


    
df = sheets_to_df(filename, sheet_names)

df = df.replace('"', '', regex=True)

df.to_csv('testResult.csv', sep=',', encoding='utf-8')

Thanks in advance.

Upvotes: 0

Views: 2188

Answers (1)

Maxouille
Maxouille

Reputation: 2911

You can do this but it will convert your non-string columns to string one (see this post): 

for i, col in enumerate(df.columns):
    df.iloc[:, i] = df.iloc[:, i].str.replace('"', '')

If you don't care, you can use applymap()

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

Or much simpler, just open the file and replace all double quotes with the char you want. No need to insert your data into a DataFrame to do this.

Best

Upvotes: 2

Related Questions