Table Of Contents
- What’s New
- Installation
- Contributing to pandas
- Frequently Asked Questions (FAQ)
- Package overview
- 10 Minutes to pandas
- Tutorials
- Cookbook
- Intro to Data Structures
- Series
- DataFrame
- From dict of Series or dicts
- From dict of ndarrays / lists
- From structured or record array
- From a list of dicts
- From a dict of tuples
- From a Series
- Alternate Constructors
- Column selection, addition, deletion
- Assigning New Columns in Method Chains
- Indexing / Selection
- Data alignment and arithmetic
- Transposing
- DataFrame interoperability with NumPy functions
- Console display
- DataFrame column attribute access and IPython completion
- Panel
- Panel4D and PanelND (Deprecated)
- Essential Basic Functionality
- Working with Text Data
- Options and Settings
- Indexing and Selecting Data
- MultiIndex / Advanced Indexing
- Computational tools
- Working with missing data
- Group By: split-apply-combine
- Merge, join, and concatenate
- Reshaping and Pivot Tables
- Time Series / Date functionality
- Time Deltas
- Categorical Data
- Visualization
- Style
- IO Tools (Text, CSV, HDF5, ...)
- Remote Data Access
- Enhancing Performance
- Sparse data structures
- Caveats and Gotchas
- rpy2 / R interface
- pandas Ecosystem
- Comparison with R / R libraries
- Comparison with SQL
- Comparison with SAS
- API Reference
- Internals
- Release Notes
Search
Enter search terms or a module, class or function name.
Intro to Data Structures¶
We’ll start with a quick, non-comprehensive overview of the fundamental datastructures in pandas to get you started. The fundamental behavior about datatypes, indexing, and axis labeling / alignment apply across all of theobjects. To get started, import numpy and load pandas into your namespace:
In [1]:importnumpyasnpIn [2]:importpandasaspd
Here is a basic tenet to keep in mind:data alignment is intrinsic. The linkbetween labels and data will not be broken unless done so explicitly by you.
We’ll give a brief intro to the data structures, then consider all of the broadcategories of functionality and methods in separate sections.
Series¶
Series is a one-dimensional labeled array capable of holding any datatype (integers, strings, floating point numbers, Python objects, etc.). The axislabels are collectively referred to as theindex. The basic method to create a Series is to call:
>>>s=pd.Series(data,index=index)
Here,data can be many different things:
- a Python dict
- an ndarray
- a scalar value (like 5)
The passedindex is a list of axis labels. Thus, this separates into a fewcases depending on whatdata is:
From ndarray
Ifdata is an ndarray,index must be the same length asdata. If noindex is passed, one will be created having values[0,...,len(data)-1].
In [3]:s=pd.Series(np.random.randn(5),index=['a','b','c','d','e'])In [4]:sOut[4]:a 0.2735b 0.6052c -0.1692d 1.8298e 0.5432dtype: float64In [5]:s.indexOut[5]:Index([u'a',u'b',u'c',u'd',u'e'],dtype='object')In [6]:pd.Series(np.random.randn(5))Out[6]:0 0.36741 -0.82302 -1.02953 -1.05234 -0.8502dtype: float64
Note
Starting in v0.8.0, pandas supports non-unique index values. If an operationthat does not support duplicate index values is attempted, an exceptionwill be raised at that time. The reason for being lazy is nearly all performance-based(there are many instances in computations, like parts of GroupBy, where the indexis not used).
From dict
Ifdata is a dict, ifindex is passed the values in data correspondingto the labels in the index will be pulled out. Otherwise, an index will beconstructed from the sorted keys of the dict, if possible.
In [7]:d={'a':0.,'b':1.,'c':2.}In [8]:pd.Series(d)Out[8]:a 0.0b 1.0c 2.0dtype: float64In [9]:pd.Series(d,index=['b','c','d','a'])Out[9]:b 1.0c 2.0d NaNa 0.0dtype: float64
Note
NaN (not a number) is the standard missing data marker used in pandas
From scalar value Ifdata is a scalar value, an index must beprovided. The value will be repeated to match the length ofindex
In [10]:pd.Series(5.,index=['a','b','c','d','e'])Out[10]:a 5.0b 5.0c 5.0d 5.0e 5.0dtype: float64
Series is ndarray-like¶
Series acts very similarly to andarray, and is a valid argument to most NumPy functions.However, things like slicing also slice the index.
In [11]:s[0]Out[11]:0.27348116325673794In [12]:s[:3]Out[12]:a 0.2735b 0.6052c -0.1692dtype: float64In [13]:s[s>s.median()]Out[13]:b 0.6052d 1.8298dtype: float64In [14]:s[[4,3,1]]Out[14]:e 0.5432d 1.8298b 0.6052dtype: float64In [15]:np.exp(s)Out[15]:a 1.3145b 1.8317c 0.8443d 6.2327e 1.7215dtype: float64
We will address array-based indexing in a separatesection.
Series is dict-like¶
A Series is like a fixed-size dict in that you can get and set values by indexlabel:
In [16]:s['a']Out[16]:0.27348116325673794In [17]:s['e']=12.In [18]:sOut[18]:a 0.2735b 0.6052c -0.1692d 1.8298e 12.0000dtype: float64In [19]:'e'insOut[19]:TrueIn [20]:'f'insOut[20]:False
If a label is not contained, an exception is raised:
>>>s['f']KeyError: 'f'
Using theget method, a missing label will return None or specified default:
In [21]:s.get('f')In [22]:s.get('f',np.nan)Out[22]:nan
See also thesection on attribute access.
Vectorized operations and label alignment with Series¶
When doing data analysis, as with raw NumPy arrays looping through Seriesvalue-by-value is usually not necessary. Series can be also be passed into mostNumPy methods expecting an ndarray.
In [23]:s+sOut[23]:a 0.5470b 1.2104c -0.3385d 3.6596e 24.0000dtype: float64In [24]:s*2Out[24]:a 0.5470b 1.2104c -0.3385d 3.6596e 24.0000dtype: float64In [25]:np.exp(s)Out[25]:a 1.3145b 1.8317c 0.8443d 6.2327e 162754.7914dtype: float64
A key difference between Series and ndarray is that operations between Seriesautomatically align the data based on label. Thus, you can write computationswithout giving consideration to whether the Series involved have the samelabels.
In [26]:s[1:]+s[:-1]Out[26]:a NaNb 1.2104c -0.3385d 3.6596e NaNdtype: float64
The result of an operation between unaligned Series will have theunion ofthe indexes involved. If a label is not found in one Series or the other, theresult will be marked as missingNaN. Being able to write code without doingany explicit data alignment grants immense freedom and flexibility ininteractive data analysis and research. The integrated data alignment featuresof the pandas data structures set pandas apart from the majority of relatedtools for working with labeled data.
Note
In general, we chose to make the default result of operations betweendifferently indexed objects yield theunion of the indexes in order toavoid loss of information. Having an index label, though the data ismissing, is typically important information as part of a computation. Youof course have the option of dropping labels with missing data via thedropna function.
Name attribute¶
Series can also have aname attribute:
In [27]:s=pd.Series(np.random.randn(5),name='something')In [28]:sOut[28]:0 1.51401 -1.23452 0.56663 -1.01844 0.1081Name: something, dtype: float64In [29]:s.nameOut[29]:'something'
The Seriesname will be assigned automatically in many cases, in particularwhen taking 1D slices of DataFrame as you will see below.
New in version 0.18.0.
You can rename a Series with thepandas.Series.rename() method.
In [30]:s2=s.rename("different")In [31]:s2.nameOut[31]:'different'
Note thats ands2 refer to different objects.
DataFrame¶
DataFrame is a 2-dimensional labeled data structure with columns ofpotentially different types. You can think of it like a spreadsheet or SQLtable, or a dict of Series objects. It is generally the most commonly usedpandas object. Like Series, DataFrame accepts many different kinds of input:
- Dict of 1D ndarrays, lists, dicts, or Series
- 2-D numpy.ndarray
- Structured or record ndarray
- A
Series- Another
DataFrame
Along with the data, you can optionally passindex (row labels) andcolumns (column labels) arguments. If you pass an index and / or columns,you are guaranteeing the index and / or columns of the resultingDataFrame. Thus, a dict of Series plus a specific index will discard all datanot matching up to the passed index.
If axis labels are not passed, they will be constructed from the input databased on common sense rules.
From dict of Series or dicts¶
The resultindex will be theunion of the indexes of the variousSeries. If there are any nested dicts, these will be first converted toSeries. If no columns are passed, the columns will be the sorted list of dictkeys.
In [32]:d={'one':pd.Series([1.,2.,3.],index=['a','b','c']), ....:'two':pd.Series([1.,2.,3.,4.],index=['a','b','c','d'])} ....:In [33]:df=pd.DataFrame(d)In [34]:dfOut[34]: one twoa 1.0 1.0b 2.0 2.0c 3.0 3.0d NaN 4.0In [35]:pd.DataFrame(d,index=['d','b','a'])Out[35]: one twod NaN 4.0b 2.0 2.0a 1.0 1.0In [36]:pd.DataFrame(d,index=['d','b','a'],columns=['two','three'])Out[36]: two threed 4.0 NaNb 2.0 NaNa 1.0 NaN
The row and column labels can be accessed respectively by accessing theindex andcolumns attributes:
Note
When a particular set of columns is passed along with a dict of data, thepassed columns override the keys in the dict.
In [37]:df.indexOut[37]:Index([u'a',u'b',u'c',u'd'],dtype='object')In [38]:df.columnsOut[38]:Index([u'one',u'two'],dtype='object')
From dict of ndarrays / lists¶
The ndarrays must all be the same length. If an index is passed, it mustclearly also be the same length as the arrays. If no index is passed, theresult will berange(n), wheren is the array length.
In [39]:d={'one':[1.,2.,3.,4.], ....:'two':[4.,3.,2.,1.]} ....:In [40]:pd.DataFrame(d)Out[40]: one two0 1.0 4.01 2.0 3.02 3.0 2.03 4.0 1.0In [41]:pd.DataFrame(d,index=['a','b','c','d'])Out[41]: one twoa 1.0 4.0b 2.0 3.0c 3.0 2.0d 4.0 1.0
From structured or record array¶
This case is handled identically to a dict of arrays.
In [42]:data=np.zeros((2,),dtype=[('A','i4'),('B','f4'),('C','a10')])In [43]:data[:]=[(1,2.,'Hello'),(2,3.,"World")]In [44]:pd.DataFrame(data)Out[44]: A B C0 1 2.0 Hello1 2 3.0 WorldIn [45]:pd.DataFrame(data,index=['first','second'])Out[45]: A B Cfirst 1 2.0 Hellosecond 2 3.0 WorldIn [46]:pd.DataFrame(data,columns=['C','A','B'])Out[46]: C A B0 Hello 1 2.01 World 2 3.0
Note
DataFrame is not intended to work exactly like a 2-dimensional NumPyndarray.
From a list of dicts¶
In [47]:data2=[{'a':1,'b':2},{'a':5,'b':10,'c':20}]In [48]:pd.DataFrame(data2)Out[48]: a b c0 1 2 NaN1 5 10 20.0In [49]:pd.DataFrame(data2,index=['first','second'])Out[49]: a b cfirst 1 2 NaNsecond 5 10 20.0In [50]:pd.DataFrame(data2,columns=['a','b'])Out[50]: a b0 1 21 5 10
From a dict of tuples¶
You can automatically create a multi-indexed frame by passing a tuples dictionary
In [51]:pd.DataFrame({('a','b'):{('A','B'):1,('A','C'):2}, ....:('a','a'):{('A','C'):3,('A','B'):4}, ....:('a','c'):{('A','B'):5,('A','C'):6}, ....:('b','a'):{('A','C'):7,('A','B'):8}, ....:('b','b'):{('A','D'):9,('A','B'):10}}) ....:Out[51]: a b a b c a bA B 4.0 1.0 5.0 8.0 10.0 C 3.0 2.0 6.0 7.0 NaN D NaN NaN NaN NaN 9.0
From a Series¶
The result will be a DataFrame with the same index as the input Series, andwith one column whose name is the original name of the Series (only if no othercolumn name provided).
Missing Data
Much more will be said on this topic in theMissing datasection. To construct a DataFrame with missing data, usenp.nan for thosevalues which are missing. Alternatively, you may pass anumpy.MaskedArrayas the data argument to the DataFrame constructor, and its masked entries willbe considered missing.
Alternate Constructors¶
DataFrame.from_dict
DataFrame.from_dict takes a dict of dicts or a dict of array-like sequencesand returns a DataFrame. It operates like theDataFrame constructor exceptfor theorient parameter which is'columns' by default, but which can beset to'index' in order to use the dict keys as row labels.
DataFrame.from_records
DataFrame.from_records takes a list of tuples or an ndarray with structureddtype. Works analogously to the normalDataFrame constructor, except thatindex maybe be a specific field of the structured dtype to use as the index.For example:
In [52]:dataOut[52]:array([(1, 2.0, 'Hello'), (2, 3.0, 'World')], dtype=[('A', '<i4'), ('B', '<f4'), ('C', 'S10')])In [53]:pd.DataFrame.from_records(data,index='C')Out[53]: A BCHello 1 2.0World 2 3.0
DataFrame.from_items
DataFrame.from_items works analogously to the form of thedictconstructor that takes a sequence of(key,value) pairs, where the keys arecolumn (or row, in the case oforient='index') names, and the value are thecolumn values (or row values). This can be useful for constructing a DataFramewith the columns in a particular order without having to pass an explicit listof columns:
In [54]:pd.DataFrame.from_items([('A',[1,2,3]),('B',[4,5,6])])Out[54]: A B0 1 41 2 52 3 6
If you passorient='index', the keys will be the row labels. But in thiscase you must also pass the desired column names:
In [55]:pd.DataFrame.from_items([('A',[1,2,3]),('B',[4,5,6])], ....:orient='index',columns=['one','two','three']) ....:Out[55]: one two threeA 1 2 3B 4 5 6
Column selection, addition, deletion¶
You can treat a DataFrame semantically like a dict of like-indexed Seriesobjects. Getting, setting, and deleting columns works with the same syntax asthe analogous dict operations:
In [56]:df['one']Out[56]:a 1.0b 2.0c 3.0d NaNName: one, dtype: float64In [57]:df['three']=df['one']*df['two']In [58]:df['flag']=df['one']>2In [59]:dfOut[59]: one two three flaga 1.0 1.0 1.0 Falseb 2.0 2.0 4.0 Falsec 3.0 3.0 9.0 Trued NaN 4.0 NaN False
Columns can be deleted or popped like with a dict:
In [60]:deldf['two']In [61]:three=df.pop('three')In [62]:dfOut[62]: one flaga 1.0 Falseb 2.0 Falsec 3.0 Trued NaN False
When inserting a scalar value, it will naturally be propagated to fill thecolumn:
In [63]:df['foo']='bar'In [64]:dfOut[64]: one flag fooa 1.0 False barb 2.0 False barc 3.0 True bard NaN False bar
When inserting a Series that does not have the same index as the DataFrame, itwill be conformed to the DataFrame’s index:
In [65]:df['one_trunc']=df['one'][:2]In [66]:dfOut[66]: one flag foo one_trunca 1.0 False bar 1.0b 2.0 False bar 2.0c 3.0 True bar NaNd NaN False bar NaN
You can insert raw ndarrays but their length must match the length of theDataFrame’s index.
By default, columns get inserted at the end. Theinsert function isavailable to insert at a particular location in the columns:
In [67]:df.insert(1,'bar',df['one'])In [68]:dfOut[68]: one bar flag foo one_trunca 1.0 1.0 False bar 1.0b 2.0 2.0 False bar 2.0c 3.0 3.0 True bar NaNd NaN NaN False bar NaN
Assigning New Columns in Method Chains¶
New in version 0.16.0.
Inspired bydplyr’smutate verb, DataFrame has anassign()method that allows you to easily create new columns that are potentiallyderived from existing columns.
In [69]:iris=pd.read_csv('data/iris.data')In [70]:iris.head()Out[70]: SepalLength SepalWidth PetalLength PetalWidth Name0 5.1 3.5 1.4 0.2 Iris-setosa1 4.9 3.0 1.4 0.2 Iris-setosa2 4.7 3.2 1.3 0.2 Iris-setosa3 4.6 3.1 1.5 0.2 Iris-setosa4 5.0 3.6 1.4 0.2 Iris-setosaIn [71]:(iris.assign(sepal_ratio=iris['SepalWidth']/iris['SepalLength']) ....:.head()) ....:Out[71]: SepalLength SepalWidth PetalLength PetalWidth Name sepal_ratio0 5.1 3.5 1.4 0.2 Iris-setosa 0.68631 4.9 3.0 1.4 0.2 Iris-setosa 0.61222 4.7 3.2 1.3 0.2 Iris-setosa 0.68093 4.6 3.1 1.5 0.2 Iris-setosa 0.67394 5.0 3.6 1.4 0.2 Iris-setosa 0.7200
Above was an example of inserting a precomputed value. We can also pass ina function of one argument to be evalutated on the DataFrame being assigned to.
In [72]:iris.assign(sepal_ratio=lambdax:(x['SepalWidth']/ ....:x['SepalLength'])).head() ....:Out[72]: SepalLength SepalWidth PetalLength PetalWidth Name sepal_ratio0 5.1 3.5 1.4 0.2 Iris-setosa 0.68631 4.9 3.0 1.4 0.2 Iris-setosa 0.61222 4.7 3.2 1.3 0.2 Iris-setosa 0.68093 4.6 3.1 1.5 0.2 Iris-setosa 0.67394 5.0 3.6 1.4 0.2 Iris-setosa 0.7200
assignalways returns a copy of the data, leaving the originalDataFrame untouched.
Passing a callable, as opposed to an actual value to be inserted, isuseful when you don’t have a reference to the DataFrame at hand. This iscommon when usingassign in chains of operations. For example,we can limit the DataFrame to just those observations with a Sepal Lengthgreater than 5, calculate the ratio, and plot:
In [73]:(iris.query('SepalLength > 5') ....:.assign(SepalRatio=lambdax:x.SepalWidth/x.SepalLength, ....:PetalRatio=lambdax:x.PetalWidth/x.PetalLength) ....:.plot(kind='scatter',x='SepalRatio',y='PetalRatio')) ....:Out[73]:<matplotlib.axes._subplots.AxesSubplotat0x7fd2448fa490>
Since a function is passed in, the function is computed on the DataFramebeing assigned to. Importantly, this is the DataFrame that’s been filteredto those rows with sepal length greater than 5. The filtering happens first,and then the ratio calculations. This is an example where we didn’thave a reference to thefiltered DataFrame available.
The function signature forassign is simply**kwargs. The keysare the column names for the new fields, and the values are either a valueto be inserted (for example, aSeries or NumPy array), or a functionof one argument to be called on theDataFrame. Acopy of the originalDataFrame is returned, with the new values inserted.
Warning
Since the function signature ofassign is**kwargs, a dictionary,the order of the new columns in the resulting DataFrame cannot be guaranteedto match the order you pass in. To make things predictable, items are insertedalphabetically (by key) at the end of the DataFrame.
All expressions are computed first, and then assigned. So you can’t referto another column being assigned in the same call toassign. For example:
In [74]:# Don't do this, bad reference to `C` df.assign(C = lambda x: x['A'] + x['B'], D = lambda x: x['A'] + x['C'])In [2]:# Instead, break it into two assigns (df.assign(C = lambda x: x['A'] + x['B']) .assign(D = lambda x: x['A'] + x['C']))
Indexing / Selection¶
The basics of indexing are as follows:
| Operation | Syntax | Result |
|---|---|---|
| Select column | df[col] | Series |
| Select row by label | df.loc[label] | Series |
| Select row by integer location | df.iloc[loc] | Series |
| Slice rows | df[5:10] | DataFrame |
| Select rows by boolean vector | df[bool_vec] | DataFrame |
Row selection, for example, returns a Series whose index is the columns of theDataFrame:
In [75]:df.loc['b']Out[75]:one 2bar 2flag Falsefoo barone_trunc 2Name: b, dtype: objectIn [76]:df.iloc[2]Out[76]:one 3bar 3flag Truefoo barone_trunc NaNName: c, dtype: object
For a more exhaustive treatment of more sophisticated label-based indexing andslicing, see thesection on indexing. We will address thefundamentals of reindexing / conforming to new sets of labels in thesection on reindexing.
Data alignment and arithmetic¶
Data alignment between DataFrame objects automatically align onboth thecolumns and the index (row labels). Again, the resulting object will have theunion of the column and row labels.
In [77]:df=pd.DataFrame(np.random.randn(10,4),columns=['A','B','C','D'])In [78]:df2=pd.DataFrame(np.random.randn(7,3),columns=['A','B','C'])In [79]:df+df2Out[79]: A B C D0 0.5222 0.3225 -0.7566 NaN1 -0.8441 0.2334 0.8818 NaN2 -2.2079 -0.1572 -0.3875 NaN3 2.8080 -1.0927 1.0432 NaN4 -1.7511 -2.0812 2.7477 NaN5 -3.2473 -1.0850 0.7898 NaN6 -1.7107 0.0661 0.1294 NaN7 NaN NaN NaN NaN8 NaN NaN NaN NaN9 NaN NaN NaN NaN
When doing an operation between DataFrame and Series, the default behavior isto align the Seriesindex on the DataFramecolumns, thusbroadcastingrow-wise. For example:
In [80]:df-df.iloc[0]Out[80]: A B C D0 0.0000 0.0000 0.0000 0.00001 -2.6396 -1.0702 1.7214 -0.78962 -2.7662 -1.6918 2.2776 -2.54013 0.8679 -3.5247 1.9365 -0.13314 -1.9883 -3.2162 2.0464 -1.07005 -3.3932 -4.0976 1.6366 -2.16356 -1.3668 -1.9572 1.6523 -0.71917 -0.7949 -2.1663 0.9706 -2.62978 -0.8383 -1.3630 1.6702 -2.08659 0.8588 0.0814 3.7305 -1.3737
In the special case of working with time series data, and the DataFrame indexalso contains dates, the broadcasting will be column-wise:
In [81]:index=pd.date_range('1/1/2000',periods=8)In [82]:df=pd.DataFrame(np.random.randn(8,3),index=index,columns=list('ABC'))In [83]:dfOut[83]: A B C2000-01-01 0.2731 0.3604 -1.15152000-01-02 1.1577 1.4787 -0.65282000-01-03 -0.7712 0.2203 -0.57392000-01-04 -0.6356 -1.1703 -0.07892000-01-05 -1.4687 0.1705 -1.87962000-01-06 -1.2037 0.9568 -1.13832000-01-07 -0.6540 -0.2169 0.38432000-01-08 -2.1639 -0.8145 -1.2475In [84]:type(df['A'])Out[84]:pandas.core.series.SeriesIn [85]:df-df['A']Out[85]: 2000-01-01 00:00:00 2000-01-02 00:00:00 2000-01-03 00:00:00 \2000-01-01 NaN NaN NaN2000-01-02 NaN NaN NaN2000-01-03 NaN NaN NaN2000-01-04 NaN NaN NaN2000-01-05 NaN NaN NaN2000-01-06 NaN NaN NaN2000-01-07 NaN NaN NaN2000-01-08 NaN NaN NaN 2000-01-04 00:00:00 ... 2000-01-08 00:00:00 A B C2000-01-01 NaN ... NaN NaN NaN NaN2000-01-02 NaN ... NaN NaN NaN NaN2000-01-03 NaN ... NaN NaN NaN NaN2000-01-04 NaN ... NaN NaN NaN NaN2000-01-05 NaN ... NaN NaN NaN NaN2000-01-06 NaN ... NaN NaN NaN NaN2000-01-07 NaN ... NaN NaN NaN NaN2000-01-08 NaN ... NaN NaN NaN NaN[8 rows x 11 columns]
Warning
df-df['A']
is now deprecated and will be removed in a future release. The preferred wayto replicate this behavior is
df.sub(df['A'],axis=0)
For explicit control over the matching and broadcasting behavior, see thesection onflexible binary operations.
Operations with scalars are just as you would expect:
In [86]:df*5+2Out[86]: A B C2000-01-01 3.3655 3.8018 -3.75752000-01-02 7.7885 9.3936 -1.26412000-01-03 -1.8558 3.1017 -0.86962000-01-04 -1.1781 -3.8513 1.60562000-01-05 -5.3437 2.8523 -7.39822000-01-06 -4.0186 6.7842 -3.69152000-01-07 -1.2699 0.9157 3.92172000-01-08 -8.8194 -2.0724 -4.2375In [87]:1/dfOut[87]: A B C2000-01-01 3.6616 2.7751 -0.86842000-01-02 0.8638 0.6763 -1.53182000-01-03 -1.2967 4.5383 -1.74242000-01-04 -1.5733 -0.8545 -12.67592000-01-05 -0.6809 5.8662 -0.53202000-01-06 -0.8308 1.0451 -0.87852000-01-07 -1.5291 -4.6113 2.60192000-01-08 -0.4621 -1.2278 -0.8016In [88]:df**4Out[88]: A B C2000-01-01 0.0056 0.0169 1.7581e+002000-01-02 1.7964 4.7813 1.8162e-012000-01-03 0.3537 0.0024 1.0849e-012000-01-04 0.1632 1.8755 3.8733e-052000-01-05 4.6534 0.0008 1.2482e+012000-01-06 2.0995 0.8382 1.6789e+002000-01-07 0.1829 0.0022 2.1819e-022000-01-08 21.9244 0.4401 2.4219e+00
Boolean operators work as well:
In [89]:df1=pd.DataFrame({'a':[1,0,1],'b':[0,1,1]},dtype=bool)In [90]:df2=pd.DataFrame({'a':[0,1,1],'b':[1,1,0]},dtype=bool)In [91]:df1&df2Out[91]: a b0 False False1 False True2 True FalseIn [92]:df1|df2Out[92]: a b0 True True1 True True2 True TrueIn [93]:df1^df2Out[93]: a b0 True True1 True False2 False TrueIn [94]:-df1Out[94]: a b0 False True1 True False2 False False
Transposing¶
To transpose, access theT attribute (also thetranspose function),similar to an ndarray:
# only show the first 5 rowsIn [95]:df[:5].TOut[95]: 2000-01-01 2000-01-02 2000-01-03 2000-01-04 2000-01-05A 0.2731 1.1577 -0.7712 -0.6356 -1.4687B 0.3604 1.4787 0.2203 -1.1703 0.1705C -1.1515 -0.6528 -0.5739 -0.0789 -1.8796
DataFrame interoperability with NumPy functions¶
Elementwise NumPy ufuncs (log, exp, sqrt, ...) and various other NumPy functionscan be used with no issues on DataFrame, assuming the data within are numeric:
In [96]:np.exp(df)Out[96]: A B C2000-01-01 1.3140 1.4338 0.31622000-01-02 3.1826 4.3873 0.52062000-01-03 0.4625 1.2465 0.56332000-01-04 0.5296 0.3103 0.92412000-01-05 0.2302 1.1859 0.15262000-01-06 0.3001 2.6034 0.32042000-01-07 0.5200 0.8050 1.46862000-01-08 0.1149 0.4429 0.2872In [97]:np.asarray(df)Out[97]:array([[ 0.2731, 0.3604, -1.1515], [ 1.1577, 1.4787, -0.6528], [-0.7712, 0.2203, -0.5739], [-0.6356, -1.1703, -0.0789], [-1.4687, 0.1705, -1.8796], [-1.2037, 0.9568, -1.1383], [-0.654 , -0.2169, 0.3843], [-2.1639, -0.8145, -1.2475]])
The dot method on DataFrame implements matrix multiplication:
In [98]:df.T.dot(df)Out[98]: A B CA 11.1298 2.8864 6.0015B 2.8864 5.3895 -1.8913C 6.0015 -1.8913 8.6204
Similarly, the dot method on Series implements dot product:
In [99]:s1=pd.Series(np.arange(5,10))In [100]:s1.dot(s1)Out[100]:255
DataFrame is not intended to be a drop-in replacement for ndarray as itsindexing semantics are quite different in places from a matrix.
Console display¶
Very large DataFrames will be truncated to display them in the console.You can also get a summary usinginfo().(Here I am reading a CSV version of thebaseball dataset from theplyrR package):
In [101]:baseball=pd.read_csv('data/baseball.csv')In [102]:print(baseball) id player year stint ... hbp sh sf gidp0 88641 womacto01 2006 2 ... 0.0 3.0 0.0 0.01 88643 schilcu01 2006 1 ... 0.0 0.0 0.0 0.0.. ... ... ... ... ... ... ... ... ...98 89533 aloumo01 2007 1 ... 2.0 0.0 3.0 13.099 89534 alomasa02 2007 1 ... 0.0 0.0 0.0 0.0[100 rows x 23 columns]In [103]:baseball.info()<class 'pandas.core.frame.DataFrame'>RangeIndex: 100 entries, 0 to 99Data columns (total 23 columns):id 100 non-null int64player 100 non-null objectyear 100 non-null int64stint 100 non-null int64team 100 non-null objectlg 100 non-null objectg 100 non-null int64ab 100 non-null int64r 100 non-null int64h 100 non-null int64X2b 100 non-null int64X3b 100 non-null int64hr 100 non-null int64rbi 100 non-null float64sb 100 non-null float64cs 100 non-null float64bb 100 non-null int64so 100 non-null float64ibb 100 non-null float64hbp 100 non-null float64sh 100 non-null float64sf 100 non-null float64gidp 100 non-null float64dtypes: float64(9), int64(11), object(3)memory usage: 18.0+ KB
However, usingto_string will return a string representation of theDataFrame in tabular form, though it won’t always fit the console width:
In [104]:print(baseball.iloc[-20:,:12].to_string()) id player year stint team lg g ab r h X2b X3b80 89474 finlest01 2007 1 COL NL 43 94 9 17 3 081 89480 embreal01 2007 1 OAK AL 4 0 0 0 0 082 89481 edmonji01 2007 1 SLN NL 117 365 39 92 15 283 89482 easleda01 2007 1 NYN NL 76 193 24 54 6 084 89489 delgaca01 2007 1 NYN NL 139 538 71 139 30 085 89493 cormirh01 2007 1 CIN NL 6 0 0 0 0 086 89494 coninje01 2007 2 NYN NL 21 41 2 8 2 087 89495 coninje01 2007 1 CIN NL 80 215 23 57 11 188 89497 clemero02 2007 1 NYA AL 2 2 0 1 0 089 89498 claytro01 2007 2 BOS AL 8 6 1 0 0 090 89499 claytro01 2007 1 TOR AL 69 189 23 48 14 091 89501 cirilje01 2007 2 ARI NL 28 40 6 8 4 092 89502 cirilje01 2007 1 MIN AL 50 153 18 40 9 293 89521 bondsba01 2007 1 SFN NL 126 340 75 94 14 094 89523 biggicr01 2007 1 HOU NL 141 517 68 130 31 395 89525 benitar01 2007 2 FLO NL 34 0 0 0 0 096 89526 benitar01 2007 1 SFN NL 19 0 0 0 0 097 89530 ausmubr01 2007 1 HOU NL 117 349 38 82 16 398 89533 aloumo01 2007 1 NYN NL 87 328 51 112 19 199 89534 alomasa02 2007 1 NYN NL 8 22 1 3 1 0
New since 0.10.0, wide DataFrames will now be printed across multiple rows bydefault:
In [105]:pd.DataFrame(np.random.randn(3,12))Out[105]: 0 1 2 3 4 5 6 \0 2.173014 1.273573 0.888325 0.631774 0.206584 -1.745845 -0.5053101 -1.240418 2.177280 -0.082206 0.827373 -0.700792 0.524540 -1.1013962 0.269598 -0.453050 -1.821539 -0.126332 -0.153257 0.405483 -0.504557 7 8 9 10 110 1.376623 0.741168 -0.509153 -2.012112 -1.2044181 1.115750 0.294139 0.286939 1.709761 -0.2125962 1.405148 0.778061 -0.799024 -0.670727 0.086877
You can change how much to print on a single row by setting thedisplay.widthoption:
In [106]:pd.set_option('display.width',40)# default is 80In [107]:pd.DataFrame(np.random.randn(3,12))Out[107]: 0 1 2 \0 1.179465 0.777427 -1.9234601 0.054928 0.776156 0.3720602 -0.243404 -1.506557 -1.977226 3 4 5 \0 0.782432 0.203446 0.2506521 0.710963 -0.784859 0.1684052 -0.226582 -0.777971 0.231309 6 7 8 \0 -2.349580 -0.540814 -0.7489391 0.159230 0.866492 1.2660252 1.394479 0.723474 -0.097256 9 10 110 -0.994345 1.478624 -0.3419911 0.555240 0.731803 0.2193832 0.375274 -0.314401 -2.363136
You can adjust the max width of the individual columns by settingdisplay.max_colwidth
In [108]:datafile={'filename':['filename_01','filename_02'], .....:'path':["media/user_name/storage/folder_01/filename_01", .....:"media/user_name/storage/folder_02/filename_02"]} .....:In [109]:pd.set_option('display.max_colwidth',30)In [110]:pd.DataFrame(datafile)Out[110]: filename \0 filename_011 filename_02 path0 media/user_name/storage/fo...1 media/user_name/storage/fo...In [111]:pd.set_option('display.max_colwidth',100)In [112]:pd.DataFrame(datafile)Out[112]: filename \0 filename_011 filename_02 path0 media/user_name/storage/folder_01/filename_011 media/user_name/storage/folder_02/filename_02
You can also disable this feature via theexpand_frame_repr option.This will print the table in one block.
DataFrame column attribute access and IPython completion¶
If a DataFrame column label is a valid Python variable name, the column can beaccessed like attributes:
In [113]:df=pd.DataFrame({'foo1':np.random.randn(5), .....:'foo2':np.random.randn(5)}) .....:In [114]:dfOut[114]: foo1 foo20 -0.412237 0.2132321 -0.237644 1.7401392 1.272869 -0.2414913 1.220450 -0.8685144 1.315172 0.407544In [115]:df.foo1Out[115]:0 -0.4122371 -0.2376442 1.2728693 1.2204504 1.315172Name: foo1, dtype: float64
The columns are also connected to theIPythoncompletion mechanism so they can be tab-completed:
In [5]:df.fo<TAB>df.foo1 df.foo2
Panel¶
Panel is a somewhat less-used, but still important container for 3-dimensionaldata. The termpanel data isderived from econometrics and is partially responsible for the name pandas:pan(el)-da(ta)-s. The names for the 3 axes are intended to give some semanticmeaning to describing operations involving panel data and, in particular,econometric analysis of panel data. However, for the strict purposes of slicingand dicing a collection of DataFrame objects, you may find the axis namesslightly arbitrary:
- items: axis 0, each item corresponds to a DataFrame contained inside
- major_axis: axis 1, it is theindex (rows) of each of theDataFrames
- minor_axis: axis 2, it is thecolumns of each of the DataFrames
Construction of Panels works about like you would expect:
From 3D ndarray with optional axis labels¶
In [116]: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']) .....:In [117]:wpOut[117]:<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
From dict of DataFrame objects¶
In [118]:data={'Item1':pd.DataFrame(np.random.randn(4,3)), .....:'Item2':pd.DataFrame(np.random.randn(4,2))} .....:In [119]:pd.Panel(data)Out[119]:<class 'pandas.core.panel.Panel'>Dimensions: 2 (items) x 4 (major_axis) x 3 (minor_axis)Items axis: Item1 to Item2Major_axis axis: 0 to 3Minor_axis axis: 0 to 2
Note that the values in the dict need only beconvertible toDataFrame. Thus, they can be any of the other valid inputs to DataFrame asper above.
One helpful factory method isPanel.from_dict, which takes adictionary of DataFrames as above, and the following named parameters:
| Parameter | Default | Description |
|---|---|---|
| intersect | False | drops elements whose indices do not align |
| orient | items | useminor to use DataFrames’ columns as panel items |
For example, compare to the construction above:
In [120]:pd.Panel.from_dict(data,orient='minor')Out[120]:<class 'pandas.core.panel.Panel'>Dimensions: 3 (items) x 4 (major_axis) x 2 (minor_axis)Items axis: 0 to 2Major_axis axis: 0 to 3Minor_axis axis: Item1 to Item2
Orient is especially useful for mixed-type DataFrames. If you pass a dict ofDataFrame objects with mixed-type columns, all of the data will get upcasted todtype=object unless you passorient='minor':
In [121]:df=pd.DataFrame({'a':['foo','bar','baz'], .....:'b':np.random.randn(3)}) .....:In [122]:dfOut[122]: a b0 foo -1.1428631 bar -1.0153212 baz 0.683625In [123]:data={'item1':df,'item2':df}In [124]:panel=pd.Panel.from_dict(data,orient='minor')In [125]:panel['a']Out[125]: item1 item20 foo foo1 bar bar2 baz bazIn [126]:panel['b']Out[126]: item1 item20 -1.142863 -1.1428631 -1.015321 -1.0153212 0.683625 0.683625In [127]:panel['b'].dtypesOut[127]:item1 float64item2 float64dtype: object
Note
Unfortunately Panel, being less commonly used than Series and DataFrame,has been slightly neglected feature-wise. A number of methods and optionsavailable in DataFrame are not available in Panel. This will get workedon, of course, in future releases. And faster if you join me in working onthe codebase.
From DataFrame usingto_panel method¶
This method was introduced in v0.7 to replaceLongPanel.to_long, and convertsa DataFrame with a two-level index to a Panel.
In [128]:midx=pd.MultiIndex(levels=[['one','two'],['x','y']],labels=[[1,1,0,0],[1,0,1,0]])In [129]:df=pd.DataFrame({'A':[1,2,3,4],'B':[5,6,7,8]},index=midx)In [130]:df.to_panel()Out[130]:<class 'pandas.core.panel.Panel'>Dimensions: 2 (items) x 2 (major_axis) x 2 (minor_axis)Items axis: A to BMajor_axis axis: one to twoMinor_axis axis: x to y
Item selection / addition / deletion¶
Similar to DataFrame functioning as a dict of Series, Panel is like a dictof DataFrames:
In [131]:wp['Item1']Out[131]: A B C D2000-01-01 -0.729430 0.427693 -0.121325 -0.7364182000-01-02 0.739037 -0.648805 -0.383057 0.3850272000-01-03 2.321064 -1.290881 0.105458 -1.0970352000-01-04 0.158759 -1.261191 -0.081710 1.3905062000-01-05 -1.962031 -0.505580 0.021253 -0.317071In [132]:wp['Item3']=wp['Item1']/wp['Item2']
The API for insertion and deletion is the same as for DataFrame. And as withDataFrame, if the item is a valid python identifier, you can access it as anattribute and tab-complete it in IPython.
Transposing¶
A Panel can be rearranged using itstranspose method (which does not make acopy by default unless the data are heterogeneous):
In [133]:wp.transpose(2,0,1)Out[133]:<class 'pandas.core.panel.Panel'>Dimensions: 4 (items) x 3 (major_axis) x 5 (minor_axis)Items axis: A to DMajor_axis axis: Item1 to Item3Minor_axis axis: 2000-01-01 00:00:00 to 2000-01-05 00:00:00
Indexing / Selection¶
| Operation | Syntax | Result |
|---|---|---|
| Select item | wp[item] | DataFrame |
| Get slice at major_axis label | wp.major_xs(val) | DataFrame |
| Get slice at minor_axis label | wp.minor_xs(val) | DataFrame |
For example, using the earlier example data, we could do:
In [134]:wp['Item1']Out[134]: A B C D2000-01-01 -0.729430 0.427693 -0.121325 -0.7364182000-01-02 0.739037 -0.648805 -0.383057 0.3850272000-01-03 2.321064 -1.290881 0.105458 -1.0970352000-01-04 0.158759 -1.261191 -0.081710 1.3905062000-01-05 -1.962031 -0.505580 0.021253 -0.317071In [135]:wp.major_xs(wp.major_axis[2])Out[135]: Item1 Item2 Item3A 2.321064 -0.538606 -4.309389B -1.290881 0.791512 -1.630905C 0.105458 -0.020302 -5.194337D -1.097035 0.184430 -5.948253In [136]:wp.minor_axisOut[136]:Index([u'A',u'B',u'C',u'D'],dtype='object')In [137]:wp.minor_xs('C')Out[137]: Item1 Item2 Item32000-01-01 -0.121325 1.413524 -0.0858322000-01-02 -0.383057 1.243178 -0.3081272000-01-03 0.105458 -0.020302 -5.1943372000-01-04 -0.081710 -1.811565 0.0451052000-01-05 0.021253 -1.040542 -0.020425
Squeezing¶
Another way to change the dimensionality of an object is tosqueeze a 1-len object, similar towp['Item1']
In [138]:wp.reindex(items=['Item1']).squeeze()Out[138]: A B C D2000-01-01 -0.729430 0.427693 -0.121325 -0.7364182000-01-02 0.739037 -0.648805 -0.383057 0.3850272000-01-03 2.321064 -1.290881 0.105458 -1.0970352000-01-04 0.158759 -1.261191 -0.081710 1.3905062000-01-05 -1.962031 -0.505580 0.021253 -0.317071In [139]:wp.reindex(items=['Item1'],minor=['B']).squeeze()Out[139]:2000-01-01 0.4276932000-01-02 -0.6488052000-01-03 -1.2908812000-01-04 -1.2611912000-01-05 -0.505580Freq: D, Name: B, dtype: float64
Conversion to DataFrame¶
A Panel can be represented in 2D form as a hierarchically indexedDataFrame. See the sectionhierarchical indexingfor more on this. To convert a Panel to a DataFrame, use theto_framemethod:
In [140]:panel=pd.Panel(np.random.randn(3,5,4),items=['one','two','three'], .....:major_axis=pd.date_range('1/1/2000',periods=5), .....:minor_axis=['a','b','c','d']) .....:In [141]:panel.to_frame()Out[141]: one two threemajor minor2000-01-01 a -1.876826 -0.383171 -0.117339 b -1.873827 -0.172217 0.780048 c -0.251457 -1.674685 2.162047 d 0.027599 0.762474 0.8742332000-01-02 a 1.235291 0.481666 -0.764147 b 0.850574 1.217546 -0.484495 c -1.140302 0.577103 0.298570 d 2.149143 -0.076021 0.8251362000-01-03 a 0.504452 0.720235 -0.388020 b 0.678026 0.202660 -0.339279 c -0.628443 -0.314950 0.141164 d 1.191156 -0.410852 0.5659302000-01-04 a -1.145363 0.542758 -1.749969 b -0.523153 1.955407 -1.402941 c -1.299878 -0.940645 0.623222 d -0.110240 0.076257 0.0201292000-01-05 a -0.333712 -0.897159 -2.858463 b 0.416876 -1.265679 0.885765 c -0.436400 -0.528311 0.158014 d 0.999768 -0.660014 -1.981797
Panel4D and PanelND (Deprecated)¶
Warning
In 0.19.0Panel4D andPanelND are deprecated and will be removed ina future version. The recommended way to represent these types ofn-dimensional data are with thexarray package.Pandas provides ato_xarray() method to automatethis conversion.
See thedocs of a previous versionfor documentation on these objects.