> For the complete documentation index, see [llms.txt](https://stb11816.gitbook.io/python_note/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://stb11816.gitbook.io/python_note/web/456.md).

# pandas & csv

### Pandas

官方文件

<https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_numpy.html#pandas.DataFrame.to_numpy>

1.讀取csv : `data=pandas.read_csv('filename')`

2.瀏覽資料內容 : `data.head()`

3.讀取特定欄位資料(列，直直的) : `detail=data[['name1','name2']......]`

3-1. 轉為一般陣列: `data['name'].tolist()`

3-2. 可用numpy將series或者dataframe型態轉為numpy.array

4.計算各欄位的多種統計資訊(count,mean,std,min,max,25%,50%,75%) : `data.describe()`

5.所有欄位名稱: `data.colums`

6.直接選取各筆資料 `data[1:]` (list切割方式)

Source:\
[pandas 資料處理](http://brucecodeworld.logdown.com/posts/240977-python-pandas-little-teaching)

## iloc

data.iloc\[2] --> 第三個row (第三筆資料，橫的)

data.iloc\[2:,0:2] --> 切割區塊(row,colums)，抓取第一筆之後所有資料，但只包含第一、二項特徵

data.iloc\[1:4,:] --> 以row選取

data.iloc\[:,1:4] --> 以colums選取

iloc\[0] --> Series

iloc\[\[0]] --> DataFrame

Source:[Python Pandas DataFrame 的 iloc\[0\] 與 iloc\[\[0\]\]](http://rickchungtw-blog.logdown.com/posts/686802)

### 讀取

```python
import pandas as pd
df = pd.read_csv("City_Zhvi_AllHomes.csv")
df.head(15)
# df.地址，在read_excel()進來時，第一列就會被當成他的屬性。所以df.地址就抓到地址這一行
```

<https://tinycorner.tw/2018/04/24/使用python讀取csv和xlsx檔/>

<https://tinycorner.tw/2018/04/24/使用python讀取csv和xlsx檔/>

### 處理空值

```python
test['name'] = test['name'].fillna('我是空的！')
```

### 判斷nan

```python
import math
x = float('nan')
math.isnan(x)  # True
```

### 四捨五入

```python
df = pd.DataFrame(np.random.random([3, 3]),
     columns=['A', 'B', 'C'], index=['first', 'second', 'third']

df.round(2)  # 取至小數點第二位
```

<https://blog.csdn.net/shuibuzhaodeshiren/article/details/84324885>

### Series轉換

```python
scores = pd.Series({'小明':90, '小華':80, '小李':70, '小強':55})
# 把Series轉成Dataframe只需要使用指令就好
score_df = scores.to_frame() #搞定
```

[https://medium.com/@weilihmen/python-pandas-%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C%E6%95%99%E5%AD%B8-%E6%88%90%E7%B8%BE%E8%A1%A8-f6d0ec4f89](https://medium.com/@weilihmen/python-pandas-基本操作教學-成績表-f6d0ec4f89)

### 轉置矩陣

```python
df.transpose()
```

<http://violin-tao.blogspot.com/2017/06/pandas-1-indexing.html>

### row

用loop讀取

```python
for (index_label, row_series) in empDfObj.iterrows():
    print('Row Index label : ', index_label)
    print('Row Content as Series : ', row_series.values)
```

<https://thispointer.com/pandas-6-different-ways-to-iterate-over-rows-in-a-dataframe-update-while-iterating-row-by-row/>

直接轉成numpy array

```python
df.to_numpy()
```

<https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_numpy.html#pandas.DataFrame.to_numpy>

### colum

```python
# 新增
df.insert(loc, column, value)

#刪除
del df['A']
```

<https://www.delftstack.com/zh-tw/howto/python-pandas/how-to-delete-pandas-dataframe-column/>

### dataframe轉dict

可是value會是list (\[0])

```python
dataframe.to_dict('list')
# {key: [value]}
```

<https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_dict.html>

<https://stackoverflow.com/questions/26716616/convert-a-pandas-dataframe-to-a-dictionary>

### 日期轉換

```python
df['DATE'] = df['Date'].apply(lambda x: x.strftime('%Y-%m-%d'))
```

<https://t.codebug.vip/questions-1537063.htm>

### CSV

1.讀取csv : `inputfile=open('filepath','r')`

2.讀取內容 : `data=csv.reader(inputfile)` (list, 包含欄位名稱(第一項\[0]))

Source : [CSV讀寫](http://dewerzht-blog.logdown.com/posts/701780-python-log-with-csv)

### Python之xlsx檔案與csv檔案相互轉換 <a href="#outline__1" id="outline__1"></a>

Workbook轉csv

```python
from openpyxl import Workbook
for sheet in wb.get_sheet_names():
    with open("export/" + file_csv, 'w', newline='') as op:
        csv_file_writer = csv.writer(op)
        sheet_ranges = wb[sheet]
        for row in sheet_ranges.rows:
            row_container = []
            for cell in row:
                if cell.value == None:
                    row_container.append('')
                else:
                    row_container.append(str(cell.value).replace('\n', ''))

            csv_file_writer.writerow(row_container)
```

[https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/434360/](https://codertw.com/程式語言/434360/)

<https://blog.csdn.net/qq_33689414/article/details/78307031>

[https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/369201/](https://codertw.com/程式語言/369201/)

issue: 輸出csv多一行空白

```
writefile = open('result.csv','w'，newline =‘’)
writer = csv.writer(writefile)
```

<https://blog.csdn.net/pfm685757/article/details/47806469>

## openpyxl

```python
import openpyxl 

wb = openpyxl.Workbook() 
sheet = wb.active
sheet.title = "New Title"

# 新增資料表
ws1 = wb.create_sheet("Mysheet")

sheet.cell(row = 2, column = 2).value = ' everyone '

# 設定欄位長度與寬度
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['B'].width = 20
```

<https://www.geeksforgeeks.org/python-adjusting-rows-and-columns-of-an-excel-file-using-openpyxl-module/>

<https://www.itread01.com/content/1544850004.html>

自動調整寬度尚未測試