Ad
Count Of Unique Values By Groupby In Two Columns
I want to determine the count
of unique
values based off two columns
in a pandas
df
.
Below is an example:
import pandas as pd
d = ({
'B' : ['08:00:00','John','08:10:00','Gary','08:41:42','John','08:50:00','John', '09:00:00', 'Gary','09:15:00','John','09:21:00','Gary','09:30:00','Gary','09:40:00','Gary'],
'C' : ['1','1','1','1','1','1','2','2','2', '2','2','2','3','3','3', '3','3','3'],
'A' : ['Stop','','Res','','Start','','Stop','','Res','','Start','','Stop','','Res','','Start','']
})
df = pd.DataFrame(data=d)
Output:
A B C
0 Stop 08:00:00 1
1 John 1
2 Res 08:10:00 1
3 Gary 1
4 Start 08:41:42 1
5 John 1
6 Stop 08:50:00 2
7 John 2
8 Res 09:00:00 2
9 Gary 2
10 Start 09:15:00 2
11 John 2
12 Stop 09:21:00 3
13 Gary 3
14 Res 09:30:00 3
15 Gary 3
16 Start 09:40:00 3
17 Gary 3
If I perform the count based of Column A
and C
I return the following:
k = df.groupby('A').C.nunique()
Res 3
Start 3
Stop 3
I'm hoping to split those up based on the people in Column B
. So the intended output would be:
John Stop 2
Res 0 #Nan
Start 2
Gary Stop 1
Res 3
Start 1
I have tried k = df.groupby('A').B.C.nunique()
Ad
Answer
we can create a flattened DF:
In [34]: d = pd.DataFrame(np.column_stack((df.iloc[::2], df.iloc[1::2, [0]])), columns=['time','id','op','name'])
In [35]: d
Out[35]:
time id op name
0 08:00:00 1 Stop John
1 08:10:00 1 Res Gary
2 08:41:42 1 Start John
3 08:50:00 2 Stop John
4 09:00:00 2 Res Gary
5 09:15:00 2 Start John
6 09:21:00 3 Stop Gary
7 09:30:00 3 Res Gary
8 09:40:00 3 Start Gary
prepare a multi-index, which will include all combinations:
In [36]: idx = pd.MultiIndex.from_product((d.name.unique(), d.op.unique()))
and group by two columns:
In [39]: res = d.groupby(['name','op'])['id'].count().reindex(idx, fill_value=0)
In [40]: res
Out[40]:
John Stop 2
Res 0
Start 2
Gary Stop 1
Res 3
Start 1
Name: id, dtype: int64
Ad
source: stackoverflow.com
Related Questions
- → What are the pluses/minuses of different ways to configure GPIOs on the Beaglebone Black?
- → Django, code inside <script> tag doesn't work in a template
- → React - Django webpack config with dynamic 'output'
- → GAE Python app - Does URL matter for SEO?
- → Put a Rendered Django Template in Json along with some other items
- → session disappears when request is sent from fetch
- → Python Shopify API output formatted datetime string in django template
- → Can't turn off Javascript using Selenium
- → WebDriver click() vs JavaScript click()
- → Shopify app: adding a new shipping address via webhook
- → Shopify + Python library: how to create new shipping address
- → shopify python api: how do add new assets to published theme?
- → Access 'HTTP_X_SHOPIFY_SHOP_API_CALL_LIMIT' with Python Shopify Module
Ad