This is an update to the structure of my DataFrame, I formulated the structure in haste, I was inspecting a single user and mocked up that structure. @liliscent's remark: "data accidentally satisfies this condition" is also true and value_counts and cum_sum() solves it. But then user_id's also change, and different user's can have the same meet_id if they have the same text.
这是对我的DataFrame结构的更新，我匆忙地制定了这个结构，检查了一个用户并模拟了这个结构。@ li气味的评论:“数据意外满足这个条件”也是正确的，value_counts和cum_sum()解决了这个问题。但是user_id也会改变，如果不同的用户有相同的文本，他们可以有相同的_meetid。
Updated DataFrames structure:
更新DataFrames结构:
mytable = pd.DataFrame({'user_id': [ '3c', '3c', '3c', '3c','3c', '3c', '3c', '3c', '3c', '3c', '3c', '3c', '3c', '3d',
'3d', '3d', '3d', '3e', '3e', '3r', '3w', '3w', '3w', '3w'],
'meet_id': [1,1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,4,5,6,1,2,1,1], 'text': ['abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc',
'xyz', 'xyz', 'xyz', 'xyz', 'xyz', 'xyz', 'npq', 'npq', 'npq', 'npq', 'tt', 'op', 'li', 'abc', 'xyz', 'abc', 'abc'], 'label': ['A', 'A', 'A', 'A', 'A','B', 'B', 'B', 'B', 'B',
'C', 'C', 'A', 'G', 'H', 'H', 'H', 'A', 'A', 'B', 'E', 'G', 'B', 'B']})
mytable = mytable[['user_id', 'meet_id', 'text', 'label']] # ordering columns in the way I would like to be printed out.
user_id meet_id text label
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc B
3c 1 abc B
3c 2 xyz B
3c 2 xyz B
3c 2 xyz B
3c 2 xyz C
3c 2 xyz C
3c 2 xyz A
3d 3 npq G
3d 3 npq H
3d 3 npq H
3d 3 npq H
3e 4 tt A
3e 5 op A
3r 6 li B
3w 1 abc E
3w 2 xyz G
3w 1 abc B
3w 1 abc B
I would like to groupby on [user_id & meet_id] column and concatenate the label column in such a way that the label with higher frequency for that group is left untouched, while the second most frequent label will have the first label concatenated, and the last label will have all labels concatenated.
我想groupby[user_id & meet_id]列和连接列这样的标签标签与高频率组保持不变,而第二个最频繁的标签将有第一个标签连接,最后连接标签将所有标签。
updated DataFrame output is what I am looking for
更新的DataFrame输出是我正在寻找的。
mytable_pro = pd.DataFrame({'user_id': ['3c', '3c', '3c', '3c','3c', '3c', '3c', '3c', '3c', '3c', '3c', '3c', '3c','3d',
'3d', '3d', '3d', '3e', '3e', '3r', '3w', '3w', '3w', '3w'],
'meet_id': [1,1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,4,5,6,1,2,1,1], 'text': ['abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc',
'xyz', 'xyz', 'xyz', 'xyz', 'xyz', 'xyz','npq', 'npq', 'npq', 'npq', 'tt', 'op', 'li', 'abc', 'xyz', 'abc', 'abc' ], 'label': ['A', 'A', 'A', 'A', 'A', 'B,A', 'B,A', 'B', 'B', 'B',
'B, C', 'B, C', 'A,B,C', 'H,G', 'H', 'H', 'H', 'A', 'A', 'B', 'E,B', 'G', 'B', 'B']})
mytable_pro = mytable_pro[['user_id', 'meet_id', 'text', 'label']] # ordering columns in the way I would like to be printed out.
This gives:
这给:
user_id meet_id text label
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc B,A
3c 1 abc B,A
3c 2 xyz B
3c 2 xyz B
3c 2 xyz B
3c 2 xyz B, C
3c 2 xyz B, C
3c 2 xyz A,B,C
3d 3 npq H,G
3d 3 npq H
3d 3 npq H
3d 3 npq H
3e 4 tt A
3e 5 op A
3r 6 li B
3w 1 abc E,B
3w 2 xyz G
3w 1 abc B
3w 1 abc B
The answer given by @piRSquared:
@piRSquared给出的答案是:
mytable.groupby('meet_id').label.value_counts().groupby('meet_id').apply(
lambda d: d.index.to_series().str[1].cumsum().str.join(', '))
is the CORRECT ANSWER for the WRONG question I asked, thanks a ton and really sorry. It solves the ordering problem as mentioned previously but would not work if a different user has the same meet_id. Just to be exhaustive, if the label frequency turns out to be equal for a group, it does not matter which of the label gets the other concatenated.
是我问错的问题的正确答案，非常感谢，非常抱歉。它解决了前面提到的排序问题，但如果另一个用户拥有相同的meet_id，则无法工作。简单地说，如果标签的频率对于一个组来说是相等的，那么标签的哪个得到另一个连接并不重要。
It gives:
它给:
user_id meet_id text label
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc A
3c 1 abc A, B
3c 1 abc A, B
3c 2 xyz B
3c 2 xyz B
3c 2 xyz B
3c 2 xyz B, C
3c 2 xyz B, C
3c 2 xyz B, C, A
3d 3 npq H, G
3d 3 npq H
3d 3 npq H
3d 3 npq H
3e 4 tt A
3e 5 op A
3r 6 li B
3w 1 abc A, B, E
3w 2 xyz B, C, A, G
3w 1 abc A, B
3w 1 abc A, B
The labels for 3w are off since the labels for meet_id are picked up ignoring the difference is user_id. My bad!
3w的标签是关闭的，因为会议id的标签被选中，而忽略了不同的是user_id。我的坏!
Now, since user_id must also be considered, I tried the following:
现在，由于user_id也必须考虑，我尝试了以下操作:
s = mytable.groupby(['user_id', 'meet_id']).label.value_counts().groupby(['user_id, 'meet_id']).apply(
lambda d: d.index.to_series().str[1].cumsum().str.join(', '))
This throws:
这一扔:
AttributeError: Can only use .str accessor with string values, which use np.object_ dtype in pandas
Ah! Another small update, In reality I have words in my label column.
啊!另一个小更新，实际上我的标签列中有单词。
dummy_boo = pd.DataFrame({'user_id': ['3g', '3g', '3g'], 'meet_id': [9,9,9], 'text': ['baby', 'baby', 'baby'], 'label':['hello', 'hello', 'why']}
Output:
输出:
user_id meet_id text label
3g 9 baby hello
3g 9 baby hello
3g 9 baby why
Applying the above code is resulting in each character being separated by a comma.
应用上述代码会导致每个字符被逗号分隔。
user_id meet_id text label
3g 9 baby h, e, l, l, o
3g 9 baby h, e, l, l, o
3g 9 baby h, e, l, l, o, w, h, y
Instead I need:
我需要:
user_id meet_id text label
3g 9 baby hello
3g 9 baby hello
3g 9 baby hello, why
The dtype for label is object. Should we use astype instead. A Big thank you to everyone for helping me out.
标签的dtype是对象。我们应该用astype来代替吗?非常感谢大家对我的帮助。
3 个解决方案
#1
8
value_counts and cumsum
value_counts sorts by descending count
value_count按下降计数排序。
cols = ['meet_id', 'user_id']
s = mytable.groupby(cols).label.value_counts().groupby(cols).apply(
lambda d: d.index.to_series().str[-1].cumsum().str.join(', ')
)
mytable.assign(label=[s.get((a, b, c)) for a, b, c in mytable[cols + ['label']].values])
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz B, C, A
13 3d 3 npq H, G
14 3d 3 npq H
15 3d 3 npq H
16 3d 3 npq H
17 3e 4 tt A
18 3e 5 op A
19 3r 6 li B
20 3w 1 abc B, E
21 3w 2 xyz G
22 3w 1 abc B
23 3w 1 abc B
Include sorted as well
cols = ['meet_id', 'user_id']
s = mytable.groupby(cols).label.value_counts().groupby(cols).apply(
lambda d: d.index.to_series().str[-1].cumsum().apply(sorted).str.join(', ')
)
mytable.assign(label=[s.get((a, b, c)) for a, b, c in mytable[cols + ['label']].values])
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz A, B, C
13 3d 3 npq G, H
14 3d 3 npq H
15 3d 3 npq H
16 3d 3 npq H
17 3e 4 tt A
18 3e 5 op A
19 3r 6 li B
20 3w 1 abc B, E
21 3w 2 xyz G
22 3w 1 abc B
23 3w 1 abc B
And to adjust for words rather than single characters
调整单词而不是单个字符
cols = ['meet_id', 'user_id']
s = mytable.groupby(cols).label.value_counts().groupby(cols).apply(
lambda d: d.index.to_series().str[-1].add('|').cumsum().apply(
lambda e: ', '.join(sorted(e.strip('|').split('|')))
)
)
mytable.assign(label=[s.get((a, b, c)) for a, b, c in mytable[cols + ['label']].values])
Old Answer
With transform and a custom cumulative unique function
具有转换和自定义累积唯一函数
from collections import Counter
def cum_unique(x):
return pd.Series(list(map(
Counter, x
))).cumsum().str.join(', ')
mytable.assign(label=mytable.groupby('meet_id').label.transform(cum_unique))
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz B, C, A
Shortened version
缩短版本
mytable.assign(label=mytable.groupby('meet_id').label.transform(
lambda x: pd.Series(list(map(Counter, x))).cumsum().str.join(', ')
))
Per comment
每评论
by liliscent
由liliscent
We can sort first by meet_id and group size
我们可以先按meet_id和组大小排序
sizes = mytable.groupby(['meet_id', 'label']).label.transform('size')
m1 = mytable.assign(sizes=sizes).sort_values(
['meet_id', 'sizes'], ascending=[True, False]).drop('sizes', 1)
m1
m1.assign(label=m1.groupby('meet_id').label.transform(
lambda x: pd.Series(list(map(Counter, x))).cumsum().str.join(', ')
)).reindex(mytable.index)
#2
6
You could try something like the following:
你可以试试以下方法:
mytable['label'] = (mytable.groupby('meet_id')
.label.transform(lambda x: list(x.cumsum()))
.apply(set))
>>> mytable
user_id meet_id text label
0 3c 1 abc {A}
1 3c 1 abc {A}
2 3c 1 abc {A}
3 3c 1 abc {A}
4 3c 1 abc {A}
5 3c 1 abc {A, B}
6 3c 1 abc {A, B}
7 3c 2 xyz {B}
8 3c 2 xyz {B}
9 3c 2 xyz {B}
10 3c 2 xyz {C, B}
11 3c 2 xyz {C, B}
12 3c 2 xyz {C, B, A}
If you want to get rid of the set data type and just have it as a string (as in your desired output), you could apply ', '.join(sorted(set(x)))) instead of simply set (thanks @Wen and @ScottBoston):
如果您想要删除set数据类型并将其作为字符串(如您希望的输出)，您可以应用'、'.join(sort (set(x)))，而不是简单地设置(感谢@Wen和@ScottBoston):
mytable['label'] = (mytable.groupby('meet_id')
.label.transform(lambda x: list(x.cumsum()))
.apply(lambda x: ', '.join(sorted(set(x)))))
>>> mytable
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz A, B, C
#3
3
Edit: Okay much more simple solution:
mytable['label'] = mytable.groupby(['user_id','meet_id','text'])['label']\
.apply(lambda x: x.cumsum()).apply(lambda x: sorted(set(x)))
My ugly attempt:
我的丑陋的尝试:
mytable['label'] = mytable.groupby(['user_id','meet_id','text'])['label']\
.apply(lambda x: x.cumsum().str.extractall('(.)')\
.groupby(level=0)[0].apply(lambda x: sorted(set(x))))
Output:
输出:
user_id meet_id text label
0 3c 1 abc [A]
1 3c 1 abc [A]
2 3c 1 abc [A]
3 3c 1 abc [A]
4 3c 1 abc [A]
5 3c 1 abc [A, B]
6 3c 1 abc [A, B]
7 3c 2 xyz [B]
8 3c 2 xyz [B]
9 3c 2 xyz [B]
10 3c 2 xyz [B, C]
11 3c 2 xyz [B, C]
12 3c 2 xyz [A, B, C]
#1
8
value_counts and cumsum
value_counts sorts by descending count
value_count按下降计数排序。
cols = ['meet_id', 'user_id']
s = mytable.groupby(cols).label.value_counts().groupby(cols).apply(
lambda d: d.index.to_series().str[-1].cumsum().str.join(', ')
)
mytable.assign(label=[s.get((a, b, c)) for a, b, c in mytable[cols + ['label']].values])
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz B, C, A
13 3d 3 npq H, G
14 3d 3 npq H
15 3d 3 npq H
16 3d 3 npq H
17 3e 4 tt A
18 3e 5 op A
19 3r 6 li B
20 3w 1 abc B, E
21 3w 2 xyz G
22 3w 1 abc B
23 3w 1 abc B
Include sorted as well
cols = ['meet_id', 'user_id']
s = mytable.groupby(cols).label.value_counts().groupby(cols).apply(
lambda d: d.index.to_series().str[-1].cumsum().apply(sorted).str.join(', ')
)
mytable.assign(label=[s.get((a, b, c)) for a, b, c in mytable[cols + ['label']].values])
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz A, B, C
13 3d 3 npq G, H
14 3d 3 npq H
15 3d 3 npq H
16 3d 3 npq H
17 3e 4 tt A
18 3e 5 op A
19 3r 6 li B
20 3w 1 abc B, E
21 3w 2 xyz G
22 3w 1 abc B
23 3w 1 abc B
And to adjust for words rather than single characters
调整单词而不是单个字符
cols = ['meet_id', 'user_id']
s = mytable.groupby(cols).label.value_counts().groupby(cols).apply(
lambda d: d.index.to_series().str[-1].add('|').cumsum().apply(
lambda e: ', '.join(sorted(e.strip('|').split('|')))
)
)
mytable.assign(label=[s.get((a, b, c)) for a, b, c in mytable[cols + ['label']].values])
Old Answer
With transform and a custom cumulative unique function
具有转换和自定义累积唯一函数
from collections import Counter
def cum_unique(x):
return pd.Series(list(map(
Counter, x
))).cumsum().str.join(', ')
mytable.assign(label=mytable.groupby('meet_id').label.transform(cum_unique))
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz B, C, A
Shortened version
缩短版本
mytable.assign(label=mytable.groupby('meet_id').label.transform(
lambda x: pd.Series(list(map(Counter, x))).cumsum().str.join(', ')
))
Per comment
每评论
by liliscent
由liliscent
We can sort first by meet_id and group size
我们可以先按meet_id和组大小排序
sizes = mytable.groupby(['meet_id', 'label']).label.transform('size')
m1 = mytable.assign(sizes=sizes).sort_values(
['meet_id', 'sizes'], ascending=[True, False]).drop('sizes', 1)
m1
m1.assign(label=m1.groupby('meet_id').label.transform(
lambda x: pd.Series(list(map(Counter, x))).cumsum().str.join(', ')
)).reindex(mytable.index)
#2
6
You could try something like the following:
你可以试试以下方法:
mytable['label'] = (mytable.groupby('meet_id')
.label.transform(lambda x: list(x.cumsum()))
.apply(set))
>>> mytable
user_id meet_id text label
0 3c 1 abc {A}
1 3c 1 abc {A}
2 3c 1 abc {A}
3 3c 1 abc {A}
4 3c 1 abc {A}
5 3c 1 abc {A, B}
6 3c 1 abc {A, B}
7 3c 2 xyz {B}
8 3c 2 xyz {B}
9 3c 2 xyz {B}
10 3c 2 xyz {C, B}
11 3c 2 xyz {C, B}
12 3c 2 xyz {C, B, A}
If you want to get rid of the set data type and just have it as a string (as in your desired output), you could apply ', '.join(sorted(set(x)))) instead of simply set (thanks @Wen and @ScottBoston):
如果您想要删除set数据类型并将其作为字符串(如您希望的输出)，您可以应用'、'.join(sort (set(x)))，而不是简单地设置(感谢@Wen和@ScottBoston):
mytable['label'] = (mytable.groupby('meet_id')
.label.transform(lambda x: list(x.cumsum()))
.apply(lambda x: ', '.join(sorted(set(x)))))
>>> mytable
user_id meet_id text label
0 3c 1 abc A
1 3c 1 abc A
2 3c 1 abc A
3 3c 1 abc A
4 3c 1 abc A
5 3c 1 abc A, B
6 3c 1 abc A, B
7 3c 2 xyz B
8 3c 2 xyz B
9 3c 2 xyz B
10 3c 2 xyz B, C
11 3c 2 xyz B, C
12 3c 2 xyz A, B, C
#3
3
Edit: Okay much more simple solution:
mytable['label'] = mytable.groupby(['user_id','meet_id','text'])['label']\
.apply(lambda x: x.cumsum()).apply(lambda x: sorted(set(x)))
My ugly attempt:
我的丑陋的尝试:
mytable['label'] = mytable.groupby(['user_id','meet_id','text'])['label']\
.apply(lambda x: x.cumsum().str.extractall('(.)')\
.groupby(level=0)[0].apply(lambda x: sorted(set(x))))
Output:
输出:
user_id meet_id text label
0 3c 1 abc [A]
1 3c 1 abc [A]
2 3c 1 abc [A]
3 3c 1 abc [A]
4 3c 1 abc [A]
5 3c 1 abc [A, B]
6 3c 1 abc [A, B]
7 3c 2 xyz [B]
8 3c 2 xyz [B]
9 3c 2 xyz [B]
10 3c 2 xyz [B, C]
11 3c 2 xyz [B, C]
12 3c 2 xyz [A, B, C]