convert columns to rows based on row value using pandas

Question

I have an excel file as follows:

ID      wk48     wk49    wk50    wk51   wk52   wk1   wk2 
1123     10        22      233     2     4     22     11
1198      9         4       44    23    34     5     234
101       3         6        3    43    33     34     78

I want the output as follows in python

1123 wk48  10
1123 wk49  22
1123 wk50  233
1123 wk51  2
1123 wk52  4
1123 wk1   22
1123 wk2   11
1198 wk48  9
1198 wk49  4
1198 wk50  44
1198 wk51  23
1198 wk52  34
1198 wk1   5
1198 wk2   234

Any suggestions

You're going to get downvoted (or even have your question closed) because this doesn't meet the stack overflow guidelines: what have you tried? Please provide a sample of code showing your efforts — Julien Marrec
– Julien Marrec, Commented Dec 14, 2016 at 0:11
This would be a reasonable question if you explained exactly what you want to do, and if you made it clear what parts of your desired output are supposed to be columns, and which are supposed to be the index. — Alex
– Alex, Commented Dec 14, 2016 at 0:37
Input is (Input.xlsx) col1 col2 cole3 ID wk48 wk49 wk50 wk51 wk52 wk1 wk2 asdf 123 7ds3 1123 10 22 233 2 4 22 11 uerr 4dd 3de34 1198 9 4 44 23 34 5 234 Ouput needed is (output.csv) asdf 123 7ds3 1123 wk48 10 asdf 123 7ds3 1123 wk49 22 asdf 123 7ds3 1123 wk50 233 asdf 123 7ds3 1123 wk51 2 asdf 123 7ds3 1123 wk52 4 asdf 123 7ds3 1123 wk1 22 asdf 123 7ds3 1123 wk2 11 uerr 4dd 3de34 1198 wk48 9 uerr 4dd 3de34 1198 wk49 4 ...... — vinod kumar
– vinod kumar, Commented Dec 16, 2016 at 8:57

Michael Silverstein · Accepted Answer · 2016-12-14 00:27:40Z

2

The first thing I would do is set ID as your index with:

df.set_index('ID',inplace=True)

Next, you can use the following command to reorient your dataframe:

df = df.stack().reset_index()
print df
-------------------------------------------
Output:
      ID level_1    0
0   1123    wk48   10
1   1123    wk49   22
2   1123    wk50  233
3   1123    wk51    2
4   1123    wk52    4
5   1123     wk1   22
6   1123     wk2   11
7   1198    wk48    9
8   1198    wk49    4
9   1198    wk50   44
10  1198    wk51   23
11  1198    wk52   34
12  1198     wk1    5
13  1198     wk2  234
14   101    wk48    3
15   101    wk49    6
16   101    wk50    3
17   101    wk51   43
18   101    wk52   33
19   101     wk1   34
20   101     wk2   78

answered Dec 14, 2016 at 0:27

Michael Silverstein

1,85317 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vinod kumar Over a year ago

Thanks for the details Michael.. I have used pd.melt command like pd.melt(df, id_vars = ["Name"], var_name = "Wk") after this i did a sort and then saved to a csv file ..is that also works ?

Julien Marrec · Accepted Answer · 2016-12-14 00:54:56Z

Read the data in pandas:

In[1]:
import pandas as pd
df = pd.read_excel('yourfile.xlsx', index_col=0)

Then use DataFrame.stack to move the columns to the index:

In [2]: s = df.stack()
        # Rename the index names for cleaner output
        s.index.names = ['ID','Week']

You'll get a pd.Series like you want:

In [3]: s.head(10)
Out[3]: 
ID    Week    
1123  wk48     10
      wk49     22
      wk50    233
      wk51      2
      wk52      4
      wk1      22
      wk2      11
1198  wk48      9
      wk49      4
      wk50     44
dtype: int64

In case you want that back as a dataframe:

In [4]: s.name = 'Value'
        df = s.to_frame()

You can then export that back to Excel for example:

In [5]: df.to_excel('newfile.xslx')

Or if you don't like to have ID being grouped (merged cells) in excel, you can do this:

In [6]: df.reset_index().to_excel('newfile.xlsx', index=False)

Collectives™ on Stack Overflow

convert columns to rows based on row value using pandas

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related