用 Python处理Excel中的数据,把以下图片中的图一转到下面图二的形式:
就是一个列转到行。
解决思路:
1.用Pandas库读取表格中的数据;
2.把读取出来的数据处理下,形成列转到行;
3.再把数据写入一个新的Excel表格。
Python代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 import pandas as pdimport numpy as npimport osdef read_ex (ex_file ): df = pd.read_excel(ex_file, sheet_name='Sheet1' , keep_default_na='' ) data_list = np.array(df).tolist() return data_list def process_data (data_list ): new_data_list = [] for sublist in data_list: company = sublist[0 ] for i in range (1 , len (sublist)): if sublist[i] != '' : new_data_list.append([company, sublist[i]]) return new_data_list def write_to_ex (ex_file, data_list ): if os.path.exists(ex_file): os.remove(ex_file) df = pd.DataFrame(data_list) df.to_excel(ex_file, index=False , header=False ) ex_file = r'.\test1.xlsx' data_list = read_ex(ex_file) new_data_list = process_data(data_list) write_to_ex(r'.\new_data.xlsx' , new_data_list) print ('-' *10 , 'fine!' )