CODEWITHSUNDEEP
pythonexcelpandasmulti-indexxlwt
Mark K
Mark K

Reputation: 9348

Writing multi-indexed data to an excel file with Python/Pandas

I want to create an Excel spreadsheet and insert equal number of rows for each of variable. The ideal result shall looked like Columns A & B in the picture.

What I can do so far is only to insert for 1 name (Columns D & E), and have no idea do the proper enumeration for the rest.

name and food

This is what I have:

import xlwt, xlrd
import os

current_file = xlwt.Workbook()
write_table = current_file.add_sheet('Sheet1')

name_list = ["Jack", "David", "Andy"]
food_list = ["Ice-cream", "Mango", "Apple", "Cake"]

total_rows = len(name_list) * len(food_list)   # how to use it?

write_table.write(0, 0, "Jack")

for row, food in enumerate(food_list):
    write_table.write(row, 1, food)

current_file.save("c:\\name_food.xls")

How can I do it for all? Thank you.

Upvotes: 3

Views: 583

Answers (2)

jezrael
jezrael

Reputation: 863246

You can create DataFrame by numpy.tile and numpy.repeat and then remove duplicated in a column:

df = pd.DataFrame({'a': np.repeat(name_list, len(food_list)),
                   'b': np.tile(food_list, len(name_list))})

df['a'] = np.where(df['a'].duplicated(), '', df['a'])
print (df)
        a          b
0    Jack  Ice-cream
1              Mango
2              Apple
3               Cake
4   David  Ice-cream
5              Mango
6              Apple
7               Cake
8    Andy  Ice-cream
9              Mango
10             Apple
11              Cake

Another solution with list comprehension:

df = pd.DataFrame({'a': [y for x in name_list for y in [x] + [''] * (len(food_list)-1)],
                   'b': food_list * len(name_list)})
print (df)
        a          b
0    Jack  Ice-cream
1              Mango
2              Apple
3               Cake
4   David  Ice-cream
5              Mango
6              Apple
7               Cake
8    Andy  Ice-cream
9              Mango
10             Apple
11              Cake

And last write to_excel:

df.to_excel('c:\\name_food.xls', index=False, header=False)

Upvotes: 4

Fejs
Fejs

Reputation: 2888

Something like this should work:

import xlwt, xlrd
import os

current_file = xlwt.Workbook()
write_table = current_file.add_sheet('Sheet1')

name_list = ["Jack", "David", "Andy"]
food_list = ["Ice-cream", "Mango", "Apple", "Cake"]

for i, name in enumerate(name_list):
    write_table.write(i * len(food_list), 0, name_list[i])

    for row, food in enumerate(food_list):
        write_table.write(i * len(food_list) + row, 1, food)

current_file.save("c:\\name_food.xls")

The important part is

write_table.write(i * len(food_list), 0, name_list[i])

where You say that name should be written in rows 0, 4, 8, 12...

Also, part

write_table.write(i * len(food_list) + row, 1, food)

writes food to corresponding section increased by row number.

Upvotes: 3

Related Questions