python实现excel数据处理

以下是代码， 只需要导入模块，更换文件地址即可

import xlrd
import xlwt
import xlutils
from pyecharts.charts import Bar,Line
import pandas as pd
import numpy as np
import csv
#作图分析
import matplotlib as mpl
import matplotlib.pyplot as plt
# import seaborn as sns
import matplotlib
matplotlib.colors
matplotlib.colors.rgb_to_hsv
matplotlib.colors.to_rgba
matplotlib.figure.Figure.get_size_inches
matplotlib.figure.Figure.subplots_adjust
matplotlib.axes.Axes.text
matplotlib.axes.Axes.hlines
mpl.rcParams['font.sans-serif'] = ['SimHei']

workbook = xlrd.open_workbook('E:\\春季英语a\\All_Data.xlsx')
workbook1 = xlrd.open_workbook('E:\\春季英语a\\all_datagirl.xlsx')
#worksheets = workbook.sheet_names()
data = pd.read_csv('E:\\春季英语a\\All_data_202005261011\\All_Data_Original.csv', encoding='gb2312')
#print(data.head())
#data = data.drop(['答卷编号'], axis=1)
ret = list(data.columns)
ret=['gender','grade','Professional_category','Start_using_mobile_phone','One_day_using_mobile_phone','Communication',\
                      'browsing_the_news','listen_to_music','Watch_a_play','play_games','Take_pictures','Read_ebook','other','others',\
                      'Unconsciously_took_out_the_phone','Reduce_actual_interaction','Without_a_mobile_phone, you_will_be_at_a_loss','Unconsciously_open_the_mobile_app','Feel_empty','Feel_irritable',\
                      'Feel_full','Feel_fantasy','Feel_dependent','no_effect','Get_up_in_the_morning','In_class','After_school_or_during_breaks',\
                      'Line_up_for_dinner','Before_sleep','While_walking','control_ability','Unable_toconcentrate','Little_sleep','Show_personal_ability',\
                      'Lack_of_judgment','In_tension','Unable_to_overcome_difficulties','Unable_feel_fun','Face_difficulties','Depressed_mood','Lose_faith']

data.columns=ret
#data = data.drop(['operating system'], axis=1)
print(data.head())
data['gender'].value_counts()
#zero_col_count = dict(data[0].value_counts())#统计第0列元素的值的个数
#three_row_count = dict(data.loc[3].value_counts())#统计第3行元素的值的个数
labels = ['女','男']
count=222
share =[110/count,112/count]
# 设置分裂属性
explode = [0.05, 0.05]
# 分裂饼图
plt.pie(share, explode = explode,
        labels = labels, autopct = '%3.1f%%',
        startangle = 180, shadow = False,
        colors = ['lightcoral', 'lightskyblue'])

# 标题
plt.title('男女比例总数222人')
plt.savefig("./boy_girl.png",dpi=500,bbox_inches = 'tight')
plt.show()

labels = ['通讯交流','浏览新闻','看剧','听音乐','玩游戏','拍照','看电子书']
count=0.4606
share =[0.08/count,0.059/count,0.117/count,0.1134/count,0.091/count,0.096/count,0.021/count]
# 设置分裂属性
explode = [0.05, 0.05,0.05, 0.05,0.05, 0.05,0.05]
# 分裂饼图
plt.pie(share, explode = explode,
        labels = labels, autopct = '%3.1f%%',
        startangle = 180, shadow = False,
        colors = ['lightcoral', 'lightskyblue','c','m','w','g','k'])

# 标题
plt.title('使用习惯相关性分析')
plt.savefig("./使用习惯相关性分析.png",dpi=500,bbox_inches = 'tight')
plt.show()
#男女差异


table = workbook.sheets()[0]
#gender = table.col_values(0)
grade = table.col_values(0 )
Professional_category = table.col_values(1)
Start_using_mobile_phone = table.col_values(2)
One_day_using_mobile_phone = table.col_values(3)
Communication = table.col_values(4)
browsing_the_news = table.col_values(5)
listen_to_music= table.col_values(6)
Watch_a_play= table.col_values(7)
play_games  = table.col_values(8)
Take_pictures= table.col_values(9)
Read_ebook = table.col_values(10)
other = table.col_values(11)

Unconsciously_took_out_the_phone  = table.col_values(12)
Reduce_actual_interaction= table.col_values(13)
Without_a_mobile_phone= table.col_values(14)
you_will_be_at_a_loss= table.col_values(15)
Unconsciously_open_the_mobile_app = table.col_values(16)
Feel_empty = table.col_values(17)

Feel_irritable = table.col_values(18)
Feel_full = table.col_values(19)
Feel_fantasy = table.col_values(20)
Feel_dependent = table.col_values(21)
no_effect = table.col_values(22)
Get_up_in_the_morning= table.col_values(23)
In_class = table.col_values(24)
After_school_or_during_breaks = table.col_values(25)

Line_up_for_dinner = table.col_values(26)
Before_sleep= table.col_values(27)
While_walking = table.col_values(28)
control_ability = table.col_values(29)
Unable_toconcentrate= table.col_values(30)
Little_sleep  = table.col_values(31)
Show_personal_ability = table.col_values(32)
Lack_of_judgment= table.col_values(33)
In_tension= table.col_values(34)
#Unable_overcome_difficulties= table.col_values(36)
Unable_feel_fun = table.col_values(35)
Face_difficulties= table.col_values(36)
Depressed_mood = table.col_values(37)
Lose_faith = table.col_values(38)

line = Line()
time=[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]
line.add_xaxis(time[0:])
#line.add_yaxis('gender', gender[1:])
line.add_yaxis('Grade', grade[1:])
line.add_yaxis('Professional_category ', Professional_category[1:])
line.add_yaxis('Start_using_mobile_phone', Start_using_mobile_phone[1:])
line.add_yaxis('One_day_using_mobile_phone', One_day_using_mobile_phone[1:])
line.add_yaxis('Communication', Communication[1:])
line.add_yaxis('browsing_the_news', browsing_the_news[1:])
line.add_yaxis('listen_to_music', listen_to_music[1:])
line.add_yaxis('Watch_a_play',Watch_a_play[1:])
line.add_yaxis('play_games', play_games[1:])
line.add_yaxis('Take_pictures', Take_pictures[1:])
line.add_yaxis('Read_ebook', Read_ebook[1:])
line.add_yaxis('other', other[1:])

line.add_yaxis('Unconsciously_took_out_the_phone ', Unconsciously_took_out_the_phone [1:])
line.add_yaxis('Reduce_actual_interaction', Reduce_actual_interaction[1:])
line.add_yaxis('Without_a_mobile_phone', Without_a_mobile_phone[1:])
line.add_yaxis('you_will_be_at_a_loss', you_will_be_at_a_loss[1:])
line.add_yaxis('Unconsciously_open_the_mobile_app', Unconsciously_open_the_mobile_app[1:])
line.add_yaxis('Feel_empty', Feel_empty[1:])

line.add_yaxis('Feel_irritable', Feel_irritable[1:])

line.render('girl0-19.html')

line = Line()
time=[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]
line.add_xaxis(time[0:])
line.add_yaxis('Feel_full',Feel_full[1:])
line.add_yaxis('Feel_fantasy', Feel_fantasy[1:])
line.add_yaxis('Feel_dependent',Feel_dependent[1:])
line.add_yaxis('no_effect',no_effect[1:])
line.add_yaxis('Get_up_in_the_morning', Get_up_in_the_morning[1:])
line.add_yaxis('In_class', In_class[1:])

line.add_yaxis('After_school_or_during_breaks', After_school_or_during_breaks[1:])
line.add_yaxis('Line_up_for_dinner', Line_up_for_dinner[1:])
line.add_yaxis('Before_sleep', Before_sleep[1:])
line.add_yaxis('While_walking', While_walking[1:])
line.add_yaxis('control_ability', control_ability[1:])
line.add_yaxis('Unable_toconcentrate',Unable_toconcentrate[1:])
line.add_yaxis('Little_sleep', Little_sleep[1:])

line.add_yaxis('Show_personal_ability', Show_personal_ability[1:])
line.add_yaxis('Lack_of_judgment', Lack_of_judgment[1:])
line.add_yaxis('In_tension',In_tension[1:])
#line.add_yaxis('Unable_overcome_difficulties', Unable_overcome_difficulties[1:])
line.add_yaxis('Unable_feel_fun', Unable_feel_fun[1:])
line.add_yaxis('Face_difficulties', Face_difficulties[1:])
line.add_yaxis('Depressed_mood', Depressed_mood[1:])
line.add_yaxis('Lose_faith',Lose_faith[1:])

line.render('girl20-38.html')




table = workbook1.sheets()[0]
#gender = table.col_values(0)
grade = table.col_values(0 )
Professional_category = table.col_values(1)
Start_using_mobile_phone = table.col_values(2)
One_day_using_mobile_phone = table.col_values(3)
Communication = table.col_values(4)
browsing_the_news = table.col_values(5)
listen_to_music= table.col_values(6)
Watch_a_play= table.col_values(7)
play_games  = table.col_values(8)
Take_pictures= table.col_values(9)
Read_ebook = table.col_values(10)
other = table.col_values(11)

Unconsciously_took_out_the_phone  = table.col_values(12)
Reduce_actual_interaction= table.col_values(13)
Without_a_mobile_phone= table.col_values(14)
you_will_be_at_a_loss= table.col_values(15)
Unconsciously_open_the_mobile_app = table.col_values(16)
Feel_empty = table.col_values(17)

Feel_irritable = table.col_values(18)
Feel_full = table.col_values(19)
Feel_fantasy = table.col_values(20)
Feel_dependent = table.col_values(21)
no_effect = table.col_values(22)
Get_up_in_the_morning= table.col_values(23)
In_class = table.col_values(24)
After_school_or_during_breaks = table.col_values(25)

Line_up_for_dinner = table.col_values(26)
Before_sleep= table.col_values(27)
While_walking = table.col_values(28)
control_ability = table.col_values(29)
Unable_toconcentrate= table.col_values(30)
Little_sleep  = table.col_values(31)
Show_personal_ability = table.col_values(32)
Lack_of_judgment= table.col_values(33)
In_tension= table.col_values(34)
#Unable_overcome_difficulties= table.col_values(36)
Unable_feel_fun = table.col_values(35)
Face_difficulties= table.col_values(36)
Depressed_mood = table.col_values(37)
Lose_faith = table.col_values(38)

line = Line()
time=[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]
line.add_xaxis(time[0:])
#line.add_yaxis('gender', gender[1:])
line.add_yaxis('Grade', grade[1:])
line.add_yaxis('Professional_category ', Professional_category[1:])
line.add_yaxis('Start_using_mobile_phone', Start_using_mobile_phone[1:])
line.add_yaxis('One_day_using_mobile_phone', One_day_using_mobile_phone[1:])
line.add_yaxis('Communication', Communication[1:])
line.add_yaxis('browsing_the_news', browsing_the_news[1:])
line.add_yaxis('listen_to_music', listen_to_music[1:])
line.add_yaxis('Watch_a_play',Watch_a_play[1:])
line.add_yaxis('play_games', play_games[1:])
line.add_yaxis('Take_pictures', Take_pictures[1:])
line.add_yaxis('Read_ebook', Read_ebook[1:])
line.add_yaxis('other', other[1:])

line.add_yaxis('Unconsciously_took_out_the_phone ', Unconsciously_took_out_the_phone [1:])
line.add_yaxis('Reduce_actual_interaction', Reduce_actual_interaction[1:])
line.add_yaxis('Without_a_mobile_phone', Without_a_mobile_phone[1:])
line.add_yaxis('you_will_be_at_a_loss', you_will_be_at_a_loss[1:])
line.add_yaxis('Unconsciously_open_the_mobile_app', Unconsciously_open_the_mobile_app[1:])
line.add_yaxis('Feel_empty', Feel_empty[1:])

line.add_yaxis('Feel_irritable', Feel_irritable[1:])

line.render('boy0-19.html')

line = Line()
time=[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]
line.add_xaxis(time[0:])
line.add_yaxis('Feel_full',Feel_full[1:])
line.add_yaxis('Feel_fantasy', Feel_fantasy[1:])
line.add_yaxis('Feel_dependent',Feel_dependent[1:])
line.add_yaxis('no_effect',no_effect[1:])
line.add_yaxis('Get_up_in_the_morning', Get_up_in_the_morning[1:])
line.add_yaxis('In_class', In_class[1:])

line.add_yaxis('After_school_or_during_breaks', After_school_or_during_breaks[1:])
line.add_yaxis('Line_up_for_dinner', Line_up_for_dinner[1:])
line.add_yaxis('Before_sleep', Before_sleep[1:])
line.add_yaxis('While_walking', While_walking[1:])
line.add_yaxis('control_ability', control_ability[1:])
line.add_yaxis('Unable_toconcentrate',Unable_toconcentrate[1:])
line.add_yaxis('Little_sleep', Little_sleep[1:])

line.add_yaxis('Show_personal_ability', Show_personal_ability[1:])
line.add_yaxis('Lack_of_judgment', Lack_of_judgment[1:])
line.add_yaxis('In_tension',In_tension[1:])
#line.add_yaxis('Unable_overcome_difficulties', Unable_overcome_difficulties[1:])
line.add_yaxis('Unable_feel_fun', Unable_feel_fun[1:])
line.add_yaxis('Face_difficulties', Face_difficulties[1:])
line.add_yaxis('Depressed_mood', Depressed_mood[1:])
line.add_yaxis('Lose_faith',Lose_faith[1:])

line.render('boy20-38.html')



```python