How to reshape a dataframe (different type in columns)

Question

I have seen many different answer about this topic. In my situation the case is a bit diffrent , I have the following dataframe

     A    B      C  
0  OPXXX  OPT1 70
1  OPXXX  OPT2 KO
2  OPXXX  OPT3 KO
3  OPXXX  OPT4 B
4  OPXXX  OPT5 175000
5  OPXXX  OPT6 Europ
6  OPXXX  OPT7 2019-01-21
7  OPXXX  OPT8 2019-05-21
8  OPXXX  OPT9 2019-10-21

I would like to have it like that but the issue is pivot.table is not working because of the aggreagtion function:

 A     OPT1  OPT2 OPT3 OPT4 OPT5   OPT6         OPT7       OPT8         OPT9    
OPXXX   70     KO    KO   B 175000  Europe    2019-01-21  2019-10-21  2019-10-21

when I do : df.pivot(index='A',values='C', columns= 'B')

Ihave the following error

Traceback (most recent call last): File "C:/Users/V002697/PycharmProjects/portia/tiaex.py", line 17, in print(df.pivot(index='ticker',values='value', columns= 'field')) File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\frame.py", line 3853, in pivot return pivot(self, index=index, columns=columns, values=values) File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 378, in pivot return indexed.unstack(columns) File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\series.py", line 2028, in unstack return unstack(self, level, fill_value) File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 458, in unstack fill_value=fill_value) File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 110, in init self._make_selectors() File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\reshape\reshape.py", line 148, in _make_selectors raise ValueError('Index contains duplicate entries, ' ValueError: Index contains duplicate entries, cannot reshape

I would like to know if you have some ideas ?

Thanks

The error is saying that you have duplicated items in 'B' thus it cannot be made into an index. — Chris
In column 'A' I have duplicates tike in my example, 'B' does not have any duplicates — Prou Prou Tyu
Example you provide works perfectly fine. Can you check if len(df.B) - df.B.nunique() is 0? — Chris

Chris Chris · Accepted Answer · 2019-02-26T10:54:57

You can use pandas.DataFrame.pivot:

df.pivot(index='A', columns = 'B', values='C')

B     OPT1 OPT2 OPT3 OPT4    OPT5   OPT6        OPT7        OPT8        OPT9
A                                                                           
OPXXX   70   KO   KO    B  175000  Europ  2019-01-21  2019-05-21  2019-10-21

How to reshape a dataframe (different type in columns)

2 Answers