Python PivotTable/Groupby Text Columns

Question

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.

Answer 1

How about pd.crosstab? Read in your data as is, and then transform:

In [227]: pd.crosstab(df.Group, df.Function)
Out[227]: 
Function  A  B  C  D  E
Group                  
1         1  1  1  0  0
2         1  0  1  0  0
3         1  0  1  1  0
4         0  0  0  0  1

Use df.replace to get rid of zeros:

In [228]: pd.crosstab(df.Group, df.Function).replace(0, '')
Out[228]: 
Function  A  B  C  D  E
Group                  
1         1  1  1      
2         1     1      
3         1     1  1   
4                     1