Slicing multi index DataFrame into JSON object

Question

I have a MultiIndex pd.DataFrame that I generated from a .txt that is forecast model data.

Edit:

per request I've included a small data sample to generate the DataFrame

The dict below can be used to generate a more concise version of my data DataFrame

data_dict:

{0: {('1000mb', 'gph'): 166.88, ('1000mb', 'temp'): 283.88, ('1000mb', 'dewpt'): 280.18, ('1000mb', 'dir'): 300.86, ('1000mb', 'speed'): 6.0, ('975mb', 'gph'): 377.88, ('975mb', 'temp'): 282.95, ('975mb', 'dewpt'): 278.56, ('975mb', 'dir'): 313.81, ('975mb', 'speed'): 13.0, ('950mb', 'gph'): 592.7, ('950mb', 'temp'): 280.97, ('950mb', 'dewpt'): 277.65, ('950mb', 'dir'): 319.71, ('950mb', 'speed'): 14.0, ('925mb', 'gph'): 811.98, ('925mb', 'temp'): 279.11, ('925mb', 'dewpt'): 276.72, ('925mb', 'dir'): 315.06, ('925mb', 'speed'): 13.0, ('900mb', 'gph'): 1035.98, ('900mb', 'temp'): 278.04, ('900mb', 'dewpt'): 276.56, ('900mb', 'dir'): 301.76, ('900mb', 'speed'): 10.0, ('875mb', 'gph'): 1266.98, ('875mb', 'temp'): 279.16, ('875mb', 'dewpt'): 277.68, ('875mb', 'dir'): 296.34, ('875mb', 'speed'): 8.0, ('850mb', 'gph'): 1503.98, ('850mb', 'temp'): 278.64, ('850mb', 'dewpt'): 276.81, ('850mb', 'dir'): 298.57, ('850mb', 'speed'): 9.0, ('825mb', 'gph'): 1747.98, ('825mb', 'temp'): 277.25, ('825mb', 'dewpt'): 275.69, ('825mb', 'dir'): 295.48, ('825mb', 'speed'): 11.0, ('800mb', 'gph'): 1998.26, ('800mb', 'temp'): 277.12, ('800mb', 'dewpt'): 273.89, ('800mb', 'dir'): 297.67, ('800mb', 'speed'): 13.0, ('775mb', 'gph'): 2256.98, ('775mb', 'temp'): 277.94, ('775mb', 'dewpt'): 272.48, ('775mb', 'dir'): 302.27, ('775mb', 'speed'): 15.0, ('750mb', 'gph'): 2523.86, ('750mb', 'temp'): 277.0, ('750mb', 'dewpt'): 270.96, ('750mb', 'dir'): 303.6, ('750mb', 'speed'): 16.0, ('725mb', 'gph'): 2798.8, ('725mb', 'temp'): 275.64, ('725mb', 'dewpt'): 269.21, ('725mb', 'dir'): 301.65, ('725mb', 'speed'): 19.0, ('700mb', 'gph'): 3081.8, ('700mb', 'temp'): 273.87, ('700mb', 'dewpt'): 266.74, ('700mb', 'dir'): 301.08, ('700mb', 'speed'): 23.0, ('675mb', 'gph'): 3371.96, ('675mb', 'temp'): 272.21, ('675mb', 'dewpt'): 263.85, ('675mb', 'dir'): 301.7, ('675mb', 'speed'): 25.0, ('650mb', 'gph'): 3673.08, ('650mb', 'temp'): 270.48, ('650mb', 'dewpt'): 260.75, ('650mb', 'dir'): 302.23, ('650mb', 'speed'): 28.0, ('625mb', 'gph'): 3982.04, ('625mb', 'temp'): 268.73, ('625mb', 'dewpt'): 257.99, ('625mb', 'dir'): 299.64, ('625mb', 'speed'): 29.0, ('600mb', 'gph'): 4303.62, ('600mb', 'temp'): 266.9, ('600mb', 'dewpt'): 255.05, ('600mb', 'dir'): 297.19, ('600mb', 'speed'): 30.0, ('575mb', 'gph'): 4633.92, ('575mb', 'temp'): 264.93, ('575mb', 'dewpt'): 251.89, ('575mb', 'dir'): 295.12, ('575mb', 'speed'): 31.0, ('550mb', 'gph'): 4978.9, ('550mb', 'temp'): 262.88, ('550mb', 'dewpt'): 248.45, ('550mb', 'dir'): 293.07, ('550mb', 'speed'): 32.0, ('525mb', 'gph'): 5333.54, ('525mb', 'temp'): 260.29, ('525mb', 'dewpt'): 245.08, ('525mb', 'dir'): 292.02, ('525mb', 'speed'): 33.0, ('500mb', 'gph'): 5705.5, ('500mb', 'temp'): 257.56, ('500mb', 'dewpt'): 241.47, ('500mb', 'dir'): 291.0, ('500mb', 'speed'): 35.0, ('475mb', 'gph'): 6087.4, ('475mb', 'temp'): 254.42, ('475mb', 'dewpt'): 241.58, ('475mb', 'dir'): 289.75, ('475mb', 'speed'): 34.0, ('450mb', 'gph'): 6489.96, ('450mb', 'temp'): 251.1, ('450mb', 'dewpt'): 240.94, ('450mb', 'dir'): 288.38, ('450mb', 'speed'): 33.0, ('425mb', 'gph'): 6904.36, ('425mb', 'temp'): 247.61, ('425mb', 'dewpt'): 237.23, ('425mb', 'dir'): 288.55, ('425mb', 'speed'): 34.0, ('400mb', 'gph'): 7343.9, ('400mb', 'temp'): 243.89, ('400mb', 'dewpt'): 233.3, ('400mb', 'dir'): 288.71, ('400mb', 'speed'): 36.0, ('375mb', 'gph'): 7798.15, ('375mb', 'temp'): 240.77, ('375mb', 'dewpt'): 226.58, ('375mb', 'dir'): 290.47, ('375mb', 'speed'): 43.0, ('350mb', 'gph'): 8283.76, ('350mb', 'temp'): 237.42, ('350mb', 'dewpt'): 216.64, ('350mb', 'dir'): 291.77, ('350mb', 'speed'): 52.0, ('325mb', 'gph'): 8790.03, ('325mb', 'temp'): 233.47, ('325mb', 'dewpt'): 217.34, ('325mb', 'dir'): 291.04, ('325mb', 'speed'): 57.0, ('300mb', 'gph'): 9336.86, ('300mb', 'temp'): 229.21, ('300mb', 'dewpt'): 216.5, ('300mb', 'dir'): 290.39, ('300mb', 'speed'): 62.0, ('275mb', 'gph'): 9909.55, ('275mb', 'temp'): 225.18, ('275mb', 'dewpt'): 214.54, ('275mb', 'dir'): 289.57, ('275mb', 'speed'): 62.0, ('250mb', 'gph'): 10536.86, ('250mb', 'temp'): 220.76, ('250mb', 'dewpt'): 211.98, ('250mb', 'dir'): 288.67, ('250mb', 'speed'): 62.0, ('225mb', 'gph'): 11209.65, ('225mb', 'temp'): 218.6, ('225mb', 'dewpt'): 208.69, ('225mb', 'dir'): 287.05, ('225mb', 'speed'): 62.0, ('200mb', 'gph'): 11961.78, ('200mb', 'temp'): 216.2, ('200mb', 'dewpt'): 204.77, ('200mb', 'dir'): 285.21, ('200mb', 'speed'): 62.0, ('175mb', 'gph'): 12805.89, ('175mb', 'temp'): 216.36, ('175mb', 'dewpt'): 201.73, ('175mb', 'dir'): 289.56, ('175mb', 'speed'): 56.0, ('150mb', 'gph'): 13780.35, ('150mb', 'temp'): 216.54, ('150mb', 'dewpt'): 194.67, ('150mb', 'dir'): 295.83, ('150mb', 'speed'): 49.0, ('125mb', 'gph'): 14929.06, ('125mb', 'temp'): 215.55, ('125mb', 'dewpt'): 193.0, ('125mb', 'dir'): 293.53, ('125mb', 'speed'): 41.0, ('100mb', 'gph'): 16334.96, ('100mb', 'temp'): 214.34, ('100mb', 'dewpt'): 190.78, ('100mb', 'dir'): 288.88, ('100mb', 'speed'): 30.0, ('75mb', 'gph'): 18128.15, ('75mb', 'temp'): 214.56, ('75mb', 'dewpt'): 189.31, ('75mb', 'dir'): 292.03, ('75mb', 'speed'): 22.0, ('50mb', 'gph'): 20655.52, ('50mb', 'temp'): 214.89, ('50mb', 'dewpt'): 186.13, ('50mb', 'dir'): 303.46, ('50mb', 'speed'): 12.0}, 1: {('1000mb', 'gph'): 165.16, ('1000mb', 'temp'): 283.48, ('1000mb', 'dewpt'): 280.17, ('1000mb', 'dir'): 305.02, ('1000mb', 'speed'): 6.0, ('975mb', 'gph'): 375.34, ('975mb', 'temp'): 282.49, ('975mb', 'dewpt'): 278.69, ('975mb', 'dir'): 317.14, ('975mb', 'speed'): 13.0, ('950mb', 'gph'): 590.16, ('950mb', 'temp'): 280.58, ('950mb', 'dewpt'): 277.87, ('950mb', 'dir'): 324.11, ('950mb', 'speed'): 13.0, ('925mb', 'gph'): 809.16, ('925mb', 'temp'): 278.92, ('925mb', 'dewpt'): 276.77, ('925mb', 'dir'): 313.02, ('925mb', 'speed'): 12.0, ('900mb', 'gph'): 1033.7, ('900mb', 'temp'): 278.32, ('900mb', 'dewpt'): 276.69, ('900mb', 'dir'): 291.26, ('900mb', 'speed'): 11.0, ('875mb', 'gph'): 1263.98, ('875mb', 'temp'): 279.08, ('875mb', 'dewpt'): 277.62, ('875mb', 'dir'): 281.37, ('875mb', 'speed'): 10.0, ('850mb', 'gph'): 1501.7, ('850mb', 'temp'): 278.63, ('850mb', 'dewpt'): 276.58, ('850mb', 'dir'): 283.97, ('850mb', 'speed'): 10.0, ('825mb', 'gph'): 1745.64, ('825mb', 'temp'): 277.59, ('825mb', 'dewpt'): 275.47, ('825mb', 'dir'): 289.33, ('825mb', 'speed'): 12.0, ('800mb', 'gph'): 1996.26, ('800mb', 'temp'): 277.69, ('800mb', 'dewpt'): 274.2, ('800mb', 'dir'): 296.36, ('800mb', 'speed'): 15.0, ('775mb', 'gph'): 2255.26, ('775mb', 'temp'): 278.15, ('775mb', 'dewpt'): 272.78, ('775mb', 'dir'): 297.89, ('775mb', 'speed'): 17.0, ('750mb', 'gph'): 2522.8, ('750mb', 'temp'): 276.96, ('750mb', 'dewpt'): 271.04, ('750mb', 'dir'): 294.99, ('750mb', 'speed'): 18.0, ('725mb', 'gph'): 2797.14, ('725mb', 'temp'): 275.4, ('725mb', 'dewpt'): 268.56, ('725mb', 'dir'): 294.07, ('725mb', 'speed'): 20.0, ('700mb', 'gph'): 3079.8, ('700mb', 'temp'): 273.71, ('700mb', 'dewpt'): 265.46, ('700mb', 'dir'): 296.39, ('700mb', 'speed'): 23.0, ('675mb', 'gph'): 3369.96, ('675mb', 'temp'): 272.08, ('675mb', 'dewpt'): 263.34, ('675mb', 'dir'): 300.0, ('675mb', 'speed'): 25.0, ('650mb', 'gph'): 3671.08, ('650mb', 'temp'): 270.39, ('650mb', 'dewpt'): 261.12, ('650mb', 'dir'): 303.18, ('650mb', 'speed'): 27.0, ('625mb', 'gph'): 3979.72, ('625mb', 'temp'): 268.74, ('625mb', 'dewpt'): 256.77, ('625mb', 'dir'): 301.49, ('625mb', 'speed'): 29.0, ('600mb', 'gph'): 4300.96, ('600mb', 'temp'): 267.02, ('600mb', 'dewpt'): 251.51, ('600mb', 'dir'): 299.96, ('600mb', 'speed'): 31.0, ('575mb', 'gph'): 4631.58, ('575mb', 'temp'): 264.99, ('575mb', 'dewpt'): 249.31, ('575mb', 'dir'): 297.06, ('575mb', 'speed'): 32.0, ('550mb', 'gph'): 4976.9, ('550mb', 'temp'): 262.87, ('550mb', 'dewpt'): 247.0, ('550mb', 'dir'): 294.2, ('550mb', 'speed'): 33.0, ('525mb', 'gph'): 5331.25, ('525mb', 'temp'): 260.22, ('525mb', 'dewpt'): 245.18, ('525mb', 'dir'): 293.26, ('525mb', 'speed'): 33.0, ('500mb', 'gph'): 5702.9, ('500mb', 'temp'): 257.43, ('500mb', 'dewpt'): 243.23, ('500mb', 'dir'): 292.32, ('500mb', 'speed'): 34.0, ('475mb', 'gph'): 6085.06, ('475mb', 'temp'): 254.43, ('475mb', 'dewpt'): 241.41, ('475mb', 'dir'): 291.09, ('475mb', 'speed'): 34.0, ('450mb', 'gph'): 6487.9, ('450mb', 'temp'): 251.26, ('450mb', 'dewpt'): 239.39, ('450mb', 'dir'): 289.79, ('450mb', 'speed'): 34.0, ('425mb', 'gph'): 6902.76, ('425mb', 'temp'): 248.02, ('425mb', 'dewpt'): 233.63, ('425mb', 'dir'): 293.14, ('425mb', 'speed'): 38.0, ('400mb', 'gph'): 7342.78, ('400mb', 'temp'): 244.58, ('400mb', 'dewpt'): 226.55, ('400mb', 'dir'): 295.98, ('400mb', 'speed'): 43.0, ('375mb', 'gph'): 7798.48, ('375mb', 'temp'): 241.27, ('375mb', 'dewpt'): 223.77, ('375mb', 'dir'): 295.9, ('375mb', 'speed'): 49.0, ('350mb', 'gph'): 8285.64, ('350mb', 'temp'): 237.73, ('350mb', 'dewpt'): 220.79, ('350mb', 'dir'): 295.83, ('350mb', 'speed'): 55.0, ('325mb', 'gph'): 8791.36, ('325mb', 'temp'): 233.48, ('325mb', 'dewpt'): 220.96, ('325mb', 'dir'): 292.78, ('325mb', 'speed'): 57.0, ('300mb', 'gph'): 9337.58, ('300mb', 'temp'): 228.89, ('300mb', 'dewpt'): 219.65, ('300mb', 'dir'): 289.8, ('300mb', 'speed'): 60.0, ('275mb', 'gph'): 9909.7, ('275mb', 'temp'): 225.04, ('275mb', 'dewpt'): 216.01, ('275mb', 'dir'): 288.82, ('275mb', 'speed'): 60.0, ('250mb', 'gph'): 10536.4, ('250mb', 'temp'): 220.82, ('250mb', 'dewpt'): 212.03, ('250mb', 'dir'): 287.75, ('250mb', 'speed'): 60.0, ('225mb', 'gph'): 11207.71, ('225mb', 'temp'): 218.23, ('225mb', 'dewpt'): 208.96, ('225mb', 'dir'): 285.43, ('225mb', 'speed'): 61.0, ('200mb', 'gph'): 11958.17, ('200mb', 'temp'): 215.33, ('200mb', 'dewpt'): 205.5, ('200mb', 'dir'): 282.93, ('200mb', 'speed'): 63.0, ('175mb', 'gph'): 12802.98, ('175mb', 'temp'): 216.25, ('175mb', 'dewpt'): 202.77, ('175mb', 'dir'): 287.48, ('175mb', 'speed'): 56.0, ('150mb', 'gph'): 13778.24, ('150mb', 'temp'): 217.31, ('150mb', 'dewpt'): 194.46, ('150mb', 'dir'): 294.22, ('150mb', 'speed'): 49.0, ('125mb', 'gph'): 14926.09, ('125mb', 'temp'): 215.54, ('125mb', 'dewpt'): 192.81, ('125mb', 'dir'): 292.33, ('125mb', 'speed'): 42.0, ('100mb', 'gph'): 16330.96, ('100mb', 'temp'): 213.37, ('100mb', 'dewpt'): 190.79, ('100mb', 'dir'): 288.86, ('100mb', 'speed'): 33.0, ('75mb', 'gph'): 18120.98, ('75mb', 'temp'): 214.0, ('75mb', 'dewpt'): 189.51, ('75mb', 'dir'): 291.2, ('75mb', 'speed'): 24.0, ('50mb', 'gph'): 20643.88, ('50mb', 'temp'): 214.89, ('50mb', 'dewpt'): 186.35, ('50mb', 'dir'): 300.53, ('50mb', 'speed'): 12.0}}

In the data provided above the I dropped the unused values as such...

PROPS_2_DROP = ['gph_dval', 'temp_dval', 'clouds', 'fl-vis', 'icing_type', 'cwmr',
                'rh', 'theta-e', 'parcel_temp', 'vvs', 'mixing_ratio', 'turbulence']
midf = self.DataFrame[range(0, 2)].drop('sfc').drop(labels=PROPS_2_DROP, level='props')
midf.to_dict(orient='dict' )

data_dict -> MultiIndex.DataFrame

midf = pd.DataFrame(data_dict)

DataFrame

                       0        1        2        3        4        5    ...      139      140      141      142      143      144
lvl  props                                                               ...                                                      
sfc  mean_slp      1019.73  1019.50  1019.19  1018.83  1019.03  1019.02  ...   995.10   995.44   995.79   995.99   996.20   996.41
     altimeter       30.10    30.09    30.09    30.07    30.08    30.08  ...    29.37    29.38    29.39    29.40    29.40    29.41
     press_alt      285.78   291.39   299.50   309.49   304.92   305.71  ...   963.21   953.97   944.74   939.51   934.28   929.05
     density_alt    -55.22   -88.18  -135.38  -186.09  -235.12  -262.16  ...  1265.79  1224.16  1182.28  1137.12  1091.92  1046.88
     2_m_agl_tmp    283.50   283.17   282.70   282.18   281.82   281.59  ...   287.05   286.84   286.62   286.34   286.06   285.78
...                    ...      ...      ...      ...      ...      ...  ...      ...      ...      ...      ...      ...      ...
50mb mixing_ratio     0.00     0.00     0.00     0.00     0.00     0.00  ...     0.00     0.00     0.00     0.00     0.00     0.00
     cwmr             0.00     0.00     0.00     0.00     0.00     0.00  ...     0.00     0.00     0.00     0.00     0.00     0.00
     icing_type      -1.00    -1.00    -1.00    -1.00    -1.00    -1.00  ...    -1.00    -1.00    -1.00    -1.00    -1.00    -1.00
     turbulence       0.00     0.00     0.00     0.00     0.00     0.00  ...     0.00     0.00     0.00     0.00     0.00     0.00
     vvs              0.00     0.00    -0.00    -0.00    -0.00     0.00  ...    -0.00    -0.00    -0.00    -0.00    -0.00     0.00

[804 rows x 145 columns]

There are various methods for working with the forecast DataFrame. The one I've been working on tonight slices and structures data into a JSON object that is sent to a TypeScript application.

types

type Dataset = Datum[][];
type Datums = Datum[];
interface Datum {
    press: number;
    hght: number;
    temp: number;
    dwpt: number;
    wdir: number;
    wspd: number;
};

method

DICT_KEYS = ['temp', 'dwpt', 'wdir', 'wspd', 'hght', 'press']
ABSOLUTE_ZERO = -273.15
def feature_skewt_dataset(self, start=0, stop=30) -> Dict[str, List[List[Dict[str, int]]]]:
    # slice multi-index-dataframe time by argument range
    midf = self.DataFrame[range(start, stop)]

    # kelvin to celcius
    temperature = midf.loc[(slice(None), "temp"), :] + ABSOLUTE_ZERO # - 273.15
    dewpoint = midf.loc[(slice(None), "dewpt"), :] + ABSOLUTE_ZERO #- 273.15
    wind_speed = midf.loc[(slice(None), "speed"), :]
    wind_direction = midf.loc[(slice(None), "dir"), :]
    geopotential_height = midf.loc[(slice(None), "gph"), :]
    # milibars = self._make_mbars(39)
    # milibars = midf.droplevel(1).index.unique().str.strip('mb')
    milibars = midf.index.get_level_values('lvl').unique().str.rstrip('mb')

    # STEP 3 zip the keys -> stack
    dataset = [[dict(zip(DICT_KEYS, stack))for stack in np.column_stack([
        # STEP 2 ->  slice the properties by the time index
        temperature.loc[:, time_index],
        dewpoint.loc[:, time_index],
        wind_direction.loc[:, time_index],
        wind_speed.loc[:, time_index],
        geopotential_height.loc[:, time_index],
        milibars
    # STEP 1 -> iterate the time_index column index
    ]).astype(int)] for time_index in midf.columns]

    return {'dataset': dataset}

Answer 1

It's good that you provided data_dict. In my suggested code, I had first dumped this dataframe to a pickle to be able to get it back without fuss and without having to include it in the source code verbatim.

You initially didn't define your pressure quantity; _make_mbars was missing. You now say that it's effectively

midf.droplevel(1).index.unique( ).str.strip('mb')

but this is non-ideal for a collection of reasons:

• you don't actually want to get unique values, but instead want to associate each value with a row;
• you should use rstrip instead of strip;
• you need to cast to integers; and
• rather than droplevel, for this use you should just use get_level_values.

Likewise, you failed to provide the whole class so I ignored self and just accepted a starting dataframe as a function parameter.

You're over-abbreviating your column and variable names. Just write the names in plain English. You offered two reasons for the abbreviated names, the first being that you need to adhere to a NOAA format. Externally that's fine; but internally you should use sane names and not the names you're required to use in external serialised formats.

The second reason you offered for over-abbreviation is

they are being called in a React d3 application [...] frequently

Cutting a couple of characters in your field names is premature and mis-directed optimisation. There are plenty of opportunities for actual optimisation elsewhere.

Put 273.15 into a constant rather than leaving it as a magic number. When you subtract this number, it's probably a good idea to round(); I was conservative and rounded to 12 decimals to cut the error. This is only possible because your real data stop at two decimals.

Most of your difficulty comes from the fact that your data frame is effectively rotated, and needs to be rotated again to be sane. In Pandas terminology this rotation is called stacking. You need to unstack your property names to columns, and you need to stack your time columns to indices. Once this is done, the data are much, much easier to manipulate with (nearly) stock Pandas functions.

Suggested

from pprint import pprint

import pandas as pd

ABSOLUTE_ZERO = -273.15


def kelvin_to_celsius(temperature: pd.Series) -> pd.Series:
    return (temperature + ABSOLUTE_ZERO).round(decimals=12)


def sanitise(df: pd.DataFrame) -> pd.DataFrame:
    stacked: pd.DataFrame = df.stack().unstack(level=1)
    stacked.rename(
        columns={
            'dewpt': 'dewpoint',
            'gph': 'height',
            'dir': 'wind_direction',
            'speed': 'wind_speed',
            'temp': 'temperature',
        },
        inplace=True,
    )

    stacked.temperature = kelvin_to_celsius(stacked.temperature)
    stacked.dewpoint = kelvin_to_celsius(stacked.dewpoint)

    stacked['pressure'] = (
        stacked.index.get_level_values(level=0)
        .str.rstrip('mb').astype(int)
    )
    return stacked.droplevel(0)


def feature_skewt_dataset(
    midf: pd.DataFrame,
    start_time: int = 0,
    stop_time: int = 30,
) -> dict[str, list]:
    # Since the index only contains time and is non-unique, we cannot use
    # to_dict(orient='index')

    return {
        'dataset': [
            midf.loc[time_index].to_dict(orient='records')
            for time_index in range(start_time, stop_time)
        ]
    }


def test() -> None:
    insane = pd.read_pickle('midf.pickle')
    sane = sanitise(insane)
    dataset = feature_skewt_dataset(sane, stop_time=2)
    pprint(dataset)


if __name__ == '__main__':
    test()