Python Pandas iterate over a dataframe

Question

I have a dataframe that has keys in one column and values in another column. I'm trying to find a way to make this into a 3 column dataframe. But....if I'm missing a 1 or 2 key value, I want pandas to populate an N/A.

What I have:

   | key | Number |
   +-----+--------+
   |   0 |     10 |
   |   1 |     20 |
   |   2 |     30 |
   |   0 |     40 |
   |   1 |     50 |
   |   2 |     60 |
   +-----+--------+



What I want:

+---------+---------+---------+
| Column1 | Column2 | Column3 |
+---------+---------+---------+
|      10 |      20 |      30 |
|      40 |      50 |      60 |
+---------+---------+---------+

Welcome to stack overflow. Please have a look at how to create a [mcve] and show _code_ for what you've tried so far based on your own research — G. Anderson, Aug 28 '20 at 21:42
`df.melt` might be what you are looking for i think `df.melt('key')` maybe? — Joran Beasley, Aug 28 '20 at 21:45

score 3 · Answer 1 · answered Aug 28 '20 at 22:02

You can also, do this using dataframe reshaping:

df.set_index([df.groupby('key').cumcount(), 'key'])['Number']\
  .unstack()\
  .rename(columns=lambda x: f'Column{x+1}')

Output:

key  Column1  Column2  Column3
0         10       20       30
1         40       50       60

Or

df.assign(row=df.groupby('key').cumcount())\
  .pivot('row','key','Number')\
  .rename(columns=labmda x: f'Column{x+1}')

Output:

key  Column1  Column2  Column3
row                           
0         10       20       30
1         40       50       60

score 1 · Answer 2 · answered Aug 28 '20 at 21:55

You can create a new DataFrame using a dict-comprehension over a groupby:

>>> df
   key  Number
0    0      10
1    1      20
2    2      30
3    0      40
4    1      50
5    2      60
>>> pd.DataFrame({"Column{}".format(k+1):list(v) for k,v in df.Number.groupby(df.key)})
   Column1  Column2  Column3
0       10       20       30
1       40       50       60

Python Pandas iterate over a dataframe

2 Answers2