Create a new column in a Pandas DataFrame from existing column names

Question

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?

d.b · Accepted Answer · 2021-11-10 00:27:23Z

10

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]]

edited Nov 10, 2021 at 0:27

answered Nov 10, 2021 at 0:03

d.b

32.6k6 gold badges46 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user17242583 Over a year ago

This is definitely the best (although mine is good, this is more concise and even more "true Pandas-style").

Larry the Llama Over a year ago

Does it need to be in the same order?

d.b Over a year ago

@LarrytheLlama, you could do df.unstack().swaplevel().reset_index().sort_values("level_0").values.tolist() if the order is important

user17242583 · Accepted Answer · 2021-11-10 00:04:16Z

6

True Pandas-style:

lst = [[*k, v] for k, v in df.unstack().swaplevel().to_dict().items()]

answered Nov 10, 2021 at 0:04

user17242583

1 Comment

Celius Stingher Over a year ago

Beautiful use of nest list comprehensions and multiple pandas methods!

U13-Forward · Accepted Answer · 2021-11-10 03:20:40Z

2

I'd prefer stacking over unstacking then swapping the levels:

>>> df.stack().reset_index().to_numpy()
array([['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]], dtype=object)
>>>

Or with melt and ignore_index=False:

>>> df.melt(ignore_index=False).reset_index().to_numpy()
array([['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]], dtype=object)
>>>

edited Nov 10, 2021 at 3:20

answered Nov 10, 2021 at 3:14

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

1 Comment

user17242583 Over a year ago

This is a good one too!

Larry the Llama · Accepted Answer · 2021-11-09 23:58:43Z

1

The structure that you want your data in is very messy, so this is probably the best method given the data you want.

# Results
res = []

# Nested loop: first for length of index col, then next for cols
for i in range(len(index_col)):
    for j in range(len(cols)):
        # Format of data
        res.append([index_col[i], cols[j], values[i][j]])

# Return results
print(res)
return res

answered Nov 9, 2021 at 23:58

Larry the Llama

1,1206 silver badges17 bronze badges

Comments

fam-woodpecker · Accepted Answer · 2021-11-10 00:01:35Z

1

You can iterate over dataframe items using

data = []

for col, row in df.items():
    for ind, val in row.reset_index().values:
        data.append([ind, col, val])

data

You could avoid the second loop for sacrificing the order you requested the output in as it is a bit of a full breakdown of how the structure started.

answered Nov 10, 2021 at 0:01

fam-woodpecker

7175 silver badges16 bronze badges

Comments

sammywemmy · Accepted Answer · 2021-11-10 00:08:28Z

1

temp = df.stack()

[[*ent, val] for ent, val in zip(temp.index, temp)]

[['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]]

answered Nov 10, 2021 at 0:08

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Create a new column in a Pandas DataFrame from existing column names

6 Answers 6

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related