[Python] Road to a snake charmer (6) Manipulate Pandas

This is a training to become a data scientist using Python. Finally, let's do one of the three sacred treasures, Pandas.

Pandas is Python's most powerful data analysis package. It's very convenient because you can do anything you can do with Excel. (You can do things that Excel can't do) It goes well with numpy and matplotlib and can be used seamlessly.

I think it's best to use the IPython Notebook described below to run Python programs using Pandas. ** PREV ** → [Python] Road to snake charmer (5) Play with Matplotlib ** NEXT ** → Pandas application (as soon as possible)

It visualizes DataFrames and graphs beautifully like this. amazing.

Pandas data structure

Pandas has three data structures.

--One-dimensional array: Series --Two-dimensional array: DataFrame --Three-dimensional array: Panel

Let's generate these from a Numpy array.

Series example (1D)

Python

import numpy as np
import pandas as pd

Python

# Series
a1 = np.arange(2)
idx = pd.Index(['A', 'B'], name = 'index')
series = pd.Series(a1, index=idx)
series

index
A    0
B    1
dtype: int64

DataFrame example (2D)

Python

# DataFrame
a2 = np.arange(4).reshape(2, 2)
col = pd.Index(['a', 'b'], name= 'column')
df = pd.DataFrame(a2, index=idx, columns=col)
df

Panel example (3D)

Python

a3 = np.arange(8).reshape(2,2,2)
itm = pd.Index(['p', 'q'], name='item')
panel = pd.Panel(a3, items=itm, major_axis=idx, minor_axis=col)
panel['p']

Python

panel['q']

Example of Series with double index (effectively 2D)

Python

a1=np.arange(4)
idx = pd.MultiIndex.from_product([['A','B'],['a','b']], names=('i','j'))
series2 = pd.Series(a1, index=idx)
series2

#From even if the axis multiplicity is 3 or more_product can be used.
# pd.MultiIndex.from_product([['A', 'B'],['a', 'b'],['1', '2']], names=('i', 'j', 'k'))

i  j
A  a    0
   b    1
B  a    2
   b    3
dtype: int64

Example of DataFrame with double index (effectively 4D)

Python

a2 = np.arange(16) .reshape(4,4)
idx = pd.MultiIndex.from_product( [['A','B'],['a','b']], names=('i','j'))
col = pd.MultiIndex.from_product( [['C','D'],['c','d']], names=('k','l'))
df = pd.DataFrame(a2, index=idx, columns=col)
df

Although omitted this time, Panel can also have multiple indexes in the same way.

How to access data (Indexing)

The big difference between Pandas and Numpy is that Numpy's index concept is more sophisticated in Pandas. Let's compare the 2D array from earlier with Numpy and Pandas DataFrame.

Python

a2 = np.arange(4).reshape(2, 2)
a2

array([[0, 1],
       [2, 3]])

Python

idx = pd.Index(['A', 'B'], name='index')
col = pd.Index(['a', 'b'], name='column')
df = pd.DataFrame(a2, index=idx, columns=col)
df

The rows are labeled A and B, and the columns are labeled a and b, respectively, which Pandas can use as an index. This is called the ** label-based index **. On the other hand, the 0-based integer index used in Numpy is called the ** position-based index **. Both are available in Pandas.

Python

a2[1, 1]

3

Python

df.ix[1, 1]

3

Python

df.ix['B', 'b']

3

Since ** label-based index ** is the key of dict and ** position-based index ** is the index of list, you can think of Pandas as having both dict and list properties. I can do it. So, of course, slice, fancy indexing, and boolean indexing are possible as well as Numpy.

slice

Python

df = pd.DataFrame( np.arange(16).reshape(4, 4), index=list('ABCD'),columns=list('abcd'))
df

Python

#First line and above,Less than 3rd line
df.ix[1:3]

Python

#Line A and above,Line C and below
# label-For based index, not less than
df.ix['A' : 'C']

fancy indexing

Python

#Specify multiple lines
df.ix[['A', 'B', 'D']]

Python

#Specify multiple columns
df[[ 'b', 'd']]

boolean indexing

Python

#See line that is True
df.ix[[True, False, True]]

Python

#See column that is True
df.ix[:,[True, False, True]]

Python

#See a row where twice the a value is greater than the c value
df.ix[df['a']*2 > df['c']]

Axis and index manipulation

Introducing axis swapping, moving, index renaming, index sorting, etc. that are important for using Pandas.

Swap axes

Python

a2 = np.arange(4) .reshape(2, 2)
idx = pd.Index(['A', 'B'], name='index')
col = pd.Index(['a', 'b'], name='column')
df = pd.DataFrame(a2, index=idx,columns=col)
df

Python

#Axis 0(line)And the first axis(Column)To replace
df.swapaxes(0, 1)

Python

#Transpose in 2D(T)Even if it is the same
df.T # transpose()Abbreviation for

Axis movement (stack / unstack)

Python

#Column moved to row side
df.stack()

index  column
A      a         0
       b         1
B      a         2
       b         3
dtype: int64

Python

#Row moves to column side
df.unstack()

column  index
a       A        0
        B        2
b       A        1
        B        3
dtype: int64

Python

#stack()And unstack()Is the reverse operation, so if you repeat these two operations, it will return to the original
df.stack().unstack()

When the row moves to the column side, the column is represented by a double index. If you stack () or unstack () a (non-multiplexed) DataFrame, the output will be Series.

Swap axes

Python

a2 = np.arange(64).reshape(8,8)
idx = pd.MultiIndex.from_product( [['A','B'],['C','D'],['E','F']],names=list('ijk'))
col = pd.MultiIndex.from_product([['a','b'],['c','d'],['e','f']],names=list('xyz'))
df = pd.DataFrame(a2, index=idx,columns=col)
df

This is an example where each axis has 3 layers (3 layers), but swapaxes () replaces all layers entirely.

Python

df.swapaxes(0,1)

Swapping multiple axes (swaplevel / reorder_levels)

Python

#Axis 0(line)Of the 0th layer(i)And the second level(z)Swap
df.reorder_levels([2, 1, 0])
# swaplevel(0, 2)Or swaplevel('i', 'z')But the same

Python

#1st axis(Column)Of the 0th layer(x)And the first level(y)Swap
df.reorder_levels([1,0,2],axis=1)
# swaplevel(0, 1, axis=1)Or swaplevel('i', 'j', axis=1)But the same

Multi-axis movement (stack / unstack)

stack () moves the bottom level axis of the column axis to the bottom level axis of the row axis.

Python

df.stack()

unstack () moves the bottom level axis of the row axis to the bottom level axis of the column axis.

Python

df.unstack()

index rename

Python

df = pd.DataFrame( [[90, 50], [60, 80]], index=['t', 'h'],columns=['m', 'e'])
df

Python

df.index.name='name'
df.columns.name='Subject'
df.rename(index=dict(t='Taro', h='Hanako'), columns=dict(m='Math', e='English'))

Sort by index

Python

df = pd.DataFrame (np.arange(9).reshape(3,3), index=['B','A','C'], columns=['c','b','a'])
df

Python

#Sort rows by row index
df.sort_index(axis=0)

Python

#Sort columns by column index
df.sort_index(axis=0).sort_index(axis=1)

Data conversion

Series data conversion

Python

series=pd.Series([2, 3], index=list('ab'))
series

a    2
b    3
dtype: int64

Python

#Square each value in Series
series ** 2

a    4
b    9
dtype: int64

Python

#You can also pass functions and dicts on the map
series.map(lambda x: x**2)
# series.map( {x:x**2 for x in range(3) })

a    4
b    9
dtype: int64

DataFrame data conversion

Python

df = pd.DataFrame( [[2, 3], [4, 5]], index=list('AB'),columns=list('ab'))
df

Python

#Similar to Series
df ** 2
# df.map(lambda x: x**2)

Python

#function(Series to scalar)Apply to each column
#One dimension down, the result is Series
df.apply(lambda c: c['A']*c['B'], axis=0)

a     8
b    15
dtype: int64

Python

#function(Series to Series)Apply to each line
#The result is a DataFrame
df.apply(lambda r: pd.Series(dict(a=r['a']+r['b'], b=r['a']*r['b'])), axis=1)

Concat and merge

Pandas allows you to concatenate and combine multiple Series and DataFrames.

Series concat (concat)

Even if there are duplicate indexes, they will be combined as they are.

Python

ser1=pd.Series([1,2], index=list('ab'))
ser2=pd.Series([3,4], index=list('bc'))
pd.concat([ser1, ser2])

a    1
b    2
b    3
c    4
dtype: int64

Python

#When making the index unique, remove duplicate inex
dif_idx = ser2.index.difference(ser1.index)
pd.concat([ser1, ser2[list(dif_idx)]])

a    1
b    2
c    4
dtype: int64

DataFrame concat (concat)

Python

df1 = pd.DataFrame([[1, 2], [3, 4]], index=list('AB'),columns=list('ab'))
df2 = pd.DataFrame([[5, 6], [7, 8]], index=list('CD'),columns=list('ab'))
df3 = pd.DataFrame([[5, 6], [7, 8]], index=list('AB'),columns=list('cd'))

Python

df1

Python

df2

Python

df3

Python

#0th axis(line)Stack in the direction
pd.concat([df1, df2], axis=0)

Python

#1st axis(Column)Stack in the direction
pd.concat([df1, df3], axis=1)

DataFrame merge

Python

df1.index.name = df3.index.name = 'A'
df10 = df1.reset_index()
df30 = df3.reset_index()

Python

df10

Python

df30

Python

#Join in column A
pd.merge(df10, df30, on='A')

On is given the column name to use for the join. Multiple specifications can be specified, in which case it is given as a list. If omitted, the common column name of the two DataFrames is adopted. In the above example, the common column name is only A, so it can be omitted. Note that in merge, the index column is ignored.

Input / output of various file formats

Pandas can input and output various formats.

Python

a2 = np.arange(16) .reshape(4,4)
idx = pd.MultiIndex.from_product( [['A','B'],['a','b']], names=('i','j'))
col = pd.MultiIndex.from_product( [['C','D'],['c','d']], names=('k','l'))
df = pd.DataFrame(a2, index=idx, columns=col)
df

Export file

Python

#Output to HTML file
df.to_html('a2.html')

Python

#Output to excel file(Requires openpyxl)
df.to_excel('a2.xlsx')

Read file

Python

xl = pd.ExcelFile('test.xlsx')
#Specifying sheet
df = xl.parse('Sheet1')
df

Creating a graph

Pandas works great with Matplotlib and makes it easy to create graphs from DataFrames.

Python

%matplotlib inline

x = np.linspace(0, 2*np.pi, 10)
df = pd.DataFrame(dict(sin=np.sin(x), cos=np.cos(x)), index=x)
df

Python

#Graph output
df.plot()