How to create .json file based on Pandas DataFrame? Python

Question

I have Pandas DataFrame with ID, Latitude and Longitude:

    Id      Latitude    Longitude   
0   01    -17.658200    36.123498   
1   01    -17.658151    36.123522
2   01    -17.658063    36.123576
3   02    -11.896096    30.388277   
4   02    -11.896096    30.388277   
5   02    -11.896088    30.388275

I would like to create from this .json file (format like this because it have to be accepted by Sentinelhub)

Here is example of json file accepted by Sentinelhub:

{'type': 'FeatureCollection',
 'features': [{'type': 'Feature',
   'properties': {},
   'geometry': {'type': 'Polygon',
    'coordinates': [[[0.57952880859375, 20.037870053952016],
      [0.43121337890625, 20.035289711352377],
      [0.43121337890625, 19.93720533223859]]]}}]}

So for my tiny example desired output should look like this (it is two structured because we have 2 places (polygons) defined as ID:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              -17.658200,
               36.123498
            ],
            [
              -17.658151,
               36.123522
            ],
            [
              -17.658063,
               36.123576
            ]
          ]
        ]
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              -11.896096,
               30.388277
            ],
            [
              -11.896096,
               30.388277
            ],
            [
              -11.896088,
               30.388275
            ]
          ]
        ]
      }
    }
  ]
}

As you see the fixed element is repeating for every ID.

Im totally new to working with this type of data so what I managed to do till now is creating a dictionary like this:

places = {}
    for row in df.itertuples():
        if row.Id not in places:
            places[row.Id] = [[row.Latitude, row.Longitude]]
        else:
            places[row.Id].append([row.Latitude, row.Longitude])

Which results in dictionary in which I have separated coordinates by ID... :

{01: [[-17.658200,36.123498],
      [-17.658151,36.123522],
      [-17.658063,36.123576]],
 02: [[-11.896096,30.388277],
     [-11.896096,30.388277],
     [-11.896088,30.388275]}

Answer 1

You can try groupby:

def f(x):
    return {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "type": "Polygon",
        "coordinates": x[["Latitude", "Longitude"]].values.tolist()
      }
    }
out = {
    "type": "FeatureCollection",
    "features": df.groupby("Id").apply(f).to_list()
}

Explanations:

1. Use groupby to group row by id : df.groupby("Id")
2. Apply on each row a custom function to build a "feature" item: df.groupby("Id").apply(f)
3. Use to_list to convert output to a list: df.groupby("Id").apply(f).to_list()
4. Integrate the output in the output dict:

out = {
    "type": "FeatureCollection",
    "features": df.groupby("Id").apply(f).to_list()
}

---

Output:

{
    'type': 'FeatureCollection', 
    'features': [
        {
            'type': 'Feature', 
            'properties': {}, 
            'geometry': {
                'type': 'Polygon', 
                'coordinates': [[-17.6582, 36.123498], [-17.658151, 36.123522], [-17.658063, 36.123576]]
            }
        }, 
        {
            'type': 'Feature', 
            'properties': {}, 
            'geometry': {
                'type': 'Polygon', 
                'coordinates': [[-11.896096, 30.388277], [-11.896096, 30.388277], [-11.896088, 30.388275]]
            }
        }
    ]
}