## 2.7 课堂活动1——了解你的班级
```
#任务1 最受欢迎的爱好或生活习惯
import pandas as pd
# 读取Excel文件
df = pd.read_excel('3227.xlsx').dropna()
# 假设除了姓名和性别列之外，其他列都是评分数据
# 选择评分数据列，并转换为整数类型
scores = df.iloc[:, 2:].astype(int)
# 计算每列的总分
total_scores = scores.sum()
# 找到总分最高的列名，即最喜爱的项目
most_popular_item = total_scores.idxmax()
# 打印结果
print(f"全班最喜爱的项目是：{most_popular_item}")
# 任务2：找出生活习惯不太好的同学，并提醒他。
#寻找规则：爱吃火锅或烧烤 且 喜爱点外卖 且 作息不好或爱熬夜
import pandas as pd
# 读取Excel文件
df = pd.read_excel('3227.xlsx').dropna()
df1 = df.iloc[:, 2:].astype(int)
# 定义筛选条件
condition1 =(df1['火锅'] > 3) | (df1['烧烤'] > 3)
condition2 = df1['点外卖'] > 3
condition3 =(df1['早睡早起']<2) |(df1['熬夜'] > 3)
# 组合所有条件
combined_condition = condition1 & condition2 & condition3
# 筛选出满足条件的同学
filtered_df = df1[combined_condition]
# 打印筛选结果
print(filtered_df)
df.loc[14]
```
## 2.7 课堂活动2——寻找与你“相似”的同学
```
import pandas as pd
import numpy as np
# 读取Excel文件，假设文件名为'students_data.xlsx'且位于当前工作目录
file_path = '3227.xlsx'
#读取时去掉没填的同学的行
df = pd.read_excel(file_path).dropna()
# 初始化一个空字典来存储整理后的数据
students = {}
# 遍历数据框的每一行，假设学生姓名在第一列
for index, row in df.iterrows():
student_name = row['学生姓名'] # 获取学生姓名
# 从第二列开始，到最后一列（不包含空列），提取数据并转换为numpy数组
scores = np.array(row.tolist()[1:])
# 将学生姓名和对应的分数数组添加到字典中
students[student_name] = scores
# 打印整理后的数据结构
print(students)
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# 计算两个向量（商品）之间的内积
def inner_product(vec1, vec2):
return np.dot(vec1, vec2)
#找出相速度最高的菜
def findSimilar(x):
# 获取给定菜品的特征向量
target_vec = students[x]
# 初始化最大相似度和最相似菜品
max_similarity = 0.0
most_similar_student = None
# 遍历所有菜品，计算与给定菜品的内积
for student, vec in students.items():
if student != x: # 排除自身
similarity = inner_product(target_vec, vec)
# 如果当前内积大于之前的最大相似度，则更新最大相似度和最相似菜品
if similarity > max_similarity:
max_similarity = similarity
most_similar_student = student
# 返回最相似菜品和相似度
return most_similar_student, max_similarity
```
## 3.1 API接口获取天气数据
```python
import requests
import json
#知心天气官网：
#东软秘钥
#eyJhbGciOiJIUzUxMiJ9.eyJsb2dpbl91c2VyX2tleSI6IjY4ZTQ3ZjFiLTRjMmYtNDBiOS1iZmY4LWQ3YTg3MGFiYzcwMiJ9._TrSam5rj-3EGY2GwEg4qzpEgqWC6fFV5BoS1YILYVOGwllPjw8mHG3SO2nhuaiIhT7oBs-hKrkBam03dW159Q
def fetch_weather_data():
# 构建请求的URL
#url = f"http://124.93.196.45:10001/dev-api/bs-weather-report/weather/getCurrentDayData/上海市"
#url = f"https://route.showapi.com/9-2?showapi_appid=1581147&showapi_sign=3a013bb035394181947e42fff8287556&area=南通"
url = "https://api.seniverse.com/v3/weather/daily.json?key=SND4L7mkeZRVCghJH&location=nantong&language=zh-Hans&unit=c&start=0&days=5"
# 设置请求头，包含认证参数 -东软
# headers = {
# 'Authorization': f'Bearer eyJhbGciOiJIUzUxMiJ9.eyJsb2dpbl91c2VyX2tleSI6IjY4ZTQ3ZjFiLTRjMmYtNDBiOS1iZmY4LWQ3YTg3MGFiYzcwMiJ9._TrSam5rj-3EGY2GwEg4qzpEgqWC6fFV5BoS1YILYVOGwllPjw8mHG3SO2nhuaiIhT7oBs-hKrkBam03dW159Q'
# }
# 发送GET请求，包含请求头
#response = requests.get(url, headers=headers)
response = requests.get(url)
# 检查请求是否成功
if response.status_code == 200:
# 解析返回的JSON数据
data = response.json()
city = data["results"][0]["location"]["name"]
list = data["results"][0]["daily"]
# print(list)
# for a in list:
# print(a["date"])
weather_data = {
'city': city,
'weatherList': list,
}
return weather_data
weather_data = fetch_weather_data()
weather_data
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
from IPython.display import display,Image
print(weather_data["city"])
print("~~~~~~~~~~~~~~~~~~~~~~~华丽分隔线~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
for daily_weather in weather_data["weatherList"]:
print("日期:"+daily_weather["date"])
print("天气："+daily_weather["text_day"])
print("温度："+daily_weather["low"]+"--"+daily_weather["high"]+"℃")
display(Image(f"{daily_weather['text_day']}.png"))
# print(file_path)
# print(daily_weather)
print("~~~~~~~~~~~~~~~~~~~~~~~华丽分隔线~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~")
```
## 3.2 充电桩查询
```
#任务1 将所有的充电桩数据转换为Excel可编辑的文件
import requests
import json
import pandas as pd
def fetch_weather_data():
# 构建请求的URL
url = f"http://124.93.196.45:10001/dev-api/bs-smart-charger/pile/alllist"
# 设置请求头，包含认证参数 -东软
headers = {
'Authorization': f'Bearer eyJhbGciOiJIUzUxMiJ9.eyJsb2dpbl91c2VyX2tleSI6IjY4ZTQ3ZjFiLTRjMmYtNDBiOS1iZmY4LWQ3YTg3MGFiYzcwMiJ9._TrSam5rj-3EGY2GwEg4qzpEgqWC6fFV5BoS1YILYVOGwllPjw8mHG3SO2nhuaiIhT7oBs-hKrkBam03dW159Q'
}
# 发送GET请求，包含请求头
response = requests.get(url, headers=headers)
#response = requests.get(url)
# 检查请求是否成功
if response.status_code == 200:
# 解析返回的JSON数据
data = response.json()
pileList = data['data']
print(type(pileList))
df = pd.DataFrame(pileList)
print(df)
return df
df = fetch_weather_data()
df.to_excel('output.xlsx')
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#任务2 查询：目前是空闲状态且充电速率快的充电桩。
def fetch_weather_data():
# 构建请求的URL
url = f"http://124.93.196.45:10001/dev-api/bs-smart-charger/pile/alllist?chargingPileState=2&chargingPileRate=2"
# 设置请求头，包含认证参数 -东软
headers = {
'Authorization': f'Bearer eyJhbGciOiJIUzUxMiJ9.eyJsb2dpbl91c2VyX2tleSI6IjY4ZTQ3ZjFiLTRjMmYtNDBiOS1iZmY4LWQ3YTg3MGFiYzcwMiJ9._TrSam5rj-3EGY2GwEg4qzpEgqWC6fFV5BoS1YILYVOGwllPjw8mHG3SO2nhuaiIhT7oBs-hKrkBam03dW159Q'
}
# 发送GET请求，包含请求头
response = requests.get(url, headers=headers)
#response = requests.get(url)
# 检查请求是否成功
if response.status_code == 200:
# 解析返回的JSON数据
data = response.json()
pileList = data['data']
df = pd.DataFrame(pileList)
print(df)
return df
fetch_weather_data()
```
