(macOS)[python] Numpy Excel 다루기 - 2 : excel .xlsx read

Intro

 - Editor : sublime text3

 - python 3.9.1

 - numpy 1.19.4

 - xlrd 2.0.1

 - openpyxl 3.0.5

 

Data

row	col	x	y	d	x	y	xs	ys
1	1	-300	300	1	-301	302	2	4
	2	-250	300	1	-251	302	2	4
	3	-200	300	1	-201	302	2	4
	4	-150	300	1	-151	302	2	4
	5	-100	300	1	-101	302	2	4
	6	-50	300	1	-51	302	2	4
	7	0	300	1	-1	302	2	4
	8	50	300	1	49	302	2	4
	9	100	300	1	99	302	2	4
	10	150	300	1	149	302	2	4
	11	200	300	1	199	302	2	4
	12	250	300	1	249	302	2	4
	13	300	300	1	299	302	2	4

 

Directory Tree

.

├── data

   ├── test1.csv

   └── test1.xlsx

└── test_read.py

 

 

# -*- coding: utf-8 -*-

import os
import numpy as np
from openpyxl import load_workbook as loadwb


#### dir base
fpath_base = './data'

#### file_name, sheet_name
fnames = ('test1.csv','test1.xlsx')
shnames = ('Sheet1','Sheet2','Sheet3')

#### file_dir_base
fpath_bases = [os.path.join(fpath_base,fname) for fname in fnames]

#### file_read_csv : no text, just number
data_csv = np.loadtxt(fpath_bases[0], delimiter=",")
print("*********************************")
print("Example var info for .csv  \n {}".format(data_csv))
print("*********************************\n\n")
# Pandas_dataframe_read
# df_csv = pd.read_csv(fpath_bases[0])


#### file_read_excel 
wb = loadwb(fpath_bases[1], data_only=True)
ws = wb[shnames[0]]
ws_nrow = ws.max_row
ws_ncol = ws.max_column
wb.close()
#### use with
# with open(fpath_bases[1]) as wb:
#	 wb = loadwb(fpath_bases[1], data_only=True)
#	 ws = wb[shnames[0]]
#	 ws_nrow = ws.max_row
#	 ws_ncol = ws.max_column

print("*********************************")
print("Worksheet info for .xlsx  \n {}".format(ws))
print("dtype is {}".format(type(ws)))
print("row_len = {}, col_len = {}".format(ws_nrow,ws_ncol))
print("*********************************\n\n")

# row = 1 Column Names Read
col_names = []
for i in range(1,ws_ncol+1):
	col_name = ws.cell(1,i).value
	col_names.append(col_name)
print("*********************************")
print("Column Names \n{}".format(col_names))
print("*********************************\n\n")

# row = 2: col = 3 cell.value Read
data_one_column = np.zeros((ws_nrow-1,ws_ncol-2))
print("*********************************")
print("dtype is {}\n data size = {}".format(type(data_one_column),data_one_column.shape))
print("*********************************\n\n")


## row = 2: col = 3: cell.value Read
for i in range(1,ws_nrow):
	# print(ws.cell(i+1,3).value)
	data_one_column[i-1,0] = ws.cell(i+1,3).value
print("*********************************")
print("3rd Column data \n{}".format(data_one_column))
print("dtype is {} \ndata size = {}".format(type(data_one_column),data_one_column.shape))
print("*********************************\n\n")


data_full = np.zeros((ws_nrow-1,ws_ncol-2))
for j in range(1,ws_ncol-1):
	for i in range(1,ws_nrow):
		data_full[i-1,j-1] = ws.cell(i+1,j+2).value
print("*********************************")
print("Column full data from 3rd to 9th \n{}".format(data_full))
print("*********************************\n\n")

 

 

 

실행결과 : ----*REPL*[python]----

*********************************
Example var info for .csv  
 [[-300.  300.    1. ...  302.    2.    4.]
 [-250.  300.    1. ...  302.    2.    4.]
 [-200.  300.    1. ...  302.    2.    4.]
 ...
 [ 200. -300.    1. ... -298.    2.    4.]
 [ 250. -300.    1. ... -298.    2.    4.]
 [ 300. -300.    1. ... -298.    2.    4.]]
*********************************


*********************************
Worksheet info for .xlsx  
 <Worksheet "Sheet1">
dtype is <class 'openpyxl.worksheet.worksheet.Worksheet'>
row_len = 170, col_len = 9
*********************************


*********************************
Column Names 
['row', 'col', 'x', 'y', 'd', 'x', 'y', 'xs', 'ys']
*********************************


*********************************
dtype is <class 'numpy.ndarray'>
 data size = (169, 7)
*********************************


*********************************
3rd Column data 
[[-300.    0.    0. ...    0.    0.    0.]
 [-250.    0.    0. ...    0.    0.    0.]
 [-200.    0.    0. ...    0.    0.    0.]
 ...
 [ 200.    0.    0. ...    0.    0.    0.]
 [ 250.    0.    0. ...    0.    0.    0.]
 [ 300.    0.    0. ...    0.    0.    0.]]
dtype is <class 'numpy.ndarray'> 
data size = (169, 7)
*********************************


*********************************
Column full data from 3rd to 9th 
[[-300.  300.    1. ...  302.    2.    4.]
 [-250.  300.    1. ...  302.    2.    4.]
 [-200.  300.    1. ...  302.    2.    4.]
 ...
 [ 200. -300.    1. ... -298.    2.    4.]
 [ 250. -300.    1. ... -298.    2.    4.]
 [ 300. -300.    1. ... -298.    2.    4.]]
*********************************