Python文本转table-DataFrame

工作中，经常遇到需要将查询结果的文本，转换为二维表(类似Excel的表格）格式，供代码处理， 或者保存为Excel/CSV。该代码：

1. 切割文本为list的 list（行间分隔符，行内值分割符）
2. 将切割后list的list转换为一个DataFrame或者Dictionry List。
3. 然后通过pandas保存为Excel。

import pandas as pd

data = """Alice,1.1,3.0,1.7,5.0,2,FEOL
Bob,2.2,2.6,2.5,2.6,3,MEOL
Cathy,3.3,2.0,3.6,2.4,0,MEOL
Dany,4.4,1.7,2.4,1.3,1,FEOL
Ella,5.0,3.0,5.0,3.0,7,MEOL
Ford,3.2,3.3,2.2,3.6,2,FEOL
Gary,2.4,4.4,3.3,2.4,1,MEOL
Ham,1.5,5.0,4.4,5.0,5,MEOL
Ico,4.3,3.2,1.5,2.2,3,FEOL
Jack,4.5,2.4,4.3,3.1,4,MEOL"""
columns_names=['name', 'Math_A', 'English_A', 'Math_B', 'English_B', 'Project_num', 'Sex']

"""
# 1. Split string to dataframe - table: 1) split the txt to a row list - 'EOL' is the row separator; 2) split each row to field values list - ',' is the field separator Remove the invalid row(field values list) - len of field values list should same as the columns name list
"""
data_list = [row for row in [i.strip('\n').split(',') for i in data.strip('\n').split('EOL')] if len(row) == len(columns_names)]
print(">>>>> 1. list list is: \n ", data_list)

# 2. Transform list list to dataframe
data_df = pd.DataFrame(data_list, columns= columns_names)
print(">>>>> 2. The dataframe is: \n", data_df)

# 3. transform list list to dictionary
value_dict_list = [dict(zip(columns_names, value)) for value in data_list]
print(" >>>>> 3. The Dictionary list: \n", value_dict_list)
#pd.DataFrame(value_dict_list).to_csv('test2.csv')