1

My project is composed by several lists - that I put all together in a dataframe with pandas, to excel. But one of my list contains sublists, and I don't know how to deal with that.

my_dataframe = pd.DataFrame({
        "V1": list1,
        "V2": list2,
        "V3": list3
    })
my_dataframe.to_excel("test.xlsx", sheet_name="Sheet 1", index=False, encoding='utf8')

Let's says that:

list1=[1,2,3]
list2=['a','b','c']
list3=['d',['a','b','c'],'e']

I would like to end in my excel file file with:

enter image description here

I have really no idea how to proceed - if this is even possible? Any help is welcomed :) Thanks!

LoC
  • 115
  • 7
  • Perhaps the answer you are looking for is [here](https://stackoverflow.com/questions/27263805/pandas-when-cell-contents-are-lists-create-a-row-for-each-element-in-the-list), but then add the to excel part at the end? – run-out May 21 '19 at 13:33

1 Answers1

0

Try this before calling to_excel :

my_dataframe = (my_dataframe["V3"].apply(pd.Series)
.merge(my_dataframe.drop("V3", axis = 1), right_index = True, left_index = True)
.melt(id_vars = ['V1', 'V2'], value_name = "V3")
.drop("variable", axis = 1)
.dropna()
.sort_values("V1"))

credits to Bartosz

Hope this helps.

cyrilb38
  • 924
  • 6
  • 17
  • Thank you very much, this is perfect!! – LoC May 22 '19 at 11:54
  • Hello cyrilb38 Do you know if we can do that with 2 lists containing sublists? I mean by that, if I take back my example, I have a list4=['1',['2','3','4'],'5'] and I want to do the same as with the list3. I've tried to use twice the code you put above, but this is not working, and I can't find the way to put 2 lists in the code you put above. Both lists have the same structure. Thanks! – LoC Jun 04 '19 at 07:45
  • Unfortunately this solution is not generalizable. I would suggest to use the solution from MaxU : https://stackoverflow.com/a/40449726/6081921. Your first need to convert the lists you want to explode (list3 & list4) e.g. : list3 = [x if isinstance(x, list) else [x] for x in list3]. Then use the explode function and that should do it – cyrilb38 Jun 04 '19 at 11:29
  • Thank you cyrilb38, I'll then try to work around to avoid this. – LoC Jun 05 '19 at 15:50