Sorry I realise the title is fairly unclear. I couldn't really describe this problem very well in a sentence (hence my issues in solving it!).
I have a dataset of organisations linked to a particular place ID - one place ID can have multiple places of different types associated with it, or just one. The data looks like this:
| name | type | id |
|---|---|---|
| Kent County Council | county | 1 |
| Canterbury City Council | district | 1 |
| City of Westminster | unitary | 2 |
| Hampshire County Council | county | 3 |
| Test Valley Borough Council | district | 3 |
I want an output that has each place ID, with columns that represent the council type (if it has one). Ideally looking like this:
| id | county_council_name | district_council_name | unitary_council_name |
|---|---|---|---|
| 1 | Kent County Council | Canterbury City Council | NaN |
| 2 | NaN | NaN | City of Westminster |
| 3 | Hampshire County Council | Canterbury City Council | NaN |
This seems like a pivot of some sort, or maybe iterating over the dataframe in some way? I can't really think of the language I need to use to even ask the question!
Thanks in advance
