pandas convert some columns into rows

Question

So my dataset has some information by location for n dates  The problem is each date is actually a different column header  For example the CSV looks like  location    name    Jan-2010    Feb-2010    March-2010 A            test   12          20          30 B            foo    18          20          25   What I would like is for it to look like  location    name    Date        Value A            test   Jan-2010    12        A            test   Feb-2010    20 A            test   March-2010  30 B            foo    Jan-2010    18        B            foo    Feb-2010    20 B            foo    March-2010  25   problem is I don t know how many dates are in the column  though I know they will always start after name

User · Answer

Use set index with stack for MultiIndex Series  then for DataFrame add reset index with rename   df1    df set index   location    name              stack             reset index name  Value             rename columns   level 2   Date     print  df1    location  name        Date  Value 0        A  test    Jan-2010     12 1        A  test    Feb-2010     20 2        A  test  March-2010     30 3        B   foo    Jan-2010     18 4        B   foo    Feb-2010     20 5        B   foo  March-2010     25

User · Answer

pd wide to long  You can add a prefix to your year columns and then feed directly to pd wide to long  I won t pretend this is efficient  but it may in certain situations be more convenient than pd melt  e g  when your columns already have an appropriate prefix   df columns   np hstack  df columns  2   df columns 2   map lambda x  f Value x       res   pd wide to long df  stubnames   Value    i  name   j  Date   reset index             sort values   location    name     print res      name        Date location  Value 0  test    Jan-2010        A     12 2  test    Feb-2010        A     20 4  test  March-2010        A     30 1   foo    Jan-2010        B     18 3   foo    Feb-2010        B     20 5   foo  March-2010        B     25

User · Answer

UPDATE From v0 20  melt is a first order function  you can now use  df melt id vars   location    name             var name  Date            value name  Value      location    name        Date  Value 0        A   test     Jan-2010     12 1        B    foo     Jan-2010     18 2        A   test     Feb-2010     20 3        B    foo     Feb-2010     20 4        A   test   March-2010     30 5        B    foo   March-2010     25     OLD ER  VERSIONS   lt 0 20  You can use pd melt to get most of the way there  and then sort    gt  gt  gt  df   location  name  Jan-2010  Feb-2010  March-2010 0        A  test        12        20          30 1        B   foo        18        20          25  gt  gt  gt  df2   pd melt df  id vars   location    name                       var name  Date   value name  Value    gt  gt  gt  df2   location  name        Date  Value 0        A  test    Jan-2010     12 1        B   foo    Jan-2010     18 2        A  test    Feb-2010     20 3        B   foo    Feb-2010     20 4        A  test  March-2010     30 5        B   foo  March-2010     25  gt  gt  gt  df2   df2 sort   location    name     gt  gt  gt  df2   location  name        Date  Value 0        A  test    Jan-2010     12 2        A  test    Feb-2010     20 4        A  test  March-2010     30 1        B   foo    Jan-2010     18 3        B   foo    Feb-2010     20 5        B   foo  March-2010     25    Might want to throw in a  reset index drop True   just to keep the output clean    Note  pd DataFrame sort has been deprecated in favour of pd DataFrame sort values

User · Answer

I guess I found a simpler solution  temp1   pd melt df1  id vars   location    var name  Date   value name  Value   temp2   pd melt df1  id vars   name    var name  Date   value name  Value     Concat whole temp1 with temp2 s column name  temp1  new column     temp2  name     You now have what you asked for

[python] pandas convert some columns into rows

Examples related to python

Examples related to pandas