这是我的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/