Pandas: Flipping df-wide-values and index?

user_19500588 0
Viewed 30

I'm trying to flip values and index in this manner:

df = pd.DataFrame({"day1":{"JOHN":"A","JANE":"B","JILL":"C"},"day2":{"JOHN":"A","JANE":"C","JILL":np.nan},"day3":{"JOHN":"C","JANE":"A","JILL":"B"}})
df

     day1   day2    day3
JOHN    A      A       C
JANE    B      C       A
JILL    C    NaN       B

looking for:

     day1    day2    day3
A    JOHN    JOHN    JANE
B    JANE    NaN     JILL
C    JILL    JANE    JOHN

I'm having trouble conceptualizing how to tackle that. My mind jumps to pivoting somehow, but I'm not getting anywhere.

Thanks

pandas dataframe pivot python
1 Answers

Use DataFrame.stack with convert first level of MultiIndex to column and column 0 append to Index, so possible use Series.unstack with transpose:

df = df.stack().reset_index(level=0).set_index(0, append=True)['level_0'].unstack().T
print (df)
   day1  day2  day3
0                  
A  JOHN  JOHN  JANE
B  JANE   NaN  JILL
C  JILL  JANE  JOHN

Or use DataFrame.melt with convert index to column and then remove missing values with DataFrame.pivot:

df = df.reset_index().melt('index').dropna().pivot('value','variable','index')
print (df)
variable  day1  day2  day3
value                     
A         JOHN  JOHN  JANE
B         JANE   NaN  JILL
C         JILL  JANE  JOHN
user_29010020
Related