CODEWITHSUNDEEP
pythonpandasdataframepython-2.7
fizgi
fizgi

Reputation: 48

Creating a new dataframe from an existing dataframe

I have a big dataframe as same structure as follows:

"date" "key0" "key1" "key2" "key3" "key4" ...
 2010   val0   val1   val2   val3   val4  ...
 2011   val5   val6   val7   val8   val9  ...
 ....   ....   ....   ....   ....   ....

What I want to achieve is to get each date-key-value group and create a new dataframe as:

"date"  "key"  "val"
 2010    key0   val0
 2010    key1   val1
 2010    key2   val2
 2010    key3   val3
 2010    key4   val4
 2011    key0   val5
 2011    key1   val6
 2011    key2   val7
 2011    key3   val8
 2011    key4   val9
 ....    ....   ....

Is there a way that I can get this kind of a dataframe other than doing it with a for loop manually?

Upvotes: 0

Views: 88

Answers (2)

SeaBean
SeaBean

Reputation: 23227

You can use .melt() as follows:

(df.melt('date', var_name='key', value_name='val')
   .sort_values('date')
).reset_index(drop=True)

Result:

   date   key   val
0  2010  key0  val0
1  2010  key1  val1
2  2010  key2  val2
3  2010  key3  val3
4  2010  key4  val4
5  2011  key0  val5
6  2011  key1  val6
7  2011  key2  val7
8  2011  key3  val8
9  2011  key4  val9

Upvotes: 1

Corralien
Corralien

Reputation: 120559

Mainly, use stack as suggested by @NickODell to accomplish the transformation:

>>> df.set_index('date') \
      .rename_axis(columns='key') \
      .stack() \
      .rename('val') \
      .reset_index()

   date   key   val
0  2010  key0  val0
1  2010  key1  val1
2  2010  key2  val2
3  2010  key3  val3
4  2010  key4  val4
5  2011  key0  val5
6  2011  key1  val6
7  2011  key2  val7
8  2011  key3  val8
9  2011  key4  val9

Upvotes: 1

Related Questions