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

样本数据:

对于前。死诗人社会存在于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做到这一点,因为这样做很简单。

码:

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

关于python - 如何相对于Python的Excel工作表中其他某个列中存在的特定值显示1列的值?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/48370318/

10-12 18:31
查看更多