> For the complete documentation index, see [llms.txt](https://jen-hsuan-hsieh.gitbook.io/python/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://jen-hsuan-hsieh.gitbook.io/python/chapter-2courses/21python-for-data-science-and-machine-learning-bootcamp/214python-for-data-analysis-pandas/2147data-input-and-output.md).

# 2.1.4.7.Data input and output

## 需要的套件

```
conda install sqlalchemy
conda install xml
conda install html5lib
conda install BeautifulSoup4
```

## 使用library

```
import numpy as np
import pandas as pd
```

## 事前準備

* 注意jupyter notebook的當前目錄下有檔案

## 讀寫Excel檔案

* 讀入csv file
  * 讀進來會是一個Dataframe

    ```
    df = pd.read_csv('Excel_Sample.csv')
    type(df)
    ```
* 取得csv資訊   

```
df.info()
```

* 寫出csv file
  * index參數為控制是否要輸出index值, 預設為True

    ```
    df.to_csv('output_csv', index = False)
    ```
* 讀入xlsx file
  * 安裝套件

    ```
    conda install xlrd
    ```
  * 讀進xlsx
    * 因為xlsx有許多sheet, 讀入時可以指定是哪個sheet

      ```
      df = pd.read_excel('Excel_Sample.xlsx', Sheetname='Sheet1')
      ```
  * 寫出xlsx

    ```
    df.to_excel('Excel_Sample2.xlsx', sheet_name='Sheet123')
    ```

## 讀寫HTML檔案

* 讀入csv file
  * 讀進來會是一個list

    ```
    data = pd.read_html('http://python.jobbole.com/81212/')
    ```

## 讀寫SQL檔案 (sqlite)

* 讀入SQL file

```
from sqlalchemy import create_engine
sqldf = pd.read_sql('mytable', con = engine)
```

* 寫入SQL file

```
engine = create_engine('sqlite:///:memory:')
df.to_sql('mytable', engine)
```