Python Pandas –数据输入和输出

Pandas as a library can read and write data to a wide variety of sources. In this article, we would concentrate on the following,

熊猫作为图书馆可以读取和写入各种来源的数据。 在本文中，我们将重点介绍以下内容，

• CSV

  CSV

• Excel

  电子表格

• HTML

  HTML

• SQL

  SQL

In order to work with HTML files and SQL database, along with pandas, we would need to install the below library as well,

为了与HTML文件和SQL数据库以及pandas一起使用，我们还需要安装以下库，

1. pip install sqlalchemy

   pip安装sqlalchemy

2. pip install lxml

   pip安装lxml

3. pip install html5lib

   点安装html5lib

4. pip install beautifulsoup4

   pip安装beautifulsoup4

5. pip install openpyxl

   pip安装openpyxl

Note: In the below example, the reference files are located in the same folder as the python files are.

注意：在以下示例中，参考文件与python文件位于同一文件夹中。

读取CSV文件 (Read a CSV file)

import numpy as np
import pandas as pd

# example is the csv file name
df = pd.read_csv('example')
print(df)

'''
Output:
    a   b   c   d
0   0   1   2   3
1   4   5   6   7
2   8   9  10  11
3  12  13  14  15
'''

Pandas has built in function to read from numerous type of file format, such as csv , clipboard, html, json etc.

Pandas具有内置功能，可以读取多种类型的文件格式，例如csv，剪贴板，html，json等。

写入CSV文件 (Write to CSV file)

In the above example, we have the csv content assigned to a dataFrame variable called df.

在上面的示例中，我们将csv内容分配给了一个名为df的dataFrame变量。

# here index = False coz I don't want 
# to save the index as coulmn
df.to_csv('test_csv', index=False) 
pd.read_csv('test_csv')

'''
Output:
    a   b   c   d
0   0   1   2   3
1   4   5   6   7
2   8   9  10  11
3  12  13  14  15
'''

从Excel文件读取 (Reading from Excel file)

While the pandas can read the data from the Excel file, it is important to note that 'pandas' can not read or import the formulas or images or macros. Trying to read them can cause pandas to crash.

尽管大熊猫可以从Excel文件读取数据，但必须注意， “大熊猫”无法读取或导入公式，图像或宏。 尝试阅读它们可能会导致熊猫崩溃。

Also, there can be an error such as requesting us to install the xlrd library in case it is not available. [pip install xlrd].

另外，如果没有xlrd库 ，可能会出现诸如请求我们安装xlrd库的错误。 [pip install xlrd]。

pd.read_excel('Excel_Sample.xlsx')

'''
Output:
   Unnamed: 	0   	a   b   c   d
0           	0   	0   1   2   3
1           	1   	4   5   6   7
2           	2   	8   9  10  11
3           	3  	12  13  14  15
'''

df.to_excel('Excel_Sample_2.xlsx',sheet_name='Sheet1')

HTML输入 (HTML input)

Pandas read_html() function will read tables off of a webpage and return a list of DataFrame objects,

熊猫的read_html()函数将从网页中读取表并返回DataFrame对象的列表，

df = pd.read_html('http://www.fdic.gov/bank/individual/failed/banklist.html')
print(df[0] )

SQL (SQL)

The pandas.io.sql module provides a collection of query wrappers to both facilitate data retrieval and to reduce dependency on DB-specific API. Database abstraction is provided by SQLAlchemy if installed. Also, a driver library is required for the database. Examples of such drivers are psycopg2 for PostgreSQL or pymysql for MySQL. For SQLite, this is included in Python's standard library by default.

pandas.io.sql模块提供了查询包装的集合，以促进数据检索并减少对特定于数据库的API的依赖性。 如果安装了SQLAlchemy，则提供数据库抽象。 另外，数据库需要驱动程序库。 此类驱动程序的示例是用于PostgreSQLpsycopg2或用于MySQL的pymysql。 对于SQLite，默认情况下将其包含在Python的标准库中。

If SQLAlchemy is not installed, a fallback is only provided for SQLite (and for MySQL for backward compatibility, but this is deprecated and will be removed in a future version). This mode requires a Python database adapter that respects the Python DB-API.

如果未安装SQLAlchemy，则仅为SQLite(和向后兼容MySQL)提供回退功能，但不建议使用此功能，并将在以后的版本中将其删除。 此模式需要一个遵循Python DB-API的Python数据库适配器。

See also some cookbook examples for some advanced strategies.

另请参阅一些食谱示例以了解一些高级策略。

The key functions are,

关键功能是

• read_sql_table(table_name, con[, schema, ...]) : Read SQL database table into a DataFrame.

  read_sql_table(table_name，con [，schema，...]) ：将SQL数据库表读入DataFrame。

• read_sql_query(sql, con[, index_col, ...]) : Read SQL query into a DataFrame.

  read_sql_query(sql，con [，index_col，...]) ：将SQL查询读入DataFrame。

• read_sql(sql, con[, index_col, ...]) : Read SQL query or database table into a DataFrame.

  read_sql(sql，con [，index_col，...]) ：将SQL查询或数据库表读入DataFrame。

• DataFrame.to_sql(name, con[, flavor, ...]) : Write records stored in a DataFrame to a SQL database.

  DataFrame.to_sql(name，con [，flavor，...]) ：将存储在DataFrame中的记录写入SQL数据库。

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
df.to_sql('data', engine)
sql_df = pd.read_sql('data',con=engine)
print(sql_df)

'''
Output:
   index         0   a   b   c   d
0      0           0   0   1   2   3
1      1           1   4   5   6   7
2      2           2   8   9  10  11
3      3           3  12  13  14  15
'''

翻译自: https://www.includehelp.com/python/python-pandas-data-input-and-output.aspx