Tony Tony - 2 months ago 28
Python Question

Python PivotTable/Groupby Text Columns

I'm trying to broaden my python horizons and learn to do something that is fairly simple to execute in Excel.

I have this data:

Group Function
1 A
1 B
1 C
2 A
2 C
3 C
3 A
3 D
4 E


And I would like to a table that shows the information in this format (accomplished with a pivot table in Excel with Columns: Function, Rows: Group, Values: Count of Group)

Function
Group A B C D E
1 1 1 1
2 1 1
3 1 1 1
4 1


I've created a dataframe and added a column as below:

df = pd.read_excel(filepath)
df['1']=1

print(df.groupby('GROUP'))


but:

1) It's not recognizing the Function field because it's dtype: object
2) It's not really perfomring the function I'm looking for, which makes me think that it's probably not the function I need. I've also tried to work various iterations of pivot_table but can't seem to get that to work etiher.

Does anyone have any ideas? Thanks in advance.

Wen Wen
Answer Source

Or using pivot

df1['val']=1
df1.pivot(index='Group', columns='Function')['val']

Function    A    B    C    D    E
Group                            
1         1.0  1.0  1.0  NaN  NaN
2         1.0  NaN  1.0  NaN  NaN
3         1.0  NaN  1.0  1.0  NaN
4         NaN  NaN  NaN  NaN  1.0


df1.pivot(index='Group', columns='Function')['val'].fillna(' ')

Function  A  B  C  D  E
Group                  
1         1  1  1      
2         1     1      
3         1     1  1   
4                     1

Or using groupby :

df1.groupby(['Group','Function']).size().unstack().fillna(' ')
Function  A  B  C  D  E
Group                  
1         1  1  1      
2         1     1      
3         1     1  1   
4                     1