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 |
How do i get rid of the_ in the expense and revenue columns?
@dhakim1996 Sorry, I updated the workflow