Enter search terms or a module, class or function name.
Here we discuss a lot of the essential functionality common to the pandas datastructures. Here’s how to create some of the objects used in the examples fromthe previous section:
In [1]:index=pd.date_range('1/1/2000',periods=8)In [2]:s=pd.Series(np.random.randn(5),index=['a','b','c','d','e'])In [3]:df=pd.DataFrame(np.random.randn(8,3),index=index, ...:columns=['A','B','C']) ...:In [4]:wp=pd.Panel(np.random.randn(2,5,4),items=['Item1','Item2'], ...:major_axis=pd.date_range('1/1/2000',periods=5), ...:minor_axis=['A','B','C','D']) ...:
To view a small sample of a Series or DataFrame object, use thehead() andtail() methods. The default numberof elements to display is five, but you may pass a custom number.
In [5]:long_series=pd.Series(np.random.randn(1000))In [6]:long_series.head()Out[6]:0 -0.3053841 -0.4791952 0.0950313 -0.2700994 -0.707140dtype: float64In [7]:long_series.tail(3)Out[7]:997 0.588446998 0.026465999 -1.728222dtype: float64
pandas objects have a number of attributes enabling you to access the metadata
- shape: gives the axis dimensions of the object, consistent with ndarray
- Axis labels
- Series:index (only axis)
- DataFrame:index (rows) andcolumns
- Panel:items,major_axis, andminor_axis
Note,these attributes can be safely assigned to!
In [8]:df[:2]Out[8]: A B C2000-01-01 0.187483 -1.933946 0.3773122000-01-02 0.734122 2.141616 -0.011225In [9]:df.columns=[x.lower()forxindf.columns]In [10]:dfOut[10]: a b c2000-01-01 0.187483 -1.933946 0.3773122000-01-02 0.734122 2.141616 -0.0112252000-01-03 0.048869 -1.360687 -0.4790102000-01-04 -0.859661 -0.231595 -0.5277502000-01-05 -1.296337 0.150680 0.1238362000-01-06 0.571764 1.555563 -0.8237612000-01-07 0.535420 -1.032853 1.4697252000-01-08 1.304124 1.449735 0.203109
To get the actual data inside a data structure, one need only access thevalues property:
In [11]:s.valuesOut[11]:array([0.1122,0.8717,-0.8161,-0.7849,1.0307])In [12]:df.valuesOut[12]:array([[ 0.1875, -1.9339, 0.3773], [ 0.7341, 2.1416, -0.0112], [ 0.0489, -1.3607, -0.479 ], [-0.8597, -0.2316, -0.5278], [-1.2963, 0.1507, 0.1238], [ 0.5718, 1.5556, -0.8238], [ 0.5354, -1.0329, 1.4697], [ 1.3041, 1.4497, 0.2031]])In [13]:wp.valuesOut[13]:array([[[-1.032 , 0.9698, -0.9627, 1.3821], [-0.9388, 0.6691, -0.4336, -0.2736], [ 0.6804, -0.3084, -0.2761, -1.8212], [-1.9936, -1.9274, -2.0279, 1.625 ], [ 0.5511, 3.0593, 0.4553, -0.0307]], [[ 0.9357, 1.0612, -2.1079, 0.1999], [ 0.3236, -0.6416, -0.5875, 0.0539], [ 0.1949, -0.382 , 0.3186, 2.0891], [-0.7283, -0.0903, -0.7482, 1.3189], [-2.0298, 0.7927, 0.461 , -0.5427]]])
If a DataFrame or Panel contains homogeneously-typed data, the ndarray canactually be modified in-place, and the changes will be reflected in the datastructure. For heterogeneous data (e.g. some of the DataFrame’s columns are notall the same dtype), this will not be the case. The values attribute itself,unlike the axis labels, cannot be assigned to.
Note
When working with heterogeneous data, the dtype of the resulting ndarraywill be chosen to accommodate all of the data involved. For example, ifstrings are involved, the result will be of object dtype. If there are onlyfloats and integers, the resulting array will be of float dtype.
pandas has support for accelerating certain types of binary numerical and boolean operations usingthenumexpr library (starting in 0.11.0) and thebottleneck libraries.
These libraries are especially useful when dealing with large data sets, and provide largespeedups.numexpr uses smart chunking, caching, and multiple cores.bottleneck isa set of specialized cython routines that are especially fast when dealing with arrays that havenans.
Here is a sample (using 100 column x 100,000 rowDataFrames):
| Operation | 0.11.0 (ms) | Prior Version (ms) | Ratio to Prior |
|---|---|---|---|
df1>df2 | 13.32 | 125.35 | 0.1063 |
df1*df2 | 21.71 | 36.63 | 0.5928 |
df1+df2 | 22.04 | 36.50 | 0.6039 |
You are highly encouraged to install both libraries. See the sectionRecommended Dependencies for more installation info.
With binary operations between pandas data structures, there are two key pointsof interest:
- Broadcasting behavior between higher- (e.g. DataFrame) andlower-dimensional (e.g. Series) objects.
- Missing data in computations
We will demonstrate how to manage these issues independently, though they canbe handled simultaneously.
DataFrame has the methodsadd(),sub(),mul(),div() and related functionsradd(),rsub(), ...for carrying out binary operations. For broadcasting behavior,Series input is of primary interest. Using these functions, you can use toeither match on theindex orcolumns via theaxis keyword:
In [14]:df=pd.DataFrame({'one':pd.Series(np.random.randn(3),index=['a','b','c']), ....:'two':pd.Series(np.random.randn(4),index=['a','b','c','d']), ....:'three':pd.Series(np.random.randn(3),index=['b','c','d'])}) ....:In [15]:dfOut[15]: one three twoa -0.626544 NaN -0.351587b -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789d NaN 1.124472 -1.101558In [16]:row=df.ix[1]In [17]:column=df['two']In [18]:df.sub(row,axis='columns')Out[18]: one three twoa -0.487650 NaN -1.487837b 0.000000 0.000000 0.000000c 0.150512 0.639504 -1.585038d NaN 1.301762 -2.237808In [19]:df.sub(row,axis=1)Out[19]: one three twoa -0.487650 NaN -1.487837b 0.000000 0.000000 0.000000c 0.150512 0.639504 -1.585038d NaN 1.301762 -2.237808In [20]:df.sub(column,axis='index')Out[20]: one three twoa -0.274957 NaN 0.0b -1.275144 -1.313539 0.0c 0.460406 0.911003 0.0d NaN 2.226031 0.0In [21]:df.sub(column,axis=0)Out[21]: one three twoa -0.274957 NaN 0.0b -1.275144 -1.313539 0.0c 0.460406 0.911003 0.0d NaN 2.226031 0.0
Furthermore you can align a level of a multi-indexed DataFrame with a Series.
In [22]:dfmi=df.copy()In [23]:dfmi.index=pd.MultiIndex.from_tuples([(1,'a'),(1,'b'),(1,'c'),(2,'a')], ....:names=['first','second']) ....:In [24]:dfmi.sub(column,axis=0,level='second')Out[24]: one three twofirst second1 a -0.274957 NaN 0.000000 b -1.275144 -1.313539 0.000000 c 0.460406 0.911003 0.0000002 a NaN 1.476060 -0.749971
With Panel, describing the matching behavior is a bit more difficult, sothe arithmetic methods instead (and perhaps confusingly?) give you the optionto specify thebroadcast axis. For example, suppose we wished to demean thedata over a particular axis. This can be accomplished by taking the mean overan axis and broadcasting over the same axis:
In [25]:major_mean=wp.mean(axis='major')In [26]:major_meanOut[26]: Item1 Item2A -0.546569 -0.260774B 0.492478 0.147993C -0.649010 -0.532794D 0.176307 0.623812In [27]:wp.sub(major_mean,axis='major')Out[27]:<class 'pandas.core.panel.Panel'>Dimensions: 2 (items) x 5 (major_axis) x 4 (minor_axis)Items axis: Item1 to Item2Major_axis axis: 2000-01-01 00:00:00 to 2000-01-05 00:00:00Minor_axis axis: A to D
And similarly foraxis="items" andaxis="minor".
Note
I could be convinced to make theaxis argument in the DataFrame methodsmatch the broadcasting behavior of Panel. Though it would require atransition period so users can change their code...
Series and Index also support thedivmod() builtin. This function takesthe floor division and modulo operation at the same time returning a two-tupleof the same type as the left hand side. For example:
In [28]:s=pd.Series(np.arange(10))In [29]:sOut[29]:0 01 12 23 34 45 56 67 78 89 9dtype: int64In [30]:div,rem=divmod(s,3)In [31]:divOut[31]:0 01 02 03 14 15 16 27 28 29 3dtype: int64In [32]:remOut[32]:0 01 12 23 04 15 26 07 18 29 0dtype: int64In [33]:idx=pd.Index(np.arange(10))In [34]:idxOut[34]:Int64Index([0,1,2,3,4,5,6,7,8,9],dtype='int64')In [35]:div,rem=divmod(idx,3)In [36]:divOut[36]:Int64Index([0,0,0,1,1,1,2,2,2,3],dtype='int64')In [37]:remOut[37]:Int64Index([0,1,2,0,1,2,0,1,2,0],dtype='int64')
We can also do elementwisedivmod():
In [38]:div,rem=divmod(s,[2,2,3,3,4,4,5,5,6,6])In [39]:divOut[39]:0 01 02 03 14 15 16 17 18 19 1dtype: int64In [40]:remOut[40]:0 01 12 23 04 05 16 17 28 29 3dtype: int64
In Series and DataFrame (though not yet in Panel), the arithmetic functionshave the option of inputting afill_value, namely a value to substitute whenat most one of the values at a location are missing. For example, when addingtwo DataFrame objects, you may wish to treat NaN as 0 unless both DataFramesare missing that value, in which case the result will be NaN (you can laterreplace NaN with some other value usingfillna if you wish).
In [41]:dfOut[41]: one three twoa -0.626544 NaN -0.351587b -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789d NaN 1.124472 -1.101558In [42]:df2Out[42]: one three twoa -0.626544 1.000000 -0.351587b -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789d NaN 1.124472 -1.101558In [43]:df+df2Out[43]: one three twoa -1.253088 NaN -0.703174b -0.277789 -0.354579 2.272499c 0.023235 0.924429 -0.897577d NaN 2.248945 -2.203116In [44]:df.add(df2,fill_value=0)Out[44]: one three twoa -1.253088 1.000000 -0.703174b -0.277789 -0.354579 2.272499c 0.023235 0.924429 -0.897577d NaN 2.248945 -2.203116
Starting in v0.8, pandas introduced binary comparison methods eq, ne, lt, gt,le, and ge to Series and DataFrame whose behavior is analogous to the binaryarithmetic operations described above:
In [45]:df.gt(df2)Out[45]: one three twoa False False Falseb False False Falsec False False Falsed False False FalseIn [46]:df2.ne(df)Out[46]: one three twoa False True Falseb False False Falsec False False Falsed True False False
These operations produce a pandas object the same type as the left-hand-side inputthat if of dtypebool. Theseboolean objects can be used in indexing operations,seehere
You can apply the reductions:empty,any(),all(), andbool() to provide away to summarize a boolean result.
In [47]:(df>0).all()Out[47]:one Falsethree Falsetwo Falsedtype: boolIn [48]:(df>0).any()Out[48]:one Truethree Truetwo Truedtype: bool
You can reduce to a final boolean value.
In [49]:(df>0).any().any()Out[49]:True
You can test if a pandas object is empty, via theempty property.
In [50]:df.emptyOut[50]:FalseIn [51]:pd.DataFrame(columns=list('ABC')).emptyOut[51]:True
To evaluate single-element pandas objects in a boolean context, use the methodbool():
In [52]:pd.Series([True]).bool()Out[52]:TrueIn [53]:pd.Series([False]).bool()Out[53]:FalseIn [54]:pd.DataFrame([[True]]).bool()Out[54]:TrueIn [55]:pd.DataFrame([[False]]).bool()Out[55]:False
Warning
You might be tempted to do the following:
>>>ifdf: ...
Or
>>>dfanddf2
These both will raise as you are trying to compare multiple values.
ValueError:Thetruthvalueofanarrayisambiguous.Usea.empty,a.any()ora.all().
Seegotchas for a more detailed discussion.
Often you may find there is more than one way to compute the sameresult. As a simple example, considerdf+df anddf*2. To testthat these two computations produce the same result, given the toolsshown above, you might imagine using(df+df==df*2).all(). But infact, this expression is False:
In [56]:df+df==df*2Out[56]: one three twoa True False Trueb True True Truec True True Trued False True TrueIn [57]:(df+df==df*2).all()Out[57]:one Falsethree Falsetwo Truedtype: bool
Notice that the boolean DataFramedf+df==df*2 contains some False values!That is because NaNs do not compare as equals:
In [58]:np.nan==np.nanOut[58]:False
So, as of v0.13.1, NDFrames (such as Series, DataFrames, and Panels)have anequals() method for testing equality, with NaNs incorresponding locations treated as equal.
In [59]:(df+df).equals(df*2)Out[59]:True
Note that the Series or DataFrame index needs to be in the same order forequality to be True:
In [60]:df1=pd.DataFrame({'col':['foo',0,np.nan]})In [61]:df2=pd.DataFrame({'col':[np.nan,0,'foo']},index=[2,1,0])In [62]:df1.equals(df2)Out[62]:FalseIn [63]:df1.equals(df2.sort_index())Out[63]:True
You can conveniently do element-wise comparisons when comparing a pandasdata structure with a scalar value:
In [64]:pd.Series(['foo','bar','baz'])=='foo'Out[64]:0 True1 False2 Falsedtype: boolIn [65]:pd.Index(['foo','bar','baz'])=='foo'Out[65]:array([True,False,False],dtype=bool)
Pandas also handles element-wise comparisons between different array-likeobjects of the same length:
In [66]:pd.Series(['foo','bar','baz'])==pd.Index(['foo','bar','qux'])Out[66]:0 True1 True2 Falsedtype: boolIn [67]:pd.Series(['foo','bar','baz'])==np.array(['foo','bar','qux'])Out[67]:0 True1 True2 Falsedtype: bool
Trying to compareIndex orSeries objects of different lengths willraise a ValueError:
In [55]:pd.Series(['foo','bar','baz'])==pd.Series(['foo','bar'])ValueError: Series lengths must match to compareIn [56]:pd.Series(['foo','bar','baz'])==pd.Series(['foo'])ValueError: Series lengths must match to compare
Note that this is different from the numpy behavior where a comparison canbe broadcast:
In [68]:np.array([1,2,3])==np.array([2])Out[68]:array([False,True,False],dtype=bool)
or it can return False if broadcasting can not be done:
In [69]:np.array([1,2,3])==np.array([1,2])Out[69]:False
A problem occasionally arising is the combination of two similar data setswhere values in one are preferred over the other. An example would be two dataseries representing a particular economic indicator where one is considered tobe of “higher quality”. However, the lower quality series might extend furtherback in history or have more complete data coverage. As such, we would like tocombine two DataFrame objects where missing values in one DataFrame areconditionally filled with like-labeled values from the other DataFrame. Thefunction implementing this operation iscombine_first(),which we illustrate:
In [70]:df1=pd.DataFrame({'A':[1.,np.nan,3.,5.,np.nan], ....:'B':[np.nan,2.,3.,np.nan,6.]}) ....:In [71]:df2=pd.DataFrame({'A':[5.,2.,4.,np.nan,3.,7.], ....:'B':[np.nan,np.nan,3.,4.,6.,8.]}) ....:In [72]:df1Out[72]: A B0 1.0 NaN1 NaN 2.02 3.0 3.03 5.0 NaN4 NaN 6.0In [73]:df2Out[73]: A B0 5.0 NaN1 2.0 NaN2 4.0 3.03 NaN 4.04 3.0 6.05 7.0 8.0In [74]:df1.combine_first(df2)Out[74]: A B0 1.0 NaN1 2.0 2.02 3.0 3.03 5.0 4.04 3.0 6.05 7.0 8.0
Thecombine_first() method above calls the more generalDataFrame methodcombine(). This method takes another DataFrameand a combiner function, aligns the input DataFrame and then passes the combinerfunction pairs of Series (i.e., columns whose names are the same).
So, for instance, to reproducecombine_first() as above:
In [75]:combiner=lambdax,y:np.where(pd.isnull(x),y,x)In [76]:df1.combine(df2,combiner)Out[76]: A B0 1.0 NaN1 2.0 2.02 3.0 3.03 5.0 4.04 3.0 6.05 7.0 8.0
A large number of methods for computing descriptive statistics and other relatedoperations onSeries,DataFrame, andPanel. Most of theseare aggregations (hence producing a lower-dimensional result) likesum(),mean(), andquantile(),but some of them, likecumsum() andcumprod(),produce an object of the same size. Generally speaking, these methods take anaxis argument, just likendarray.{sum, std, ...}, but the axis can bespecified by name or integer:
- Series: no axis argument needed
- DataFrame: “index” (axis=0, default), “columns” (axis=1)
- Panel: “items” (axis=0), “major” (axis=1, default), “minor”(axis=2)
For example:
In [77]:dfOut[77]: one three twoa -0.626544 NaN -0.351587b -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789d NaN 1.124472 -1.101558In [78]:df.mean(0)Out[78]:one -0.251274three 0.469799two -0.191421dtype: float64In [79]:df.mean(1)Out[79]:a -0.489066b 0.273355c 0.008348d 0.011457dtype: float64
All such methods have askipna option signaling whether to exclude missingdata (True by default):
In [80]:df.sum(0,skipna=False)Out[80]:one NaNthree NaNtwo -0.765684dtype: float64In [81]:df.sum(axis=1,skipna=True)Out[81]:a -0.978131b 0.820066c 0.025044d 0.022914dtype: float64
Combined with the broadcasting / arithmetic behavior, one can describe variousstatistical procedures, like standardization (rendering data zero mean andstandard deviation 1), very concisely:
In [82]:ts_stand=(df-df.mean())/df.std()In [83]:ts_stand.std()Out[83]:one 1.0three 1.0two 1.0dtype: float64In [84]:xs_stand=df.sub(df.mean(1),axis=0).div(df.std(1),axis=0)In [85]:xs_stand.std(1)Out[85]:a 1.0b 1.0c 1.0d 1.0dtype: float64
Note that methods likecumsum() andcumprod()preserve the location of NA values:
In [86]:df.cumsum()Out[86]: one three twoa -0.626544 NaN -0.351587b -0.765438 -0.177289 0.784662c -0.753821 0.284925 0.335874d NaN 1.409398 -0.765684
Here is a quick reference summary table of common functions. Each also takes anoptionallevel parameter which applies only if the object has ahierarchical index.
| Function | Description |
|---|---|
count | Number of non-null observations |
sum | Sum of values |
mean | Mean of values |
mad | Mean absolute deviation |
median | Arithmetic median of values |
min | Minimum |
max | Maximum |
mode | Mode |
abs | Absolute Value |
prod | Product of values |
std | Bessel-corrected sample standard deviation |
var | Unbiased variance |
sem | Standard error of the mean |
skew | Sample skewness (3rd moment) |
kurt | Sample kurtosis (4th moment) |
quantile | Sample quantile (value at %) |
cumsum | Cumulative sum |
cumprod | Cumulative product |
cummax | Cumulative maximum |
cummin | Cumulative minimum |
Note that by chance some NumPy methods, likemean,std, andsum,will exclude NAs on Series input by default:
In [87]:np.mean(df['one'])Out[87]:-0.2512736517583951In [88]:np.mean(df['one'].values)Out[88]:nan
Series also has a methodnunique() which will return thenumber of unique non-null values:
In [89]:series=pd.Series(np.random.randn(500))In [90]:series[20:500]=np.nanIn [91]:series[10:20]=5In [92]:series.nunique()Out[92]:11
There is a convenientdescribe() function which computes a variety of summarystatistics about a Series or the columns of a DataFrame (excluding NAs ofcourse):
In [93]:series=pd.Series(np.random.randn(1000))In [94]:series[::2]=np.nanIn [95]:series.describe()Out[95]:count 500.000000mean -0.039663std 1.069371min -3.46378925% -0.73110150% -0.05891875% 0.672758max 3.120271dtype: float64In [96]:frame=pd.DataFrame(np.random.randn(1000,5),columns=['a','b','c','d','e'])In [97]:frame.ix[::2]=np.nanIn [98]:frame.describe()Out[98]: a b c d ecount 500.000000 500.000000 500.000000 500.000000 500.000000mean 0.000954 -0.044014 0.075936 -0.003679 0.020751std 1.005133 0.974882 0.967432 1.004732 0.963812min -3.010899 -2.782760 -3.401252 -2.944925 -3.79412725% -0.682900 -0.681161 -0.528190 -0.663503 -0.61571750% -0.001651 -0.006279 0.040098 -0.003378 0.00628275% 0.656439 0.632852 0.717919 0.687214 0.653423max 3.007143 2.627688 2.702490 2.850852 3.072117
You can select specific percentiles to include in the output:
In [99]:series.describe(percentiles=[.05,.25,.75,.95])Out[99]:count 500.000000mean -0.039663std 1.069371min -3.4637895% -1.74133425% -0.73110150% -0.05891875% 0.67275895% 1.854383max 3.120271dtype: float64
By default, the median is always included.
For a non-numerical Series object,describe() will give a simplesummary of the number of unique values and most frequently occurring values:
In [100]:s=pd.Series(['a','a','b','b','a','a',np.nan,'c','d','a'])In [101]:s.describe()Out[101]:count 9unique 4top afreq 5dtype: object
Note that on a mixed-type DataFrame object,describe() willrestrict the summary to include only numerical columns or, if none are, onlycategorical columns:
In [102]:frame=pd.DataFrame({'a':['Yes','Yes','No','No'],'b':range(4)})In [103]:frame.describe()Out[103]: bcount 4.000000mean 1.500000std 1.290994min 0.00000025% 0.75000050% 1.50000075% 2.250000max 3.000000
This behaviour can be controlled by providing a list of types asinclude/excludearguments. The special valueall can also be used:
In [104]:frame.describe(include=['object'])Out[104]: acount 4unique 2top Nofreq 2In [105]:frame.describe(include=['number'])Out[105]: bcount 4.000000mean 1.500000std 1.290994min 0.00000025% 0.75000050% 1.50000075% 2.250000max 3.000000In [106]:frame.describe(include='all')Out[106]: a bcount 4 4.000000unique 2 NaNtop No NaNfreq 2 NaNmean NaN 1.500000std NaN 1.290994min NaN 0.00000025% NaN 0.75000050% NaN 1.50000075% NaN 2.250000max NaN 3.000000
That feature relies onselect_dtypes. Refer tothere for details about accepted inputs.
Theidxmin() andidxmax() functions on Seriesand DataFrame compute the index labels with the minimum and maximumcorresponding values:
In [107]:s1=pd.Series(np.random.randn(5))In [108]:s1Out[108]:0 -0.8727251 1.5224112 0.0805943 -1.6760674 0.435804dtype: float64In [109]:s1.idxmin(),s1.idxmax()Out[109]:(3,1)In [110]:df1=pd.DataFrame(np.random.randn(5,3),columns=['A','B','C'])In [111]:df1Out[111]: A B C0 0.445734 -1.649461 0.1696601 1.246181 0.131682 -2.0019882 -1.273023 0.870502 0.2145833 0.088452 -0.173364 1.2074664 0.546121 0.409515 -0.310515In [112]:df1.idxmin(axis=0)Out[112]:A 2B 0C 1dtype: int64In [113]:df1.idxmax(axis=1)Out[113]:0 A1 A2 B3 C4 Adtype: object
When there are multiple rows (or columns) matching the minimum or maximumvalue,idxmin() andidxmax() return the firstmatching index:
In [114]:df3=pd.DataFrame([2,1,1,3,np.nan],columns=['A'],index=list('edcba'))In [115]:df3Out[115]: Ae 2.0d 1.0c 1.0b 3.0a NaNIn [116]:df3['A'].idxmin()Out[116]:'d'
Note
idxmin andidxmax are calledargmin andargmax in NumPy.
Thevalue_counts() Series method and top-level function computes a histogramof a 1D array of values. It can also be used as a function on regular arrays:
In [117]:data=np.random.randint(0,7,size=50)In [118]:dataOut[118]:array([5, 3, 2, 2, 1, 4, 0, 4, 0, 2, 0, 6, 4, 1, 6, 3, 3, 0, 2, 1, 0, 5, 5, 3, 6, 1, 5, 6, 2, 0, 0, 6, 3, 3, 5, 0, 4, 3, 3, 3, 0, 6, 1, 3, 5, 5, 0, 4, 0, 6])In [119]:s=pd.Series(data)In [120]:s.value_counts()Out[120]:0 113 106 75 74 52 51 5dtype: int64In [121]:pd.value_counts(data)Out[121]:0 113 106 75 74 52 51 5dtype: int64
Similarly, you can get the most frequently occurring value(s) (the mode) of the values in a Series or DataFrame:
In [122]:s5=pd.Series([1,1,3,3,3,5,5,7,7,7])In [123]:s5.mode()Out[123]:0 31 7dtype: int64In [124]:df5=pd.DataFrame({"A":np.random.randint(0,7,size=50), .....:"B":np.random.randint(-10,15,size=50)}) .....:In [125]:df5.mode()Out[125]: A B0 1 -5
Continuous values can be discretized using thecut() (bins based on values)andqcut() (bins based on sample quantiles) functions:
In [126]:arr=np.random.randn(20)In [127]:factor=pd.cut(arr,4)In [128]:factorOut[128]:[(-0.645, 0.336], (-2.61, -1.626], (-1.626, -0.645], (-1.626, -0.645], (-1.626, -0.645], ..., (0.336, 1.316], (0.336, 1.316], (0.336, 1.316], (0.336, 1.316], (-2.61, -1.626]]Length: 20Categories (4, object): [(-2.61, -1.626] < (-1.626, -0.645] < (-0.645, 0.336] < (0.336, 1.316]]In [129]:factor=pd.cut(arr,[-5,-1,0,1,5])In [130]:factorOut[130]:[(-1, 0], (-5, -1], (-1, 0], (-5, -1], (-1, 0], ..., (0, 1], (1, 5], (0, 1], (0, 1], (-5, -1]]Length: 20Categories (4, object): [(-5, -1] < (-1, 0] < (0, 1] < (1, 5]]
qcut() computes sample quantiles. For example, we could slice up somenormally distributed data into equal-size quartiles like so:
In [131]:arr=np.random.randn(30)In [132]:factor=pd.qcut(arr,[0,.25,.5,.75,1])In [133]:factorOut[133]:[(-0.139, 1.00736], (1.00736, 1.976], (1.00736, 1.976], [-1.0705, -0.439], [-1.0705, -0.439], ..., (1.00736, 1.976], [-1.0705, -0.439], (-0.439, -0.139], (-0.439, -0.139], (-0.439, -0.139]]Length: 30Categories (4, object): [[-1.0705, -0.439] < (-0.439, -0.139] < (-0.139, 1.00736] < (1.00736, 1.976]]In [134]:pd.value_counts(factor)Out[134]:(1.00736, 1.976] 8[-1.0705, -0.439] 8(-0.139, 1.00736] 7(-0.439, -0.139] 7dtype: int64
We can also pass infinite values to define the bins:
In [135]:arr=np.random.randn(20)In [136]:factor=pd.cut(arr,[-np.inf,0,np.inf])In [137]:factorOut[137]:[(-inf, 0], (0, inf], (0, inf], (0, inf], (-inf, 0], ..., (-inf, 0], (0, inf], (-inf, 0], (-inf, 0], (0, inf]]Length: 20Categories (2, object): [(-inf, 0] < (0, inf]]
To apply your own or another library’s functions to pandas objects,you should be aware of the three methods below. The appropriatemethod to use depends on whether your function expects to operateon an entireDataFrame orSeries, row- or column-wise, or elementwise.
pipe()apply()applymap()New in version 0.16.2.
DataFrames andSeries can of course just be passed into functions.However, if the function needs to be called in a chain, consider using thepipe() method.Compare the following
# f, g, and h are functions taking and returning ``DataFrames``>>>f(g(h(df),arg1=1),arg2=2,arg3=3)
with the equivalent
>>>(df.pipe(h) .pipe(g, arg1=1) .pipe(f, arg2=2, arg3=3) )
Pandas encourages the second style, which is known as method chaining.pipe makes it easy to use your own or another library’s functionsin method chains, alongside pandas’ methods.
In the example above, the functionsf,g, andh each expected theDataFrame as the first positional argument.What if the function you wish to apply takes its data as, say, the second argument?In this case, providepipe with a tuple of(callable,data_keyword)..pipe will route theDataFrame to the argument specified in the tuple.
For example, we can fit a regression using statsmodels. Their API expects a formula first and aDataFrame as the second argument,data. We pass in the function, keyword pair(sm.poisson,'data') topipe:
In [138]:importstatsmodels.formula.apiassmIn [139]:bb=pd.read_csv('data/baseball.csv',index_col='id')In [140]:(bb.query('h > 0') .....:.assign(ln_h=lambdadf:np.log(df.h)) .....:.pipe((sm.poisson,'data'),'hr ~ ln_h + year + g + C(lg)') .....:.fit() .....:.summary() .....:) .....:Optimization terminated successfully. Current function value: 2.116284 Iterations 24Out[140]:<class 'statsmodels.iolib.summary.Summary'>""" Poisson Regression Results==============================================================================Dep. Variable: hr No. Observations: 68Model: Poisson Df Residuals: 63Method: MLE Df Model: 4Date: Don, 03 Nov 2016 Pseudo R-squ.: 0.6878Time: 16:46:53 Log-Likelihood: -143.91converged: True LL-Null: -460.91 LLR p-value: 6.774e-136=============================================================================== coef std err z P>|z| [95.0% Conf. Int.]-------------------------------------------------------------------------------Intercept -1267.3636 457.867 -2.768 0.006 -2164.767 -369.960C(lg)[T.NL] -0.2057 0.101 -2.044 0.041 -0.403 -0.008ln_h 0.9280 0.191 4.866 0.000 0.554 1.302year 0.6301 0.228 2.762 0.006 0.183 1.077g 0.0099 0.004 2.754 0.006 0.003 0.017==============================================================================="""
The pipe method is inspired by unix pipes and more recentlydplyr andmagrittr, whichhave introduced the popular(%>%) (read pipe) operator forR.The implementation ofpipe here is quite clean and feels right at home in python.We encourage you to view the source code (pd.DataFrame.pipe?? in IPython).
Arbitrary functions can be applied along the axes of a DataFrame or Panelusing theapply() method, which, like the descriptivestatistics methods, take an optionalaxis argument:
In [141]:df.apply(np.mean)Out[141]:one -0.251274three 0.469799two -0.191421dtype: float64In [142]:df.apply(np.mean,axis=1)Out[142]:a -0.489066b 0.273355c 0.008348d 0.011457dtype: float64In [143]:df.apply(lambdax:x.max()-x.min())Out[143]:one 0.638161three 1.301762two 2.237808dtype: float64In [144]:df.apply(np.cumsum)Out[144]: one three twoa -0.626544 NaN -0.351587b -0.765438 -0.177289 0.784662c -0.753821 0.284925 0.335874d NaN 1.409398 -0.765684In [145]:df.apply(np.exp)Out[145]: one three twoa 0.534436 NaN 0.703570b 0.870320 0.837537 3.115063c 1.011685 1.587586 0.638401d NaN 3.078592 0.332353
Depending on the return type of the function passed toapply(),the result will either be of lower dimension or the same dimension.
apply() combined with some cleverness can be used to answer many questionsabout a data set. For example, suppose we wanted to extract the date where themaximum value for each column occurred:
In [146]:tsdf=pd.DataFrame(np.random.randn(1000,3),columns=['A','B','C'], .....:index=pd.date_range('1/1/2000',periods=1000)) .....:In [147]:tsdf.apply(lambdax:x.idxmax())Out[147]:A 2001-04-27B 2002-06-02C 2000-04-02dtype: datetime64[ns]
You may also pass additional arguments and keyword arguments to theapply()method. For instance, consider the following function you would like to apply:
defsubtract_and_divide(x,sub,divide=1):return(x-sub)/divide
You may then apply this function as follows:
df.apply(subtract_and_divide,args=(5,),divide=3)
Another useful feature is the ability to pass Series methods to carry out someSeries operation on each column or row:
In [148]:tsdfOut[148]: A B C2000-01-01 1.796883 -0.930690 3.5428462000-01-02 -1.242888 -0.695279 -1.0008842000-01-03 -0.720299 0.546303 -0.0820422000-01-04 NaN NaN NaN2000-01-05 NaN NaN NaN2000-01-06 NaN NaN NaN2000-01-07 NaN NaN NaN2000-01-08 -0.527402 0.933507 0.1296462000-01-09 -0.338903 -1.265452 -1.9690042000-01-10 0.532566 0.341548 0.150493In [149]:tsdf.apply(pd.Series.interpolate)Out[149]: A B C2000-01-01 1.796883 -0.930690 3.5428462000-01-02 -1.242888 -0.695279 -1.0008842000-01-03 -0.720299 0.546303 -0.0820422000-01-04 -0.681720 0.623743 -0.0397042000-01-05 -0.643140 0.701184 0.0026332000-01-06 -0.604561 0.778625 0.0449712000-01-07 -0.565982 0.856066 0.0873092000-01-08 -0.527402 0.933507 0.1296462000-01-09 -0.338903 -1.265452 -1.9690042000-01-10 0.532566 0.341548 0.150493
Finally,apply() takes an argumentraw which is False by default, whichconverts each row or column into a Series before applying the function. Whenset to True, the passed function will instead receive an ndarray object, whichhas positive performance implications if you do not need the indexingfunctionality.
See also
The section onGroupBy demonstrates related, flexiblefunctionality for grouping by some criterion, applying, and combining theresults into a Series, DataFrame, etc.
Since not all functions can be vectorized (accept NumPy arrays and returnanother array or value), the methodsapplymap() on DataFrameand analogouslymap() on Series accept any Python function takinga single value and returning a single value. For example:
In [150]:df4Out[150]: one three twoa -0.626544 NaN -0.351587b -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789d NaN 1.124472 -1.101558In [151]:f=lambdax:len(str(x))In [152]:df4['one'].map(f)Out[152]:a 14b 15c 15d 3Name: one, dtype: int64In [153]:df4.applymap(f)Out[153]: one three twoa 14 3 15b 15 15 11c 15 14 15d 3 13 14
Series.map() has an additional feature which is that it can be used to easily“link” or “map” values defined by a secondary series. This is closely relatedtomerging/joining functionality:
In [154]:s=pd.Series(['six','seven','six','seven','six'], .....:index=['a','b','c','d','e']) .....:In [155]:t=pd.Series({'six':6.,'seven':7.})In [156]:sOut[156]:a sixb sevenc sixd sevene sixdtype: objectIn [157]:s.map(t)Out[157]:a 6.0b 7.0c 6.0d 7.0e 6.0dtype: float64
Applying with aPanel will pass aSeries to the applied function. If the appliedfunction returns aSeries, the result of the application will be aPanel. If the applied functionreduces to a scalar, the result of the application will be aDataFrame.
Note
Prior to 0.13.1apply on aPanel would only work onufuncs (e.g.np.sum/np.max).
In [158]:importpandas.util.testingastmIn [159]:panel=tm.makePanel(5)In [160]:panelOut[160]:<class 'pandas.core.panel.Panel'>Dimensions: 3 (items) x 5 (major_axis) x 4 (minor_axis)Items axis: ItemA to ItemCMajor_axis axis: 2000-01-03 00:00:00 to 2000-01-07 00:00:00Minor_axis axis: A to DIn [161]:panel['ItemA']Out[161]: A B C D2000-01-03 0.330418 1.893177 0.801111 0.5281542000-01-04 1.761200 0.170247 0.445614 -0.0293712000-01-05 0.567133 -0.916844 1.453046 -0.6311172000-01-06 -0.251020 0.835024 2.430373 -0.1724412000-01-07 1.020099 1.259919 0.653093 -1.020485
A transformational apply.
In [162]:result=panel.apply(lambdax:x*2,axis='items')In [163]:resultOut[163]:<class 'pandas.core.panel.Panel'>Dimensions: 3 (items) x 5 (major_axis) x 4 (minor_axis)Items axis: ItemA to ItemCMajor_axis axis: 2000-01-03 00:00:00 to 2000-01-07 00:00:00Minor_axis axis: A to DIn [164]:result['ItemA']Out[164]: A B C D2000-01-03 0.660836 3.786354 1.602222 1.0563082000-01-04 3.522400 0.340494 0.891228 -0.0587422000-01-05 1.134266 -1.833689 2.906092 -1.2622342000-01-06 -0.502039 1.670047 4.860747 -0.3448822000-01-07 2.040199 2.519838 1.306185 -2.040969
A reduction operation.
In [165]:panel.apply(lambdax:x.dtype,axis='items')Out[165]: A B C D2000-01-03 float64 float64 float64 float642000-01-04 float64 float64 float64 float642000-01-05 float64 float64 float64 float642000-01-06 float64 float64 float64 float642000-01-07 float64 float64 float64 float64
A similar reduction type operation
In [166]:panel.apply(lambdax:x.sum(),axis='major_axis')Out[166]: ItemA ItemB ItemCA 3.427831 -2.581431 0.840809B 3.241522 -1.409935 -1.114512C 5.783237 0.319672 -0.431906D -1.325260 -2.914834 0.857043
This last reduction is equivalent to
In [167]:panel.sum('major_axis')Out[167]: ItemA ItemB ItemCA 3.427831 -2.581431 0.840809B 3.241522 -1.409935 -1.114512C 5.783237 0.319672 -0.431906D -1.325260 -2.914834 0.857043
A transformation operation that returns aPanel, but is computingthe z-score across themajor_axis.
In [168]:result=panel.apply( .....:lambdax:(x-x.mean())/x.std(), .....:axis='major_axis') .....:In [169]:resultOut[169]:<class 'pandas.core.panel.Panel'>Dimensions: 3 (items) x 5 (major_axis) x 4 (minor_axis)Items axis: ItemA to ItemCMajor_axis axis: 2000-01-03 00:00:00 to 2000-01-07 00:00:00Minor_axis axis: A to DIn [170]:result['ItemA']Out[170]: A B C D2000-01-03 -0.469761 1.156225 -0.441347 1.3417312000-01-04 1.422763 -0.444015 -0.882647 0.3986612000-01-05 -0.156654 -1.453694 0.367936 -0.6192102000-01-06 -1.238841 0.173423 1.581149 0.1566542000-01-07 0.442494 0.568061 -0.625091 -1.277837
Apply can also accept multiple axes in theaxis argument. This will pass aDataFrame of the cross-section to the applied function.
In [171]:f=lambdax:((x.T-x.mean(1))/x.std(1)).TIn [172]:result=panel.apply(f,axis=['items','major_axis'])In [173]:resultOut[173]:<class 'pandas.core.panel.Panel'>Dimensions: 4 (items) x 5 (major_axis) x 3 (minor_axis)Items axis: A to DMajor_axis axis: 2000-01-03 00:00:00 to 2000-01-07 00:00:00Minor_axis axis: ItemA to ItemCIn [174]:result.loc[:,:,'ItemA']Out[174]: A B C D2000-01-03 0.864236 1.132969 0.557316 0.5751062000-01-04 0.795745 0.652527 0.534808 -0.0706742000-01-05 -0.310864 0.558627 1.086688 -1.0514772000-01-06 -0.001065 0.832460 0.846006 0.0436022000-01-07 1.128946 1.152469 -0.218186 -0.891680
This is equivalent to the following
In [175]:result=pd.Panel(dict([(ax,f(panel.loc[:,:,ax])) .....:foraxinpanel.minor_axis])) .....:In [176]:resultOut[176]:<class 'pandas.core.panel.Panel'>Dimensions: 4 (items) x 5 (major_axis) x 3 (minor_axis)Items axis: A to DMajor_axis axis: 2000-01-03 00:00:00 to 2000-01-07 00:00:00Minor_axis axis: ItemA to ItemCIn [177]:result.loc[:,:,'ItemA']Out[177]: A B C D2000-01-03 0.864236 1.132969 0.557316 0.5751062000-01-04 0.795745 0.652527 0.534808 -0.0706742000-01-05 -0.310864 0.558627 1.086688 -1.0514772000-01-06 -0.001065 0.832460 0.846006 0.0436022000-01-07 1.128946 1.152469 -0.218186 -0.891680
reindex() is the fundamental data alignment method in pandas.It is used to implement nearly all other features relying on label-alignmentfunctionality. Toreindex means to conform the data to match a given set oflabels along a particular axis. This accomplishes several things:
- Reorders the existing data to match a new set of labels
- Inserts missing value (NA) markers in label locations where no data forthat label existed
- If specified,fill data for missing labels using logic (highly relevantto working with time series data)
Here is a simple example:
In [178]:s=pd.Series(np.random.randn(5),index=['a','b','c','d','e'])In [179]:sOut[179]:a -1.010924b -0.672504c -1.139222d 0.354653e 0.563622dtype: float64In [180]:s.reindex(['e','b','f','d'])Out[180]:e 0.563622b -0.672504f NaNd 0.354653dtype: float64
Here, thef label was not contained in the Series and hence appears asNaN in the result.
With a DataFrame, you can simultaneously reindex the index and columns:
In [181]:dfOut[181]: one three twoa -0.626544 NaN -0.351587b -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789d NaN 1.124472 -1.101558In [182]:df.reindex(index=['c','f','b'],columns=['three','two','one'])Out[182]: three two onec 0.462215 -0.448789 0.011617f NaN NaN NaNb -0.177289 1.136249 -0.138894
For convenience, you may utilize thereindex_axis() method, whichtakes the labels and a keywordaxis parameter.
Note that theIndex objects containing the actual axis labels can beshared between objects. So if we have a Series and a DataFrame, thefollowing can be done:
In [183]:rs=s.reindex(df.index)In [184]:rsOut[184]:a -1.010924b -0.672504c -1.139222d 0.354653dtype: float64In [185]:rs.indexisdf.indexOut[185]:True
This means that the reindexed Series’s index is the same Python object as theDataFrame’s index.
See also
MultiIndex / Advanced Indexing is an even more concise way ofdoing reindexing.
Note
When writing performance-sensitive code, there is a good reason to spendsome time becoming a reindexing ninja:many operations are faster onpre-aligned data. Adding two unaligned DataFrames internally triggers areindexing step. For exploratory analysis you will hardly notice thedifference (becausereindex has been heavily optimized), but when CPUcycles matter sprinkling a few explicitreindex calls here and there canhave an impact.
You may wish to take an object and reindex its axes to be labeled the same asanother object. While the syntax for this is straightforward albeit verbose, itis a common enough operation that thereindex_like() method isavailable to make this simpler:
In [186]:df2Out[186]: one twoa -0.626544 -0.351587b -0.138894 1.136249c 0.011617 -0.448789In [187]:df3Out[187]: one twoa -0.375270 -0.463545b 0.112379 1.024292c 0.262891 -0.560746In [188]:df.reindex_like(df2)Out[188]: one twoa -0.626544 -0.351587b -0.138894 1.136249c 0.011617 -0.448789
align¶Thealign() method is the fastest way to simultaneously align two objects. Itsupports ajoin argument (related tojoining and merging):
join='outer': take the union of the indexes (default)join='left': use the calling object’s indexjoin='right': use the passed object’s indexjoin='inner': intersect the indexes
It returns a tuple with both of the reindexed Series:
In [189]:s=pd.Series(np.random.randn(5),index=['a','b','c','d','e'])In [190]:s1=s[:4]In [191]:s2=s[1:]In [192]:s1.align(s2)Out[192]:(a -0.365106 b 1.092702 c -1.481449 d 1.781190 e NaN dtype: float64, a NaN b 1.092702 c -1.481449 d 1.781190 e -0.031543 dtype: float64)In [193]:s1.align(s2,join='inner')Out[193]:(b 1.092702 c -1.481449 d 1.781190 dtype: float64, b 1.092702 c -1.481449 d 1.781190 dtype: float64)In [194]:s1.align(s2,join='left')Out[194]:(a -0.365106 b 1.092702 c -1.481449 d 1.781190 dtype: float64, a NaN b 1.092702 c -1.481449 d 1.781190 dtype: float64)
For DataFrames, the join method will be applied to both the index and thecolumns by default:
In [195]:df.align(df2,join='inner')Out[195]:( one two a -0.626544 -0.351587 b -0.138894 1.136249 c 0.011617 -0.448789, one two a -0.626544 -0.351587 b -0.138894 1.136249 c 0.011617 -0.448789)
You can also pass anaxis option to only align on the specified axis:
In [196]:df.align(df2,join='inner',axis=0)Out[196]:( one three two a -0.626544 NaN -0.351587 b -0.138894 -0.177289 1.136249 c 0.011617 0.462215 -0.448789, one two a -0.626544 -0.351587 b -0.138894 1.136249 c 0.011617 -0.448789)
If you pass a Series toDataFrame.align(), you can choose to align bothobjects either on the DataFrame’s index or columns using theaxis argument:
In [197]:df.align(df2.ix[0],axis=1)Out[197]:( one three two a -0.626544 NaN -0.351587 b -0.138894 -0.177289 1.136249 c 0.011617 0.462215 -0.448789 d NaN 1.124472 -1.101558, one -0.626544 three NaN two -0.351587 Name: a, dtype: float64)
reindex() takes an optional parametermethod which is afilling method chosen from the following table:
| Method | Action |
|---|---|
| pad / ffill | Fill values forward |
| bfill / backfill | Fill values backward |
| nearest | Fill from the nearest index value |
We illustrate these fill methods on a simple Series:
In [198]:rng=pd.date_range('1/3/2000',periods=8)In [199]:ts=pd.Series(np.random.randn(8),index=rng)In [200]:ts2=ts[[0,3,6]]In [201]:tsOut[201]:2000-01-03 0.4809932000-01-04 0.6042442000-01-05 -0.4872652000-01-06 1.9905332000-01-07 0.3270072000-01-08 1.0536392000-01-09 -2.9278082000-01-10 0.082065Freq: D, dtype: float64In [202]:ts2Out[202]:2000-01-03 0.4809932000-01-06 1.9905332000-01-09 -2.927808dtype: float64In [203]:ts2.reindex(ts.index)Out[203]:2000-01-03 0.4809932000-01-04 NaN2000-01-05 NaN2000-01-06 1.9905332000-01-07 NaN2000-01-08 NaN2000-01-09 -2.9278082000-01-10 NaNFreq: D, dtype: float64In [204]:ts2.reindex(ts.index,method='ffill')Out[204]:2000-01-03 0.4809932000-01-04 0.4809932000-01-05 0.4809932000-01-06 1.9905332000-01-07 1.9905332000-01-08 1.9905332000-01-09 -2.9278082000-01-10 -2.927808Freq: D, dtype: float64In [205]:ts2.reindex(ts.index,method='bfill')Out[205]:2000-01-03 0.4809932000-01-04 1.9905332000-01-05 1.9905332000-01-06 1.9905332000-01-07 -2.9278082000-01-08 -2.9278082000-01-09 -2.9278082000-01-10 NaNFreq: D, dtype: float64In [206]:ts2.reindex(ts.index,method='nearest')Out[206]:2000-01-03 0.4809932000-01-04 0.4809932000-01-05 1.9905332000-01-06 1.9905332000-01-07 1.9905332000-01-08 -2.9278082000-01-09 -2.9278082000-01-10 -2.927808Freq: D, dtype: float64
These methods require that the indexes areordered increasing ordecreasing.
Note that the same result could have been achieved usingfillna (except formethod='nearest') orinterpolate:
In [207]:ts2.reindex(ts.index).fillna(method='ffill')Out[207]:2000-01-03 0.4809932000-01-04 0.4809932000-01-05 0.4809932000-01-06 1.9905332000-01-07 1.9905332000-01-08 1.9905332000-01-09 -2.9278082000-01-10 -2.927808Freq: D, dtype: float64
reindex() will raise a ValueError if the index is not monotonicincreasing or decreasing.fillna() andinterpolate()will not make any checks on the order of the index.
Thelimit andtolerance arguments provide additional control overfilling while reindexing. Limit specifies the maximum count of consecutivematches:
In [208]:ts2.reindex(ts.index,method='ffill',limit=1)Out[208]:2000-01-03 0.4809932000-01-04 0.4809932000-01-05 NaN2000-01-06 1.9905332000-01-07 1.9905332000-01-08 NaN2000-01-09 -2.9278082000-01-10 -2.927808Freq: D, dtype: float64
In contrast, tolerance specifies the maximum distance between the index andindexer values:
In [209]:ts2.reindex(ts.index,method='ffill',tolerance='1 day')Out[209]:2000-01-03 0.4809932000-01-04 0.4809932000-01-05 NaN2000-01-06 1.9905332000-01-07 1.9905332000-01-08 NaN2000-01-09 -2.9278082000-01-10 -2.927808Freq: D, dtype: float64
Notice that when used on aDatetimeIndex,TimedeltaIndex orPeriodIndex,tolerance will coerced into aTimedelta if possible.This allows you to specify tolerance with appropriate strings.
A method closely related toreindex is thedrop() function.It removes a set of labels from an axis:
In [210]:dfOut[210]: one three twoa -0.626544 NaN -0.351587b -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789d NaN 1.124472 -1.101558In [211]:df.drop(['a','d'],axis=0)Out[211]: one three twob -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789In [212]:df.drop(['one'],axis=1)Out[212]: three twoa NaN -0.351587b -0.177289 1.136249c 0.462215 -0.448789d 1.124472 -1.101558
Note that the following also works, but is a bit less obvious / clean:
In [213]:df.reindex(df.index.difference(['a','d']))Out[213]: one three twob -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789
Therename() method allows you to relabel an axis based on somemapping (a dict or Series) or an arbitrary function.
In [214]:sOut[214]:a -0.365106b 1.092702c -1.481449d 1.781190e -0.031543dtype: float64In [215]:s.rename(str.upper)Out[215]:A -0.365106B 1.092702C -1.481449D 1.781190E -0.031543dtype: float64
If you pass a function, it must return a value when called with any of thelabels (and must produce a set of unique values). A dict orSeries can also be used:
In [216]:df.rename(columns={'one':'foo','two':'bar'}, .....:index={'a':'apple','b':'banana','d':'durian'}) .....:Out[216]: foo three barapple -0.626544 NaN -0.351587banana -0.138894 -0.177289 1.136249c 0.011617 0.462215 -0.448789durian NaN 1.124472 -1.101558
If the mapping doesn’t include a column/index label, it isn’t renamed. Alsoextra labels in the mapping don’t throw an error.
Therename() method also provides aninplace namedparameter that is by defaultFalse and copies the underlying data. Passinplace=True to rename the data in place.
New in version 0.18.0.
Finally,rename() also accepts a scalar or list-likefor altering theSeries.name attribute.
In [217]:s.rename("scalar-name")Out[217]:a -0.365106b 1.092702c -1.481449d 1.781190e -0.031543Name: scalar-name, dtype: float64
The Panel class has a relatedrename_axis() class which can renameany of its three axes.
The behavior of basic iteration over pandas objects depends on the type.When iterating over a Series, it is regarded as array-like, and basic iterationproduces the values. Other data structures, like DataFrame and Panel,follow the dict-like convention of iterating over the “keys” of theobjects.
In short, basic iteration (foriinobject) produces:
Thus, for example, iterating over a DataFrame gives you the column names:
In [218]:df=pd.DataFrame({'col1':np.random.randn(3),'col2':np.random.randn(3)}, .....:index=['a','b','c']) .....:In [219]:forcolindf: .....:print(col) .....:col1col2
Pandas objects also have the dict-likeiteritems() method toiterate over the (key, value) pairs.
To iterate over the rows of a DataFrame, you can use the following methods:
iterrows(): Iterate over the rows of a DataFrame as (index, Series) pairs.This converts the rows to Series objects, which can change the dtypes and has someperformance implications.itertuples(): Iterate over the rows of a DataFrameas namedtuples of the values. This is a lot faster thaniterrows(), and is in most cases preferable to useto iterate over the values of a DataFrame.Warning
Iterating through pandas objects is generallyslow. In many cases,iterating manually over the rows is not needed and can be avoided withone of the following approaches:
apply() instead of iteratingover the values. See the docs onfunction application.Warning
You shouldnever modify something you are iterating over.This is not guaranteed to work in all cases. Depending on thedata types, the iterator returns a copy and not a view, and writingto it will have no effect!
For example, in the following case setting the value has no effect:
In [220]:df=pd.DataFrame({'a':[1,2,3],'b':['a','b','c']})In [221]:forindex,rowindf.iterrows(): .....:row['a']=10 .....:In [222]:dfOut[222]: a b0 1 a1 2 b2 3 c
Consistent with the dict-like interface,iteritems() iteratesthrough key-value pairs:
For example:
In [223]:foritem,frameinwp.iteritems(): .....:print(item) .....:print(frame) .....:Item1 A B C D2000-01-01 -1.032011 0.969818 -0.962723 1.3820832000-01-02 -0.938794 0.669142 -0.433567 -0.2736102000-01-03 0.680433 -0.308450 -0.276099 -1.8211682000-01-04 -1.993606 -1.927385 -2.027924 1.6249722000-01-05 0.551135 3.059267 0.455264 -0.030740Item2 A B C D2000-01-01 0.935716 1.061192 -2.107852 0.1999052000-01-02 0.323586 -0.641630 -0.587514 0.0538972000-01-03 0.194889 -0.381994 0.318587 2.0890752000-01-04 -0.728293 -0.090255 -0.748199 1.3189312000-01-05 -2.029766 0.792652 0.461007 -0.542749
iterrows() allows you to iterate through the rows of aDataFrame as Series objects. It returns an iterator yielding eachindex value along with a Series containing the data in each row:
In [224]:forrow_index,rowindf.iterrows(): .....:print('%s\n%s'%(row_index,row)) .....:0a 1b aName: 0, dtype: object1a 2b bName: 1, dtype: object2a 3b cName: 2, dtype: object
Note
Becauseiterrows() returns a Series for each row,it doesnot preserve dtypes across the rows (dtypes arepreserved across columns for DataFrames). For example,
In [225]:df_orig=pd.DataFrame([[1,1.5]],columns=['int','float'])In [226]:df_orig.dtypesOut[226]:int int64float float64dtype: objectIn [227]:row=next(df_orig.iterrows())[1]In [228]:rowOut[228]:int 1.0float 1.5Name: 0, dtype: float64
All values inrow, returned as a Series, are now upcastedto floats, also the original integer value in columnx:
In [229]:row['int'].dtypeOut[229]:dtype('float64')In [230]:df_orig['int'].dtypeOut[230]:dtype('int64')
To preserve dtypes while iterating over the rows, it is betterto useitertuples() which returns namedtuples of the valuesand which is generally much faster asiterrows.
For instance, a contrived way to transpose the DataFrame would be:
In [231]:df2=pd.DataFrame({'x':[1,2,3],'y':[4,5,6]})In [232]:print(df2) x y0 1 41 2 52 3 6In [233]:print(df2.T) 0 1 2x 1 2 3y 4 5 6In [234]:df2_t=pd.DataFrame(dict((idx,values)foridx,valuesindf2.iterrows()))In [235]:print(df2_t) 0 1 2x 1 2 3y 4 5 6
Theitertuples() method will return an iteratoryielding a namedtuple for each row in the DataFrame. The first elementof the tuple will be the row’s corresponding index value, while theremaining values are the row values.
For instance,
In [236]:forrowindf.itertuples(): .....:print(row) .....:Pandas(Index=0, a=1, b='a')Pandas(Index=1, a=2, b='b')Pandas(Index=2, a=3, b='c')
This method does not convert the row to a Series object but justreturns the values inside a namedtuple. Therefore,itertuples() preserves the data type of the valuesand is generally faster asiterrows().
Note
The column names will be renamed to positional names if they areinvalid Python identifiers, repeated, or start with an underscore.With a large number of columns (>255), regular tuples are returned.
Series has an accessor to succinctly return datetime like properties for thevalues of the Series, if it is a datetime/period like Series.This will return a Series, indexed like the existing Series.
# datetimeIn [237]:s=pd.Series(pd.date_range('20130101 09:10:12',periods=4))In [238]:sOut[238]:0 2013-01-01 09:10:121 2013-01-02 09:10:122 2013-01-03 09:10:123 2013-01-04 09:10:12dtype: datetime64[ns]In [239]:s.dt.hourOut[239]:0 91 92 93 9dtype: int64In [240]:s.dt.secondOut[240]:0 121 122 123 12dtype: int64In [241]:s.dt.dayOut[241]:0 11 22 33 4dtype: int64
This enables nice expressions like this:
In [242]:s[s.dt.day==2]Out[242]:1 2013-01-02 09:10:12dtype: datetime64[ns]
You can easily produces tz aware transformations:
In [243]:stz=s.dt.tz_localize('US/Eastern')In [244]:stzOut[244]:0 2013-01-01 09:10:12-05:001 2013-01-02 09:10:12-05:002 2013-01-03 09:10:12-05:003 2013-01-04 09:10:12-05:00dtype: datetime64[ns, US/Eastern]In [245]:stz.dt.tzOut[245]:<DstTzInfo'US/Eastern'LMT-1day,19:04:00STD>
You can also chain these types of operations:
In [246]:s.dt.tz_localize('UTC').dt.tz_convert('US/Eastern')Out[246]:0 2013-01-01 04:10:12-05:001 2013-01-02 04:10:12-05:002 2013-01-03 04:10:12-05:003 2013-01-04 04:10:12-05:00dtype: datetime64[ns, US/Eastern]
You can also format datetime values as strings withSeries.dt.strftime() whichsupports the same format as the standardstrftime().
# DatetimeIndexIn [247]:s=pd.Series(pd.date_range('20130101',periods=4))In [248]:sOut[248]:0 2013-01-011 2013-01-022 2013-01-033 2013-01-04dtype: datetime64[ns]In [249]:s.dt.strftime('%Y/%m/%d')Out[249]:0 2013/01/011 2013/01/022 2013/01/033 2013/01/04dtype: object
# PeriodIndexIn [250]:s=pd.Series(pd.period_range('20130101',periods=4))In [251]:sOut[251]:0 2013-01-011 2013-01-022 2013-01-033 2013-01-04dtype: objectIn [252]:s.dt.strftime('%Y/%m/%d')Out[252]:0 2013/01/011 2013/01/022 2013/01/033 2013/01/04dtype: object
The.dt accessor works for period and timedelta dtypes.
# periodIn [253]:s=pd.Series(pd.period_range('20130101',periods=4,freq='D'))In [254]:sOut[254]:0 2013-01-011 2013-01-022 2013-01-033 2013-01-04dtype: objectIn [255]:s.dt.yearOut[255]:0 20131 20132 20133 2013dtype: int64In [256]:s.dt.dayOut[256]:0 11 22 33 4dtype: int64
# timedeltaIn [257]:s=pd.Series(pd.timedelta_range('1 day 00:00:05',periods=4,freq='s'))In [258]:sOut[258]:0 1 days 00:00:051 1 days 00:00:062 1 days 00:00:073 1 days 00:00:08dtype: timedelta64[ns]In [259]:s.dt.daysOut[259]:0 11 12 13 1dtype: int64In [260]:s.dt.secondsOut[260]:0 51 62 73 8dtype: int64In [261]:s.dt.componentsOut[261]: days hours minutes seconds milliseconds microseconds nanoseconds0 1 0 0 5 0 0 01 1 0 0 6 0 0 02 1 0 0 7 0 0 03 1 0 0 8 0 0 0
Note
Series.dt will raise aTypeError if you access with a non-datetimelike values
Series is equipped with a set of string processing methods that make it easy tooperate on each element of the array. Perhaps most importantly, these methodsexclude missing/NA values automatically. These are accessed via the Series’sstr attribute and generally have names matching the equivalent (scalar)built-in string methods. For example:
In [262]:s=pd.Series(['A','B','C','Aaba','Baca',np.nan,'CABA','dog','cat'])In [263]:s.str.lower()Out[263]:0 a1 b2 c3 aaba4 baca5 NaN6 caba7 dog8 catdtype: object
Powerful pattern-matching methods are provided as well, but note thatpattern-matching generally usesregular expressions by default (and in some casesalways uses them).
Please seeVectorized String Methods for a completedescription.
Warning
The sorting API is substantially changed in 0.17.0, seehere for these changes.In particular, all sorting methods now return a new object by default, andDO NOT operate in-place (except by passinginplace=True).
There are two obvious kinds of sorting that you may be interested in: sortingby label and sorting by actual values.
The primary method for sorting axislabels (indexes) are theSeries.sort_index() and theDataFrame.sort_index() methods.
In [264]:unsorted_df=df.reindex(index=['a','d','c','b'], .....:columns=['three','two','one']) .....:# DataFrameIn [265]:unsorted_df.sort_index()Out[265]: three two onea NaN NaN NaNb NaN NaN NaNc NaN NaN NaNd NaN NaN NaNIn [266]:unsorted_df.sort_index(ascending=False)Out[266]: three two oned NaN NaN NaNc NaN NaN NaNb NaN NaN NaNa NaN NaN NaNIn [267]:unsorted_df.sort_index(axis=1)Out[267]: one three twoa NaN NaN NaNd NaN NaN NaNc NaN NaN NaNb NaN NaN NaN# SeriesIn [268]:unsorted_df['three'].sort_index()Out[268]:a NaNb NaNc NaNd NaNName: three, dtype: float64
TheSeries.sort_values() andDataFrame.sort_values() are the entry points forvalue sorting (that is the values in a column or row).DataFrame.sort_values() can accept an optionalby argument foraxis=0which will use an arbitrary vector or a column name of the DataFrame todetermine the sort order:
In [269]:df1=pd.DataFrame({'one':[2,1,1,1],'two':[1,3,2,4],'three':[5,4,3,2]})In [270]:df1.sort_values(by='two')Out[270]: one three two0 2 5 12 1 3 21 1 4 33 1 2 4
Theby argument can take a list of column names, e.g.:
In [271]:df1[['one','two','three']].sort_values(by=['one','two'])Out[271]: one two three2 1 2 31 1 3 43 1 4 20 2 1 5
These methods have special treatment of NA values via thena_positionargument:
In [272]:s[2]=np.nanIn [273]:s.sort_values()Out[273]:0 A3 Aaba1 B4 Baca6 CABA8 cat7 dog2 NaN5 NaNdtype: objectIn [274]:s.sort_values(na_position='first')Out[274]:2 NaN5 NaN0 A3 Aaba1 B4 Baca6 CABA8 cat7 dogdtype: object
Series has thesearchsorted() method, which works similar tonumpy.ndarray.searchsorted().
In [275]:ser=pd.Series([1,2,3])In [276]:ser.searchsorted([0,3])Out[276]:array([0,2])In [277]:ser.searchsorted([0,4])Out[277]:array([0,3])In [278]:ser.searchsorted([1,3],side='right')Out[278]:array([1,3])In [279]:ser.searchsorted([1,3],side='left')Out[279]:array([0,2])In [280]:ser=pd.Series([3,1,2])In [281]:ser.searchsorted([0,3],sorter=np.argsort(ser))Out[281]:array([0,2])
New in version 0.14.0.
Series has thensmallest() andnlargest() methods which return thesmallest or largestn values. For a largeSeries this can be muchfaster than sorting the entire Series and callinghead(n) on the result.
In [282]:s=pd.Series(np.random.permutation(10))In [283]:sOut[283]:0 91 82 53 34 65 76 07 28 49 1dtype: int64In [284]:s.sort_values()Out[284]:6 09 17 23 38 42 54 65 71 80 9dtype: int64In [285]:s.nsmallest(3)Out[285]:6 09 17 2dtype: int64In [286]:s.nlargest(3)Out[286]:0 91 85 7dtype: int64
New in version 0.17.0.
DataFrame also has thenlargest andnsmallest methods.
In [287]:df=pd.DataFrame({'a':[-2,-1,1,10,8,11,-1], .....:'b':list('abdceff'), .....:'c':[1.0,2.0,4.0,3.2,np.nan,3.0,4.0]}) .....:In [288]:df.nlargest(3,'a')Out[288]: a b c5 11 f 3.03 10 c 3.24 8 e NaNIn [289]:df.nlargest(5,['a','c'])Out[289]: a b c5 11 f 3.03 10 c 3.24 8 e NaN2 1 d 4.01 -1 b 2.0In [290]:df.nsmallest(3,'a')Out[290]: a b c0 -2 a 1.01 -1 b 2.06 -1 f 4.0In [291]:df.nsmallest(5,['a','c'])Out[291]: a b c0 -2 a 1.01 -1 b 2.06 -1 f 4.02 1 d 4.04 8 e NaN
You must be explicit about sorting when the column is a multi-index, and fully specifyall levels toby.
In [292]:df1.columns=pd.MultiIndex.from_tuples([('a','one'),('a','two'),('b','three')])In [293]:df1.sort_values(by=('a','two'))Out[293]: a b one two three3 1 2 42 1 3 21 1 4 30 2 5 1
Thecopy() method on pandas objects copies the underlying data (though notthe axis indexes, since they are immutable) and returns a new object. Note thatit is seldom necessary to copy objects. For example, there are only ahandful of ways to alter a DataFramein-place:
- Inserting, deleting, or modifying a column
- Assigning to the
indexorcolumnsattributes- For homogeneous data, directly modifying the values via the
valuesattribute or advanced indexing
To be clear, no pandas methods have the side effect of modifying your data;almost all methods return new objects, leaving the original objectuntouched. If data is modified, it is because you did so explicitly.
The main types stored in pandas objects arefloat,int,bool,datetime64[ns] anddatetime64[ns,tz] (in >= 0.17.0),timedelta[ns],category (in >= 0.15.0), andobject. In addition these dtypeshave item sizes, e.g.int64 andint32. SeeSeries with TZ for more detail ondatetime64[ns,tz] dtypes.
A convenientdtypes attribute for DataFrames returns a Series with the data type of each column.
In [294]:dft=pd.DataFrame(dict(A=np.random.rand(3), .....:B=1, .....:C='foo', .....:D=pd.Timestamp('20010102'), .....:E=pd.Series([1.0]*3).astype('float32'), .....:F=False, .....:G=pd.Series([1]*3,dtype='int8'))) .....:In [295]:dftOut[295]: A B C D E F G0 0.954940 1 foo 2001-01-02 1.0 False 11 0.318163 1 foo 2001-01-02 1.0 False 12 0.985803 1 foo 2001-01-02 1.0 False 1In [296]:dft.dtypesOut[296]:A float64B int64C objectD datetime64[ns]E float32F boolG int8dtype: object
On aSeries use thedtype attribute.
In [297]:dft['A'].dtypeOut[297]:dtype('float64')
If a pandas object contains data multiple dtypesIN A SINGLE COLUMN, the dtype of thecolumn will be chosen to accommodate all of the data types (object is the mostgeneral).
# these ints are coerced to floatsIn [298]:pd.Series([1,2,3,4,5,6.])Out[298]:0 1.01 2.02 3.03 4.04 5.05 6.0dtype: float64# string data forces an ``object`` dtypeIn [299]:pd.Series([1,2,3,6.,'foo'])Out[299]:0 11 22 33 64 foodtype: object
The methodget_dtype_counts() will return the number of columns ofeach type in aDataFrame:
In [300]:dft.get_dtype_counts()Out[300]:bool 1datetime64[ns] 1float32 1float64 1int64 1int8 1object 1dtype: int64
Numeric dtypes will propagate and can coexist in DataFrames (starting in v0.11.0).If a dtype is passed (either directly via thedtype keyword, a passedndarray,or a passedSeries, then it will be preserved in DataFrame operations. Furthermore,different numeric dtypes willNOT be combined. The following example will give you a taste.
In [301]:df1=pd.DataFrame(np.random.randn(8,1),columns=['A'],dtype='float32')In [302]:df1Out[302]: A0 0.6476501 0.8229932 1.7787033 -1.5430484 -0.1232565 2.2397406 -0.1437787 -2.885090In [303]:df1.dtypesOut[303]:A float32dtype: objectIn [304]:df2=pd.DataFrame(dict(A=pd.Series(np.random.randn(8),dtype='float16'), .....:B=pd.Series(np.random.randn(8)), .....:C=pd.Series(np.array(np.random.randn(8),dtype='uint8')))) .....:In [305]:df2Out[305]: A B C0 0.027588 0.296947 01 -1.150391 0.007045 2552 0.246460 0.707877 13 -0.455078 0.950661 04 -1.507812 0.087527 05 -0.502441 -0.339212 06 0.528809 -0.278698 07 0.590332 1.775379 0In [306]:df2.dtypesOut[306]:A float16B float64C uint8dtype: object
By default integer types areint64 and float types arefloat64,REGARDLESS of platform (32-bit or 64-bit). The following will all result inint64 dtypes.
In [307]:pd.DataFrame([1,2],columns=['a']).dtypesOut[307]:a int64dtype: objectIn [308]:pd.DataFrame({'a':[1,2]}).dtypesOut[308]:a int64dtype: objectIn [309]:pd.DataFrame({'a':1},index=list(range(2))).dtypesOut[309]:a int64dtype: object
Numpy, however will chooseplatform-dependent types when creating arrays.The followingWILL result inint32 on 32-bit platform.
In [310]:frame=pd.DataFrame(np.array([1,2]))
Types can potentially beupcasted when combined with other types, meaning they are promotedfrom the current type (sayint tofloat)
In [311]:df3=df1.reindex_like(df2).fillna(value=0.0)+df2In [312]:df3Out[312]: A B C0 0.675238 0.296947 0.01 -0.327398 0.007045 255.02 2.025163 0.707877 1.03 -1.998126 0.950661 0.04 -1.631068 0.087527 0.05 1.737299 -0.339212 0.06 0.385030 -0.278698 0.07 -2.294758 1.775379 0.0In [313]:df3.dtypesOut[313]:A float32B float64C float64dtype: object
Thevalues attribute on a DataFrame return thelower-common-denominator of the dtypes, meaningthe dtype that can accommodateALL of the types in the resulting homogeneous dtyped numpy array. This canforce someupcasting.
In [314]:df3.values.dtypeOut[314]:dtype('float64')
You can use theastype() method to explicitly convert dtypes from one to another. These will by default return a copy,even if the dtype was unchanged (passcopy=False to change this behavior). In addition, they will raise anexception if the astype operation is invalid.
Upcasting is always according to thenumpy rules. If two different dtypes are involved in an operation,then the moregeneral one will be used as the result of the operation.
In [315]:df3Out[315]: A B C0 0.675238 0.296947 0.01 -0.327398 0.007045 255.02 2.025163 0.707877 1.03 -1.998126 0.950661 0.04 -1.631068 0.087527 0.05 1.737299 -0.339212 0.06 0.385030 -0.278698 0.07 -2.294758 1.775379 0.0In [316]:df3.dtypesOut[316]:A float32B float64C float64dtype: object# conversion of dtypesIn [317]:df3.astype('float32').dtypesOut[317]:A float32B float32C float32dtype: object
Convert a subset of columns to a specified type usingastype()
In [318]:dft=pd.DataFrame({'a':[1,2,3],'b':[4,5,6],'c':[7,8,9]})In [319]:dft[['a','b']]=dft[['a','b']].astype(np.uint8)In [320]:dftOut[320]: a b c0 1 4 71 2 5 82 3 6 9In [321]:dft.dtypesOut[321]:a uint8b uint8c int64dtype: object
Note
When trying to convert a subset of columns to a specified type usingastype() andloc(), upcasting occurs.
loc() tries to fit in what we are assigning to the current dtypes, while[] will overwrite them taking the dtype from the right hand side. Therefore the following piece of code produces the unintended result.
In [322]:dft=pd.DataFrame({'a':[1,2,3],'b':[4,5,6],'c':[7,8,9]})In [323]:dft.loc[:,['a','b']].astype(np.uint8).dtypesOut[323]:a uint8b uint8dtype: objectIn [324]:dft.loc[:,['a','b']]=dft.loc[:,['a','b']].astype(np.uint8)In [325]:dft.dtypesOut[325]:a int64b int64c int64dtype: object
pandas offers various functions to try to force conversion of types from theobject dtype to other types.The following functions are available for one dimensional object arrays or scalars:
to_numeric() (conversion to numeric dtypes)
In [326]:m=['1.1',2,3]In [327]:pd.to_numeric(m)Out[327]:array([1.1,2.,3.])
to_datetime() (conversion to datetime objects)
In [328]:importdatetimeIn [329]:m=['2016-07-09',datetime.datetime(2016,3,2)]In [330]:pd.to_datetime(m)Out[330]:DatetimeIndex(['2016-07-09','2016-03-02'],dtype='datetime64[ns]',freq=None)
to_timedelta() (conversion to timedelta objects)
In [331]:m=['5us',pd.Timedelta('1day')]In [332]:pd.to_timedelta(m)Out[332]:TimedeltaIndex(['0 days 00:00:00.000005','1 days 00:00:00'],dtype='timedelta64[ns]',freq=None)
To force a conversion, we can pass in anerrors argument, which specifies how pandas should deal with elementsthat cannot be converted to desired dtype or object. By default,errors='raise', meaning that any errors encounteredwill be raised during the conversion process. However, iferrors='coerce', these errors will be ignored and pandaswill convert problematic elements topd.NaT (for datetime and timedelta) ornp.nan (for numeric). This might beuseful if you are reading in data which is mostly of the desired dtype (e.g. numeric, datetime), but occasionally hasnon-conforming elements intermixed that you want to represent as missing:
In [333]:importdatetimeIn [334]:m=['apple',datetime.datetime(2016,3,2)]In [335]:pd.to_datetime(m,errors='coerce')Out[335]:DatetimeIndex(['NaT','2016-03-02'],dtype='datetime64[ns]',freq=None)In [336]:m=['apple',2,3]In [337]:pd.to_numeric(m,errors='coerce')Out[337]:array([nan,2.,3.])In [338]:m=['apple',pd.Timedelta('1day')]In [339]:pd.to_timedelta(m,errors='coerce')Out[339]:TimedeltaIndex([NaT,'1 days'],dtype='timedelta64[ns]',freq=None)
Theerrors parameter has a third option oferrors='ignore', which will simply return the passed in data if itencounters any errors with the conversion to a desired data type:
In [340]:importdatetimeIn [341]:m=['apple',datetime.datetime(2016,3,2)]In [342]:pd.to_datetime(m,errors='ignore')Out[342]:array(['apple',datetime.datetime(2016,3,2,0,0)],dtype=object)In [343]:m=['apple',2,3]In [344]:pd.to_numeric(m,errors='ignore')Out[344]:array(['apple',2,3],dtype=object)In [345]:m=['apple',pd.Timedelta('1day')]In [346]:pd.to_timedelta(m,errors='ignore')Out[346]:array(['apple',Timedelta('1 days 00:00:00')],dtype=object)
In addition to object conversion,to_numeric() provides another argumentdowncast, which gives theoption of downcasting the newly (or already) numeric data to a smaller dtype, which can conserve memory:
In [347]:m=['1',2,3]In [348]:pd.to_numeric(m,downcast='integer')# smallest signed int dtypeOut[348]:array([1,2,3],dtype=int8)In [349]:pd.to_numeric(m,downcast='signed')# same as 'integer'Out[349]:array([1,2,3],dtype=int8)In [350]:pd.to_numeric(m,downcast='unsigned')# smallest unsigned int dtypeOut[350]:array([1,2,3],dtype=uint8)In [351]:pd.to_numeric(m,downcast='float')# smallest float dtypeOut[351]:array([1.,2.,3.],dtype=float32)
As these methods apply only to one-dimensional arrays, lists or scalars; they cannot be used directly on multi-dimensional objects suchas DataFrames. However, withapply(), we can “apply” the function over each column efficiently:
In [352]:importdatetimeIn [353]:df=pd.DataFrame([['2016-07-09',datetime.datetime(2016,3,2)]]*2,dtype='O')In [354]:dfOut[354]: 0 10 2016-07-09 2016-03-02 00:00:001 2016-07-09 2016-03-02 00:00:00In [355]:df.apply(pd.to_datetime)Out[355]: 0 10 2016-07-09 2016-03-021 2016-07-09 2016-03-02In [356]:df=pd.DataFrame([['1.1',2,3]]*2,dtype='O')In [357]:dfOut[357]: 0 1 20 1.1 2 31 1.1 2 3In [358]:df.apply(pd.to_numeric)Out[358]: 0 1 20 1.1 2 31 1.1 2 3In [359]:df=pd.DataFrame([['5us',pd.Timedelta('1day')]]*2,dtype='O')In [360]:dfOut[360]: 0 10 5us 1 days 00:00:001 5us 1 days 00:00:00In [361]:df.apply(pd.to_timedelta)Out[361]: 0 10 00:00:00.000005 1 days1 00:00:00.000005 1 days
Performing selection operations oninteger type data can easily upcast the data tofloating.The dtype of the input data will be preserved in cases wherenans are not introduced (starting in 0.11.0)See alsointeger na gotchas
In [362]:dfi=df3.astype('int32')In [363]:dfi['E']=1In [364]:dfiOut[364]: A B C E0 0 0 0 11 0 0 255 12 2 0 1 13 -1 0 0 14 -1 0 0 15 1 0 0 16 0 0 0 17 -2 1 0 1In [365]:dfi.dtypesOut[365]:A int32B int32C int32E int64dtype: objectIn [366]:casted=dfi[dfi>0]In [367]:castedOut[367]: A B C E0 NaN NaN NaN 11 NaN NaN 255.0 12 2.0 NaN 1.0 13 NaN NaN NaN 14 NaN NaN NaN 15 1.0 NaN NaN 16 NaN NaN NaN 17 NaN 1.0 NaN 1In [368]:casted.dtypesOut[368]:A float64B float64C float64E int64dtype: object
While float dtypes are unchanged.
In [369]:dfa=df3.copy()In [370]:dfa['A']=dfa['A'].astype('float32')In [371]:dfa.dtypesOut[371]:A float32B float64C float64dtype: objectIn [372]:casted=dfa[df2>0]In [373]:castedOut[373]: A B C0 0.675238 0.296947 NaN1 NaN 0.007045 255.02 2.025163 0.707877 1.03 NaN 0.950661 NaN4 NaN 0.087527 NaN5 NaN NaN NaN6 0.385030 NaN NaN7 -2.294758 1.775379 NaNIn [374]:casted.dtypesOut[374]:A float32B float64C float64dtype: object
dtype¶New in version 0.14.1.
Theselect_dtypes() method implements subsetting of columnsbased on theirdtype.
First, let’s create aDataFrame with a slew of differentdtypes:
In [375]:df=pd.DataFrame({'string':list('abc'), .....:'int64':list(range(1,4)), .....:'uint8':np.arange(3,6).astype('u1'), .....:'float64':np.arange(4.0,7.0), .....:'bool1':[True,False,True], .....:'bool2':[False,True,False], .....:'dates':pd.date_range('now',periods=3).values, .....:'category':pd.Series(list("ABC")).astype('category')}) .....:In [376]:df['tdeltas']=df.dates.diff()In [377]:df['uint64']=np.arange(3,6).astype('u8')In [378]:df['other_dates']=pd.date_range('20130101',periods=3).valuesIn [379]:df['tz_aware_dates']=pd.date_range('20130101',periods=3,tz='US/Eastern')In [380]:dfOut[380]: bool1 bool2 category dates float64 int64 string \0 True False A 2016-11-03 16:46:56.967442 4.0 1 a1 False True B 2016-11-04 16:46:56.967442 5.0 2 b2 True False C 2016-11-05 16:46:56.967442 6.0 3 c uint8 tdeltas uint64 other_dates tz_aware_dates0 3 NaT 3 2013-01-01 2013-01-01 00:00:00-05:001 4 1 days 4 2013-01-02 2013-01-02 00:00:00-05:002 5 1 days 5 2013-01-03 2013-01-03 00:00:00-05:00
And the dtypes
In [381]:df.dtypesOut[381]:bool1 boolbool2 boolcategory categorydates datetime64[ns]float64 float64int64 int64string objectuint8 uint8tdeltas timedelta64[ns]uint64 uint64other_dates datetime64[ns]tz_aware_dates datetime64[ns, US/Eastern]dtype: object
select_dtypes() has two parametersinclude andexclude that allow you tosay “give me the columns WITH these dtypes” (include) and/or “give thecolumns WITHOUT these dtypes” (exclude).
For example, to selectbool columns
In [382]:df.select_dtypes(include=[bool])Out[382]: bool1 bool20 True False1 False True2 True False
You can also pass the name of a dtype in thenumpy dtype hierarchy:
In [383]:df.select_dtypes(include=['bool'])Out[383]: bool1 bool20 True False1 False True2 True False
select_dtypes() also works with generic dtypes as well.
For example, to select all numeric and boolean columns while excluding unsignedintegers
In [384]:df.select_dtypes(include=['number','bool'],exclude=['unsignedinteger'])Out[384]: bool1 bool2 float64 int64 tdeltas0 True False 4.0 1 NaT1 False True 5.0 2 1 days2 True False 6.0 3 1 days
To select string columns you must use theobject dtype:
In [385]:df.select_dtypes(include=['object'])Out[385]: string0 a1 b2 c
To see all the child dtypes of a genericdtype likenumpy.number youcan define a function that returns a tree of child dtypes:
In [386]:defsubdtypes(dtype): .....:subs=dtype.__subclasses__() .....:ifnotsubs: .....:returndtype .....:return[dtype,[subdtypes(dt)fordtinsubs]] .....:
All numpy dtypes are subclasses ofnumpy.generic:
In [387]:subdtypes(np.generic)Out[387]:[numpy.generic, [[numpy.number, [[numpy.integer, [[numpy.signedinteger, [numpy.int8, numpy.int16, numpy.int32, numpy.int64, numpy.int64, numpy.timedelta64]], [numpy.unsignedinteger, [numpy.uint8, numpy.uint16, numpy.uint32, numpy.uint64, numpy.uint64]]]], [numpy.inexact, [[numpy.floating, [numpy.float16, numpy.float32, numpy.float64, numpy.float128]], [numpy.complexfloating, [numpy.complex64, numpy.complex128, numpy.complex256]]]]]], [numpy.flexible, [[numpy.character, [numpy.string_, numpy.unicode_]], [numpy.void, [numpy.record]]]], numpy.bool_, numpy.datetime64, numpy.object_]]
Note
Pandas also defines the typescategory, anddatetime64[ns,tz], which are not integrated into the normalnumpy hierarchy and wont show up with the above function.
Note
Theinclude andexclude parameters must be non-string sequences.