TAGS :Viewed: 2 - Published at: a few seconds ago

[ Iterating over multiIndex dataframe ]

I have a data frame as shown below dataframe

I have a problem in iterating over the rows. for every row fetched I want to return the key value. For example in the second row for 2016-08-31 00:00:01 entry df1 & df3 has compass value 4.0 so I wanted to return the keys which has the same compass value which is df1 & df3 in this case I Have been iterating rows using

for index,row in df.iterrows():

Answer 1


Okay so now I understand your question better this will work for you. First change the shape of your dataframe with

dfs = df.stack().swaplevel(axis=0)

This will make your dataframe look like:

enter image description here

Then you can iterate the rows like before and extract the information you want. I'm just using print statements for everything, but you can put this in some more appropriate data structure.

for index, row in dfs.iterrows():
     dup_filter = row.duplicated(keep=False)
     dfss = row_tuple[dup_filter].index.values
     print("Attribute:", index[0])
     print("Index:", index[1])
     print("Matches:", dfss, "\n")

which will print out something like


Attribute: compass
Index: 5
Matches: ['df1' 'df3']

Attribute: gyro
Index: 5
Matches: ['df1' 'df3']

Attribute: accel
Index: 6
Matches: ['df1' 'df3']


You could also do it one attribute at a time by

dfs_compass = df.stack().swaplevel(axis=0).loc['compass']

and iterate through the rows with just the index.


If I understand your question correctly, i.e. you want to return the indexes of rows which have matching values on the second level of your columns, i.e. ('compass', 'accel', 'gyro'). The following will work.

compass_match_indexes = []

for index, row in df.iterrows():
    match_filter = row[:, 'compass'].duplicated()
    if len(row[:, 'compass'][match_filter] > 0)

You can use select your dataframe with that list like df.loc[compass_match_indexes]


Another approach, you could get the transform of your DataFrame with df.T and then use the duplicated function.