pandas quick start guide and EXCEL analyzing examples.

14.3 Using pandas in Python

import pandas

Official Documentation

link:https://pandas.pydata.org/pandas-docs/stable/

Intro: pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.

Install

>>>pip3 install pandas # In a local shell, Python must in PATH

Import

>>>import pandas as pd

Using pandas

Read file

To read a excel file end in .xlsx extension:

>>>excel = pd.read_excel("test.xlsx", sheet_name="Sheet1")

The datatype for excel is pandas.core.frame.DataFrame, which is a builtin Data structure in pandas to store data.

Extract data by columns

>>>print(excel."column name here")

After created excel, it has multiple methods. It will return the data in the specified column.

Extract data by rows

>>>print(excel.loc["start row":"end row"])

By this way, excel will return the data in specified rows, and print to the console.

>>>print(excel.loc[0])

Extract the first row, which are the titles in excel file, Sheet1.

Extract specified data by rows and columns

>>>print(excel.loc[3:5, ["age"]])

Extract data by cell

print(excel.at[0, "age"])

Note: dtype here is int64. When extracting by cell, the dtype becomes the datatype of that isolated object.

Write

By .to_excel

This is the first method which can write the data into a new excel file with a specified file name and sheet name.

Code:

excel = pd.read_excel("test.xlsx", sheet_name="Sheet1")

data = excel.loc[2:8, "age"]

data.to_excel("data1.xlsx", sheet_name="Sheet2")

By writer

Code:

writer = pd.ExcelWriter("data2.xlsx")
df1 = pd.DataFrame(data={ # Declare a DatFrame data on our own
    "col1":[1, 2, 3, 4],
    "col2":["a", "b", "c", "d"]
})

print(df1)
print(type(df1))

Result:

In [35]: print(df1)
   col1 col2
0     1    a
1     2    b
2     3    c
3     4    d

In [36]: print(type(df1))
<class 'pandas.core.frame.DataFrame'>

Write to file

df1.to_excel(Writer, "Sheet name")
Writer.save()

Result: