Pandas dataframe writing to excel as list. But I don't want data as list in excel-CodePudding

I have a code which iterate through excel and extract values from excel columns as loaded as list in dataframe. When I write dataframe to excel, I am seeing data with in [] and quotes for string ['']. How can I remove [''] when I write to excel. Also I want to write only first value in product ID column to excel. how can I do that?

result = pd.DataFrame.from_dict(result) # result has list of data
df_t = result.T
writer = pd.ExcelWriter(path)
df_t.to_excel(writer, 'data')
writer.save()

My output to excel

I am expecting output as below and Product_ID column should only have first value in list

CodePudding user response：

df_t['id'] = df_t['id'].str[0]  # this is a shortcut for if you only want the 0th index
df_t['other_columns'] = df_t['other_columns'].apply(lambda x: " ".join(x))  # this is to "unlist" the lists of lists which you have fed into a pandas column

CodePudding user response：

This should be the effect you want, but you have to make sure that the data in each cell is ['', ...] form, and if it's different you can modify the way it's handled in the data_clean function：

import pandas as pd

df = pd.read_excel("1.xlsx", engine="openpyxl")


def data_clean(x):
    for index, data in enumerate(x.values):
        item = eval(data)
        if len(item):
            x.values[index] = item[0]
        else:
            x.values[index] = ""
    return x

new_df = df.apply(data_clean, axis=1)

new_df.to_excel("new.xlsx")

The following is an example of df and modified new_df(Some randomly generated data):

# df

           name               Product_ID          xxx                   yyy
0     ['Allen']     ['AF124', 'AC12414']     [124124]                 [222]
1  ['Aaszflen']     ['DF124', 'AC12415']     [234125]     [22124124,124125]
2     ['Allen']  ['CF1sdv24', 'AC12416']  [123544126]  [33542124124,124126]
3   ['Azdxven']     ['BF124', 'AC12417']      [35127]                 [333]
4     ['Allen']     ['MF124', 'AC12418']       [3528]  [12352324124,124128]
5     ['Allen']     ['AF124', 'AC12419']     [122359]  [12352324124,124129]


# new_df
       name Product_ID        xxx          yyy
0     Allen      AF124     124124          222
1  Aaszflen      DF124     234125     22124124
2     Allen   CF1sdv24  123544126  33542124124
3   Azdxven      BF124      35127          333
4     Allen      MF124       3528  12352324124
5     Allen      AF124     122359  12352324124