Exporting a pivot table to csv

Joseph Darton 0
Viewed 26

When creating a pivot table using the following code:

df = df.pivot_table(index=['Player', 'Pos'], values=['Min'], aggfunc='sum')

The csv export returns:

Player    Pos        Min
A         GK         450
B         CM         1
B         DM         166
C         RB         302
C         RB,CB      169

How would I edit the code to return the following?:

Player    Pos        Min
A         GK         450
B         CM, DM     167
C         RB, CB     471
dataframe pivot-table pandas python
1 Answers

Use custom lambda function for remove duplicated values after join Pos strings in GroupBy.agg:

#if there is space or not space after separator ,
f = lambda x:  ', '.join(dict.fromkeys(','.join(x).replace(' ','').split(',')))

#if there is space after separator ,
#f = lambda x: ', '.join(dict.fromkeys(', '.join(x).split(', ')))
df1 = df.groupby('Player', as_index=False).agg(Pos=('Pos',f),Min=('Min','sum'))
print (df1)
  Player     Pos  Min
0      A      GK  450
1      B  CM, DM  167
2      C  RB, CB  471

Or:

df1 = df.groupby('Player', as_index=False).agg({'Pos': f, 'Min':'sum'})
jezrael0
Related