Pandas dataframe remove square brackets from list string when writing to xlsx

Question

The location data is a list of strings that I am getting. When I use Pandas to write to xlsx, it always puts square brackets around this 1 column.

This is what I have been trying:

def create_excel_with_format(headers,values,full_file_name_with_path):
   
   #Write to CSV in xlsx format with indentation.
   df = pd.DataFrame(data=values,columns=headers)
   #df = df.set_axis(df.index*2 + 1).reindex(range(len(df)*2)) #Create a blank row after every row.

   with pd.ExcelWriter(full_file_name_with_path) as writer:
        df.to_excel(writer, index=False)

        workbook  = writer.book
        worksheet = writer.sheets['Sheet1']      
        #Write the location each comma separated in new line if exists (For Nuget exists and thirdparty no).
        if 'Location' in df:
            location = df["Location"].str.join("\n")
            df["Location"] = location.str.replace('[', repl = '', regex = False).str.replace(']', repl  = '', regex = False)
            twrap = workbook.add_format({"text_wrap": True})
            idx_location = df.columns.get_loc("Location")
            worksheet.set_column(idx_location, idx_location, 60, twrap)

        header_format = workbook.add_format({
            'bold': True,
            'border': False,
            'text_wrap': False,
            'font_size':13})

        for col_num, value in enumerate(df.columns.values):
            worksheet.write(0, col_num, value, header_format)

   #pd.read_csv(full_file_name_with_path).iloc[:, 1:].apply(lambda x: x.replace(r"[\[\]]","",regex=True)).to_csv(full_file_name_with_path)

When I see the excel file, its always like this

     Location
['Pico\\Medman\\FluidTransferTests\\packages.config']

['EchoNET\\ExternalData\\PadsServiceConsole\\PadsServiceConsole.csproj', 'EchoNET\\ExternalData\\SerialNumberDataBase\\SerialNumberDataBase.csproj', 'EchoNET\\UI\\E2C\\E2CApp\\E2CApp.csproj', 'Fixtures\\PADS\\PADSClient\\PADSClient.csproj']

How can I remove the square brackets?

Answer 1

Convert the lists to strings before writing to excel. The below block will convert any list in your df to a comma separated string, you can change the separator to whatever you prefer in the join command.

for index,row in df.iterrows():
    for col in list(df.columns):
            if isinstance(row[col], list):
                    row[col] = ", ".join(row[col])