CODEWITHSUNDEEP
pythonpandasmulti-index
Jh123
Jh123

Reputation: 93

Unnest multi-index dataframe pandas

I have multi-index dataframe in pandas with names (A,B,C..), features (F1,..FN) and dates. At every date, each of the names have a value for each of the features. Reading the data gives me the following df:

df = pd.read_csv(path,header=[0,1],index_col=0)
df =
   Name  A                                  B                        C  ...
   Ft    F1 F2 F3           ...       F_N   F1 F2 F3    ...    F_N   F1 ...
   date
   1/1   1  4  1            ...        3    3  2  2             1    5  ...
   1/2   4  3  3            ...        1    5  1  2             1    4  ...

I now want to restructure it so the names and dates appear multiple times as separate rows instead in order to reduce the amount of columns:

df =
       
   date Name    F1 F2 F3           ...       F_N   
   1/1  A       1  4  1            ...        3     
   1/1  B       3  2  2            ...        1
   ...  ...       ...              ...       ...
   1/2  A       4  3  3            ...        1     
   1/2  B       5  1  2            ...        1
   ...

I've tried different ways of unpacking & exploding but none seems to get me the results I want.

Upvotes: 1

Views: 461

Answers (1)

piterbarg
piterbarg

Reputation: 8219

This is a simple matter of using stack and then, optionally, reset_index if you do not like the multi-index:

df.stack(level=0).reset_index()

output:

    level_0    Name      F1    F2    F3
--  ---------  ------  ----  ----  ----
 0  1/1        A          1     4     1
 1  1/1        B          3     2     2
 2  1/2        A          4     3     3
 3  1/2        B          5     1     2

Upvotes: 1

Related Questions