Inserting list contents into columns instead of rows using Pandas library-CodePudding

My goal is to add lists into three columns and export to excel. I was successful in adding the lists in rows, but the data would suit better in columns.

I added the data to rows using this code:

df = pd.DataFrame(data=[firstList,secondList,thirdList])

df.to_excel('new.xlsx', sheet_name='first sheet')

I am unsuccessful in adding it to columns using this code:

df = pd.DataFrame({
    'col1' : [firstList],
    'col2' : [secondList],
    'col3': [thirdList]
})

df.to_excel('new.xlsx', sheet_name='first sheet')

The output on the code listed above is:

Here is the desired output: Another thing I am trying to do is remove the indexing column as well, but that is less important

CodePudding user response：

If all list have the same lengths, use:

df2 = df.apply(pd.Series.explode)

NB. If you have string convert first to list:

df = df.apply(pd.eval)

CodePudding user response：

Try this:

import ast
cols = ['col1', 'col2', 'col3']
df[cols] = df[cols].apply(lambda col: col.apply(ast.literal_eval))
df = df.explode(cols)

CodePudding user response：

Split the list by using the split function and using explode function will assign each value to a separate row

#Creating Empty Dataframe

df2 = pd.DataFrame(columns=['a','b','c'])

for i in ['a','b','c']: df2[i] = df[i].str.split(',').explode().reset_index(drop=True)

df2