Code for converting data into a particular format

[kaushik_birmiwal]

Hi All,

I have been facing this recurring issue. We need to analyze the spend as per product and SKU by week, months, quarters etc. The dump from the tool is in a raw form and we end up spending lot of time in converting the data into a format which can be further used to apply pivots. I am relatively new to coding and hence would like to seek help.

I have the data in below format:

Month	Year	January	January	January	January	January
Date	2016	1	8	15	22	29
	Week No.	1	2	3	4	5
AB
Product 1	2016	SKU 1				SKU 2
Product 1	2016
Product 1	2016		SKU 3
Product 1	2016			SKU 5
Product 1	2016				SKU 6
Product 1	2016
Product 1	2016
Product 2	Planned
Product 3	Rental	$6,000	$17,655	$5,000	$40,000
Product 4	listing fee
Product 5	production cost
CD
Product 2	2016		SKU 7
Product 2	2016			SKU 11
Product 2	2016
Product 2	2016	SKU 8		SKU 9
Product 2	2016
Product 2	2016					SKU 10
Product 2	2016
Product 2	Planned
Product 2	Rental	$6,000	$17,655		$40,000	$5,000
Product 2	listing fee
Product 2	production cost

<tbody>
</tbody>

I now need to convert this data into the below mentioned format, so that i can pivots for analyzing the data.

Year	Product Category	Product	Week	SKU #	Amount
2016	AB	Product 1	1	SKU 1	$6,000
2016	AB	Product 1	1	SKU 7
2016	AB	Product 1	3	SKU 5	$5,000
2016	AB	Product 1	4	SKU 6	$40,000
2016	AB	Product 1	5	SKU 2	-
2016	AB	Product 1	2	SKU 13	$17,655
2016	AB	Product 1	2	SKU 3
2016	CD	Product 2	1	SKU 8	$6,000
2016	CD	Product 2	2	SKU 12	$17,655
2016	CD	Product 2	3	SKU 11	-
2016	CD	Product 2	3	SKU 9
2016	CD	Product 2	5	SKU 10	$5,000
2016	CD	Product 2	4	NA	$40,000

<tbody>
</tbody>

Can someone please help me with this issue.

Thanks in advance.

Regards,
Kaushik