Pandas Dynamic Stack

Viewed 54

Given the following data frame:

import numpy as np
import pandas as pd

df = pd.DataFrame({'foo':['a','b','c','d'],
                   'bar':['e','f','g','h'],
                       0:['i','j','k',np.nan],
                       1:['m',np.nan,'o','p']})
df=df[['foo','bar',0,1]]
df

    foo   bar    0      1
0   a     e      i      m
1   b     f      j     NaN
2   c     g      k      o
3   d     h     NaN     p

...which resulted from a previous procedure that produced columns 0 and 1 (and may have produced more or fewer columns than 0 and 1 depending on the data): I want to somehow stack (if that's the correct term) the data so that each value of 0 and 1 (ignoring NaNs) produces a new row like this:

    foo bar
0   a   e
0   a   i
0   a   m
1   b   f
1   b   j
2   c   g
2   c   k
2   c   o
3   d   h
3   d   p

You probably noticed that the common field is foo. It will likely occur that there are more common fields in my actual data set. Also, I'm not sure how important it is that the index values repeat in the end result across values of foo. As long as the data is correct, that's my main concern.

Update: What if I have 2+ common fields like this:

import numpy as np
import pandas as pd

df = pd.DataFrame({'foo':['a','a','b','b'],
                   'foo2':['a2','b2','c2','d2'],
                   'bar':['e','f','g','h'],
                       0:['i','j','k',np.nan],
                       1:['m',np.nan,'o','p']})
df=df[['foo','foo2','bar',0,1]]
df

    foo     foo2    bar     0   1
0   a       a2      e     i     m
1   a       b2      f     j     NaN
2   b       c2      g     k     o
3   b       d2      h     NaN   p
1 Answers
Related