20191228_Python语言课程设计

#用 pandas 库读取“pollution_us_5city_2006_2010_SO2.csv”文件，查看前五行、后两行。

import pandas as pd
import matplotlib.pyplot as plt
test=pd.read_csv('pollution_us_5city_2006_2010_SO2.csv')
print(test.head(5))
print(test.tail(2))

   ID  State Code  County Code  Site Num                      Address  \
0   1           6           37      1103  1630 N MAIN ST, LOS ANGELES   
1   2           6           37      1103  1630 N MAIN ST, LOS ANGELES   
2   3           6           37      1103  1630 N MAIN ST, LOS ANGELES   
3   4           6           37      1103  1630 N MAIN ST, LOS ANGELES   
4   5           6           37      1103  1630 N MAIN ST, LOS ANGELES   

        State       County         City Date Local          SO2 Units  \
0  California  Los Angeles  Los Angeles   2006/1/1  Parts per billion   
1  California  Los Angeles  Los Angeles   2006/1/1  Parts per billion   
2  California  Los Angeles  Los Angeles   2006/1/1  Parts per billion   
3  California  Los Angeles  Los Angeles   2006/1/1  Parts per billion   
4  California  Los Angeles  Los Angeles   2006/1/2  Parts per billion   

   SO2 Mean  SO2 1st Max Value  SO2 1st Max Hour  SO2 AQI  
0  2.043478                3.0                 5      4.0  
1  2.043478                3.0                 5      4.0  
2  2.000000                2.0                 2      NaN  
3  2.000000                2.0                 2      NaN  
4  2.000000                2.0                 0      3.0  
          ID  State Code  County Code  Site Num  \
53218  53219          36           81       124   
53219  53220          36           81       124   

                                                 Address     State  County  \
53218  Queens College   65-30 Kissena Blvd  Parking L...  New York  Queens   
53219  Queens College   65-30 Kissena Blvd  Parking L...  New York  Queens   

           City  Date Local          SO2 Units  SO2 Mean  SO2 1st Max Value  \
53218  New York  2010/12/31  Parts per billion   14.8875               16.9   
53219  New York  2010/12/31  Parts per billion   14.8875               16.9   

       SO2 1st Max Hour  SO2 AQI  
53218                 5      NaN  
53219                 5      NaN  

用 pandas 数据预处理模块将缺失值填充为该列的平均值，删除列 StateCode、County Code、Site Num、Address，并将剩余列导出到 Excel 文件
“pollution_us_5city_2006_2010_SO2.xlsx”。

test.isnull().sum()
mean_cols=test['SO2 AQI'].mean()
test['SO2 AQI'] = test['SO2 AQI'].fillna(mean_cols)
test1=test.drop(['State Code','County Code','Site Num','Address'],axis=1)

test1.to_excel('pollution_us_5city_2006_2010_SO2.xlsx')

读取新的数据集“pollution_us_5city_2006_2010_SO2.xlsx”，并选择字段
City==“New York”的所有数据集，导出为文本文件“pollution_us_NewYork_2006_2010_SO2.txt”，要求数据之间用空格分隔，
每行末尾包含换行符。

test=pd.read_excel('pollution_us_5city_2006_2010_SO2.xlsx')

test2=test.loc[test['City']=="New York"]

test2.to_csv('pollution_us_NewYork_2006_2010_SO2.txt',index=0)

读取文本文件“pollution_us_NewYork_2006_2010_SO2.txt”，并选择字段
Date Local 位于[2007/1/1, 2009/12/31] 区间的所有数据集转存到 CSV 文件
“pollution_us_NewYork_2007_2009_SO2.csv”中。

test=pd.read_csv('pollution_us_NewYork_2006_2010_SO2.txt')

test['Date Local'] = pd.to_datetime(test['Date Local'])
test = test.set_index('Date Local') # 将date设置为index

test=test['2007-01-01':'2009-12-31']

test.to_csv('pollution_us_NewYork_2007_2009_SO2.csv')

读取 CSV 文件“pollution_us_NewYork_2007_2009_SO2.csv”，计算同一个
城市（字段 City）的 SO2 Mean、SO2 1st Max Hour、SO2 AQI 的月均值，
并利用 matplotlib 库可视化显示，要求包括图例、图标题，x 轴刻度以年显
示，y 轴显示刻度值，曲线颜色为红色

test=pd.read_csv('pollution_us_NewYork_2007_2009_SO2.csv')          

test.head()

	Date Local	ID	State	County	City	SO2 Units	SO2 Mean	SO2 1st Max Value	SO2 1st Max Hour	SO2 AQI
0	2007-01-01	15225	New York	Bronx	New York	Parts per billion	6.583333	20.0	16	29.000000
1	2007-01-01	15226	New York	Bronx	New York	Parts per billion	6.583333	20.0	16	29.000000
2	2007-01-01	15227	New York	Bronx	New York	Parts per billion	6.562500	13.3	20	10.957132
3	2007-01-01	15228	New York	Bronx	New York	Parts per billion	6.562500	13.3	20	10.957132
4	2007-01-02	15229	New York	Bronx	New York	Parts per billion	7.909091	19.0	20	27.000000

test['Date Local'] = test['Date Local'].apply(lambda x: pd.Timestamp(x))
# 年份
test['年']=test['Date Local'].apply(lambda x: x.year)
# 月份
test['月']=test['Date Local'].apply(lambda x: x.month)

test=test.drop(['ID','SO2 1st Max Value'],axis=1)

test_num=test.groupby(by=['年','月'],as_index=False).mean()

test_num.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 36 entries, 0 to 35
Data columns (total 5 columns):
年                   36 non-null int64
月                   36 non-null int64
SO2 Mean            36 non-null float64
SO2 1st Max Hour    36 non-null float64
SO2 AQI             36 non-null float64
dtypes: float64(3), int64(2)
memory usage: 1.7 KB

test_num['年']=test_num['年'].astype('str')
test_num['月']=test_num['月'].astype('str')

test_num['all']=test_num['年']+'/'+test_num['月']

test_num.columns

Index(['年', '月', 'SO2 Mean', 'SO2 1st Max Hour', 'SO2 AQI', 'all'], dtype='object')

x=test_num['all']
y=test_num['SO2 Mean']
plt.figure(figsize=(20,10))
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False
plt.plot(x,y, 'r', label='SO2 Mean')
plt.xlabel('年')
plt.ylabel('label value')

Text(0,0.5,'label value')

x=test_num['all']
y=test_num['SO2 1st Max Hour']
plt.figure(figsize=(20,10))
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False
plt.plot(x,y, 'r', label='SO2 Mean')
plt.xlabel('年')
plt.ylabel('label value')

Text(0,0.5,'label value')

x=test_num['all']
y=test_num['SO2 AQI']
plt.figure(figsize=(20,10))
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False
plt.plot(x,y, 'r', label='SO2 Mean')
plt.xlabel('年')
plt.ylabel('label value')

Text(0,0.5,'label value')