How to conditionally convert rows to columns?

Question

I have the following pandas DataFrame:

df:

date        col1   col2
2020/10/29  A      1
2020/10/30  A      2
2020/10/28  B      10 
2020/10/29  B      11
2020/10/30  B      8

I need to transform it into the following DataFrame:

date        A   B
2020/10/28  Nan 10
2020/10/29  1   11
2020/10/30  2   8

How can I do it?

Does this answer your question? [How to pivot a dataframe?](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) — Henry Ecker, Jun 01 '21 at 12:21

Samir Hinojosa · Accepted Answer · 2021-06-01T12:33:16.290

1

You can try with pivot as follows.

df = df.pivot(index="date", columns="col1", values="col2")

The output

      col1   A        B
      date      
2020/10/28  NaN     10.0
2020/10/29  1.0     11.0
2020/10/30  2.0     8.0

edited Jun 01 '21 at 12:33

answered Jun 01 '21 at 12:20

Samir Hinojosa

825
7
24

score 0 · Answer 2 · answered Jun 01 '21 at 12:20

What you need to do is to first group by both date and col1 and then unstack it:

df = pd.DataFrame(data=[{'date': '2020/10/29', 'col1': 'A', 'col2': '1'},
                        {'date': '2020/10/30', 'col1': 'A', 'col2': '2'},
                        {'date': '2020/10/28', 'col1': 'B', 'col2': '10 '},
                        {'date': '2020/10/29', 'col1': 'B', 'col2': '11'},
                        {'date': '2020/10/30', 'col1': 'B', 'col2': '8'}])
df = df.groupby(['col1', 'date'])['col2'].sum().unstack().T.reset_index()

How to conditionally convert rows to columns?

2 Answers2