pandas.read_excel
pandas.
read_excel
(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=False, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, parse_dates=False, date_parser=None, thousands=None, comment=None, skipfooter=0, convert_float=True, **kwds)[source]
Read an Excel table into a pandas DataFrame
Parameters: | io : string, path object (pathlib.Path or py._path.local.LocalPath),
sheet_name : string, int, mixed list of strings/ints, or None, default 0
sheetname : string, int, mixed list of strings/ints, or None, default 0
header : int, list of ints, default 0
names : array-like, default None
index_col : int, list of ints, default None
parse_cols : int or list, default None
usecols : int or list, default None
squeeze : boolean, default False
dtype : Type name or dict of column -> type, default None
engine: string, default None
converters : dict, default None
true_values : list, default None
false_values : list, default None
skiprows : list-like
nrows : int, default None
na_values : scalar, str, list-like, or dict, default None
keep_default_na : bool, default True
verbose : boolean, default False
thousands : str, default None
comment : str, default None
skip_footer : int, default 0
skipfooter : int, default 0
convert_float : boolean, default True
|
---|---|
Returns: | parsed : DataFrame or Dict of DataFrames
|
Examples
An example DataFrame written to a local file
>>> df_out = pd.DataFrame([('string1', 1),
... ('string2', 2),
... ('string3', 3)],
... columns=['Name', 'Value'])
>>> df_out
Name Value
0 string1 1
1 string2 2
2 string3 3
>>> df_out.to_excel('tmp.xlsx')
The file can be read using the file name as string or an open file object:
>>> pd.read_excel('tmp.xlsx')
Name Value
0 string1 1
1 string2 2
2 string3 3
>>> pd.read_excel(open('tmp.xlsx','rb'))
Name Value
0 string1 1
1 string2 2
2 string3 3
Index and header can be specified via the index_col and header arguments
>>> pd.read_excel('tmp.xlsx', index_col=None, header=None)
0 1 2
0 NaN Name Value
1 0.0 string1 1
2 1.0 string2 2
3 2.0 string3 3
Column types are inferred but can be explicitly specified
>>> pd.read_excel('tmp.xlsx', dtype={'Name':str, 'Value':float})
Name Value
0 string1 1.0
1 string2 2.0
2 string3 3.0
True, False, and NA values, and thousands separators have defaults, but can be explicitly specified, too. Supply the values you would like as strings or lists of strings!
>>> pd.read_excel('tmp.xlsx',
... na_values=['string1', 'string2'])
Name Value
0 NaN 1
1 NaN 2
2 string3 3
Comment lines in the excel input file can be skipped using the comment kwarg
>>> df = pd.DataFrame({'a': ['1', '#2'], 'b': ['2', '3']})
>>> df.to_excel('tmp.xlsx', index=False)
>>> pd.read_excel('tmp.xlsx')
a b
0 1 2
1 #2 3
>>> pd.read_excel('tmp.xlsx', comment='#')
a b
0 1 2
pandas.DataFrame.sort_values
DataFrame.
sort_values
(by, axis=0, ascending=True, inplace=False, kind='quicksort', na_position='last')[source]
Sort by the values along either axis
Parameters: | by : str or list of str
axis : {0 or ‘index’, 1 or ‘columns’}, default 0
ascending : bool or list of bool, default True
inplace : bool, default False
kind : {‘quicksort’, ‘mergesort’, ‘heapsort’}, default ‘quicksort’
na_position : {‘first’, ‘last’}, default ‘last’
|
---|---|
Returns: | sorted_obj : DataFrame |
Examples
>>> df = pd.DataFrame({
... 'col1' : ['A', 'A', 'B', np.nan, 'D', 'C'],
... 'col2' : [2, 1, 9, 8, 7, 4],
... 'col3': [0, 1, 9, 4, 2, 3],
... })
>>> df
col1 col2 col3
0 A 2 0
1 A 1 1
2 B 9 9
3 NaN 8 4
4 D 7 2
5 C 4 3
Sort by col1
>>> df.sort_values(by=['col1'])
col1 col2 col3
0 A 2 0
1 A 1 1
2 B 9 9
5 C 4 3
4 D 7 2
3 NaN 8 4
Sort by multiple columns
>>> df.sort_values(by=['col1', 'col2'])
col1 col2 col3
1 A 1 1
0 A 2 0
2 B 9 9
5 C 4 3
4 D 7 2
3 NaN 8 4
Sort Descending
>>> df.sort_values(by='col1', ascending=False)
col1 col2 col3
4 D 7 2
5 C 4 3
2 B 9 9
0 A 2 0
1 A 1 1
3 NaN 8 4
Putting NAs first
>>> df.sort_values(by='col1', ascending=False, na_position='first')
col1 col2 col3
3 NaN 8 4
4 D 7 2
5 C 4 3
2 B 9 9
0 A 2 0
1 A 1 1
Pandas中更改列的数据类型
对于创建DataFrame的情形
如果要创建一个DataFrame,可以直接通过dtype参数指定类型:
-
df = pd.DataFrame(a, dtype='float') #示例1
-
df = pd.DataFrame(data=d, dtype=np.int8) #示例2
-
df = pd.read_csv("somefile.csv", dtype = {'column_name' : str})
对于单列或者Series
下面是一个字符串Seriess
的例子,它的dtype为object
:
-
>>> s = pd.Series(['1', '2', '4.7', 'pandas', '10'])
-
>>> s
-
0 1
-
1 2
-
2 4.7
-
3 pandas
-
4 10
-
dtype: object
使用to_numeric
转为数值。默认情况下,它不能处理字母型的字符串'pandas':
-
>>> pd.to_numeric(s) # or pd.to_numeric(s, errors='raise')
-
ValueError: Unable to parse string
可以将无效值强制转换为NaN
,如下所示:
-
>>> pd.to_numeric(s, errors='coerce')
-
0 1.0
-
1 2.0
-
2 4.7
-
3 NaN
-
4 10.0
-
dtype: float64
如果遇到无效值,第三个选项就是忽略该操作:
-
>>> pd.to_numeric(s, errors='ignore')
-
# the original Series is returned untouched
对于多列或者整个DataFrame
如果想要将这个操作应用到多个列,依次处理每一列是非常繁琐的,所以可以使用DataFrame.apply
处理每一列。
对于某个DataFrame:
-
>>> a = [['a', '1.2', '4.2'], ['b', '70', '0.03'], ['x', '5', '0']]
-
>>> df = pd.DataFrame(a, columns=['col1','col2','col3'])
-
>>> df
-
col1 col2 col3
-
0 a 1.2 4.2
-
1 b 70 0.03
-
2 x 5 0
然后可以写:
df[['col2','col3']] = df[['col2','col3']].apply(pd.to_numeric)
那么'col2'和'col3'根据需要具有float64
类型。
但是,可能不知道哪些列可以可靠地转换为数字类型。在这种情况下,设置参数:
df.apply(pd.to_numeric, errors='ignore')
然后该函数将被应用于整个DataFrame,可以转换为数字类型的列将被转换,而不能(例如,它们包含非数字字符串或日期)的列将被单独保留。
另外pd.to_datetime
和pd.to_timedelta
可将数据转换为日期和时间戳。