user1566200 user1566200 - 2 months ago 8
Python Question

Transposing a subset of columns in a Pandas DataFrame while using others as grouping variable?

Let's say I have a Pandas dataframe (that is already in the dataframe format):

x = [[1,2,8,7,9],[1,3,5.6,4.5,4],[2,3,4.5,5,5]]
df = pd.DataFrame(x, columns=['id1','id2','val1','val2','val3'])

id1 id2 val1 val2 val3
1 2 8.0 7.0 9
1 3 5.6 4.5 4
2 3 4.5 5.0 5


I want
val1
,
val2
, and
val2
in one column, with
id1
and
id2
as grouping variables. I can use this extremely convoluted code:

dfT = df.iloc[:,2::].T.reset_index(drop=True)
n_points = dfT.shape[0]
final = pd.DataFrame()
for i in range(0, df.shape[0]):
data = np.asarray([[df.ix[i,'id1']]*n_points,
[df.ix[i,'id2']]*n_points,
dfT.ix[:,i].values]).T
temp = pd.DataFrame(data, columns=['id1','id2','val'])
final = pd.concat([final, temp], axis=0)


to get my dataframe into the correct format:

id1 id2 val
0 1.0 2.0 8.0
1 1.0 2.0 7.0
2 1.0 2.0 9.0
0 1.0 3.0 5.6
1 1.0 3.0 4.5
2 1.0 3.0 4.0
0 2.0 3.0 4.5
1 2.0 3.0 5.0
2 2.0 3.0 5.0


but there must be a more efficient way of doing this, since on a large dataframe this takes way too long.

Suggestions?

Answer

You can use melt with drop column variable:

print (pd.melt(df, id_vars=['id1','id2'], value_name='val')
         .drop('variable', axis=1))

   id1  id2  val
0    1    2  8.0
1    1    3  5.6
2    2    3  4.5
3    1    2  7.0
4    1    3  4.5
5    2    3  5.0
6    1    2  9.0
7    1    3  4.0
8    2    3  5.0

Another solution with set_index and stack:

print (df.set_index(['id1','id2'])
         .stack()
         .reset_index(level=2, drop=True)
         .reset_index(name='val'))

   id1  id2  val
0    1    2  8.0
1    1    2  7.0
2    1    2  9.0
3    1    3  5.6
4    1    3  4.5
5    1    3  4.0
6    2    3  4.5
7    2    3  5.0
8    2    3  5.0