Expanding column values as key and values in DataFrame

Question

I have a DataFrame like:

df = pd.DataFrame({'A': ['a', 'b', 'c'], 'B':['d', 'e', 'f'], 'C':[1,2,3], 'D':[4,5,6]})

A   B   C   D
a   d   1   4
b   e   2   5
c   f   3   6

I have to expand columns C and D treating A and B as keys. The output should look like:

A   B   key val
a   d   C   1
a   d   D   4
b   e   C   2
b   e   D   5
c   f   C   3
c   f   D   6

I have coded this as:

df_new = pd.DataFrame()
list_to_expand = ['C', 'D']
for index, row in df.iterrows():
    for l in list_to_expand:
        df_new = df_new.append(
             pd.DataFrame({'A': row['A'],'B': row['B'], 'key': l, 'val': row[l]},
             index=[0]))

I need to optimize my code in a vectorized format but couldn't find any function. Please note that the list of columns can increase i.e. ['C', 'D', 'E'...]. I am using python3 and pandas.

try pandas melt: df.melt(id_vars=['A','B']).rename({'variable':'key','value':'val'},axis=1). pandas melt transform a 'wide' dataframe to a 'long' form — sammywemmy, Feb 20 '20 at 10:57

score 2 · Accepted Answer · answered Feb 20 '20 at 10:57

2

You want DataFrame.melt:

df.melt(id_vars=['A', 'B'], var_name='key', value_name='val')

   A  B key  val
0  a  d   C    1
1  b  e   C    2
2  c  f   C    3
3  a  d   D    4
4  b  e   D    5
5  c  f   D    6

answered Feb 20 '20 at 10:57

yatu

86,083
12
84
139

1

love it. very succinct. – sammywemmy Feb 20 '20 at 11:11

Expanding column values as key and values in DataFrame

1 Answers1