Indexing (and MultiIndex) · Geoff Ruddock

import numpy as np
import pandas as pd

Package    Version
---------  ---------
python     3.8.8
numpy      1.20.1
pandas     1.3.3

Selecting

Select only datetime columns

dates = pd.date_range('2010-01-01', '2019-12-31')
dates

df = pd.DataFrame({
    'user_id': range(6),
    'name': ['Alice', 'Bob', 'Charles', 'Denise', 'Eric', 'Fiona'],
    'age': np.random.randint(20, 80, size=6),
    'location': ['New York', 'Toronto', 'Chicago', 'Berlin', 'Paris', 'Singapore'],
    'registration_date': np.random.choice(dates, size=6),
    'purchase_date': np.random.choice(dates, size=6),
    'cancel_date': np.random.choice(dates, size=6)
}).set_index('user_id')


df.select_dtypes(include=['datetime64[ns]'])

	registration_date	purchase_date	cancel_date
user_id
0	2019-06-10	2017-02-16	2014-06-16
1	2011-10-12	2012-08-13	2014-01-26
2	2016-08-26	2015-08-25	2012-12-17
3	2018-11-07	2010-03-09	2010-05-16
4	2019-04-19	2014-09-16	2018-04-19
5	2019-01-02	2013-02-05	2014-03-09

Select only columns with non-zero values

df = pd.DataFrame(np.random.randint(1, 100, size=(10, 5)))
df.iloc[:, [2, 4]] = 0
df

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52

df.loc[:, (df != 0).any(axis=0)]

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52

Select using combination of loc and iloc

df.iloc[5:, df.columns.get_indexer([0, 2])]

	0	2
5	16	0
6	8	0
7	23	0
8	48	0
9	49	0

Modifying

Cast index as datetime

df_with_string_index = pd.DataFrame({
    'A': [1, 2, 3],
    'B': [4, 5, 6]
}, index=['2020-01-01', '2020-01-02', '2020-01-03'])

df_with_string_index

	A	B
2020-01-01	1	4
2020-01-02	2	5
2020-01-03	3	6

df_with_string_index.index

Index(['2020-01-01', '2020-01-02', '2020-01-03'], dtype='object')

df_with_dt_index = df_with_string_index.pipe(lambda x: x.set_index(pd.to_datetime(x.index)))
df_with_dt_index

	A	B
2020-01-01	1	4
2020-01-02	2	5
2020-01-03	3	6

df_with_dt_index.index

DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03'], dtype='datetime64[ns]', freq=None)

MultiIndex

Select rows in which one level of the multiindex match a list of values

bool_filt = (df.groupby(level='user_id').size() > 1)
user_ids_to_include = bool_filt[bool_filt].index
ms_df = df[lambda x: x.index.get_level_values('user_id').isin(user_ids_to_include)]

Convert all levels of a hierarchical index to string

df.columns.set_levels([x.astype(str) for x in x.columns.levels])

E.g. including rows from users with more than one row

bool_filt = (df.groupby(level='user_id').size() > 1)
user_ids_to_include = bool_filt[bool_filt].index
multi_search_df = df[lambda x: x.index.get_level_values('user_id').isin(user_ids_to_include)]

Levels

df = pd.DataFrame(index=list('abcde'), data={'A': range(5), 'B': range(5)})
display(df)

	A	B
a	0	0
b	1	1
c	2	2
d	3	3
e	4	4

Add level above existing

insert_above = df.copy()

insert_above.columns = pd.MultiIndex.from_product([['Super'], df.columns])
insert_above

	Super
	A	B
a	0	0
b	1	1
c	2	2
d	3	3
e	4	4

Add level below existing

insert_below = df.copy()

insert_below.columns = pd.MultiIndex.from_product([df.columns, ['Sub']])
insert_below

	A	B
	Sub	Sub
a	0	0
b	1	1
c	2	2
d	3	3
e	4	4

Swap existing levels

df = pd.DataFrame(index=list('abcde'), data={'sub_level_1': range(5), 'sub_level_2': range(5)})
df.columns = pd.MultiIndex.from_product([['super_level'], df.columns])
df

	super_level
	sub_level_1	sub_level_2
a	0	0
b	1	1
c	2	2
d	3	3
e	4	4

df.swaplevel(axis=1)

	sub_level_1	sub_level_2
	super_level	super_level
a	0	0
b	1	1
c	2	2
d	3	3
e	4	4

Modify a level

date_strs = ['2020-01-01', '2020-01-02', '2020-01-03']
dims = ['A', 'B', 'C']

idx = pd.MultiIndex.from_product([date_strs, dims])
df = pd.DataFrame(index=idx, data={'A': range(len(idx)), 'B': range(len(idx))})

display(df)

		A	B
2020-01-01	A	0	0
	B	1	1
	C	2	2
2020-01-02	A	3	3
	B	4	4
	C	5	5
2020-01-03	A	6	6
	B	7	7
	C	8	8

df.index.dtypes

level_0    object
level_1    object
dtype: object

df.index.set_levels(pd.to_datetime(df.index.levels[0]), level=0)

MultiIndex([('2020-01-01', 'A'),
            ('2020-01-01', 'B'),
            ('2020-01-01', 'C'),
            ('2020-01-02', 'A'),
            ('2020-01-02', 'B'),
            ('2020-01-02', 'C'),
            ('2020-01-03', 'A'),
            ('2020-01-03', 'B'),
            ('2020-01-03', 'C')],
           )

MultiIndex to DataFrame

Create a DataFrame out of the levels of a MultiIndex.

df.index.to_frame(index=False)

	0	1
0	2020-01-01	A
1	2020-01-01	B
2	2020-01-01	C
3	2020-01-02	A
4	2020-01-02	B
5	2020-01-02	C
6	2020-01-03	A
7	2020-01-03	B
8	2020-01-03	C

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52

	0	1	3
0	5	51	77
1	17	74	1
2	89	12	19
3	82	11	81
4	65	84	59
5	16	16	83
6	8	92	54
7	23	23	47
8	48	30	83
9	49	68	52