Tony - 2 months ago 28

Python 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 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
```