I have a task to differentiate between customers who are purchasing simultaneously with two different stores and those who have made a complete switch along the way.
the data I have is given below
pd.DataFrame(
{'Id': {0: 'A', 1: 'A', 2: 'A', 3: 'A', 4: 'A', 5: 'A', 6: 'A', 7: 'A', 8: 'B', 9: 'B', 10: 'B', 11: 'B', 12: 'B'}, 'Store': {0: 'A001', 1: 'A001', 2: 'A001', 3: 'A001', 4: 'C001', 5: 'C001', 6: 'C001', 7: 'C001', 8: 'A001', 9: 'A001', 10: 'B001', 11: 'B001', 12: 'B001'}, 'Date': {0: '1-Dec-21', 1: '1-Jan-22', 2: '1-Feb-22', 3: '1-Mar-22', 4: '1-Dec-21', 5: '1-Jan-22', 6: '1-Feb-22', 7: '1-Mar-22', 8: '1-Dec-21', 9: '1-Jan-22', 10: '1-Jan-22', 11: '1-Feb-22', 12: '1-Mar-22'}, 'Purchases': {0: 5000, 1: 60000, 2: 4897, 3: 8752, 4: 4000, 5: 3000, 6: 2000, 7: 1000, 8: 7500, 9: 8500, 10: 1250, 11: 10000, 12: 11000}, 'Orders': {0: 11, 1: 12, 2: 8, 3: 5, 4: 2, 5: 3, 6: 1, 7: 1, 8: 15, 9: 16, 10: 2, 11: 11, 12: 10}, 'R_Status': {0: 'No', 1: 'No', 2: 'No', 3: 'No', 4: 'No', 5: 'No', 6: 'No', 7: 'No', 8: 'No', 9: 'No', 10: 'Yes', 11: 'Yes', 12: 'Yes'}})
Shape of data
+----+-------+----------+-----------+--------+----------+
| Id | Store | Date | Purchases | Orders | R_Status |
+----+-------+----------+-----------+--------+----------+
| A | A001 | 1-Dec-21 | 5000 | 11 | No |
| A | A001 | 1-Jan-22 | 60000 | 12 | No |
| A | A001 | 1-Feb-22 | 4897 | 8 | No |
| A | A001 | 1-Mar-22 | 8752 | 5 | No |
| A | C001 | 1-Dec-21 | 4000 | 2 | No |
| A | C001 | 1-Jan-22 | 3000 | 3 | No |
| A | C001 | 1-Feb-22 | 2000 | 1 | No |
| A | C001 | 1-Mar-22 | 1000 | 1 | No |
| B | A001 | 1-Dec-21 | 7500 | 15 | No |
| B | A001 | 1-Jan-22 | 8500 | 16 | No |
| B | B001 | 1-Jan-22 | 1250 | 2 | Yes |
| B | B001 | 1-Feb-22 | 10000 | 11 | Yes |
| B | B001 | 1-Mar-22 | 11000 | 10 | Yes |
+----+-------+----------+-----------+--------+----------+
My desired output looks like this
pd.DataFrame(
{'Id': {0: 'A', 1: 'A', 2: 'A', 3: 'A', 4: 'A', 5: 'A', 6: 'A', 7: 'A', 8: 'B', 9: 'B', 10: 'B', 11: 'B', 12: 'B'}, 'Store': {0: 'A001', 1: 'A001', 2: 'A001', 3: 'A001', 4: 'C001', 5: 'C001', 6: 'C001', 7: 'C001', 8: 'A001', 9: 'A001', 10: 'B001', 11: 'B001', 12: 'B001'}, 'Date': {0: '1-Dec-21', 1: '1-Jan-22', 2: '1-Feb-22', 3: '1-Mar-22', 4: '1-Dec-21', 5: '1-Jan-22', 6: '1-Feb-22', 7: '1-Mar-22', 8: '1-Dec-21', 9: '1-Jan-22', 10: '1-Jan-22', 11: '1-Feb-22', 12: '1-Mar-22'}, 'Purchases': {0: 5000, 1: 60000, 2: 4897, 3: 8752, 4: 4000, 5: 3000, 6: 2000, 7: 1000, 8: 7500, 9: 8500, 10: 1250, 11: 10000, 12: 11000}, 'Orders': {0: 11, 1: 12, 2: 8, 3: 5, 4: 2, 5: 3, 6: 1, 7: 1, 8: 15, 9: 16, 10: 2, 11: 11, 12: 10}, 'Switch_flag': {0: 0, 1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 0, 8: 1, 9: 1, 10: 1, 11: 1, 12: 1}, 'Previous_Store': {0: 'A001', 1: 'A001', 2: 'A001', 3: 'A001', 4: 'C001', 5: 'C001', 6: 'C001', 7: 'C001', 8: 'A001', 9: 'A001', 10: 'A001', 11: 'A001', 12: 'A001'}, 'Updated_store': {0: 'A001', 1: 'A001', 2: 'A001', 3: 'A001', 4: 'C001', 5: 'C001', 6: 'C001', 7: 'C001', 8: 'B001', 9: 'B001', 10: 'B001', 11: 'B001', 12: 'B001'}, 'Updated R_status': {0: 'No', 1: 'No', 2: 'No', 3: 'No', 4: 'No', 5: 'No', 6: 'No', 7: 'No', 8: 'Yes', 9: 'Yes', 10: 'Yes', 11: 'Yes', 12: 'Yes'}})
Desired output
+----+-------+----------+-----------+--------+-------------+----------------+---------------+------------------+
| Id | Store | Date | Purchases | Orders | Switch_flag | Previous_Store | Updated_store | Updated R_status |
+----+-------+----------+-----------+--------+-------------+----------------+---------------+------------------+
| A | A001 | 1-Dec-21 | 5000 | 11 | 0 | A001 | A001 | No |
| A | A001 | 1-Jan-22 | 60000 | 12 | 0 | A001 | A001 | No |
| A | A001 | 1-Feb-22 | 4897 | 8 | 0 | A001 | A001 | No |
| A | A001 | 1-Mar-22 | 8752 | 5 | 0 | A001 | A001 | No |
| A | C001 | 1-Dec-21 | 4000 | 2 | 0 | C001 | C001 | No |
| A | C001 | 1-Jan-22 | 3000 | 3 | 0 | C001 | C001 | No |
| A | C001 | 1-Feb-22 | 2000 | 1 | 0 | C001 | C001 | No |
| A | C001 | 1-Mar-22 | 1000 | 1 | 0 | C001 | C001 | No |
| B | A001 | 1-Dec-21 | 7500 | 15 | 1 | A001 | B001 | Yes |
| B | A001 | 1-Jan-22 | 8500 | 16 | 1 | A001 | B001 | Yes |
| B | B001 | 1-Jan-22 | 1250 | 2 | 1 | A001 | B001 | Yes |
| B | B001 | 1-Feb-22 | 10000 | 11 | 1 | A001 | B001 | Yes |
| B | B001 | 1-Mar-22 | 11000 | 10 | 1 | A001 | B001 | Yes |
+----+-------+----------+-----------+--------+-------------+----------------+---------------+------------------+
So, I want to add few fields in front of the customers to identify whether customer made a switch or not and update the columns with new stores so that I can work with them for aggregation purposes.
It's also possible that resultant output has only aggregated values for customer B i.e. it has two records for Jan and I can group them into one.
Eventual goal is to have total purchases against each customer so my final output will be like this
{'Id': {0: 'A', 1: 'A', 2: 'B'},
'Store': {0: 'A001', 1: 'C001', 2: 'B001'},
'Total Purchases': {0: 78649, 1: 10000, 2: 38250},
'Orders': {0: 36, 1: 7, 2: 54},
'R_Status': {0: 'No', 1: 'No', 2: 'Yes'}}
+----+-------+-----------------+--------+----------+
| Id | Store | Total Purchases | Orders | R_Status |
+----+-------+-----------------+--------+----------+
| A | A001 | 78649 | 36 | No |
| A | C001 | 10000 | 7 | No |
| B | B001 | 38250 | 54 | Yes |
+----+-------+-----------------+--------+----------+