Python pandas 读取Excel文件

Question

Excel a.xlsx数据格式为：

['K52', 'B36', 'J06', 'J11']（这是第一行第一个单元格）
['Z01', 'K07', 'J11', 'K07']（这是第二行第一个单元格）
['K02', 'J06', 'J40', 'J40', 'J40', 'J40', 'J40', 'J11', 'J06', 'J06', 'A49', 'J06', 'J06']
['I51', 'K82', 'Z34', 'N61', 'J11']
['D22', 'J11', 'S02', 'K04', 'N76', 'N83']
一共5行,1列
想要变成的数组格式为：
data = [
['K52', 'B36', 'J06', 'J11'],
['Z01', 'K07', 'J11', 'K07'],
['K02', 'J06', 'J40', 'J40', 'J40', 'J40', 'J40', 'J11', 'J06', 'J06', 'A49', 'J06', 'J06'],
['I51', 'K82', 'Z34', 'N61', 'J11'],
['D22', 'J11', 'S02', 'K04', 'N76', 'N83']
]
不要有NAN的数据

Answer 1

import pandas as pd
data = []
df=pd.read_excel('test.xlsx')
data.append((df.columns.values.tolist())[0].split(','))
for x in range(0 , len(pd.DataFrame(df))):
    data.append((df.ix[x,0]).split(','))
print (data)