在Python的Excel工作表中，如何显示1列的值相对于其他列中的特定值？

Question

这是我的Excel工作表中的一个示例文档（我无法上传Excel工作表的屏幕截图，所以我尝试制作一个具有4个属性/列的类似表格表单）。我想用Python编写代码，这样我就可以计算第1列中的任何电影名称对于第4列中的特定值出现的次数。你知道吗

样本数据：

例如，死亡诗人协会（Dead Poety society）为A、D和E提供服务。最长的旅行为C提供服务，为D提供两次服务

+====================+====================+============+=========+
|      Column1       |      Column2       |  Column3   | Column4 |
+====================+====================+============+=========+
| Dead poet society  | Julia Roberts      | London     | A       |
+--------------------+--------------------+------------+---------+
| Before sunrise     | Sandra Bullock     | Paris      | A       |
+--------------------+--------------------+------------+---------+
| Finding Dory       | Emma Stone         | Rome       | A       |
+--------------------+--------------------+------------+---------+
| Blood diamond      | Anne Hathaway      | Canada     | A       |
+--------------------+--------------------+------------+---------+
| A Beautiful mind   | Amanda Seyfried    | Scotland   | B       |
+--------------------+--------------------+------------+---------+
| Blood diamond      | Anne Hathaway      | Canada     | B       |
+--------------------+--------------------+------------+---------+
| Before sunrise     | Sandra Bullock     | Paris      | B       |
+--------------------+--------------------+------------+---------+
| The longest ride   | Reese Witherspoon  | Denmark    | C       |
+--------------------+--------------------+------------+---------+
| Marley and me      | Jennifer Aniston   | Germany    | C       |
+--------------------+--------------------+------------+---------+
| The longest ride   | Reese Witherspoon  | Denmark    | D       |
+--------------------+--------------------+------------+---------+
| Dead poet society  | Julia Roberts      | London     | D       |
+--------------------+--------------------+------------+---------+
| Remember me        | Natalie Portman    | Bulgaria   | D       |
+--------------------+--------------------+------------+---------+
| Inception          | Kate Winslet       | Sweden     | D       |
+--------------------+--------------------+------------+---------+
| The longest ride   | Reese Witherspoon  | Denmark    | D       |
+--------------------+--------------------+------------+---------+
| Gone with the wind | Scarlett Johansson | Brazil     | E       |
+--------------------+--------------------+------------+---------+
| Dead poet society  | Julia Roberts      | London     | E       |
+--------------------+--------------------+------------+---------+
| Before sunrise     | Sandra Bullock     | Paris      | E       |
+--------------------+--------------------+------------+---------+
| Midnight in Paris  | Meg Ryan           | Queensland | E       |
+--------------------+--------------------+------------+---------+

迄今为止的代码：

这是我用过的代码，但没有用。你知道吗

import xlrd
import pandas as pd
wb = xlrd.open_workbook('sample_docu.xlsx')
cells = s.cell_value(rowx=0, colx=0)
cells_2 = s.cell_value(rowx=2, colx=3) 
count=0                            
if cells in cells_2:
    count=count+1
    print('Count={}'.format(count))

Answer 1

注意到您的代码尝试，imported pandas，我将展示如何使用pandas实现这一点，因为它使这一点非常简单。你知道吗

代码：

df = pd.read_excel('test.xlsx')
print(df.groupby(['Title', 'Category']).size())

测试代码：

import pandas as pd
from io import StringIO

# build some sample data    
sample_df = pd.read_fwf(StringIO(u"""
     Title               Name                City        Category 
     Dead poet society   Julia Roberts       London      A       
     Before sunrise      Sandra Bullock      Paris       A       
     Finding Dory        Emma Stone          Rome        A       
     Blood diamond       Anne Hathaway       Canada      A       
     A Beautiful mind    Amanda Seyfried     Scotland    B       
     Blood diamond       Anne Hathaway       Canada      B       
     Before sunrise      Sandra Bullock      Paris       B       
     The longest ride    Reese Witherspoon   Denmark     C       
     Marley and me       Jennifer Aniston    Germany     C       
     The longest ride    Reese Witherspoon   Denmark     D       
     Dead poet society   Julia Roberts       London      D       
     Remember me         Natalie Portman     Bulgaria    D       
     Inception           Kate Winslet        Sweden      D       
     The longest ride    Reese Witherspoon   Denmark     D       
     Gone with the wind  Scarlett Johansson  Brazil      E       
     Dead poet society   Julia Roberts       London      E       
     Before sunrise      Sandra Bullock      Paris       E       
     Midnight in Paris   Meg Ryan            Queensland  E"""),
                 header=1)

# save the data to an excel file, just so we can read it back in directly
sample_df.to_excel('test.xlsx')
print(sample_df)

# read the dataframe from excel
df = pd.read_excel('test.xlsx')

# show the number of time each title is in the category
print(df.groupby(['Title', 'Category']).size())

结果：

                 Title                Name        City Category
0    Dead poet society       Julia Roberts      London        A
1       Before sunrise      Sandra Bullock       Paris        A
2         Finding Dory          Emma Stone        Rome        A
3        Blood diamond       Anne Hathaway      Canada        A
4     A Beautiful mind     Amanda Seyfried    Scotland        B
5        Blood diamond       Anne Hathaway      Canada        B
6       Before sunrise      Sandra Bullock       Paris        B
7     The longest ride   Reese Witherspoon     Denmark        C
8        Marley and me    Jennifer Aniston     Germany        C
9     The longest ride   Reese Witherspoon     Denmark        D
10   Dead poet society       Julia Roberts      London        D
11         Remember me     Natalie Portman    Bulgaria        D
12           Inception        Kate Winslet      Sweden        D
13    The longest ride   Reese Witherspoon     Denmark        D
14  Gone with the wind  Scarlett Johansson      Brazil        E
15   Dead poet society       Julia Roberts      London        E
16      Before sunrise      Sandra Bullock       Paris        E
17   Midnight in Paris            Meg Ryan  Queensland        E

Title               Category
A Beautiful mind    B           1
Before sunrise      A           1
                    B           1
                    E           1
Blood diamond       A           1
                    B           1
Dead poet society   A           1
                    D           1
                    E           1
Finding Dory        A           1
Gone with the wind  E           1
Inception           D           1
Marley and me       C           1
Midnight in Paris   E           1
Remember me         D           1
The longest ride    C           1
                    D           2
dtype: int64