pandas_2-level_index_iteration.py

# df : len(unique(index.id1)) 16000, len(unique(index.id1)) 67000

# loop for id2

# test 1. make sub df for id2

A = time.time()
for id2 in df.index.get_level_values('id2'):
    df_id2 = df.loc[df.index.get_level_values('id2') == id2]
    
    id1 = df_id2.index.get_level_values('area_danji_id')[0]
    value1 = df_id2['value1'].astype(int).values[0]
    value2 = df_id2['value2'].astype(float).values[0]
    
    print(id1, id2, value1, value2)
    
print("Time: ", time.time()-A)

# It takes 55.63059902191162 secs.

# test 2. using idx loop for id2

A = time.time()
for id2 in df.index.get_level_values('id2'):
    idx_id2 = df.index.get_level_values('id2') == id2
    
    id1 = df[idx_id2].index.get_level_values('id1')[0]
    value1 = df[idx_id2]['value1'].astype(int).values[0]
    value2 = df[idx_id2]['value2'].astype(float).values[0]
    
    print(id1, id2, value1, value2)
    
print("Time: ", time.time()-A)

# It takes 93.35767602920532

# test 3. make sub df for id1, id2

A = time.time()
for id1 in np.unique(df.index.get_level_values('id1')):
    df_id1 = df.loc[df.index.get_level_values('id1') == id1]
    value2 = df_id1['value2'].astype(float).values[0]
    
    for id2 in df_id1.index.get_level_values('id2'):
        df_id2 = df_id1.loc[df_id1.index.get_level_values('id2') == id2]
        value1 = df_id2['value1'].astype(int).values[0]
        
        print(id1, id2, value1, value2)
    
print("Time: ", time.time()-A)

# It takes 45.13252782821655