how to get tuple index values in data frame in python?

Question

The data frame is below.

 | ID | FIRST_NAME| LAST_NAME| MOBILE_NUMBER | DIRECT_NUMBER|
 | ---|-----------------------------------------------------|
 | 1  |  Richard | dietzen  | +18708007709  | not available |
 | 2  |  William |macdonald | not available | +15611784776  |
 | 3  |  Richard | Dietzen  | +18708007709  | not available |
 | 4  |  dale    | Sowders  | +16162900340  | not available |
 | 5  |  dale    | Sowders  | +18708007709  | not available |

the tuple of index of data frame:

|(1, 3)|
|(4, 5)|

expected data frame;

 | ID_1 | FIRST_NAME_1 |...... | DIRECT_NUMBER_1| ID_2 | FIRST_NAME_2|......| DIRECT_NUMBER_2|
 | ---  |------------------------------------------------------------------------------------|
 | 1    |  richard     | ......| not available  |  3   |   richard   |......| not available  |
 | 4    |  dale        | ......| not available  |  5   |   dale      |......| not available  |

the output should be a data frame like above data frame, it should have the index tuple in same row of data frame

Do you want to perform a group-by operation? Because the grouping of your indices seem to match the names and/or phone numbers of the people in the initial dataframe. — 9769953
– 9769953, Commented Jul 8, 2022 at 10:22

jezrael · Accepted Answer · 2022-07-08 10:33:14Z

Create MultiIndex.from_tuples by 3 levels by list comprehension first:

tups = [(1,3),(4,5),(3,4)]

#if necessary set ID to index
df  = df.set_index('ID')

L = [(a,i+1, x) for a, b in enumerate(tups) for i, x in enumerate(b) ]
mux = pd.MultiIndex.from_tuples(L)

Then use DataFrame.reindex with convert last level to column ID and reshape by DataFrame.unstack with sorting levels by DataFrame.sort_index, last flatten MultiIndex:

df = (df.reindex(mux, level=2)
        .reset_index(level=-1)
        .rename(columns={'level_2':'ID'})
        .unstack()
        .sort_index(axis=1, level=1, sort_remaining=False))

df.columns = df.columns.map(lambda x: f'{x[0]}_{x[1]}')
print (df)
   ID_1 FIRST_NAME_1 LAST_NAME_1 MOBILE_NUMBER_1 DIRECT_NUMBER_1  ID_2  \
0     1      Richard     dietzen    +18708007709   not available     3   
1     4         dale     Sowders    +16162900340   not available     5   
2     3      Richard     Dietzen    +18708007709   not available     4   

  FIRST_NAME_2 LAST_NAME_2 MOBILE_NUMBER_2 DIRECT_NUMBER_2  
0      Richard     Dietzen    +18708007709   not available  
1         dale     Sowders    +18708007709   not available  
2         dale     Sowders    +16162900340   not available

Collectives™ on Stack Overflow

how to get tuple index values in data frame in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related