6.4. DataFrame Index
DataFrame.indexpd.DataFrame(index=...)Range Index
Index
Object Index
Datetime Index
Timedelta Index
Period Index
Interval Index
Categorical Index
Multi Index
6.4.1. SetUp
>>> import pandas as pd
6.4.2. Range Index
pd.DataFrame(index=range(n))- create DataFrame with range indexDefault behavior if index is not specified
>>> df = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot', 'age': 30},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'age': 31},
... {'firstname': 'Carol', 'lastname': 'Corn', 'age': 32},
... ])
>>>
>>> df
firstname lastname age
0 Alice Apricot 30
1 Bob Blackthorn 31
2 Carol Corn 32
>>>
>>> df.index
RangeIndex(start=0, stop=3, step=1)
6.4.3. Integer Index
df = pd.DataFrame(index=[-1, 0, 1])- create DataFrame withint64index
>>> df = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot', 'age': 30},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'age': 31},
... {'firstname': 'Carol', 'lastname': 'Corn', 'age': 32},
... ], index=[-1, 0, 1])
>>> df
firstname lastname age
-1 Alice Apricot 30
0 Bob Blackthorn 31
1 Carol Corn 32
>>> df.index
Index([-1, 0, 1], dtype='int64')
6.4.4. Str Index
df = pd.DataFrame(index=['a', 'b', 'c'])- create DataFrame withstrindex
>>> df = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot', 'age': 30},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'age': 31},
... {'firstname': 'Carol', 'lastname': 'Corn', 'age': 32},
... ], index=['a', 'b', 'c'])
>>> df
firstname lastname age
a Alice Apricot 30
b Bob Blackthorn 31
c Carol Corn 32
>>> df.index
Index(['a', 'b', 'c'], dtype='str')
6.4.5. Datetime Index
pd.date_range(start, periods, freq)- create datetime index
>>> df = pd.DataFrame([
... {'firstname': 'Alice', 'lastname': 'Apricot', 'age': 30},
... {'firstname': 'Bob', 'lastname': 'Blackthorn', 'age': 31},
... {'firstname': 'Carol', 'lastname': 'Corn', 'age': 32},
... ], index=pd.date_range('2000-01-01', periods=3))
>>> df
firstname lastname age
2000-01-01 Alice Apricot 30
2000-01-02 Bob Blackthorn 31
2000-01-03 Carol Corn 32
>>> df.index
DatetimeIndex(['2000-01-01', '2000-01-02', '2000-01-03'], dtype='datetime64[us]', freq='D')
6.4.6. Multi Index
pd.MultiIndex.from_tuples(tuples)- create multi index from tuples
>>> index = pd.MultiIndex.from_tuples([
... ('Alice', 'Apricot'),
... ('Bob', 'Blackthorn'),
... ('Carol', 'Corn'),
... ], names=['firstname', 'lastname'])
>>>
>>> df = pd.DataFrame([
... {'age': 30, 'email': 'alice@example.com'},
... {'age': 31, 'email': 'bob@example.com'},
... {'age': 32, 'email': 'carol@example.com'},
... ], index=index)
>>> df
age email
firstname lastname
Alice Apricot 30 alice@example.com
Bob Blackthorn 31 bob@example.com
Carol Corn 32 carol@example.com
>>> df.index
MultiIndex([('Alice', 'Apricot'),
( 'Bob', 'Blackthorn'),
('Carol', 'Corn')],
names=['firstname', 'lastname'])
6.4.7. Set Index
df.set_index(column)- set index to column
>>> df = pd.DataFrame([
... {'id': 1, 'firstname': 'Alice', 'lastname': 'Apricot', 'age': 30},
... {'id': 2, 'firstname': 'Bob', 'lastname': 'Blackthorn', 'age': 31},
... {'id': 3, 'firstname': 'Carol', 'lastname': 'Corn', 'age': 32},
... ])
>>> df
id firstname lastname age
0 1 Alice Apricot 30
1 2 Bob Blackthorn 31
2 3 Carol Corn 32
>>> df.set_index('id')
firstname lastname age
id
1 Alice Apricot 30
2 Bob Blackthorn 31
3 Carol Corn 32
6.4.8. Use Case - 1
>>> import pandas as pd
>>>
>>> pd.set_option('display.width', 250)
>>> pd.set_option('display.max_columns', 20)
>>> pd.set_option('display.max_rows', 30)
>>>
>>>
>>> def quantile25(column):
... return column.quantile(.25)
>>>
>>> def quantile50(column):
... return column.quantile(.50)
>>>
>>> def quantile75(column):
... return column.quantile(.75)
>>>
>>>
>>> DATA = 'https://python3.info/_static/phones-en.csv'
>>> df = pd.read_csv(DATA, parse_dates=['date'])
>>> df.drop(columns='index', inplace=True)
>>>
>>> result = df.groupby(['month','item']).agg(
... duration_count=('duration', 'count'),
... duration_sum=('duration', 'sum'),
... duration_nunique=('duration', 'nunique'),
...
... duration_mean=('duration', 'mean'),
... duration_median=('duration', 'median'),
... duration_std=('duration', 'std'),
... duration_std2=('duration', lambda column: column.std().astype(int)),
...
... duration_min=('duration', 'min'),
... duration_q25=('duration', quantile25),
... duration_q50=('duration', quantile50),
... duration_q75=('duration', quantile75),
... duration_max=('duration', 'max'),
...
... when_first=('date', 'first'),
... when_last=('date', 'last'),
... )
>>>
>>> result
duration_count duration_sum duration_nunique duration_mean duration_median duration_std duration_std2 duration_min duration_q25 duration_q50 duration_q75 duration_max when_first when_last
month item
2014-11 call 107 25547.000 76 238.757009 48.000 387.128905 387 1.000 5.500 48.000 328.000 1940.000 2014-10-15 06:58:00 2014-12-11 19:01:00
data 29 998.441 1 34.429000 34.429 0.000000 0 34.429 34.429 34.429 34.429 34.429 2014-10-15 06:58:00 2014-12-11 06:58:00
sms 94 94.000 1 1.000000 1.000 0.000000 0 1.000 1.000 1.000 1.000 1.000 2014-10-16 22:18:00 2014-11-13 22:31:00
2014-12 call 79 13561.000 61 171.658228 55.000 324.731798 324 2.000 10.500 55.000 152.000 2120.000 2014-11-14 17:24:00 2014-12-14 19:54:00
data 30 1032.870 1 34.429000 34.429 0.000000 0 34.429 34.429 34.429 34.429 34.429 2014-11-13 06:58:00 2014-12-12 06:58:00
sms 48 48.000 1 1.000000 1.000 0.000000 0 1.000 1.000 1.000 1.000 1.000 2014-11-14 17:28:00 2014-07-12 23:22:00
2015-01 call 88 17070.000 70 193.977273 55.500 300.671661 300 2.000 15.500 55.500 273.500 1859.000 2014-12-15 20:03:00 2015-01-14 20:47:00
data 31 1067.299 1 34.429000 34.429 0.000000 0 34.429 34.429 34.429 34.429 34.429 2014-12-13 06:58:00 2015-12-01 06:58:00
sms 86 86.000 1 1.000000 1.000 0.000000 0 1.000 1.000 1.000 1.000 1.000 2014-12-15 19:56:00 2015-01-14 23:36:00
2015-02 call 67 14416.000 63 215.164179 89.000 329.672914 329 1.000 30.000 89.000 241.000 1863.000 2015-01-15 10:36:00 2015-09-02 17:54:00
data 31 1067.299 1 34.429000 34.429 0.000000 0 34.429 34.429 34.429 34.429 34.429 2015-01-13 06:58:00 2015-12-02 06:58:00
sms 39 39.000 1 1.000000 1.000 0.000000 0 1.000 1.000 1.000 1.000 1.000 2015-01-15 12:23:00 2015-10-02 21:40:00
2015-03 call 47 21727.000 46 462.276596 107.000 1552.192218 1552 2.000 33.500 107.000 320.000 10528.000 2015-12-02 20:15:00 2015-04-03 12:29:00
data 29 998.441 1 34.429000 34.429 0.000000 0 34.429 34.429 34.429 34.429 34.429 2015-02-13 06:58:00 2015-03-13 06:58:00
sms 25 25.000 1 1.000000 1.000 0.000000 0 1.000 1.000 1.000 1.000 1.000 2015-02-19 18:46:00 2015-03-14 00:16:00
>>> result.loc[('2015-01','call')]
duration_count 88
duration_sum 17070.0
duration_nunique 70
duration_mean 193.977273
duration_median 55.5
duration_std 300.671661
duration_std2 300
duration_min 2.0
duration_q25 15.5
duration_q50 55.5
duration_q75 273.5
duration_max 1859.0
when_first 2014-12-15 20:03:00
when_last 2015-01-14 20:47:00
Name: (2015-01, call), dtype: object
>>> result.loc['2015-01']
duration_count duration_sum duration_nunique duration_mean duration_median duration_std duration_std2 duration_min duration_q25 duration_q50 duration_q75 duration_max when_first when_last
item
call 88 17070.000 70 193.977273 55.500 300.671661 300 2.000 15.500 55.500 273.500 1859.000 2014-12-15 20:03:00 2015-01-14 20:47:00
data 31 1067.299 1 34.429000 34.429 0.000000 0 34.429 34.429 34.429 34.429 34.429 2014-12-13 06:58:00 2015-12-01 06:58:00
sms 86 86.000 1 1.000000 1.000 0.000000 0 1.000 1.000 1.000 1.000 1.000 2014-12-15 19:56:00 2015-01-14 23:36:00
>>> result.loc['2015-01'].transpose()
item call data sms
duration_count 88 31 86
duration_sum 17070.0 1067.299 86.0
duration_nunique 70 1 1
duration_mean 193.977273 34.429 1.0
duration_median 55.5 34.429 1.0
duration_std 300.671661 0.0 0.0
duration_std2 300 0 0
duration_min 2.0 34.429 1.0
duration_q25 15.5 34.429 1.0
duration_q50 55.5 34.429 1.0
duration_q75 273.5 34.429 1.0
duration_max 1859.0 34.429 1.0
when_first 2014-12-15 20:03:00 2014-12-13 06:58:00 2014-12-15 19:56:00
when_last 2015-01-14 20:47:00 2015-12-01 06:58:00 2015-01-14 23:36:00
>>> sms = result.index.get_level_values('item') == 'sms'
>>> sms
array([False, False, True, False, False, True, False, False, True,
False, False, True, False, False, True])
>>>
>>> result[sms]
duration_count duration_sum duration_nunique duration_mean duration_median duration_std duration_std2 duration_min duration_q25 duration_q50 duration_q75 duration_max when_first when_last
month item
2014-11 sms 94 94.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-10-16 22:18:00 2014-11-13 22:31:00
2014-12 sms 48 48.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-11-14 17:28:00 2014-07-12 23:22:00
2015-01 sms 86 86.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-12-15 19:56:00 2015-01-14 23:36:00
2015-02 sms 39 39.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2015-01-15 12:23:00 2015-10-02 21:40:00
2015-03 sms 25 25.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2015-02-19 18:46:00 2015-03-14 00:16:00
Cross-section:
>>> result.xs('sms', level='item')
duration_count duration_sum duration_nunique duration_mean duration_median duration_std duration_std2 duration_min duration_q25 duration_q50 duration_q75 duration_max when_first when_last
month
2014-11 94 94.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-10-16 22:18:00 2014-11-13 22:31:00
2014-12 48 48.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-11-14 17:28:00 2014-07-12 23:22:00
2015-01 86 86.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-12-15 19:56:00 2015-01-14 23:36:00
2015-02 39 39.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2015-01-15 12:23:00 2015-10-02 21:40:00
2015-03 25 25.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2015-02-19 18:46:00 2015-03-14 00:16:00
Slicer Object:
>>> result.loc[(slice(None), 'sms'), :]
duration_count duration_sum duration_nunique duration_mean duration_median duration_std duration_std2 duration_min duration_q25 duration_q50 duration_q75 duration_max when_first when_last
month item
2014-11 sms 94 94.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-10-16 22:18:00 2014-11-13 22:31:00
2014-12 sms 48 48.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-11-14 17:28:00 2014-07-12 23:22:00
2015-01 sms 86 86.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2014-12-15 19:56:00 2015-01-14 23:36:00
2015-02 sms 39 39.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2015-01-15 12:23:00 2015-10-02 21:40:00
2015-03 sms 25 25.0 1 1.0 1.0 0.0 0 1.0 1.0 1.0 1.0 1.0 2015-02-19 18:46:00 2015-03-14 00:16:00