Sorting Excel column with Python

Question

Let's say I have a list like this:

time    type    value
80      1A      10
100     1A      20
60      18      56
80      18      7
80      2A      10
100     2A      10
80      28      10
100     28      20

and I need to change it to be like this:

            time        
type    60  80  100
1A          10  20
1B      56  7   
2A          10  10
2B          10  20

So far what I did is just basic sorting of the column:

target_column = 0
book = open_workbook('result.xls')
sheet = book.sheets()[0]
data = [sheet.row_values(i) for i in range(sheet.nrows)]
labels = data[0]
data = data[1:]
data.sort(key= lambda x: x[target_column])

bk = xlwt.Workbook()
sheet = bk.add_sheet(sheet.name)
for idx, label in enumerate(labels):
    sheet.write(0, idx, label)

for idx_r, row in enumerate(data):
    for idx_c, value in enumerate(row):
        sheet.write(idx_r+1, idx_c, value)

bk.save('resul.xls')

How can I it with Python?

This is not sorting. I don't know excel much but I think this might be possible withing MS excel pivot table. — Rahul
– Rahul, Commented Feb 26, 2018 at 5:52
Make a dictionary with key as the "type" column and value as another dictionary with keys as 60, 80 ,100 and their values with corresponding key. Example : {"1A":{80:10,100:20},"1B":{60:56,80:7}....} — Shashank Singh
– Shashank Singh, Commented Feb 26, 2018 at 6:13
Indeed this is not sorting. Checkout a Python package called Pandas. I am pretty sure it contains all you need to perform this kind of transformation. — Alexis.Rolland
– Alexis.Rolland, Commented Feb 26, 2018 at 6:20
It seems you are trying to create pivot table with python. I think pivot_table in pandas will match your needs. — Inetgate
– Inetgate, Commented Feb 26, 2018 at 6:23
@rahul : yes, it was solved by pivot table, but i'm wondering if i can do it by python programming, because initially i read the data from outside text file, and write it into excel by python. anyway, thanks! — munmu
– munmu, Commented Feb 26, 2018 at 6:29

Stephen Rauch · Accepted Answer · 2018-02-26 06:58:07Z

2

You can use pandas.DataFrame.pivot() to do that like:

Code:

df.pivot(index='type', columns='time', values='value')

Test Code:

df = pd.read_fwf(StringIO(u"""
    time    type    value
    80      1A      10
    100     1A      20
    60      18      56
    80      18      7
    80      2A      10
    100     2A      10
    80      28      10
    100     28      20"""), header=1)
print(df)

print(df.pivot(index='type', columns='time', values='value'))

Results:

   time type  value
0    80   1A     10
1   100   1A     20
2    60   18     56
3    80   18      7
4    80   2A     10
5   100   2A     10
6    80   28     10
7   100   28     20

time   60    80    100
type                  
18    56.0   7.0   NaN
1A     NaN  10.0  20.0
28     NaN  10.0  20.0
2A     NaN  10.0  10.0

answered Feb 26, 2018 at 6:58

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

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

Comments

Rahul · Accepted Answer · 2018-02-26 07:10:37Z

This is just a educational. Right answer is Pandas way by @Stephen Rauch

from xlrd import open_workbook
from openpyxl import Workbook


book = open_workbook('pivot.xls')
sheet = book.sheet_by_index(0)
pivot = {}
for row_index in range(1, sheet.nrows):
    time = sheet.cell(row_index, 0).value
    type = sheet.cell(row_index, 1).value
    value = sheet.cell(row_index, 2).value

    if type not in pivot:
        pivot[type] = {}
        pivot[type][time] = value
    else:
        pivot[type][time] = value
wb = Workbook()
ws1 = wb.active
ws1.merge_cells('B1:D1')
ws1.append(("", "time"))
ws1.append(("type", "60", "80", "100"))
for type, value in pivot.items():
    ws1.append((type, value.get(60, None), value.get(80, None), value.get(100, None)))
wb.save('out.xlsx')

Rahul · Accepted Answer · 2018-02-26 07:29:58Z

0

import pandas as pd
df = pd.read_excel('pivot.xls')
df_pivot = df.pivot(index='type', columns='time', values='value')
df_pivot.to_excel('output.xlsx')

answered Feb 26, 2018 at 7:29

Rahul

11.7k5 gold badges63 silver badges100 bronze badges

Collectives™ on Stack Overflow

Sorting Excel column with Python

3 Answers 3

Code:

Test Code:

Results:

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Code:

Test Code:

Results:

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related