Pivot PivotTable / Groupby Column

Question

I am trying to expand the horizons of python and learn how to do something that is pretty 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 a table that displays information in this format (executed using a pivot table in Excel with columns: function, rows: group, values: number of groups)

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

I created a data framework and added a column as shown below:

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

print(df.groupby('GROUP'))

and

1) It does not recognize the Function field because dtype: object 2) It really does not fulfill the function I'm looking for, which makes me think that this is probably not the function I need. I also tried to work with various pivot_table iterations, but didn't seem to be able to get this to work.

Does anyone have any ideas? Thanks in advance.

Answer 1

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

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

Answer 2

pd.crosstab? , :

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

df.replace, :

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

Answer 3

Option 1

pd.get_dummies(df.Group).T.dot(pd.get_dummies(df.Function))

   A  B  C  D  E
1  1  1  1  0  0
2  1  0  1  0  0
3  1  0  1  1  0
4  0  0  0  0  1

---

Option 2

i, r = pd.factorize(df.Group.values)
j, c = pd.factorize(df.Function.values)
a = np.zeros((r.size, c.size), dtype=int)
a[i, j] = 1
pd.DataFrame(a, r, c)

   A  B  C  D  E
1  1  1  1  0  0
2  1  0  1  0  0
3  1  0  1  1  0
4  0  0  0  0  1

---

Opttion 3

i, r = pd.factorize(df.Group.values)
j, c = pd.factorize(df.Function.values)
a = np.bincount(
    i * c.size + j, minlength=r.size * c.size
).reshape(r.size, c.size)
pd.DataFrame(a, r, c)

   A  B  C  D  E
1  1  1  1  0  0
2  1  0  1  0  0
3  1  0  1  1  0
4  0  0  0  0  1