Python visual data analysis 08, pandas_ Excel file reading and writing

​

 ​ edit

Python Visual data analysis 08、Pandas_Excel File read and write

Preface

Blog ：【 Red eye aromatherapy blog _CSDN Blog - Computer theory ,2022 Blue Bridge Cup ,MySQL Domain Blogger 】

This article is written by 【 Red eye aromatherapy 】 original , First appeared in CSDN

2022 The greatest wish of the year ：【 Serve millions of technical people 】

Python Initial environment address ：【Python Visual data analysis 01、python Environment building 】 

Environmental requirements

Environmental Science ：win10

development tool ：PyCharm Community Edition 2021.2

database ：MySQL5.6

Catalog

Python Visual data analysis 08、Pandas_Excel File read and write

Preface

Environmental requirements

Preface

Excel write in

Excel Read

Other operating

Preface

Use Pandas Reading and writing Excel file , Need to install openpyxl、xlsxwriter And xlrd this 3 A third party Library .
openpyxl：openpyxl From the PHPExcel, It provides .xlsx The function of reading and writing files
xlsxwriter： Used to write content to .xlsx In file
xlrd： For reading .xls and .xlsx The data in the file

Installation sequence  

pip3 install openpyxlpip3 install xlsxwriterpip3 install xlrd

​ edit

Excel write in

import pandas as pddf = pd.DataFrame({"id": [1, 2, 3], "name": [" Lei Jing ", " Xiaofeng ", " Spring dream "], "age": ["21", "22", "20"]})print(df)# Write to Excel file df.to_excel("test.xlsx", sheet_name='Sheet1')

​

​ edit

Excel Read

import pandas as pddf = pd.read_excel("test.xlsx")print(df)

​

Other operating

import pandas as pddf = pd.read_excel("test.xlsx")# Basic information print(df.info)# View column names print(df.columns)# View the data types of each column print(df.dtypes)# View Subscripts print(df.index)# Before data browsing 2 strip print(df.head(2))# see name To age Column print(df.loc[:, "name":"age"])# Basic statistics print(" Maximum age ：", df.age.max())print(" Average age ：", df.age.mean())# Inquire about print(df[df.name == " Spring dream "])# Sort ·True positive sequence False In reverse order print(df.sort_values(by=["age"], ascending=False))# In the second column 【 The subscript is 1】 Add columns df.insert(1, "sex", " Woman ")print(df)# Add columns at the end df["introduce"] = " Woman "print(df)# Delete a line df = df.drop(1)print(df)# Replace value = pd.Series([1, " Woman ", " Thunder and quiet ", 20, " Big eyed girl "], index=["id", "sex", "name", "age", "introduce"])df.loc[0] = valuevalue = pd.Series([4, " Woman ", " Little dragon female ", 18, " Iceberg beauty "], index=["id", "sex", "name", "age", "introduce"])df.loc[3] = valueprint(df)# Number of pieces print(len(df))

​