I have a dataframe from a .csv file that looks like this:
| Mgr | Address | Service Type | Contractor Name |
|---|---|---|---|
| John | 123 st | Fire Alarm | Company A |
| John | 123 st | BMS | Company B |
| Adam | 456 ave | Fire Alarm | Company C |
| Adam | 456 ave | BMS | Company D |
I want it to look like this:
| Address | Fire Alarm | BMS |
|---|---|---|
| 123 st | Company A | Company B |
| 456 ave | Company C | Company D |
I've tried the following code:
df = df.groupby(['Full Address','Contractor Name','Service Type'])['Contractor Name'].agg(lambda x: ', '.join(x)).unstack().reset_index()
df = df.drop('Contractor Name', axis=1)
which gives me:
| Address | Contractor Name | BMS | Fire Alarm |
|---|---|---|---|
| 123 st | Fire Alarm | NaN | Company A |
| 123 st | BMS | Company B | NaN |
| 456 ave | Fire Alarm | NaN | Company C |
| 456 ave | BMS | Company D | NaN |
I think I'm close, but not sure what's the next step here.
Any help would be greatly appreciated!
