Data Cleansing & Format

[dhakim1996]

Hi, I have the following dataset. Could someone kindly assist how i can go about doing it please:

Raw Data 

COMPANY	F2	F3	F4	F5	F6	F7
ABC
ABC	CO CODE	YEAR	MONTH
ABC	I864G	2021	12
ABC	LONG-TERM
ABC
ABC	Description	Row No.	Revenue		Expense
ABC			Individual	Group	Individual	Group
ABC	APPLE
ABC	No. of Apple	1	0	0	0	0
ABC	Size of Apple	2	0	0	0	0
ABC	Price of Apple	3	0	0	0	0
ABC
ABC	PEAR
ABC	No. of Pear	4	0	0	0	0
ABC	Size of Pear	5	0	0	0	0
ABC	Price of Pear	6	0	0	0	0
ABC	SHORT TERM
ABC
ABC	Description	Row No.	Revenue		Expense
ABC			Individual	Group	Individual	Group
ABC	APPLE
ABC	No. of Apple	1	8292251	61063937	0	0
ABC	Size of Apple	2	100361	739061	0	0
ABC	Price of Apple	3	8191890	60324876	0	0
ABC
ABC	PEAR
ABC	No. of Pear	4	5107323	51639682	0	0
ABC	Size of Pear	5	149990	1516539	0	0
ABC	Price of Pear	6	4957333	50123143	0	0

Cleansed Data 

COMPANY	SHORT/LONG TERM	YEAR	DESCRIPTION	REVENUE_INDIVIDUAL	REVENUE_GROUP	EXPENSE_INDIVIDUAL	EXPENSE_GROUP
ABC	LONG-TERM	2021	No. of Apple	0	0	0	0
ABC	LONG-TERM	2021	Size of Apple	0	0	0	0
ABC	LONG-TERM	2021	Price of Apple	0	0	0	0
ABC	LONG-TERM	2021	No. of Pear	0	0	0	0
ABC	LONG-TERM	2021	Size of Pear	0	0	0	0
ABC	LONG-TERM	2021	Price of Pear	0	0	0	0
ABC	SHORT-TERM	2021	No. of Apple	123	1617	0	0
ABC	SHORT-TERM	2021	Size of Apple	456	1819	0	0
ABC	SHORT-TERM	2021	Price of Apple	789	2021	0	0
ABC	SHORT-TERM	2021	No. of Pear	1011	2223	0	0
ABC	SHORT-TERM	2021	Size of Pear	1213	2425	0	0
ABC	SHORT-TERM	2021	Price of Pear	1415	2627	0	0

[binu_acs]

@dhakim1996 One way of doing this

[BS_THE_ANALYST]

One way, probably a long way though 😂 .. 

[dhakim1996]

How do i get rid of the_ in the expense and revenue columns?

[binu_acs]

@dhakim1996 Sorry, I updated the workflow