Create a nested JSON out of a pandas dataframe

user_14211247 0
Viewed 52
Year    temp    Name        DateTime
1950    0       De Bilt     010100
1951    1       De Bilt     010100
1950    2       De Bilt     010101
1951    3       De Bilt     010101
1950    0       Arcen       010100
1951    1       Arcen       010100

I have this dataframe (df_stations) and would like to create a JSON out of it in the following format:

{
  "De Bilt": {
    "010100": {
      "1950": {
        "temp": 0
      },
      "1951": {
        "temp": 1
      }
    },
    "010101": {
      "1950": {
        "temp": 2
      },
      "1951": {
        "temp": 3
      }
    }
  },
  "Arcen": {
    "010100": {
      "1950": {
        "temp": 0
      },
      "1951": {
        "temp": 1
      }
    },
...

However, the following code doesn't give me the right results:

def f(x):
    return (dict({k:v for k,v in zip(x.DateTime,x.Year)},**{'temp':x.temp.iloc[0]}))
(
    df_stations.groupby(['Name','DateTime','Year'])
      .apply(f)
      .groupby(level=0)
      .apply(lambda x: x.tolist())
      .to_dict()
)

Can someone help me with this? Thanks a lot!

pandas nested python json
2 Answers

Let's try double groupby:

{k: {v:d.set_index('Year').to_dict('i') for v,d in g.drop('DateTime',axis=1).groupby(g['DateTime'])}
     for k,g in df.drop('Name',axis=1).groupby(df['Name'])
}

Output:

{'Arcen':   {10100: {1950: {'temp': 0}, 1951: {'temp': 1}}},
 'De Bilt': {10100: {1950: {'temp': 0}, 1951: {'temp': 1}},
             10101: {1950: {'temp': 2}, 1951: {'temp': 3}}
            }
}

However, I do think that this is not the best JSON form due to the heavily nested data.

user_42384080 
def f(x):
    return {level1: {level2: b.xs(level1).xs(level2).to_dict() for level2 in b.xs(level1).index.levels[0] if level2 in b.xs(level1).index.get_level_values(0)} for level1 in b.index.levels[0]}

df_stations.groupby(['Name','DateTime','Year']).apply(f)[0]

will give you the result

{'Arcen': {'010100': {1950: {'temp': 0}, 1951: {'temp': 1}}},
 'De Bilt': {'010100': {1950: {'temp': 0}, 1951: {'temp': 1}},
  '010101': {1950: {'temp': 2}, 1951: {'temp': 3}}}}
user_52248810
Related