I want to deconstruct a pandas DataFrame, using column headers as a new data-column and create a list with all combinations of the row index and columns. Easier to show than explain:
index_col = ["store1", "store2", "store3"]
cols = ["January", "February", "March"]
values = [[2,3,4],[5,6,7],[8,9,10]]
df = pd.DataFrame(values, index=index_col, columns=cols)
From this DataFrame I wish to get the following list:
[['store1', 'January', 2],
['store1', 'February', 3],
['store1', 'March', 4],
['store2', 'January', 5],
['store2', 'February', 6],
['store2', 'March', 7],
['store3', 'January', 8],
['store3', 'February', 9],
['store3', 'March', 10]]
Is there a convenient way to do this?
df.unstack().swaplevel().reset_index().values.tolist()
#OR
df.reset_index().melt(id_vars="index").values.tolist()
# [['store1', 'January', 2],
# ['store2', 'January', 5],
# ['store3', 'January', 8],
# ['store1', 'February', 3],
# ['store2', 'February', 6],
# ['store3', 'February', 9],
# ['store1', 'March', 4],
# ['store2', 'March', 7],
# ['store3', 'March', 10]]
With following, the order of elements will match the output in the question.
df.transpose().unstack().reset_index().values.tolist()
# [['store1', 'January', 2],
# ['store1', 'February', 3],
# ['store1', 'March', 4],
# ['store2', 'January', 5],
# ['store2', 'February', 6],
# ['store2', 'March', 7],
# ['store3', 'January', 8],
# ['store3', 'February', 9],
# ['store3', 'March', 10]]
True Pandas-style:
lst = [[*k, v] for k, v in df.unstack().swaplevel().to_dict().items()]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With