I have a question regarding structuring panel data in Stata, and preparing the data in a panel structure to run the appropriate analysis. The tables under are shown in excel, however are imported directly into Stata for the sorting.
Problem and description inn data:
Tables:
Prod_id refers to the product id being offered by the store, at a given price, at a given date. For example, the store Brio offers product 10 for 1999 on 06.01.2017 (Table 1). This means that the next date on the same product by the same shop (Brio) will be a referred to as a price change, here 1899 n 10.01.2017.
Table 1 depicts the actual shape the inn data has in excel, such that it can be imported directly to Stata. Table 2 refers to the panel structure needed from table 1, where the minimum price of each product, from each shop is given for each date. Table 3 is simply table 2, with prices being constant between dates with price changes. Color code orange and brown indicate the same product with different variants being offered on the same shop on the same dates. Grey indicates a restart of the date cycle, i.e. another variant of the same product by the same shop, while yellow indicate a change to another product. Their content is explained in correct context in the following sentences.
Main issues: (And what I have tried)
The first issue is constructing in table 2, a pre-specified date period (here 02.01.2017-15.01.2017) such that all prices from all shops, per product can be filled inn, i.e. to get it in the format of table 2.
The main issue I have however, is sorting it in Stata after importing table 1 from excel. I try using the reshape, however this I think fails due to two complexities that occur in the structure of inn data in panel 1. These are when two (or more) prices occur on the same date, for the same shop. An example of this is marked in orange and brown in table 1. The orange case is discussed first:
When two prices occur due to product cycle restart (date starting over for same shop)
In orange, it is the same shop, Brio, that is offering two variants of the same product 10, where the grey area indicates a restart of the date, i.e. another variant of the same product. The collision occurs on 10.01.2017 with price 1899, and for the next variant at 2100, on 10.01.2017.
When two prices occur due to the same shop changing the price on the same day:
This is marked by the brown area in table 1, showing the shop Leke, changing their price from 1999 to 2034 on the same date 12.01.2017
What I need help with:
I need to sort the data in Stata from table 1, in a form shown in table 2. Table 2, shows the minimum values for each shop at each date, given each product, such that if there are complexities with two prices colliding (as stated in the issues, marked by the orange&brown area), the minimum of those is chosen for the sorting. Table 2 also needs to construct a prespecified date period for each period (here 02.01.2017-15.01.2017) such that all price observations can be included.
Table 3 is the last step, which is table 2, with all the prices in between two price changes, being constant. The yellow area indicates that the product changes from 10 to another one, 12, such that the filling out of prices has to be subject to the prespecified date period for each product, given each shop.
Summary
A panel data setup is needed from table 1, shown in table 2, where first a prespecified date period must be constructed, and then the minimum prices per date – per product – per shop be sorted in, before blank dates are filled out with the previous price. I have tried in Stata using reshape, but due to complexities, am not sure of how to do it. The original data also has much more shops, longer periods of time, such that it is preferable to do this in Stata, as excel the manual process is not feasible for such a large set.
Problem and description inn data:
Tables:
Prod_id refers to the product id being offered by the store, at a given price, at a given date. For example, the store Brio offers product 10 for 1999 on 06.01.2017 (Table 1). This means that the next date on the same product by the same shop (Brio) will be a referred to as a price change, here 1899 n 10.01.2017.
Table 1 depicts the actual shape the inn data has in excel, such that it can be imported directly to Stata. Table 2 refers to the panel structure needed from table 1, where the minimum price of each product, from each shop is given for each date. Table 3 is simply table 2, with prices being constant between dates with price changes. Color code orange and brown indicate the same product with different variants being offered on the same shop on the same dates. Grey indicates a restart of the date cycle, i.e. another variant of the same product by the same shop, while yellow indicate a change to another product. Their content is explained in correct context in the following sentences.
Main issues: (And what I have tried)
The first issue is constructing in table 2, a pre-specified date period (here 02.01.2017-15.01.2017) such that all prices from all shops, per product can be filled inn, i.e. to get it in the format of table 2.
The main issue I have however, is sorting it in Stata after importing table 1 from excel. I try using the reshape, however this I think fails due to two complexities that occur in the structure of inn data in panel 1. These are when two (or more) prices occur on the same date, for the same shop. An example of this is marked in orange and brown in table 1. The orange case is discussed first:
When two prices occur due to product cycle restart (date starting over for same shop)
In orange, it is the same shop, Brio, that is offering two variants of the same product 10, where the grey area indicates a restart of the date, i.e. another variant of the same product. The collision occurs on 10.01.2017 with price 1899, and for the next variant at 2100, on 10.01.2017.
When two prices occur due to the same shop changing the price on the same day:
This is marked by the brown area in table 1, showing the shop Leke, changing their price from 1999 to 2034 on the same date 12.01.2017
What I need help with:
I need to sort the data in Stata from table 1, in a form shown in table 2. Table 2, shows the minimum values for each shop at each date, given each product, such that if there are complexities with two prices colliding (as stated in the issues, marked by the orange&brown area), the minimum of those is chosen for the sorting. Table 2 also needs to construct a prespecified date period for each period (here 02.01.2017-15.01.2017) such that all price observations can be included.
Table 3 is the last step, which is table 2, with all the prices in between two price changes, being constant. The yellow area indicates that the product changes from 10 to another one, 12, such that the filling out of prices has to be subject to the prespecified date period for each product, given each shop.
Summary
A panel data setup is needed from table 1, shown in table 2, where first a prespecified date period must be constructed, and then the minimum prices per date – per product – per shop be sorted in, before blank dates are filled out with the previous price. I have tried in Stata using reshape, but due to complexities, am not sure of how to do it. The original data also has much more shops, longer periods of time, such that it is preferable to do this in Stata, as excel the manual process is not feasible for such a large set.
Attached Files
Comment