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

Questions

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

February 14, 2023

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:

>Solution :

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