classifing excel data row by row in n level columns

user_6717077 0
Viewed 119

I have problem with excel file to classify data in some columns and rows, I need to arrange merge cells to next column as a 1 row and next column go to beside them like this pictures:

Input:

input excel example

Output for Dairy:

output excel example

Summary:

first we took Dairy row, then we go to the second column in front of Dairy and get data in front of Dairy, then we go to the second column and in front of Milk to Mr. 1 we get the Butter to Mrs. 1 and Butter to Mrs. 2 and so on ...

After that we want to export it into an excel file like in Output picture.

I have written a code which get the first column data and finds all the data in front of it but I need to change it in order to get the data row by row like in the Output picture:

import pandas
import openpyxl
import xlwt
from xlwt import Workbook

df = pandas.read_excel('excel.xlsx')

result_first_level = []

for i, item in enumerate(df[df.columns[0]].values, 2):
    if pandas.isna(item):
        result_first_level[-1]['index'] = i
    else:
        result_first_level.append(dict(name=item, index=i, levels_name=[]))

for level in df.columns[1:]:
    move_index = 0
    for i, obj in enumerate(result_first_level):
        if i == 0:
            for item in df[level].values[0:obj['index'] - 1]:
                if pandas.isna(item):
                    move_index += 1
                    continue
                else:
                    obj['levels_name'].append(item)
                move_index += 1
        else:
            for item in df[level].values[move_index:obj['index'] - 1]:
                if pandas.isna(item):
                    move_index += 1
                    continue
                else:
                    obj['levels_name'].append(item)
                move_index += 1

# Workbook is created
wb = Workbook()

# add_sheet is used to create sheet.
sheet1 = wb.add_sheet('Sheet 1')
style = xlwt.easyxf('font: bold 1')

move_index = 0
for item in result_first_level:
    for member in item['levels_name']:
        sheet1.write(move_index, 0, item['name'], style)
        sheet1.write(move_index, 1, member)
        move_index += 1

wb.save('test.xls')

download Input File excel from here

Thanks for helping!

pandas dataset python-3.x excel python
1 Answers

First, fill forward your data to fill blank cells with the last valid value the create an ordered collection using pd.CategoricalDtype to sort the product column. Finally, you have just to iterate over columns pairwise and rename columns to allow concatenate. The last step is to sort your rows by product value.

import pandas as pd

# Prepare your dataframe
df = pd.read_excel('input.xlsx').dropna(how='all')
df.update(df.iloc[:, :-1].ffill())
df = df.drop_duplicates()

# Get keys to sort data in the final output
cats = pd.CategoricalDtype(df.T.melt()['value'].dropna().unique(), ordered=True)

# Group pairwise values
data = []
for cols in zip(df.columns, df.columns[1:]):
    col_mapping = dict(zip(cols, ['product', 'subproduct']))
    data.append(df[list(cols)].rename(columns=col_mapping))

# Merge all data
out = pd.concat(data).drop_duplicates().dropna() \
        .astype(cats).sort_values('product').reset_index(drop=True)

Output:

>>> cats
CategoricalDtype(categories=['Dairy', 'Milk to Mr.1', 'Butter to Mrs.1',
                  'Butter to Mrs.2', 'Cheese to Miss 2 ', 'Cheese to Mr.2',
                  'Milk to Miss.1', 'Milk to Mr.5', 'yoghurt to Mr.3',
                  'Milk to Mr.6', 'Fruits', 'Apples to Mr.6',
                  'Limes to Miss 5', 'Oranges to Mr.7', 'Plumbs to Miss 5',
                  'apple for mr 2', 'Foods & Drinks', 'Chips to Mr1',
                  'Jam to Mr 2.', 'Coca to Mr 5', 'Cookies to Mr1.',
                  'Coca to Mr 7', 'Coca to Mr 6', 'Juice to Miss 1',
                  'Jam to Mr 3.', 'Ice cream to Miss 3.', 'Honey to Mr 5',
                  'Cake to Mrs. 2', 'Honey to Miss 2',
                  'Chewing gum to Miss 7.'], ordered=True)

>>> out
             product              subproduct
0              Dairy            Milk to Mr.1
1              Dairy          Cheese to Mr.2
2       Milk to Mr.1         Butter to Mrs.1
3       Milk to Mr.1         Butter to Mrs.2
4    Butter to Mrs.2       Cheese to Miss 2 
5     Cheese to Mr.2          Milk to Miss.1
6     Cheese to Mr.2         yoghurt to Mr.3
7     Milk to Miss.1            Milk to Mr.5
8    yoghurt to Mr.3            Milk to Mr.6
9             Fruits          Apples to Mr.6
10            Fruits         Oranges to Mr.7
11    Apples to Mr.6         Limes to Miss 5
12   Oranges to Mr.7        Plumbs to Miss 5
13  Plumbs to Miss 5          apple for mr 2
14    Foods & Drinks            Chips to Mr1
15    Foods & Drinks         Juice to Miss 1
16    Foods & Drinks          Cake to Mrs. 2
17      Chips to Mr1            Jam to Mr 2.
18      Chips to Mr1         Cookies to Mr1.
19      Jam to Mr 2.            Coca to Mr 5
20   Cookies to Mr1.            Coca to Mr 6
21   Cookies to Mr1.            Coca to Mr 7
22   Juice to Miss 1           Honey to Mr 5
23   Juice to Miss 1            Jam to Mr 3.
24      Jam to Mr 3.    Ice cream to Miss 3.
25    Cake to Mrs. 2  Chewing gum to Miss 7.
26    Cake to Mrs. 2         Honey to Miss 2
user_152399510
Related