Shulou(Shulou.com)06/01 Report--

Editor to share with you how pandas read Excel and output, I hope you have something to gain after reading this article, let's discuss it together!

Import module

First import the library functions, the numpy library and pandas

In [2]: import numpy as np

In [3]: import pandas as pd

Read Excel table data

Read the physical examination results under disk D. xlsx file, and then convert it to DataFrame structure data.

The screenshot of the original Excel table is as follows:

After reading, the display result is as follows, which is consistent with the original table:

In [4]: df = pd.read_excel ('D:\ physical examination result. Xlsx', 'physical examination result')

In [5]: df

Out [5]:

Admission card number, name, position code, number of applicants, total score of written test. Remarks on whether the ranking physical fitness evaluation results are included in the physical examination results

0 1010401204 Wang Mei 34009019 130.7... 1 NaN is qualified NaN

1 1010208410 Li Wenqing 34009019 NaN 130.3... 2 NaN is an unqualified NaN

2 1010901103 an Junxia 34015031 NaN 112.4... 3 NaN is an unqualified NaN

3 1010306812 Zhang Mingshuo 34015032 1.0 118.9... 1 NaN is qualified NaN

4 1011101721 Guo Hui 34064121 111.9... 1 NaN is an unqualified NaN

5 1010400127 Zhang Ming 34065123 NaN 114.1... 2 NaN is an unqualified NaN

6 1011201102 Wen Hongwei 34070128 34070128 131.3... 1 NaN is a substandard NaN

7 1010202115 Yang Fang 34113189 1.0 106.8... 1 NaN is an unqualified NaN

8 1010902125 Li Yuanbei 34131228 34131228 108.1... 1 NaN is an unqualified NaN

9 1010304807 Zhao Jigang 34124213 NaN 104.8... 2 NaN is a qualified NaN

[10 rows x 12 columns]

Support DataFrame data conversion, and output Excel data transpose, and then output to D disk physical examination result 1.xlsx file, worksheet name is transpose, where df.T is transpose, to_excel is output data is excel file.

In [6]: df = df.T

In [7]: df.to_excel ('D:\ physical examination results 1.xlsxwords, sheet_name=' transposition')

Screenshots of the Excel table after transposition are as follows:

Sort the data, and then output to the 1.xlsx file of the physical examination results on disk D, and the worksheet name is sorted by column.

Sort_index is sorted by label, where axis is 0 for sorting by row label, 1 for sorting by column label, ascending:True for ascending order, and False for descending order

The following procedure sorts by column label in descending order:

In [8]: df = df.sort_index (axis=1, ascending=False)

In [9]: df.to_excel ('D:\ physical examination results 1.xlsxexamples, sheet_name=' sorted by column')

The screenshot of the sorted Excel table is as follows

The data are sorted and then output to the 1.xlsx file of the physical examination results on disk D. the worksheet is named as the ascending order of the total scores of the written test.

Sort_values sorts by specified column name

The following procedure is arranged in ascending order of written test scores:

In [10]: df = df.sort_values (by=' written test total score')

In [11]: df.to_excel ('D:\ physical examination results 1.xlsxexamination, sheet_name=' written test total score ascending order')

The screenshot of the sorted Excel table is as follows

After reading this article, I believe you have a certain understanding of "how to read Excel and output from pandas". If you want to know more about it, welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.