Python Rows-to-Column using MELTED and ROW_NUMBER() - PART 1 of 2

Опубликовано: 01 Ноябрь 2024
на канале: Afterowl

117

Quickly and effortlessly take a row of data and turn it into columns (star schema). This is an alternative to MS SQL UNPIVOT but unlike MS SQL you don't need to know the column names.

import pandas as pd

df = pd.read_excel('Afterowl.xlsx', sheet_name=0,header=0)

#RENAME COLUMN
df.rename(columns={'Source ID': 'SourceID'}, inplace=True)

#ROWS TO COLUMN
melted = pd.melt(df, id_vars = 'SourceID', var_name = 'Attribute', value_name = 'Value')

#ROW_NUMBER() PARTITION BY
melted['RN'] = melted.sort_values(['SourceID','Attribute'], ascending=[True,False]) \
.groupby(['Attribute']) \
.cumcount() + 1

#SAVE TO EXCEL
melted.to_excel('output.xlsx',index=False,header=True)