Let's assume i have the following dataframe with multiple lines
import numpy as np
import pandas as pd
data = [[152542,'201903','42','RES'],
[152542, '201904','30','RES'],
[152541, '201901','25','COM'],
[152543, '201902','80','IND'],
[152541, '201902','35','COM'],
[152544,'201904','10','PUB']]
df = pd.DataFrame(data, columns=['ID','YEARMONTH', 'PRICE','CATEGORY'])
df
How can i transform the column YEARMONTH into multiple columns with the respective price value for each unique id like this:
| ID | 201901 | 201902 | 201903 | 201904 | CATEGORY |
|---|---|---|---|---|---|
| 152541 | 25 | 35 | NULL | NULL | COM |
| 152542 | NULL | NULL | 42 | 30 | RES |
| 152543 | NULL | 80 | NULL | NULL | IND |
| 152544 | NULL | NULL | NULL | 10 | PUB |
Filling with null the values not found for that id without the price value for the respective month. Any suggestion is appreciated. Thanks.
