使用另一列查找一列中的类别计数

Question

我有一个如下所示的数据集。我想知道印度队对每个对手的比赛总数

        date      Team_1     Team_2     Winner     Margin    Ground
0  04-Jan-10  Bangladesh  Sri Lanka  Sri Lanka  7 wickets     Dhaka
1  05-Jan-10       India  Sri Lanka  Sri Lanka  5 wickets     Dhaka
2  07-Jan-10  Bangladesh      India      India  6 wickets     Dhaka
3  08-Jan-10  Bangladesh  Sri Lanka  Sri Lanka  9 wickets     Dhaka
4  10-Jan-10       India  Sri Lanka      India  8 wickets     Dhaka
5  11-Jan-10  Bangladesh      India      India  6 wickets     Dhaka
6  13-Jan-10       India  Sri Lanka  Sri Lanka  4 wickets     Dhaka
7  22-Jan-10   Australia   Pakistan  Australia  5 wickets  Brisbane

有人能帮忙吗

Answer 1

您想要的数据结构必须有重复的条目（排列与组合），因此首先要将数据帧加倍，同时反转团队的方向。然后只需按两个团队列分组并计算对：

pd.concat([scores, scores.rename(columns={"Team_1": "Team_2", "Team_2": "Team_1"})])
  [['Team_1', 'Team_2', 'date']]
  .groupby(["Team_1", "Team_2"])
  .count()

                       date
Team_1     Team_2          
Australia  Pakistan       1
Bangladesh India          2
           SriLanka       2
India      Bangladesh     2
           SriLanka       3
Pakistan   Australia      1
SriLanka   Bangladesh     2
           India          3

Answer 2

您可以尝试使用^{}和^{}，然后使用^{}从结果中过滤出India

m = df[['Team_1','Team_2']]
d = m[m.eq('India').any(1)].stack().value_counts().loc[lambda x:x.index!='India'].to_dict()

或在1行中：

d1= (df.loc[df.eq('India').any(1),['Team_1','Team_2']]
       .stack().value_counts().loc[lambda x:x.index!='India'].to_dict())

---

print(d)
#output -> {'Sri Lanka': 3, 'Bangladesh': 2}