Skip to main content
 首页 » 编程设计

python之如何显示 1 列的值相对于 Python 中 Excel 工作表中某些其他列中存在的特定值

2025年01月19日8java哥

这是我的 Excel 工作表中的示例文档(我无法上传 Excel 工作表的屏幕截图,所以我尝试制作一个具有 4 个属性/列的类似表格)。我想用 Python 编写代码,以便我可以计算第 1 列中的任何电影名称在第 4 列中的特定值出现了多少次。

示例数据:

例如。 Dead poet 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)) 

请您参考如下方法:

请注意您的代码尝试导入 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