Excel: How to count total number of unique values based on a value from a different column
Tag : excel , By : user107506
Date : March 29 2020, 07:55 AM
like below fixes the issue Here is an array formula that should accomplish what you want. To enter an array formula, hold down ctrl-shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula. =SUM(IFERROR(1/COUNTIFS(order_id,order_id,referral,"EMP_BC"),0))
Option Explicit
Option Compare Text 'Comment out this line to make case SENSITIVE
Function UniqueOrdersByReferral(rOrderID As Range, rReferral As Range, sReferralCode As String) As Long
Dim Col As Collection
Dim I As Long, vOID As Variant, vREF As Variant
vOID = rOrderID
vREF = rReferral
If UBound(vOID) <> UBound(vREF) Then
MsgBox "Order ID and Referral Ranges must be of same size"
Exit Function
End If
Set Col = New Collection
On Error Resume Next
For I = 1 To UBound(vOID)
If vREF(I, 1) = sReferralCode Then Col.Add vOID(I, 1), CStr(vOID(I, 1))
Next I
On Error GoTo 0
UniqueOrdersByReferral = Col.Count
End Function
|
(Python) How to group unique values in column with total of another column
Date : March 29 2020, 07:55 AM
hop of those help? If the 1s and 0s are boolean flags for each category then you should just need sum. df[df.country_code == 'USA'].groupby('state_code').sum().reset_index()
# state_code commerce finance software
#0 CA 0 0 1
|
Find total number of unique values in a column based on condition in another column
Date : March 29 2020, 07:55 AM
To fix the issue you can do Use numpy.where with chaining 3 boolean mask by & for bitwise AND: m = df['ad_type'] == 'sale'
#get groups with values before sale
vals = df.loc[m.groupby(df['sender_id']).cumsum() == 0, 'sender_id'].unique()
m1 = df['sender_id'].isin(vals)
#get last duplicated value per groups - for last sale
m2 = ~df.loc[m, 'sender_id'].duplicated(keep='last').reindex(df.index, fill_value=False)
df['count'] = np.where(m & m1 & m2, 'yes', '')
print (df)
sender_id reply_date ad_type count
0 1234 2016-05-16 sharing
1 1234 2017-06-20 sale yes
2 3333 2016-05-16 rental
3 3333 2016-06-20 sale
4 3333 2016-06-21 sale yes
5 6767 2016-05-16 sale
6 101 2016-04-16 sale
7 101 2016-04-17 sale
8 9999 2016-01-01 rental
9 9999 2017-01-19 sharing
10 9999 2018-04-17 sale yes
|
How do I use the tidyverse packages to get a running total of unique values occurring in a column?
Date : March 29 2020, 07:55 AM
I wish this helpful for you I'm trying to use the tidyverse (whatever package is appropriate) to add a column (via mutate()) that is a running total of the unique values that have occurred in the column so far. Here is some toy data, showing the desired output. , Here is an option with group_indices library(dplyr)
df1%>%
mutate(unique_cumsum = group_indices(., n))
# n unique_cumsum
#1 1 1
#2 1 1
#3 1 1
#4 6 2
#5 7 3
#6 8 4
#7 8 4
df1 <- data.frame("n"=c(1,1,1,6,7,8,8))
|
For unique values in one column get the total count of unique values in another column
Tag : python , By : adrianmooreuk
Date : March 29 2020, 07:55 AM
it should still fix some issue I have two a pyodbc row object that looks like: , It should be something like this: import collections
import itertools
data = [
('Emp1', 'Absent'),
('Emp1', 'Absent'),
('Emp1', 'Present'),
('Emp2', 'Present'),
('Emp2', 'Present'),
('Emp2', 'Absent'),
('Emp2', 'Present'),
('Emp2', 'Absent'),
]
sorted_data = sorted(data, key = lambda x: (x[0], x[1])) # sort our data
employees = collections.defaultdict(dict)
# group by employee
for employee, employee_group in itertools.groupby(sorted_data, lambda item: item[0]):
# group by category
for category, category_group in itertools.groupby(employee_group, lambda item: item[1]):
employees[employee][category] = sum(1 for _ in category_group)
print('employees', employees) # employees defaultdict(<class 'dict'>, {'Emp1': {'Absent': 2, 'Present': 1}, 'Emp2': {'Absent': 2, 'Present': 3}})
|
Boards Message :
Related QUESTIONS :