Writing a DataFrame to an excel file where items in a list are put into separate cells

Question

Consider a dataframe like pivoted, where replicates of some data are given as lists in a dataframe:


d = {'Compound': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C', 'C'],
     'Conc': [1, 0.5, 0.1, 1, 0.5, 0.1, 2, 1, 0.5, 0.1],
     'Data': [[100, 90, 80], [50, 40, 30], [10, 9.7, 8], 
              [20, 15, 10], [3, 4, 5, 6], [100, 110, 80],
              [30, 40, 50, 20], [10, 5, 9, 3], [2, 1, 2, 2], [1, 1, 0]]}

df = pd.DataFrame(data=d)
pivoted = df.pivot(index='Conc', columns='Compound', values='Data')

This df can be written to an excel file as such:

with pd.ExcelWriter('output.xlsx') as writer:
    pivoted.to_excel(writer, sheet_name='Sheet1', index_label='Conc')

How can this instead be written where replicate data are given in side-by-side cells? Desired excel file:

Does this answer your question? [Split a Pandas column of lists into multiple columns](https://stackoverflow.com/questions/35491274/split-a-pandas-column-of-lists-into-multiple-columns) — aaossa, Feb 14 '23 at 13:52
@aaossa this is not really the same, this wouldn't work as the list are in different columns and have uneven sizes (+ there are NaNs) — mozway, Feb 14 '23 at 13:56

score 4 · Accepted Answer · answered Feb 14 '23 at 13:51

Then you need to pivot your data in a slightly different way, first explode the Data column, and deduplicate with groupby.cumcount:

(df.explode('Data')
   .assign(n=lambda d: d.groupby(level=0).cumcount())
   .pivot(index='Conc', columns=['Compound', 'n'], values='Data')
   .droplevel('n', axis=1).rename_axis(columns=None)
)

Output:

        A    A    A    B    B    B    B   C   C   C    C
Conc                                                        
0.1    10  9.7    8  100  110   80  NaN   1   1   0  NaN
0.5    50   40   30    3    4    5    6   2   1   2    2
1.0   100   90   80   20   15   10  NaN  10   5   9    3
2.0   NaN  NaN  NaN  NaN  NaN  NaN  NaN  30  40  50   20

Thanks. How could I forget the ol group by cum count manoeuvre — John Crow, Feb 14 '23 at 13:59

score 2 · Answer 2 · answered Feb 14 '23 at 14:00

Beside the @mozway's answer, just for formatting, you can use:

piv = (df.explode('Data').assign(col=lambda x: x.groupby(level=0).cumcount())
         .pivot(index='Conc', columns=['Compound', 'col'], values='Data')
         .rename_axis(None))
piv.columns = pd.Index([i if j == 0 else '' for i, j in piv.columns], name='Conc')
piv.to_excel('file.xlsx')

Writing a DataFrame to an excel file where items in a list are put into separate cells

2 Answers2