CODEWITHSUNDEEP
pythonpandasdataframe
Khaled Koubaa
Khaled Koubaa

Reputation: 547

dataframe pivot return error: Index contains duplicate entries

The initial dataframe: 

df

Comp    Time               Match    Odds    H          A    Res  
GER D2  13:00:00    Tem1 v Team2    op      2.07    3.66    2-3(1-0)    
GER D2  13:00:00    Tem1 v Team2    cl      2.41    3.02    2-3(1-0)    
GER D1  20:30:00    Tem3 v Team4    op      5.07    1.71    3-3(1-2)    
GER D1  20:30:00    Tem3 v Team4    cl      4.76    1.71    3-3(1-2)    
FRA D2  20:00:00    Tem5 v Team6    op      2.34    3.42    1-0(1-0)    
FRA D2  20:00:00    Tem5 v Team6    cl      2.08    3.99    1-0(1-0)

I reshape it by pivot and it goes well using this code

pf = df.pivot(index='Match', columns='Odds')#, values='Home Win')
pf.columns = ['_'.join(col).rstrip('_') for col in pf.columns.values]
pf.reset_index(inplace=True)
pf = pf[['Match', 'Comp_cl', 'Date_cl','H_op', 'H_cl', 'A_op',  'A_cl', 'Res_cl']]
pf = pf.rename(columns = {'Comp_cl':'Comp', 'Date_cl':'Date', 'Res_cl': 'Res' })

this is the result:

Comp    Time               Match    H_op    H_cl    A_op    A_cl    Res
GER D2  13:00:00    Tem1 v Team2    2.07    2.41    3.66    3.02    2-3(1-0)
GER D1  20:30:00    Tem3 v Team4    5.07    4.76    1.71    1.71    3-3(1-2)
FRA D2  20:00:00    Tem5 v Team6    2.34    2.08    3.42    3.99    1-0(1-0)

Then, when I added more data and added date column to the initial dataframe, 

df_extended

Comp    Time           Match       Odds    H     A       Res        Date
GER D2  13:00:00    Tem1 v Team2    op  2.07    3.66    2-3(1-0)    2019-05-04
GER D2  13:00:00    Tem1 v Team2    cl  2.35    3.05    2-3(1-0)    2019-05-04
GER D1  20:30:00    Tem3 v Team4    op  5.07    1.71    3-3(1-2)    2019-05-04
GER D1  20:30:00    Tem3 v Team4    cl  6.50    1.66    3-3(1-2)    2019-05-04
FRA D2  20:00:00    Tem5 v Team6    op  2.34    3.42    1-0(1-0)    2019-05-03
FRA D2  20:00:00    Tem5 v Team6    cl  1.80    8.06    1-0(1-0)    2019-05-03
JAP D2  10:00:00    Tem7 v Team8    op  10.23   1.21    0-0(0-0)    2019-05-03
JAP D2  10:00:00    Tem7 v Team8    cl  12.50   1.11    0-0(0-0)    2019-05-03
…   …   …       …   …   …   …
MEX D1  12:00:00    Team12 v Team13 op  2.10    2.05    1-0(1-0)    2019-05-05
MEX D1  12:00:00    Team12 v Team13 op  2.10    2.03    1-0(1-0)    2019-05-05
USA D1  20:00:00    Team1 v Team5   cl  1.78    2.60    5-2(3-0)    2019-05-05
USA D1  20:00:00    Team1 v Team5   cl  1.88    2.66    5-2(3-0)    2019-05-05
GER D2  20:00:00    Team20 v Team2  op  1.74    3.59    2-2(0-0)    2019-05-06
GER D2  20:00:00    Team20 v Team2  op  1.75    3.60    2-2(0-0)    2019-05-06
GER D1  20:00:00    Team1 v Team6   cl  1.30    3.42    1-0(1-0)    2019-05-06
GER D1  20:00:00    Team1 v Team6   cl  1.30    3.42    1-0(1-0)    2019-05-06

and try to do the same (reshape it), and apply the same code, but I got an error

ValueError: Index contains duplicate entries, cannot reshape

I already checked all the answers to similar questions in stack, I tried every solution, but none of them resolve the problem

Upvotes: 0

Views: 139

Answers (2)

prosti
prosti

Reputation: 46361

It is the indexing problem basically. So depending on what you need you may use pivot_table because pivot doesn't accept list index and pivot_table accepts.

Here is what you may find useful:

#add more columns in the index if you need 
pf = df.pivot_table(index=['Match', 'Comp', 'Time', 'Res'], columns='Odds')
pf['date'] = "2019-05-06"
pf.columns = ['_'.join(col).rstrip('_') for col in pf.columns.values]
print(pf)

And I got:

                                       A_cl  A_op  H_cl  H_op        date
Match        Comp   Time     Res                                         
Tem1 v Team2 GER D2 13:00:00 2-3(1-0)  3.02  3.66  2.41  2.07  2019-05-06
Tem3 v Team4 GER D1 20:30:00 3-3(1-2)  1.71  1.71  4.76  5.07  2019-05-06
Tem5 v Team6 FRA D2 20:00:00 1-0(1-0)  3.99  3.42  2.08  2.34  2019-05-06

Upvotes: 1

Valdi_Bo
Valdi_Bo

Reputation: 30991

Your code probably failed because pivot does not allow source data with repeated values in columns specified with index and columns parameters.

Maybe a method to circumvent this limitation is:

  • Group your DataFrame into consecutive pairs of rows (data for the same match for Odds == both op and cl).
  • Apply a function to each of the above groups.

So you should define a function to be applied as:

def fn(src):
    wrk = pd.Series(src.H.append(src.A, ignore_index=True))
    wrk.index=['H_op', 'H_cl', 'A_op', 'A_cl']
    row0 = src.iloc[0]
    return pd.concat([row0[['Comp', 'Time', 'Match']], wrk, row0[['Res']]])

Note that this function:

  • Creates a work Series by concatenation of H and A columns, dropping the existing index and setting the proper index (which then will be column names in the result).
  • Takes all other values (Comp, Time, Match and Res) from the first row.
  • Returns a Series with proper order of source values.

Then the only thing to do is to apply this function to each pair of source rows:

df.groupby(np.arange( len(df.index)) // 2).apply(fn)

Upvotes: 1

Related Questions