how to re-arrange multiple columns into one column with same index

debugcn Published at Dev

Jay

I'm using python pandas and I want to adjust one same index to multiple columns and make it into one column. And when it's possible, I also want to delete the zero value.

I have this data frame

index A B C 
a     8 0 1 
b     2 3 0
c     0 4 0 
d     3 2 7

I'd like my output to look like this

index data value
a     A    8 
b     A    2
d     A    3
b     B    3
c     B    4
d     B    2
a     C    1
d     C    7

===
I solved this task as below. My original data has 2 indexes & 0 in dataframe were NaN values.

At first, I tried to apply melt function while removing NaN values following this (How to melt a dataframe in Pandas with the option for removing NA values), but I couldn't. Because my original data has several columns ('value_vars'). so I re-organized dataframe by 2 steps:

Firstly, I made multi-column into one-column by melt function,
Then removed NaN values in each rows by dropna function.

jdg

This looks a little like the melt function in pandas, with the only difference being the index.

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.melt.html

Here is some code you can run to test:

import pandas as pd
df = pd.DataFrame({'A': {0: 'a', 1: 'b', 2: 'c'},'B': {0: 1, 1: 3, 2: 5},'C': {0: 2, 1: 4, 2: 6}})
pd.melt(df)

With a little manipulation, you could solve for the indexing issue.

This is not particularly pythonic, but if you have a limited number of columns, you could make due with:

molten = pd.melt(df)
a = molten.merge(df, left_on='value', right_on = 'A') 
b = molten.merge(df, left_on='value', right_on = 'B') 
c = molten.merge(df, left_on='value', right_on = 'C') 
merge = pd.concat([a,b,c])