kandi background
Explore Kits

pandas | powerful data analysis / manipulation library

 by   pandas-dev Python Version: v1.4.1 License: BSD-3-Clause

 by   pandas-dev Python Version: v1.4.1 License: BSD-3-Clause

Download this library from

kandi X-RAY | pandas Summary

pandas is a Python library typically used in Data Science, Pandas applications. pandas has build file available, it has a Permissive License and it has high support. However pandas has 2481 bugs and it has 4 vulnerabilities. You can install using 'pip install pandas' or download it from GitHub, PyPI.
pandas is a Python package that provides fast, flexible, and expressive data structures designed to make working with "relational" or "labeled" data both easy and intuitive. It aims to be the fundamental high-level building block for doing practical, real world data analysis in Python. Additionally, it has the broader goal of becoming the most powerful and flexible open source data analysis / manipulation tool available in any language. It is already well on its way towards this goal.
Support
Support
Quality
Quality
Security
Security
License
License
Reuse
Reuse

kandi-support Support

  • pandas has a highly active ecosystem.
  • It has 33259 star(s) with 14245 fork(s). There are 1103 watchers for this library.
  • There were 5 major release(s) in the last 12 months.
  • There are 3338 open issues and 18946 have been closed. On average issues are closed in 477 days. There are 117 open pull requests and 0 closed requests.
  • It has a positive sentiment in the developer community.
  • The latest version of pandas is v1.4.1
pandas Support
Best in #Python
Average in #Python
pandas Support
Best in #Python
Average in #Python

quality kandi Quality

  • pandas has 2481 bugs (7 blocker, 0 critical, 2300 major, 174 minor) and 3690 code smells.
pandas Quality
Best in #Python
Average in #Python
pandas Quality
Best in #Python
Average in #Python

securitySecurity

  • pandas has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
  • pandas code analysis shows 4 unresolved vulnerabilities (4 blocker, 0 critical, 0 major, 0 minor).
  • There are 22 security hotspots that need review.
pandas Security
Best in #Python
Average in #Python
pandas Security
Best in #Python
Average in #Python

license License

  • pandas is licensed under the BSD-3-Clause License. This license is Permissive.
  • Permissive licenses have the least restrictions, and you can use them in most projects.
pandas License
Best in #Python
Average in #Python
pandas License
Best in #Python
Average in #Python

buildReuse

  • pandas releases are available to install and integrate.
  • Deployable package is available in PyPI.
  • Build file is available. You can build the component from source.
  • Installation instructions, examples and code snippets are available.
  • pandas saves you 667226 person hours of effort in developing the same functionality from scratch.
  • It has 330153 lines of code, 22612 functions and 1317 files.
  • It has high code complexity. Code complexity directly impacts maintainability of the code.
pandas Reuse
Best in #Python
Average in #Python
pandas Reuse
Best in #Python
Average in #Python
Top functions reviewed by kandi - BETA

kandi has reviewed pandas and discovered the below as its top functions. This is intended to give you an instant insight into pandas implemented functionality, and help decide if they suit your requirements.

  • Write the table to a LaTeX file .
  • Convert argument to datetime index .
  • Add numeric operations .
  • Normalize JSON data .
  • Read data from a JSON file .
  • Convert wide to long .
  • Merge two DataFrames .
  • Read data from an XML file .
  • Create a loc indexer .
  • Cut an array .

pandas Key Features

Easy handling of missing data (represented as NaN, NA, or NaT) in floating point as well as non-floating point data

Size mutability: columns can be inserted and deleted from DataFrame and higher dimensional objects

Automatic and explicit data alignment: objects can be explicitly aligned to a set of labels, or the user can simply ignore the labels and let Series, DataFrame, etc. automatically align the data for you in computations

Powerful, flexible group by functionality to perform split-apply-combine operations on data sets, for both aggregating and transforming data

Make it easy to convert ragged, differently-indexed data in other Python and NumPy data structures into DataFrame objects

Intelligent label-based slicing, fancy indexing, and subsetting of large data sets

Intuitive merging and joining data sets

Flexible reshaping and pivoting of data sets

Hierarchical labeling of axes (possible to have multiple labels per tick)

Robust IO tools for loading data from flat files (CSV and delimited), Excel files, databases, and saving/loading data from the ultrafast HDF5 format

Time series-specific functionality: date range generation and frequency conversion, moving window statistics, date shifting and lagging

Where to get it

copy iconCopydownload iconDownload
# conda
conda install pandas

Installation from sources

copy iconCopydownload iconDownload
pip install cython

Installing scipy and scikit-learn on apple m1

copy iconCopydownload iconDownload
# SciPy:
python -m pip install --no-cache --no-use-pep517 pythran cython pybind11 gast"==0.4.0"
pyenv rehash
python -m pip install --no-cache --no-binary :all: --no-use-pep517 scipy"==1.7.1"

# Scikit-Learn
python -m pip install --no-use-pep517 scikit-learn"==0.24.2"
brew install openblas openssl@1.1 pkg-config pyenv pyenv-virtualenv
python -m pip install numpy==1.19.5
-----------------------
# SciPy:
python -m pip install --no-cache --no-use-pep517 pythran cython pybind11 gast"==0.4.0"
pyenv rehash
python -m pip install --no-cache --no-binary :all: --no-use-pep517 scipy"==1.7.1"

# Scikit-Learn
python -m pip install --no-use-pep517 scikit-learn"==0.24.2"
brew install openblas openssl@1.1 pkg-config pyenv pyenv-virtualenv
python -m pip install numpy==1.19.5
-----------------------


    >> /opt/homebrew/bin/brew install openblas
    >> export OPENBLAS=$(/opt/homebrew/bin/brew --prefix openblas)
    >> export CFLAGS="-falign-functions=8 ${CFLAGS}"
    >> git clone https://github.com/scipy/scipy.git
    >> cd scipy
    >> git submodule update --init
    >> /opt/homebrew/bin/pip3 install .
    >> /opt/homebrew/bin/pip3 install scikit-learn

-----------------------
RUN OPENBLAS="/opt/homebrew/opt/openblas" CFLAGS="-falign-functions=8 ${CFLAGS}" pip3 install scipy
RUN OPENBLAS="/opt/homebrew/opt/openblas" CFLAGS="-falign-functions=8 ${CFLAGS}" pip3 install scikit-learn
-----------------------
brew install openblas
export OPENBLAS=$(/opt/homebrew/bin/brew --prefix openblas)
export CFLAGS="-falign-functions=8 ${CFLAGS}"
# ^ no need to add to .zshrc, just doing this once.
pip install scikit-learn # ==0.24.1 if you want
Building wheels for collected packages: scikit-learn
  Building wheel for scikit-learn (pyproject.toml) ... done
  Created wheel for scikit-learn: filename=scikit_learn-1.0.1-cp38-cp38-macosx_12_0_arm64.whl size=6364030 sha256=0b0cc9a21af775e0c8077ee71698ff62da05ab62efc914c5c15cd4bf97867b31
Successfully built scikit-learn
Installing collected packages: scipy, scikit-learn
Successfully installed scikit-learn-1.0.1 scipy-1.7.3
Collecting click>=7.0
  Downloading click-8.0.3-py3-none-any.whl
Collecting grpcio>=1.28.1
  Downloading grpcio-1.42.0.tar.gz (21.3 MB)
     |████████████████████████████████| 21.3 MB 12.7 MB/s
  Preparing metadata (setup.py) ... done

## later in the process it installs using setuptools 
Running setup.py install for grpcio ... done
-----------------------
brew install openblas
export OPENBLAS=$(/opt/homebrew/bin/brew --prefix openblas)
export CFLAGS="-falign-functions=8 ${CFLAGS}"
# ^ no need to add to .zshrc, just doing this once.
pip install scikit-learn # ==0.24.1 if you want
Building wheels for collected packages: scikit-learn
  Building wheel for scikit-learn (pyproject.toml) ... done
  Created wheel for scikit-learn: filename=scikit_learn-1.0.1-cp38-cp38-macosx_12_0_arm64.whl size=6364030 sha256=0b0cc9a21af775e0c8077ee71698ff62da05ab62efc914c5c15cd4bf97867b31
Successfully built scikit-learn
Installing collected packages: scipy, scikit-learn
Successfully installed scikit-learn-1.0.1 scipy-1.7.3
Collecting click>=7.0
  Downloading click-8.0.3-py3-none-any.whl
Collecting grpcio>=1.28.1
  Downloading grpcio-1.42.0.tar.gz (21.3 MB)
     |████████████████████████████████| 21.3 MB 12.7 MB/s
  Preparing metadata (setup.py) ... done

## later in the process it installs using setuptools 
Running setup.py install for grpcio ... done
-----------------------
brew install openblas
export OPENBLAS=$(/opt/homebrew/bin/brew --prefix openblas)
export CFLAGS="-falign-functions=8 ${CFLAGS}"
# ^ no need to add to .zshrc, just doing this once.
pip install scikit-learn # ==0.24.1 if you want
Building wheels for collected packages: scikit-learn
  Building wheel for scikit-learn (pyproject.toml) ... done
  Created wheel for scikit-learn: filename=scikit_learn-1.0.1-cp38-cp38-macosx_12_0_arm64.whl size=6364030 sha256=0b0cc9a21af775e0c8077ee71698ff62da05ab62efc914c5c15cd4bf97867b31
Successfully built scikit-learn
Installing collected packages: scipy, scikit-learn
Successfully installed scikit-learn-1.0.1 scipy-1.7.3
Collecting click>=7.0
  Downloading click-8.0.3-py3-none-any.whl
Collecting grpcio>=1.28.1
  Downloading grpcio-1.42.0.tar.gz (21.3 MB)
     |████████████████████████████████| 21.3 MB 12.7 MB/s
  Preparing metadata (setup.py) ... done

## later in the process it installs using setuptools 
Running setup.py install for grpcio ... done
-----------------------
brew install openblas
export OPENBLAS=$(/opt/homebrew/bin/brew --prefix openblas)
export CFLAGS="-falign-functions=8 ${CFLAGS}"
# ^ no need to add to .zshrc, just doing this once.
pip install scikit-learn # ==0.24.1 if you want
Building wheels for collected packages: scikit-learn
  Building wheel for scikit-learn (pyproject.toml) ... done
  Created wheel for scikit-learn: filename=scikit_learn-1.0.1-cp38-cp38-macosx_12_0_arm64.whl size=6364030 sha256=0b0cc9a21af775e0c8077ee71698ff62da05ab62efc914c5c15cd4bf97867b31
Successfully built scikit-learn
Installing collected packages: scipy, scikit-learn
Successfully installed scikit-learn-1.0.1 scipy-1.7.3
Collecting click>=7.0
  Downloading click-8.0.3-py3-none-any.whl
Collecting grpcio>=1.28.1
  Downloading grpcio-1.42.0.tar.gz (21.3 MB)
     |████████████████████████████████| 21.3 MB 12.7 MB/s
  Preparing metadata (setup.py) ... done

## later in the process it installs using setuptools 
Running setup.py install for grpcio ... done
-----------------------
Python 3.9.10 (main, Jan 15 2022, 11:40:53) 
[Clang 13.0.0 (clang-1300.0.29.3)] on darwin
pip 22.0.3
brew install openblas gfortran
OPENBLAS="$(brew --prefix openblas)" pip install numpy==1.19.3
OPENBLAS="$(brew --prefix openblas)" pip install scipy==1.7.2
pip install cython
brew install libomp
export CC=/usr/bin/clang
export CXX=/usr/bin/clang++
export CPPFLAGS="$CPPFLAGS -Xpreprocessor -fopenmp"
export CFLAGS="$CFLAGS -I/opt/homebrew/Cellar/libomp/13.0.1/include"
export CXXFLAGS="$CXXFLAGS -I/opt/homebrew/Cellar/libomp/13.0.1/include"
export LDFLAGS="$LDFLAGS -L/opt/homebrew/Cellar/libomp/13.0.1/lib -lomp"
export DYLD_LIBRARY_PATH=/opt/homebrew/Cellar/libomp/13.0.1/lib
pip install scikit-learn==0.21.3
-----------------------
Python 3.9.10 (main, Jan 15 2022, 11:40:53) 
[Clang 13.0.0 (clang-1300.0.29.3)] on darwin
pip 22.0.3
brew install openblas gfortran
OPENBLAS="$(brew --prefix openblas)" pip install numpy==1.19.3
OPENBLAS="$(brew --prefix openblas)" pip install scipy==1.7.2
pip install cython
brew install libomp
export CC=/usr/bin/clang
export CXX=/usr/bin/clang++
export CPPFLAGS="$CPPFLAGS -Xpreprocessor -fopenmp"
export CFLAGS="$CFLAGS -I/opt/homebrew/Cellar/libomp/13.0.1/include"
export CXXFLAGS="$CXXFLAGS -I/opt/homebrew/Cellar/libomp/13.0.1/include"
export LDFLAGS="$LDFLAGS -L/opt/homebrew/Cellar/libomp/13.0.1/lib -lomp"
export DYLD_LIBRARY_PATH=/opt/homebrew/Cellar/libomp/13.0.1/lib
pip install scikit-learn==0.21.3
-----------------------
Python 3.9.10 (main, Jan 15 2022, 11:40:53) 
[Clang 13.0.0 (clang-1300.0.29.3)] on darwin
pip 22.0.3
brew install openblas gfortran
OPENBLAS="$(brew --prefix openblas)" pip install numpy==1.19.3
OPENBLAS="$(brew --prefix openblas)" pip install scipy==1.7.2
pip install cython
brew install libomp
export CC=/usr/bin/clang
export CXX=/usr/bin/clang++
export CPPFLAGS="$CPPFLAGS -Xpreprocessor -fopenmp"
export CFLAGS="$CFLAGS -I/opt/homebrew/Cellar/libomp/13.0.1/include"
export CXXFLAGS="$CXXFLAGS -I/opt/homebrew/Cellar/libomp/13.0.1/include"
export LDFLAGS="$LDFLAGS -L/opt/homebrew/Cellar/libomp/13.0.1/lib -lomp"
export DYLD_LIBRARY_PATH=/opt/homebrew/Cellar/libomp/13.0.1/lib
pip install scikit-learn==0.21.3
-----------------------
conda install --channel=conda-forge scikit-learn

Mapping complex JSON to Pandas Dataframe

copy iconCopydownload iconDownload
def process_json(api_response): 
    
    def get_column_values(df):    
        return pd.concat([df, pd.json_normalize(df.pop('columns')).set_axis(df.index)], axis=1)
    
    def expand_children(df):
        if len(df.index) > 1:
            df['children'] = df['children'].fillna('').apply(lambda x: None if len(x) == 0 else x)
        df_children = df.pop('children').dropna().explode()
        if len(df_children.index) == 0: # return df if no children to append
            return df.index.names, df
        df_children = pd.json_normalize(df_children, max_level=0).set_axis(df_children.index).set_index('name', append=True)
        df_children = get_column_values(df_children)
        idx_names = list(df_children.index.names)
        idx_names[-1] = idx_names[-1] + '_' + str(len(idx_names))
        df[idx_names[-1]] = None
        return idx_names, pd.concat([df.set_index(idx_names[-1], append=True), df_children], axis=0)    
    
    columns_dict = pd.DataFrame(api_response['meta']['columns']).set_index('key').to_dict(orient='index') # save column definitions
    df = pd.DataFrame(api_response['data']['attributes']['total']['children']).set_index('name') # get initial dataframe     
    df = get_column_values(df) # get columns for initial level
    
    # expand children
    while 'children' in df.columns:
        idx_names, df = expand_children(df)
    
    # reorder/replace column headers and sort index
    df = (df.loc[:, [x for x in df.columns if x not in columns_dict.keys()] + list(columns_dict.keys())]
          .rename(columns={k:v['display_name'] for k,v in columns_dict.items()})
          .sort_index(na_position='first').reset_index())
    
    #collapse "name" columns (careful of potential duplicate rows)  
    for col in idx_names[::-1]:
            df[idx_names[-1]] = df[idx_names[-1]].fillna(df[col])
    df = df.rename(columns={'name': 'portfolio', idx_names[-1]: 'name'}).drop(columns=idx_names[1:-1])      
    
    return df
process_json(api_response)
54.2 ms ± 7.12 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

unpack_response(api_response) # iterrows
84.3 ms ± 9.04 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
-----------------------
def process_json(api_response): 
    
    def get_column_values(df):    
        return pd.concat([df, pd.json_normalize(df.pop('columns')).set_axis(df.index)], axis=1)
    
    def expand_children(df):
        if len(df.index) > 1:
            df['children'] = df['children'].fillna('').apply(lambda x: None if len(x) == 0 else x)
        df_children = df.pop('children').dropna().explode()
        if len(df_children.index) == 0: # return df if no children to append
            return df.index.names, df
        df_children = pd.json_normalize(df_children, max_level=0).set_axis(df_children.index).set_index('name', append=True)
        df_children = get_column_values(df_children)
        idx_names = list(df_children.index.names)
        idx_names[-1] = idx_names[-1] + '_' + str(len(idx_names))
        df[idx_names[-1]] = None
        return idx_names, pd.concat([df.set_index(idx_names[-1], append=True), df_children], axis=0)    
    
    columns_dict = pd.DataFrame(api_response['meta']['columns']).set_index('key').to_dict(orient='index') # save column definitions
    df = pd.DataFrame(api_response['data']['attributes']['total']['children']).set_index('name') # get initial dataframe     
    df = get_column_values(df) # get columns for initial level
    
    # expand children
    while 'children' in df.columns:
        idx_names, df = expand_children(df)
    
    # reorder/replace column headers and sort index
    df = (df.loc[:, [x for x in df.columns if x not in columns_dict.keys()] + list(columns_dict.keys())]
          .rename(columns={k:v['display_name'] for k,v in columns_dict.items()})
          .sort_index(na_position='first').reset_index())
    
    #collapse "name" columns (careful of potential duplicate rows)  
    for col in idx_names[::-1]:
            df[idx_names[-1]] = df[idx_names[-1]].fillna(df[col])
    df = df.rename(columns={'name': 'portfolio', idx_names[-1]: 'name'}).drop(columns=idx_names[1:-1])      
    
    return df
process_json(api_response)
54.2 ms ± 7.12 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

unpack_response(api_response) # iterrows
84.3 ms ± 9.04 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
-----------------------
pip install --upgrade jsonpath-ng
import json
import jsonpath_ng as jp
import pandas as pd

def unpack_response(r):
    # Create a dataframe from extracted data
    expr = jp.parse('$..children.[*]')
    data = [{'full_path': str(m.full_path), **m.value} for m in expr.find(r)]
    df = pd.json_normalize(data).sort_values('full_path', ignore_index=True)

    # Append a portfolio column
    df['portfolio'] = df.loc[df.full_path.str.contains(r'total\.children\.\[\d+]$'), 'name']
    df['portfolio'].fillna(method='ffill', inplace=True)

    # Deal with columns
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['full_path', 'portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols]

    return df

# Load the sample data from file
# with open('api_response_2022-02-13.json', 'r') as f:
#     api_response = json.load(f)

# Load the sample data from string
api_response = json.loads('{"meta": {"columns": [{"key": "value", "display_name": "Adjusted Value (No Div, USD)", "output_type": "Number", "currency": "USD"}, {"key": "time_weighted_return", "display_name": "Current Quarter TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "time_weighted_return_2", "display_name": "YTD TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "_custom_twr_audit_note_911328", "display_name": "TWR Audit Note", "output_type": "Word"}], "groupings": [{"key": "_custom_name_747205", "display_name": "* Reporting Client Name"}, {"key": "_custom_new_entity_group_453577", "display_name": "NEW Entity Group"}, {"key": "_custom_level_2_624287", "display_name": "* Level 2"}, {"key": "legal_entity", "display_name": "Legal Entity"}]}, "data": {"type": "portfolio_views", "attributes": {"total": {"name": "Total", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Falconer Family", "grouping": "_custom_name_747205", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Wealth Bucket A", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.045960317420568164, "time_weighted_return_2": -0.045960317420568164, "_custom_twr_audit_note_911328": null, "value": 13264448.506587159}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": [{"entity_id": 10604454, "name": "HUDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": [{"entity_id": 10604454, "name": "HUDG Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": [{"entity_id": 10604454, "name": "HKDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": [{"entity_id": 10604454, "name": "HUDW Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": [{"entity_id": 10604454, "name": "HJDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": [{"entity_id": 10604454, "name": "HADJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": []}]}]}, {"name": "Wealth Bucket B", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.04769870075659244, "time_weighted_return_2": -0.04769870075659244, "_custom_twr_audit_note_911328": null, "value": 9868044.398519998}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 2.8632718065191298e-05, "time_weighted_return_2": 2.8632718065191298e-05, "_custom_twr_audit_note_911328": null, "value": 10234.94}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 2.82679297198829e-05, "time_weighted_return_2": 2.82679297198829e-05, "_custom_twr_audit_note_911328": null, "value": 244.28}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 4.9373572795108345e-05, "time_weighted_return_2": 4.9373572795108345e-05, "_custom_twr_audit_note_911328": null, "value": 5081.08}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.609603754315074e-06, "time_weighted_return_2": 6.609603754315074e-06, "_custom_twr_audit_note_911328": null, "value": 1523.62}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": 1.0999769004760296e-05, "time_weighted_return_2": 1.0999769004760296e-05, "_custom_twr_audit_note_911328": null, "value": 1828.9}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.466673995619843e-06, "time_weighted_return_2": 6.466673995619843e-06, "_custom_twr_audit_note_911328": null, "value": 1557.06}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.024645947842438676, "time_weighted_return_2": -0.024645947842438676, "_custom_twr_audit_note_911328": null, "value": 674052.31962}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.043304004172576405, "time_weighted_return_2": -0.043304004172576405, "_custom_twr_audit_note_911328": null, "value": 52800.96}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.022408434778798836, "time_weighted_return_2": -0.022408434778798836, "_custom_twr_audit_note_911328": null, "value": 599594.11962}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.03037038746301135, "time_weighted_return_2": -0.03037038746301135, "_custom_twr_audit_note_911328": null, "value": 114472.69744}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370390035505124, "time_weighted_return_2": -0.030370390035505124, "_custom_twr_audit_note_911328": null, "value": 114472.68744000001}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 0, "time_weighted_return_2": 0, "_custom_twr_audit_note_911328": null, "value": 0.01}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.06604362523792162, "time_weighted_return_2": -0.06604362523792162, "_custom_twr_audit_note_911328": null, "value": 5722529.229999997}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06154960593668424, "time_weighted_return_2": -0.06154960593668424, "_custom_twr_audit_note_911328": null, "value": 1191838.9399999995}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06750460387418267, "time_weighted_return_2": -0.06750460387418267, "_custom_twr_audit_note_911328": null, "value": 4416618.520000002}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 38190.33}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.017118805423322003, "time_weighted_return_2": -0.017118805423322003, "_custom_twr_audit_note_911328": null, "value": 3148495.0914600003}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.015251157805867277, "time_weighted_return_2": -0.015251157805867277, "_custom_twr_audit_note_911328": null, "value": 800493.06146}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01739609576880241, "time_weighted_return_2": -0.01739609576880241, "_custom_twr_audit_note_911328": null, "value": 2215511.2700000005}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02085132265594647, "time_weighted_return_2": -0.02085132265594647, "_custom_twr_audit_note_911328": null, "value": 44031.21}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02089393244695803, "time_weighted_return_2": -0.02089393244695803, "_custom_twr_audit_note_911328": null, "value": 44394.159999999996}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.020607507059866248, "time_weighted_return_2": -0.020607507059866248, "_custom_twr_audit_note_911328": null, "value": 44065.39000000001}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.0014710489231547497, "time_weighted_return_2": -0.0014710489231547497, "_custom_twr_audit_note_911328": null, "value": 198260.12}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.0014477244560456848, "time_weighted_return_2": -0.0014477244560456848, "_custom_twr_audit_note_911328": null, "value": 44612.33}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.001477821083437858, "time_weighted_return_2": -0.001477821083437858, "_custom_twr_audit_note_911328": null, "value": 153647.78999999998}, "children": []}]}]}]}]}}, "included": []}}')

df = unpack_response(api_response)
print(df.iloc[:5:,1:])
print(df.iloc[:10,:3])
expr = jp.parse('$..children.[*]')
def unpack_response(r):
    df = pd.DataFrame()
    for _, r1 in pd.json_normalize(r, ['data', 'attributes', 'total', 'children']).iterrows(): 
        r1['portfolio'] = r1['name']
        df = df.append(r1)
        for _, r2 in pd.json_normalize(r1.children).iterrows(): 
            df = df.append(r2)
            for _, r3 in pd.json_normalize(r2.children).iterrows(): 
                df = df.append(r3).append(pd.json_normalize(r3.children))
    df['portfolio'].fillna(method='ffill', inplace=True)
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols].reset_index(drop=True)
    return df
-----------------------
pip install --upgrade jsonpath-ng
import json
import jsonpath_ng as jp
import pandas as pd

def unpack_response(r):
    # Create a dataframe from extracted data
    expr = jp.parse('$..children.[*]')
    data = [{'full_path': str(m.full_path), **m.value} for m in expr.find(r)]
    df = pd.json_normalize(data).sort_values('full_path', ignore_index=True)

    # Append a portfolio column
    df['portfolio'] = df.loc[df.full_path.str.contains(r'total\.children\.\[\d+]$'), 'name']
    df['portfolio'].fillna(method='ffill', inplace=True)

    # Deal with columns
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['full_path', 'portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols]

    return df

# Load the sample data from file
# with open('api_response_2022-02-13.json', 'r') as f:
#     api_response = json.load(f)

# Load the sample data from string
api_response = json.loads('{"meta": {"columns": [{"key": "value", "display_name": "Adjusted Value (No Div, USD)", "output_type": "Number", "currency": "USD"}, {"key": "time_weighted_return", "display_name": "Current Quarter TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "time_weighted_return_2", "display_name": "YTD TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "_custom_twr_audit_note_911328", "display_name": "TWR Audit Note", "output_type": "Word"}], "groupings": [{"key": "_custom_name_747205", "display_name": "* Reporting Client Name"}, {"key": "_custom_new_entity_group_453577", "display_name": "NEW Entity Group"}, {"key": "_custom_level_2_624287", "display_name": "* Level 2"}, {"key": "legal_entity", "display_name": "Legal Entity"}]}, "data": {"type": "portfolio_views", "attributes": {"total": {"name": "Total", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Falconer Family", "grouping": "_custom_name_747205", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Wealth Bucket A", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.045960317420568164, "time_weighted_return_2": -0.045960317420568164, "_custom_twr_audit_note_911328": null, "value": 13264448.506587159}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": [{"entity_id": 10604454, "name": "HUDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": [{"entity_id": 10604454, "name": "HUDG Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": [{"entity_id": 10604454, "name": "HKDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": [{"entity_id": 10604454, "name": "HUDW Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": [{"entity_id": 10604454, "name": "HJDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": [{"entity_id": 10604454, "name": "HADJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": []}]}]}, {"name": "Wealth Bucket B", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.04769870075659244, "time_weighted_return_2": -0.04769870075659244, "_custom_twr_audit_note_911328": null, "value": 9868044.398519998}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 2.8632718065191298e-05, "time_weighted_return_2": 2.8632718065191298e-05, "_custom_twr_audit_note_911328": null, "value": 10234.94}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 2.82679297198829e-05, "time_weighted_return_2": 2.82679297198829e-05, "_custom_twr_audit_note_911328": null, "value": 244.28}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 4.9373572795108345e-05, "time_weighted_return_2": 4.9373572795108345e-05, "_custom_twr_audit_note_911328": null, "value": 5081.08}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.609603754315074e-06, "time_weighted_return_2": 6.609603754315074e-06, "_custom_twr_audit_note_911328": null, "value": 1523.62}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": 1.0999769004760296e-05, "time_weighted_return_2": 1.0999769004760296e-05, "_custom_twr_audit_note_911328": null, "value": 1828.9}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.466673995619843e-06, "time_weighted_return_2": 6.466673995619843e-06, "_custom_twr_audit_note_911328": null, "value": 1557.06}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.024645947842438676, "time_weighted_return_2": -0.024645947842438676, "_custom_twr_audit_note_911328": null, "value": 674052.31962}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.043304004172576405, "time_weighted_return_2": -0.043304004172576405, "_custom_twr_audit_note_911328": null, "value": 52800.96}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.022408434778798836, "time_weighted_return_2": -0.022408434778798836, "_custom_twr_audit_note_911328": null, "value": 599594.11962}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.03037038746301135, "time_weighted_return_2": -0.03037038746301135, "_custom_twr_audit_note_911328": null, "value": 114472.69744}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370390035505124, "time_weighted_return_2": -0.030370390035505124, "_custom_twr_audit_note_911328": null, "value": 114472.68744000001}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 0, "time_weighted_return_2": 0, "_custom_twr_audit_note_911328": null, "value": 0.01}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.06604362523792162, "time_weighted_return_2": -0.06604362523792162, "_custom_twr_audit_note_911328": null, "value": 5722529.229999997}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06154960593668424, "time_weighted_return_2": -0.06154960593668424, "_custom_twr_audit_note_911328": null, "value": 1191838.9399999995}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06750460387418267, "time_weighted_return_2": -0.06750460387418267, "_custom_twr_audit_note_911328": null, "value": 4416618.520000002}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 38190.33}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.017118805423322003, "time_weighted_return_2": -0.017118805423322003, "_custom_twr_audit_note_911328": null, "value": 3148495.0914600003}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.015251157805867277, "time_weighted_return_2": -0.015251157805867277, "_custom_twr_audit_note_911328": null, "value": 800493.06146}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01739609576880241, "time_weighted_return_2": -0.01739609576880241, "_custom_twr_audit_note_911328": null, "value": 2215511.2700000005}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02085132265594647, "time_weighted_return_2": -0.02085132265594647, "_custom_twr_audit_note_911328": null, "value": 44031.21}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02089393244695803, "time_weighted_return_2": -0.02089393244695803, "_custom_twr_audit_note_911328": null, "value": 44394.159999999996}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.020607507059866248, "time_weighted_return_2": -0.020607507059866248, "_custom_twr_audit_note_911328": null, "value": 44065.39000000001}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.0014710489231547497, "time_weighted_return_2": -0.0014710489231547497, "_custom_twr_audit_note_911328": null, "value": 198260.12}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.0014477244560456848, "time_weighted_return_2": -0.0014477244560456848, "_custom_twr_audit_note_911328": null, "value": 44612.33}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.001477821083437858, "time_weighted_return_2": -0.001477821083437858, "_custom_twr_audit_note_911328": null, "value": 153647.78999999998}, "children": []}]}]}]}]}}, "included": []}}')

df = unpack_response(api_response)
print(df.iloc[:5:,1:])
print(df.iloc[:10,:3])
expr = jp.parse('$..children.[*]')
def unpack_response(r):
    df = pd.DataFrame()
    for _, r1 in pd.json_normalize(r, ['data', 'attributes', 'total', 'children']).iterrows(): 
        r1['portfolio'] = r1['name']
        df = df.append(r1)
        for _, r2 in pd.json_normalize(r1.children).iterrows(): 
            df = df.append(r2)
            for _, r3 in pd.json_normalize(r2.children).iterrows(): 
                df = df.append(r3).append(pd.json_normalize(r3.children))
    df['portfolio'].fillna(method='ffill', inplace=True)
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols].reset_index(drop=True)
    return df
-----------------------
pip install --upgrade jsonpath-ng
import json
import jsonpath_ng as jp
import pandas as pd

def unpack_response(r):
    # Create a dataframe from extracted data
    expr = jp.parse('$..children.[*]')
    data = [{'full_path': str(m.full_path), **m.value} for m in expr.find(r)]
    df = pd.json_normalize(data).sort_values('full_path', ignore_index=True)

    # Append a portfolio column
    df['portfolio'] = df.loc[df.full_path.str.contains(r'total\.children\.\[\d+]$'), 'name']
    df['portfolio'].fillna(method='ffill', inplace=True)

    # Deal with columns
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['full_path', 'portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols]

    return df

# Load the sample data from file
# with open('api_response_2022-02-13.json', 'r') as f:
#     api_response = json.load(f)

# Load the sample data from string
api_response = json.loads('{"meta": {"columns": [{"key": "value", "display_name": "Adjusted Value (No Div, USD)", "output_type": "Number", "currency": "USD"}, {"key": "time_weighted_return", "display_name": "Current Quarter TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "time_weighted_return_2", "display_name": "YTD TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "_custom_twr_audit_note_911328", "display_name": "TWR Audit Note", "output_type": "Word"}], "groupings": [{"key": "_custom_name_747205", "display_name": "* Reporting Client Name"}, {"key": "_custom_new_entity_group_453577", "display_name": "NEW Entity Group"}, {"key": "_custom_level_2_624287", "display_name": "* Level 2"}, {"key": "legal_entity", "display_name": "Legal Entity"}]}, "data": {"type": "portfolio_views", "attributes": {"total": {"name": "Total", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Falconer Family", "grouping": "_custom_name_747205", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Wealth Bucket A", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.045960317420568164, "time_weighted_return_2": -0.045960317420568164, "_custom_twr_audit_note_911328": null, "value": 13264448.506587159}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": [{"entity_id": 10604454, "name": "HUDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": [{"entity_id": 10604454, "name": "HUDG Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": [{"entity_id": 10604454, "name": "HKDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": [{"entity_id": 10604454, "name": "HUDW Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": [{"entity_id": 10604454, "name": "HJDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": [{"entity_id": 10604454, "name": "HADJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": []}]}]}, {"name": "Wealth Bucket B", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.04769870075659244, "time_weighted_return_2": -0.04769870075659244, "_custom_twr_audit_note_911328": null, "value": 9868044.398519998}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 2.8632718065191298e-05, "time_weighted_return_2": 2.8632718065191298e-05, "_custom_twr_audit_note_911328": null, "value": 10234.94}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 2.82679297198829e-05, "time_weighted_return_2": 2.82679297198829e-05, "_custom_twr_audit_note_911328": null, "value": 244.28}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 4.9373572795108345e-05, "time_weighted_return_2": 4.9373572795108345e-05, "_custom_twr_audit_note_911328": null, "value": 5081.08}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.609603754315074e-06, "time_weighted_return_2": 6.609603754315074e-06, "_custom_twr_audit_note_911328": null, "value": 1523.62}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": 1.0999769004760296e-05, "time_weighted_return_2": 1.0999769004760296e-05, "_custom_twr_audit_note_911328": null, "value": 1828.9}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.466673995619843e-06, "time_weighted_return_2": 6.466673995619843e-06, "_custom_twr_audit_note_911328": null, "value": 1557.06}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.024645947842438676, "time_weighted_return_2": -0.024645947842438676, "_custom_twr_audit_note_911328": null, "value": 674052.31962}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.043304004172576405, "time_weighted_return_2": -0.043304004172576405, "_custom_twr_audit_note_911328": null, "value": 52800.96}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.022408434778798836, "time_weighted_return_2": -0.022408434778798836, "_custom_twr_audit_note_911328": null, "value": 599594.11962}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.03037038746301135, "time_weighted_return_2": -0.03037038746301135, "_custom_twr_audit_note_911328": null, "value": 114472.69744}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370390035505124, "time_weighted_return_2": -0.030370390035505124, "_custom_twr_audit_note_911328": null, "value": 114472.68744000001}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 0, "time_weighted_return_2": 0, "_custom_twr_audit_note_911328": null, "value": 0.01}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.06604362523792162, "time_weighted_return_2": -0.06604362523792162, "_custom_twr_audit_note_911328": null, "value": 5722529.229999997}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06154960593668424, "time_weighted_return_2": -0.06154960593668424, "_custom_twr_audit_note_911328": null, "value": 1191838.9399999995}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06750460387418267, "time_weighted_return_2": -0.06750460387418267, "_custom_twr_audit_note_911328": null, "value": 4416618.520000002}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 38190.33}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.017118805423322003, "time_weighted_return_2": -0.017118805423322003, "_custom_twr_audit_note_911328": null, "value": 3148495.0914600003}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.015251157805867277, "time_weighted_return_2": -0.015251157805867277, "_custom_twr_audit_note_911328": null, "value": 800493.06146}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01739609576880241, "time_weighted_return_2": -0.01739609576880241, "_custom_twr_audit_note_911328": null, "value": 2215511.2700000005}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02085132265594647, "time_weighted_return_2": -0.02085132265594647, "_custom_twr_audit_note_911328": null, "value": 44031.21}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02089393244695803, "time_weighted_return_2": -0.02089393244695803, "_custom_twr_audit_note_911328": null, "value": 44394.159999999996}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.020607507059866248, "time_weighted_return_2": -0.020607507059866248, "_custom_twr_audit_note_911328": null, "value": 44065.39000000001}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.0014710489231547497, "time_weighted_return_2": -0.0014710489231547497, "_custom_twr_audit_note_911328": null, "value": 198260.12}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.0014477244560456848, "time_weighted_return_2": -0.0014477244560456848, "_custom_twr_audit_note_911328": null, "value": 44612.33}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.001477821083437858, "time_weighted_return_2": -0.001477821083437858, "_custom_twr_audit_note_911328": null, "value": 153647.78999999998}, "children": []}]}]}]}]}}, "included": []}}')

df = unpack_response(api_response)
print(df.iloc[:5:,1:])
print(df.iloc[:10,:3])
expr = jp.parse('$..children.[*]')
def unpack_response(r):
    df = pd.DataFrame()
    for _, r1 in pd.json_normalize(r, ['data', 'attributes', 'total', 'children']).iterrows(): 
        r1['portfolio'] = r1['name']
        df = df.append(r1)
        for _, r2 in pd.json_normalize(r1.children).iterrows(): 
            df = df.append(r2)
            for _, r3 in pd.json_normalize(r2.children).iterrows(): 
                df = df.append(r3).append(pd.json_normalize(r3.children))
    df['portfolio'].fillna(method='ffill', inplace=True)
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols].reset_index(drop=True)
    return df
-----------------------
pip install --upgrade jsonpath-ng
import json
import jsonpath_ng as jp
import pandas as pd

def unpack_response(r):
    # Create a dataframe from extracted data
    expr = jp.parse('$..children.[*]')
    data = [{'full_path': str(m.full_path), **m.value} for m in expr.find(r)]
    df = pd.json_normalize(data).sort_values('full_path', ignore_index=True)

    # Append a portfolio column
    df['portfolio'] = df.loc[df.full_path.str.contains(r'total\.children\.\[\d+]$'), 'name']
    df['portfolio'].fillna(method='ffill', inplace=True)

    # Deal with columns
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['full_path', 'portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols]

    return df

# Load the sample data from file
# with open('api_response_2022-02-13.json', 'r') as f:
#     api_response = json.load(f)

# Load the sample data from string
api_response = json.loads('{"meta": {"columns": [{"key": "value", "display_name": "Adjusted Value (No Div, USD)", "output_type": "Number", "currency": "USD"}, {"key": "time_weighted_return", "display_name": "Current Quarter TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "time_weighted_return_2", "display_name": "YTD TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "_custom_twr_audit_note_911328", "display_name": "TWR Audit Note", "output_type": "Word"}], "groupings": [{"key": "_custom_name_747205", "display_name": "* Reporting Client Name"}, {"key": "_custom_new_entity_group_453577", "display_name": "NEW Entity Group"}, {"key": "_custom_level_2_624287", "display_name": "* Level 2"}, {"key": "legal_entity", "display_name": "Legal Entity"}]}, "data": {"type": "portfolio_views", "attributes": {"total": {"name": "Total", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Falconer Family", "grouping": "_custom_name_747205", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Wealth Bucket A", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.045960317420568164, "time_weighted_return_2": -0.045960317420568164, "_custom_twr_audit_note_911328": null, "value": 13264448.506587159}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": [{"entity_id": 10604454, "name": "HUDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": [{"entity_id": 10604454, "name": "HUDG Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": [{"entity_id": 10604454, "name": "HKDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": [{"entity_id": 10604454, "name": "HUDW Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": [{"entity_id": 10604454, "name": "HJDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": [{"entity_id": 10604454, "name": "HADJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": []}]}]}, {"name": "Wealth Bucket B", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.04769870075659244, "time_weighted_return_2": -0.04769870075659244, "_custom_twr_audit_note_911328": null, "value": 9868044.398519998}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 2.8632718065191298e-05, "time_weighted_return_2": 2.8632718065191298e-05, "_custom_twr_audit_note_911328": null, "value": 10234.94}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 2.82679297198829e-05, "time_weighted_return_2": 2.82679297198829e-05, "_custom_twr_audit_note_911328": null, "value": 244.28}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 4.9373572795108345e-05, "time_weighted_return_2": 4.9373572795108345e-05, "_custom_twr_audit_note_911328": null, "value": 5081.08}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.609603754315074e-06, "time_weighted_return_2": 6.609603754315074e-06, "_custom_twr_audit_note_911328": null, "value": 1523.62}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": 1.0999769004760296e-05, "time_weighted_return_2": 1.0999769004760296e-05, "_custom_twr_audit_note_911328": null, "value": 1828.9}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.466673995619843e-06, "time_weighted_return_2": 6.466673995619843e-06, "_custom_twr_audit_note_911328": null, "value": 1557.06}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.024645947842438676, "time_weighted_return_2": -0.024645947842438676, "_custom_twr_audit_note_911328": null, "value": 674052.31962}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.043304004172576405, "time_weighted_return_2": -0.043304004172576405, "_custom_twr_audit_note_911328": null, "value": 52800.96}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.022408434778798836, "time_weighted_return_2": -0.022408434778798836, "_custom_twr_audit_note_911328": null, "value": 599594.11962}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.03037038746301135, "time_weighted_return_2": -0.03037038746301135, "_custom_twr_audit_note_911328": null, "value": 114472.69744}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370390035505124, "time_weighted_return_2": -0.030370390035505124, "_custom_twr_audit_note_911328": null, "value": 114472.68744000001}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 0, "time_weighted_return_2": 0, "_custom_twr_audit_note_911328": null, "value": 0.01}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.06604362523792162, "time_weighted_return_2": -0.06604362523792162, "_custom_twr_audit_note_911328": null, "value": 5722529.229999997}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06154960593668424, "time_weighted_return_2": -0.06154960593668424, "_custom_twr_audit_note_911328": null, "value": 1191838.9399999995}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06750460387418267, "time_weighted_return_2": -0.06750460387418267, "_custom_twr_audit_note_911328": null, "value": 4416618.520000002}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 38190.33}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.017118805423322003, "time_weighted_return_2": -0.017118805423322003, "_custom_twr_audit_note_911328": null, "value": 3148495.0914600003}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.015251157805867277, "time_weighted_return_2": -0.015251157805867277, "_custom_twr_audit_note_911328": null, "value": 800493.06146}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01739609576880241, "time_weighted_return_2": -0.01739609576880241, "_custom_twr_audit_note_911328": null, "value": 2215511.2700000005}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02085132265594647, "time_weighted_return_2": -0.02085132265594647, "_custom_twr_audit_note_911328": null, "value": 44031.21}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02089393244695803, "time_weighted_return_2": -0.02089393244695803, "_custom_twr_audit_note_911328": null, "value": 44394.159999999996}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.020607507059866248, "time_weighted_return_2": -0.020607507059866248, "_custom_twr_audit_note_911328": null, "value": 44065.39000000001}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.0014710489231547497, "time_weighted_return_2": -0.0014710489231547497, "_custom_twr_audit_note_911328": null, "value": 198260.12}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.0014477244560456848, "time_weighted_return_2": -0.0014477244560456848, "_custom_twr_audit_note_911328": null, "value": 44612.33}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.001477821083437858, "time_weighted_return_2": -0.001477821083437858, "_custom_twr_audit_note_911328": null, "value": 153647.78999999998}, "children": []}]}]}]}]}}, "included": []}}')

df = unpack_response(api_response)
print(df.iloc[:5:,1:])
print(df.iloc[:10,:3])
expr = jp.parse('$..children.[*]')
def unpack_response(r):
    df = pd.DataFrame()
    for _, r1 in pd.json_normalize(r, ['data', 'attributes', 'total', 'children']).iterrows(): 
        r1['portfolio'] = r1['name']
        df = df.append(r1)
        for _, r2 in pd.json_normalize(r1.children).iterrows(): 
            df = df.append(r2)
            for _, r3 in pd.json_normalize(r2.children).iterrows(): 
                df = df.append(r3).append(pd.json_normalize(r3.children))
    df['portfolio'].fillna(method='ffill', inplace=True)
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols].reset_index(drop=True)
    return df
-----------------------
pip install --upgrade jsonpath-ng
import json
import jsonpath_ng as jp
import pandas as pd

def unpack_response(r):
    # Create a dataframe from extracted data
    expr = jp.parse('$..children.[*]')
    data = [{'full_path': str(m.full_path), **m.value} for m in expr.find(r)]
    df = pd.json_normalize(data).sort_values('full_path', ignore_index=True)

    # Append a portfolio column
    df['portfolio'] = df.loc[df.full_path.str.contains(r'total\.children\.\[\d+]$'), 'name']
    df['portfolio'].fillna(method='ffill', inplace=True)

    # Deal with columns
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['full_path', 'portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols]

    return df

# Load the sample data from file
# with open('api_response_2022-02-13.json', 'r') as f:
#     api_response = json.load(f)

# Load the sample data from string
api_response = json.loads('{"meta": {"columns": [{"key": "value", "display_name": "Adjusted Value (No Div, USD)", "output_type": "Number", "currency": "USD"}, {"key": "time_weighted_return", "display_name": "Current Quarter TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "time_weighted_return_2", "display_name": "YTD TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "_custom_twr_audit_note_911328", "display_name": "TWR Audit Note", "output_type": "Word"}], "groupings": [{"key": "_custom_name_747205", "display_name": "* Reporting Client Name"}, {"key": "_custom_new_entity_group_453577", "display_name": "NEW Entity Group"}, {"key": "_custom_level_2_624287", "display_name": "* Level 2"}, {"key": "legal_entity", "display_name": "Legal Entity"}]}, "data": {"type": "portfolio_views", "attributes": {"total": {"name": "Total", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Falconer Family", "grouping": "_custom_name_747205", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Wealth Bucket A", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.045960317420568164, "time_weighted_return_2": -0.045960317420568164, "_custom_twr_audit_note_911328": null, "value": 13264448.506587159}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": [{"entity_id": 10604454, "name": "HUDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": [{"entity_id": 10604454, "name": "HUDG Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": [{"entity_id": 10604454, "name": "HKDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": [{"entity_id": 10604454, "name": "HUDW Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": [{"entity_id": 10604454, "name": "HJDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": [{"entity_id": 10604454, "name": "HADJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": []}]}]}, {"name": "Wealth Bucket B", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.04769870075659244, "time_weighted_return_2": -0.04769870075659244, "_custom_twr_audit_note_911328": null, "value": 9868044.398519998}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 2.8632718065191298e-05, "time_weighted_return_2": 2.8632718065191298e-05, "_custom_twr_audit_note_911328": null, "value": 10234.94}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 2.82679297198829e-05, "time_weighted_return_2": 2.82679297198829e-05, "_custom_twr_audit_note_911328": null, "value": 244.28}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 4.9373572795108345e-05, "time_weighted_return_2": 4.9373572795108345e-05, "_custom_twr_audit_note_911328": null, "value": 5081.08}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.609603754315074e-06, "time_weighted_return_2": 6.609603754315074e-06, "_custom_twr_audit_note_911328": null, "value": 1523.62}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": 1.0999769004760296e-05, "time_weighted_return_2": 1.0999769004760296e-05, "_custom_twr_audit_note_911328": null, "value": 1828.9}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.466673995619843e-06, "time_weighted_return_2": 6.466673995619843e-06, "_custom_twr_audit_note_911328": null, "value": 1557.06}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.024645947842438676, "time_weighted_return_2": -0.024645947842438676, "_custom_twr_audit_note_911328": null, "value": 674052.31962}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.043304004172576405, "time_weighted_return_2": -0.043304004172576405, "_custom_twr_audit_note_911328": null, "value": 52800.96}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.022408434778798836, "time_weighted_return_2": -0.022408434778798836, "_custom_twr_audit_note_911328": null, "value": 599594.11962}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.03037038746301135, "time_weighted_return_2": -0.03037038746301135, "_custom_twr_audit_note_911328": null, "value": 114472.69744}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370390035505124, "time_weighted_return_2": -0.030370390035505124, "_custom_twr_audit_note_911328": null, "value": 114472.68744000001}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 0, "time_weighted_return_2": 0, "_custom_twr_audit_note_911328": null, "value": 0.01}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.06604362523792162, "time_weighted_return_2": -0.06604362523792162, "_custom_twr_audit_note_911328": null, "value": 5722529.229999997}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06154960593668424, "time_weighted_return_2": -0.06154960593668424, "_custom_twr_audit_note_911328": null, "value": 1191838.9399999995}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06750460387418267, "time_weighted_return_2": -0.06750460387418267, "_custom_twr_audit_note_911328": null, "value": 4416618.520000002}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 38190.33}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.017118805423322003, "time_weighted_return_2": -0.017118805423322003, "_custom_twr_audit_note_911328": null, "value": 3148495.0914600003}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.015251157805867277, "time_weighted_return_2": -0.015251157805867277, "_custom_twr_audit_note_911328": null, "value": 800493.06146}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01739609576880241, "time_weighted_return_2": -0.01739609576880241, "_custom_twr_audit_note_911328": null, "value": 2215511.2700000005}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02085132265594647, "time_weighted_return_2": -0.02085132265594647, "_custom_twr_audit_note_911328": null, "value": 44031.21}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02089393244695803, "time_weighted_return_2": -0.02089393244695803, "_custom_twr_audit_note_911328": null, "value": 44394.159999999996}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.020607507059866248, "time_weighted_return_2": -0.020607507059866248, "_custom_twr_audit_note_911328": null, "value": 44065.39000000001}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.0014710489231547497, "time_weighted_return_2": -0.0014710489231547497, "_custom_twr_audit_note_911328": null, "value": 198260.12}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.0014477244560456848, "time_weighted_return_2": -0.0014477244560456848, "_custom_twr_audit_note_911328": null, "value": 44612.33}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.001477821083437858, "time_weighted_return_2": -0.001477821083437858, "_custom_twr_audit_note_911328": null, "value": 153647.78999999998}, "children": []}]}]}]}]}}, "included": []}}')

df = unpack_response(api_response)
print(df.iloc[:5:,1:])
print(df.iloc[:10,:3])
expr = jp.parse('$..children.[*]')
def unpack_response(r):
    df = pd.DataFrame()
    for _, r1 in pd.json_normalize(r, ['data', 'attributes', 'total', 'children']).iterrows(): 
        r1['portfolio'] = r1['name']
        df = df.append(r1)
        for _, r2 in pd.json_normalize(r1.children).iterrows(): 
            df = df.append(r2)
            for _, r3 in pd.json_normalize(r2.children).iterrows(): 
                df = df.append(r3).append(pd.json_normalize(r3.children))
    df['portfolio'].fillna(method='ffill', inplace=True)
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols].reset_index(drop=True)
    return df
-----------------------
pip install --upgrade jsonpath-ng
import json
import jsonpath_ng as jp
import pandas as pd

def unpack_response(r):
    # Create a dataframe from extracted data
    expr = jp.parse('$..children.[*]')
    data = [{'full_path': str(m.full_path), **m.value} for m in expr.find(r)]
    df = pd.json_normalize(data).sort_values('full_path', ignore_index=True)

    # Append a portfolio column
    df['portfolio'] = df.loc[df.full_path.str.contains(r'total\.children\.\[\d+]$'), 'name']
    df['portfolio'].fillna(method='ffill', inplace=True)

    # Deal with columns
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['full_path', 'portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols]

    return df

# Load the sample data from file
# with open('api_response_2022-02-13.json', 'r') as f:
#     api_response = json.load(f)

# Load the sample data from string
api_response = json.loads('{"meta": {"columns": [{"key": "value", "display_name": "Adjusted Value (No Div, USD)", "output_type": "Number", "currency": "USD"}, {"key": "time_weighted_return", "display_name": "Current Quarter TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "time_weighted_return_2", "display_name": "YTD TWR (USD)", "output_type": "Percent", "currency": "USD"}, {"key": "_custom_twr_audit_note_911328", "display_name": "TWR Audit Note", "output_type": "Word"}], "groupings": [{"key": "_custom_name_747205", "display_name": "* Reporting Client Name"}, {"key": "_custom_new_entity_group_453577", "display_name": "NEW Entity Group"}, {"key": "_custom_level_2_624287", "display_name": "* Level 2"}, {"key": "legal_entity", "display_name": "Legal Entity"}]}, "data": {"type": "portfolio_views", "attributes": {"total": {"name": "Total", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Falconer Family", "grouping": "_custom_name_747205", "columns": {"time_weighted_return": -0.046732301295604683, "time_weighted_return_2": -0.046732301295604683, "_custom_twr_audit_note_911328": null, "value": 23132492.905107163}, "children": [{"name": "Wealth Bucket A", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.045960317420568164, "time_weighted_return_2": -0.045960317420568164, "_custom_twr_audit_note_911328": null, "value": 13264448.506587159}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": [{"entity_id": 10604454, "name": "HUDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": 3.434094574039648e-06, "time_weighted_return_2": 3.434094574039648e-06, "_custom_twr_audit_note_911328": null, "value": 3337.99}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": [{"entity_id": 10604454, "name": "HUDG Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.025871339096964152, "time_weighted_return_2": -0.025871339096964152, "_custom_twr_audit_note_911328": null, "value": 1017004.7192636987}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": [{"entity_id": 10604454, "name": "HKDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370376329670656, "time_weighted_return_2": -0.030370376329670656, "_custom_twr_audit_note_911328": null, "value": 231142.67772000004}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": [{"entity_id": 10604454, "name": "HUDW Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05382756475465478, "time_weighted_return_2": -0.05382756475465478, "_custom_twr_audit_note_911328": null, "value": 9791282.570000006}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": [{"entity_id": 10604454, "name": "HJDJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01351630404081805, "time_weighted_return_2": -0.01351630404081805, "_custom_twr_audit_note_911328": null, "value": 2153366.6396034593}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": [{"entity_id": 10604454, "name": "HADJ Trust", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.002298190175237247, "time_weighted_return_2": -0.002298190175237247, "_custom_twr_audit_note_911328": null, "value": 68313.90999999999}, "children": []}]}]}, {"name": "Wealth Bucket B", "grouping": "_custom_new_entity_group_453577", "columns": {"time_weighted_return": -0.04769870075659244, "time_weighted_return_2": -0.04769870075659244, "_custom_twr_audit_note_911328": null, "value": 9868044.398519998}, "children": [{"name": "Asset Class A", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": 2.8632718065191298e-05, "time_weighted_return_2": 2.8632718065191298e-05, "_custom_twr_audit_note_911328": null, "value": 10234.94}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 2.82679297198829e-05, "time_weighted_return_2": 2.82679297198829e-05, "_custom_twr_audit_note_911328": null, "value": 244.28}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 4.9373572795108345e-05, "time_weighted_return_2": 4.9373572795108345e-05, "_custom_twr_audit_note_911328": null, "value": 5081.08}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.609603754315074e-06, "time_weighted_return_2": 6.609603754315074e-06, "_custom_twr_audit_note_911328": null, "value": 1523.62}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": 1.0999769004760296e-05, "time_weighted_return_2": 1.0999769004760296e-05, "_custom_twr_audit_note_911328": null, "value": 1828.9}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": 6.466673995619843e-06, "time_weighted_return_2": 6.466673995619843e-06, "_custom_twr_audit_note_911328": null, "value": 1557.06}, "children": []}]}, {"name": "Asset Class B", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.024645947842438676, "time_weighted_return_2": -0.024645947842438676, "_custom_twr_audit_note_911328": null, "value": 674052.31962}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.043304004172576405, "time_weighted_return_2": -0.043304004172576405, "_custom_twr_audit_note_911328": null, "value": 52800.96}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.022408434778798836, "time_weighted_return_2": -0.022408434778798836, "_custom_twr_audit_note_911328": null, "value": 599594.11962}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.039799855483646174, "time_weighted_return_2": -0.039799855483646174, "_custom_twr_audit_note_911328": null, "value": 7219.08}, "children": []}]}, {"name": "Asset Class C", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.03037038746301135, "time_weighted_return_2": -0.03037038746301135, "_custom_twr_audit_note_911328": null, "value": 114472.69744}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.030370390035505124, "time_weighted_return_2": -0.030370390035505124, "_custom_twr_audit_note_911328": null, "value": 114472.68744000001}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": 0, "time_weighted_return_2": 0, "_custom_twr_audit_note_911328": null, "value": 0.01}, "children": []}]}, {"name": "Asset Class D", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.06604362523792162, "time_weighted_return_2": -0.06604362523792162, "_custom_twr_audit_note_911328": null, "value": 5722529.229999997}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06154960593668424, "time_weighted_return_2": -0.06154960593668424, "_custom_twr_audit_note_911328": null, "value": 1191838.9399999995}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.06750460387418267, "time_weighted_return_2": -0.06750460387418267, "_custom_twr_audit_note_911328": null, "value": 4416618.520000002}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 38190.33}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.05604507809250081, "time_weighted_return_2": -0.05604507809250081, "_custom_twr_audit_note_911328": null, "value": 37940.72}, "children": []}]}, {"name": "Asset Class E", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.017118805423322003, "time_weighted_return_2": -0.017118805423322003, "_custom_twr_audit_note_911328": null, "value": 3148495.0914600003}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.015251157805867277, "time_weighted_return_2": -0.015251157805867277, "_custom_twr_audit_note_911328": null, "value": 800493.06146}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.01739609576880241, "time_weighted_return_2": -0.01739609576880241, "_custom_twr_audit_note_911328": null, "value": 2215511.2700000005}, "children": []}, {"entity_id": 10598341, "name": "Cht 11th Tr HBO Shirley", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02085132265594647, "time_weighted_return_2": -0.02085132265594647, "_custom_twr_audit_note_911328": null, "value": 44031.21}, "children": []}, {"entity_id": 10598337, "name": "Cht 11th Tr HBO Hannah", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.02089393244695803, "time_weighted_return_2": -0.02089393244695803, "_custom_twr_audit_note_911328": null, "value": 44394.159999999996}, "children": []}, {"entity_id": 10598334, "name": "Cht 11th Tr HBO Lau", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.020607507059866248, "time_weighted_return_2": -0.020607507059866248, "_custom_twr_audit_note_911328": null, "value": 44065.39000000001}, "children": []}]}, {"name": "Asset Class F", "grouping": "_custom_level_2_624287", "columns": {"time_weighted_return": -0.0014710489231547497, "time_weighted_return_2": -0.0014710489231547497, "_custom_twr_audit_note_911328": null, "value": 198260.12}, "children": [{"entity_id": 10868778, "name": "2012 Desc Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.0014477244560456848, "time_weighted_return_2": -0.0014477244560456848, "_custom_twr_audit_note_911328": null, "value": 44612.33}, "children": []}, {"entity_id": 10643052, "name": "2013 Irrev Tr HBO Thalia", "grouping": "legal_entity", "columns": {"time_weighted_return": -0.001477821083437858, "time_weighted_return_2": -0.001477821083437858, "_custom_twr_audit_note_911328": null, "value": 153647.78999999998}, "children": []}]}]}]}]}}, "included": []}}')

df = unpack_response(api_response)
print(df.iloc[:5:,1:])
print(df.iloc[:10,:3])
expr = jp.parse('$..children.[*]')
def unpack_response(r):
    df = pd.DataFrame()
    for _, r1 in pd.json_normalize(r, ['data', 'attributes', 'total', 'children']).iterrows(): 
        r1['portfolio'] = r1['name']
        df = df.append(r1)
        for _, r2 in pd.json_normalize(r1.children).iterrows(): 
            df = df.append(r2)
            for _, r3 in pd.json_normalize(r2.children).iterrows(): 
                df = df.append(r3).append(pd.json_normalize(r3.children))
    df['portfolio'].fillna(method='ffill', inplace=True)
    trans = {'columns.' + c['key']: c['display_name'] for c in r['meta']['columns']}
    cols = ['portfolio', 'name', 'entity_id', 'Adjusted Value (No Div, USD)', 'Current Quarter TWR (USD)', 'YTD TWR (USD)', 'TWR Audit Note']
    df = df.rename(columns=trans)[cols].reset_index(drop=True)
    return df
-----------------------
import pandas as pd
import json

# get the json file
json_dict = json.load(open('api_response_2022-02-13.json'))
    

# create a (nested) df out of it, and rename the 'top-level' name field to 'portfolio'
packed_df = pd.DataFrame.from_dict(json_dict['data']['attributes']['total']['children'])\
            .rename(columns={'name': 'portfolio'})

# expand the level-1 'children' (and call their 'name' field 'grand-parent')
unpacked_df = packed_df.groupby('portfolio')['children']\
              .apply(lambda x: pd.DataFrame(x.values[0])).reset_index()\
              .rename(columns={'name': 'grand_parent_name'})

# expand the level-2 'children' (and call their 'name' field 'parent')
unpacked_df = unpacked_df.groupby(['portfolio', 'grand_parent_name'])['children']\
              .apply(lambda x: pd.DataFrame(x.values[0])).reset_index()\
              .rename(columns={'name': 'parent_name'})

# expand the level-3 'children' (and keep their name as is)
unpacked_df = unpacked_df.groupby(['portfolio', 'grand_parent_name', 'parent_name'])['children']\
              .apply(lambda x: pd.DataFrame(x.values[0])).reset_index()

# expand the column field info from 'dict' to multiple columns
unpacked_df = pd.concat([unpacked_df.drop('columns', axis=1), pd.DataFrame(unpacked_df['columns'].tolist())], axis=1)

AttributeError: Can't get attribute 'new_block' on <module 'pandas.core.internals.blocks'>

copy iconCopydownload iconDownload
import numpy as np 
import pandas as pd
df =pd.DataFrame(np.random.rand(3,6))

with open("dump_from_v1.3.4.pickle", "wb") as f: 
    pickle.dump(df, f) 

quit()
import pickle

with open("dump_from_v1.3.4.pickle", "rb") as f: 
    df = pickle.load(f) 


---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-2-ff5c218eca92> in <module>
      1 with open("dump_from_v1.3.4.pickle", "rb") as f:
----> 2     df = pickle.load(f)
      3 

AttributeError: Can't get attribute 'new_block' on <module 'pandas.core.internals.blocks' from '/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py'>
-----------------------
import numpy as np 
import pandas as pd
df =pd.DataFrame(np.random.rand(3,6))

with open("dump_from_v1.3.4.pickle", "wb") as f: 
    pickle.dump(df, f) 

quit()
import pickle

with open("dump_from_v1.3.4.pickle", "rb") as f: 
    df = pickle.load(f) 


---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-2-ff5c218eca92> in <module>
      1 with open("dump_from_v1.3.4.pickle", "rb") as f:
----> 2     df = pickle.load(f)
      3 

AttributeError: Can't get attribute 'new_block' on <module 'pandas.core.internals.blocks' from '/opt/anaconda3/lib/python3.7/site-packages/pandas/core/internals/blocks.py'>

How to update pandas DataFrame.drop() for Future Warning - all arguments of DataFrame.drop except for the argument 'labels' will be keyword-only

copy iconCopydownload iconDownload
df = df.drop('market', axis=1)
df = df.drop(columns='market')
-----------------------
df = df.drop('market', axis=1)
df = df.drop(columns='market')
-----------------------
df.drop('market', axis=1)
df.drop(columns='market')
-----------------------
df.drop('market', axis=1)
df.drop(columns='market')

Cannot set up a conda environment with python 3.10

copy iconCopydownload iconDownload
conda install python=3.9
-----------------------
conda update -n base -c defaults conda

Merge two pandas DataFrame based on partial match

copy iconCopydownload iconDownload
import pandas as pd
import numpy as np

from thefuzz import process

def fuzzy_match(
    a: pd.DataFrame, b: pd.DataFrame, col: str, limit: int = 5, thresh: int = 80
):
    """use fuzzy matching to join on column"""

    s = b[col].tolist()

    matches = a[col].apply(lambda x: process.extract(x, s, limit=limit))
    matches = pd.DataFrame(np.concatenate(matches), columns=["match", "score"])

    # join other columns in b to matches
    to_join = (
        pd.merge(left=b, right=matches, how="right", left_on="City", right_on="match")
        .set_index(  # create an index that represents the matching row in df a, you can drop this when `limit=1`
            np.array(
                list(
                    np.repeat(i, limit if limit < len(b) else len(b))
                    for i in range(len(a))
                )
            ).flatten()
        )
        .drop(columns=["match"])
        .astype({"score": "int16"})
    )
    print(f"\t the index here represents the row in dataframe a on which to join")
    print(to_join)

    res = pd.merge(
        left=a, right=to_join, left_index=True, right_index=True, suffixes=("", "_b")
    )

    # return only the highest match or you can just set the limit to 1
    # and remove this
    df = res.reset_index()
    df = df.iloc[df.groupby(by="index")["score"].idxmax()].reset_index(drop=True)

    return df.drop(columns=["City_b", "score", "index"])

def test(df):

    expected = pd.DataFrame(
        {
            "City": ["San Francisco, CA", "Oakland, CA"],
            "Val": [1, 2],
            "Geo": ["geo1", "geo1"],
        }
    )

    print(f'{"expected":-^70}')
    print(expected)

    print(f'{"res":-^70}')
    print(df)

    assert expected.equals(df)


if __name__ == "__main__":

    a = pd.DataFrame({"City": ["San Francisco, CA", "Oakland, CA"], "Val": [1, 2]})
    b = pd.DataFrame(
        {"City": ["San Francisco-Oakland, CA", "Salinas, CA"], "Geo": ["geo1", "geo2"]}
    )

    print(f'\n\n{"fuzzy match":-^70}')
    res = fuzzy_match(a, b, col="City")
    test(res)

-----------------------
import numpy as np
import pandas as pd

from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity


# https://stackoverflow.com/a/27086669/8615419
# as a preprocessor for TfidfVectorizer
def clean_corpus(s: str):
    """return clean corpus -- replaced any non word chars with space"""
    for ch in ['\\','`','*','_','{','}','[',']','(',')','>','#','+','-','.','!','$','\'',',']:
        if ch in s:
            s = s.replace(ch, " ")
    return s.lower()

# why n-grams?
# this should account for any word misspellings
def fit_vectorizer(corpus: np.array, n: int = 3):

    vectorizer = TfidfVectorizer(analyzer="char_wb", preprocessor=clean_corpus, ngram_range=(n, n))
    tfidf = vectorizer.fit_transform(corpus)

    return tfidf, vectorizer


def cosine_similarity_join(a, b, col_name):

    a_len = len(a[col_name])

    # all of the "documents" in a 1D array
    corpus = np.concatenate([a[col_name].to_numpy(), b[col_name].to_numpy()])

    tfidf, vectorizer = fit_vectorizer(corpus, 3)
    # print(vectorizer.get_feature_names())

    # in this matrix each row represents the str in a and the col is the str from b, value is the cosine similarity
    res = cosine_similarity(tfidf[:a_len], tfidf[a_len:])
    print('in this matrix each row represents the str in a and the col is the str from b')
    print(res)

    res_series = pd.DataFrame(res).stack().rename("score")
    res_series.index.set_names(['a', 'b'], inplace=True)
    # print(res_series)
    
    # join scores to b
    b_scored = pd.merge(left=b, right=res_series, left_index=True, right_on='b').droplevel('b')
    # print(b_scored.sort_index())
   
    # find the indices on which to match, (highest score in each row)
    # best_match = np.argmax(res, axis=1)
    
    res = pd.merge(left=a, right=b_scored, left_index=True, right_index=True, suffixes=('', '_b'))
    print(res)

    df = res.reset_index()
    df = df.iloc[df.groupby(by="index")["score"].idxmax()].reset_index(drop=True)

    return df.drop(columns=["City_b", "score", "index"])  

def test(df):

    expected = pd.DataFrame(
        {
            "City": ["San Francisco, CA", "Oakland, CA"],
            "Val": [1, 2],
            "Geo": ["geo1", "geo1"],
        }
    )

    print(f'{"expected":-^70}')
    print(expected)

    print(f'{"res":-^70}')
    print(df)

    assert expected.equals(df)


if __name__ == "__main__":

    a = pd.DataFrame({"City": ["San Francisco, CA", "Oakland, CA"], "Val": [1, 2]})
    b = pd.DataFrame(
        {"City": ["San Francisco-Oakland, CA", "Salinas, CA"], "Geo": ["geo1", "geo2"]}
    )

    print(f'\n\n{"n-gram cosine similarity":-^70}')
    res = cosine_similarity_join(a, b, col_name="City")
    test(res)
-----------------------
# Python env: pip install thefuzz
# Anaconda env: pip install thefuzz
# -> thefuzz is not yet available on Anaconda (2021-09-18)
# -> you can use the old package: conda install -c conda-forge fuzzywuzzy

from thefuzz import process

best_city = lambda x: process.extractOne(x, df2["City"])[2]  # See note below
df1['Geo'] = df2.loc[df1["City"].map(best_city).values, 'Geo'].values
>>> df1
                City  Val   Geo
0  San Francisco, CA    1  geo1
1        Oakland, CA    2  geo1
-----------------------
# Python env: pip install thefuzz
# Anaconda env: pip install thefuzz
# -> thefuzz is not yet available on Anaconda (2021-09-18)
# -> you can use the old package: conda install -c conda-forge fuzzywuzzy

from thefuzz import process

best_city = lambda x: process.extractOne(x, df2["City"])[2]  # See note below
df1['Geo'] = df2.loc[df1["City"].map(best_city).values, 'Geo'].values
>>> df1
                City  Val   Geo
0  San Francisco, CA    1  geo1
1        Oakland, CA    2  geo1

Create a new column in a Pandas DataFrame from existing column names

copy iconCopydownload iconDownload
# Results
res = []

# Nested loop: first for length of index col, then next for cols
for i in range(len(index_col)):
    for j in range(len(cols)):
        # Format of data
        res.append([index_col[i], cols[j], values[i][j]])

# Return results
print(res)
return res
-----------------------
data = []

for col, row in df.items():
    for ind, val in row.reset_index().values:
        data.append([ind, col, val])

data
-----------------------
df.unstack().swaplevel().reset_index().values.tolist()
#OR
df.reset_index().melt(id_vars="index").values.tolist()
# [['store1', 'January', 2],
#  ['store2', 'January', 5],
#  ['store3', 'January', 8],
#  ['store1', 'February', 3],
#  ['store2', 'February', 6],
#  ['store3', 'February', 9],
#  ['store1', 'March', 4],
#  ['store2', 'March', 7],
#  ['store3', 'March', 10]]
df.transpose().unstack().reset_index().values.tolist()
# [['store1', 'January', 2],
#  ['store1', 'February', 3],
#  ['store1', 'March', 4],
#  ['store2', 'January', 5],
#  ['store2', 'February', 6],
#  ['store2', 'March', 7],
#  ['store3', 'January', 8],
#  ['store3', 'February', 9],
#  ['store3', 'March', 10]]
-----------------------
df.unstack().swaplevel().reset_index().values.tolist()
#OR
df.reset_index().melt(id_vars="index").values.tolist()
# [['store1', 'January', 2],
#  ['store2', 'January', 5],
#  ['store3', 'January', 8],
#  ['store1', 'February', 3],
#  ['store2', 'February', 6],
#  ['store3', 'February', 9],
#  ['store1', 'March', 4],
#  ['store2', 'March', 7],
#  ['store3', 'March', 10]]
df.transpose().unstack().reset_index().values.tolist()
# [['store1', 'January', 2],
#  ['store1', 'February', 3],
#  ['store1', 'March', 4],
#  ['store2', 'January', 5],
#  ['store2', 'February', 6],
#  ['store2', 'March', 7],
#  ['store3', 'January', 8],
#  ['store3', 'February', 9],
#  ['store3', 'March', 10]]
-----------------------
lst = [[*k, v] for k, v in df.unstack().swaplevel().to_dict().items()]
-----------------------
temp = df.stack()

[[*ent, val] for ent, val in zip(temp.index, temp)]

[['store1', 'January', 2],
 ['store1', 'February', 3],
 ['store1', 'March', 4],
 ['store2', 'January', 5],
 ['store2', 'February', 6],
 ['store2', 'March', 7],
 ['store3', 'January', 8],
 ['store3', 'February', 9],
 ['store3', 'March', 10]]
-----------------------
>>> df.stack().reset_index().to_numpy()
array([['store1', 'January', 2],
       ['store1', 'February', 3],
       ['store1', 'March', 4],
       ['store2', 'January', 5],
       ['store2', 'February', 6],
       ['store2', 'March', 7],
       ['store3', 'January', 8],
       ['store3', 'February', 9],
       ['store3', 'March', 10]], dtype=object)
>>> 
>>> df.melt(ignore_index=False).reset_index().to_numpy()
array([['store1', 'January', 2],
       ['store2', 'January', 5],
       ['store3', 'January', 8],
       ['store1', 'February', 3],
       ['store2', 'February', 6],
       ['store3', 'February', 9],
       ['store1', 'March', 4],
       ['store2', 'March', 7],
       ['store3', 'March', 10]], dtype=object)
>>> 
-----------------------
>>> df.stack().reset_index().to_numpy()
array([['store1', 'January', 2],
       ['store1', 'February', 3],
       ['store1', 'March', 4],
       ['store2', 'January', 5],
       ['store2', 'February', 6],
       ['store2', 'March', 7],
       ['store3', 'January', 8],
       ['store3', 'February', 9],
       ['store3', 'March', 10]], dtype=object)
>>> 
>>> df.melt(ignore_index=False).reset_index().to_numpy()
array([['store1', 'January', 2],
       ['store2', 'January', 5],
       ['store3', 'January', 8],
       ['store1', 'February', 3],
       ['store2', 'February', 6],
       ['store3', 'February', 9],
       ['store1', 'March', 4],
       ['store2', 'March', 7],
       ['store3', 'March', 10]], dtype=object)
>>> 

After conda update, python kernel crashes when matplotlib is used

copy iconCopydownload iconDownload
  2021-10-31 10:47:22  (rev 3)
     bokeh  {2.3.3 (defaults/win-64) -> 2.4.1 (defaults/win-64)}
     click  {8.0.1 (defaults/noarch) -> 8.0.3 (defaults/noarch)}
     filelock  {3.0.12 (defaults/noarch) -> 3.3.1 (defaults/noarch)}
     freetype  {2.10.4 (defaults/win-64) -> 2.11.0 (defaults/win-64)}
     imagecodecs  {2021.6.8 (defaults/win-64) -> 2021.8.26 (defaults/win-64)}
     joblib  {1.0.1 (defaults/noarch) -> 1.1.0 (defaults/noarch)}
     lerc  {2.2.1 (defaults/win-64) -> 3.0 (defaults/win-64)}
     more-itertools  {8.8.0 (defaults/noarch) -> 8.10.0 (defaults/noarch)}
     pyopenssl  {20.0.1 (defaults/noarch) -> 21.0.0 (defaults/noarch)}
     scikit-learn  {0.24.2 (defaults/win-64) -> 1.0.1 (defaults/win-64)}
     statsmodels  {0.12.2 (defaults/win-64) -> 0.13.0 (defaults/win-64)}
     sympy  {1.8 (defaults/win-64) -> 1.9 (defaults/win-64)}
     tqdm  {4.62.2 (defaults/noarch) -> 4.62.3 (defaults/noarch)}
     xlwings  {0.24.7 (defaults/win-64) -> 0.24.9 (defaults/win-64)}
-----------------------
conda update -c anaconda numpy

conda upgrade -c conda-forge matplotlib

How do I melt a pandas dataframe?

copy iconCopydownload iconDownload
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob
-----------------------
print(df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades'))
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
    Name  Age  Subject Grade
0    Bob   13  English     C
1   John   16  English     B
2    Foo   14  English     B
3    Bar   15  English    A+
4   Alex   17  English     F
5    Tom   12  English     A
6    Bob   13     Math    A+
7   John   16     Math     B
8    Foo   14     Math     A
9    Bar   15     Math     F
10  Alex   17     Math     D
11   Tom   12     Math     C
print(
    df.melt(
        id_vars=["Name", "Age"],
        value_vars="Math",
        var_name="Subject",
        value_name="Grades",
    )
)
print(
    df.set_index(["Name", "Age"])[["Math"]]
    .stack()
    .reset_index(name="Grade")
    .rename(columns={"level_2": "Subject"})
    .sort_values("Subject")
    .reset_index(drop=True)
)
   Name  Age Subject Grade
0   Bob   13    Math    A+
1  John   16    Math     B
2   Foo   16    Math     A
3   Bar   15    Math     F
4  Alex   15    Math     D
5   Tom   13    Math     C
print(
    df.melt(id_vars=["Name", "Age"])
    .groupby("value", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .rename(columns={"level_2": "Subjects", 0: "Grade"})
    .groupby("Grade", as_index=False)
    .agg(", ".join)
)
  Grade             Name                Subjects
0     A         Foo, Tom           Math, English
1    A+         Bob, Bar           Math, English
2     B  John, John, Foo  Math, English, English
3     C         Bob, Tom           English, Math
4     D             Alex                    Math
5     F        Bar, Alex           Math, English
df = df.melt(id_vars=['Name', 'Age'], var_name='Subject', value_name='Grades')
print(
    df.pivot_table("Grades", ["Name", "Age"], "Subject", aggfunc="first")
    .reset_index()
    .rename_axis(columns=None)
)
   Name  Age English Math
0  Alex   15       F    D
1   Bar   15      A+    F
2   Bob   13       C   A+
3   Foo   16       B    A
4  John   16       B    B
5   Tom   13       A    C
print(
    df.melt(id_vars=["Name", "Age"], var_name="Subject", value_name="Grades")
    .groupby("Name", as_index=False)
    .agg(", ".join)
)
print(
    df.set_index(["Name", "Age"])
    .stack()
    .reset_index()
    .groupby("Name", as_index=False)
    .agg(", ".join)
    .rename({"level_2": "Subjects", 0: "Grades"}, axis=1)
)
   Name       Subjects Grades
0  Alex  Math, English   D, F
1   Bar  Math, English  F, A+
2   Bob  Math, English  A+, C
3   Foo  Math, English   A, B
4  John  Math, English   B, B
5   Tom  Math, English   C, A
print(df.melt(var_name='Column', value_name='Value'))
print(
    df.stack()
    .reset_index(level=1)
    .sort_values("level_1")
    .reset_index(drop=True)
    .set_axis(["Column", "Value"], axis=1)
)
     Column Value
0       Age    16
1       Age    15
2       Age    15
3       Age    16
4       Age    13
5       Age    13
6   English    A+
7   English     B
8   English     B
9   English     A
10  English     F
11  English     C
12     Math     C
13     Math    A+
14     Math     D
15     Math     B
16     Math     F
17     Math     A
18     Name  Alex
19     Name   Bar
20     Name   Tom
21     Name   Foo
22     Name  John
23     Name   Bob

Convert subset of columns to rows by combining columns

copy iconCopydownload iconDownload
df.columns = df.columns.str.split('_', expand=True)
df.stack().rename_axis(['code', 'type'])
           click  view
code type             
1    A         3  3000
     B         5  1200
2    A        23  2300
     B         3   300
-----------------------
df.columns = df.columns.str.split('_', expand=True)
df.stack().rename_axis(['code', 'type'])
           click  view
code type             
1    A         3  3000
     B         5  1200
2    A        23  2300
     B         3   300
-----------------------
out = pd.wide_to_long(df.reset_index(),
                      ['view','click'],
                      i='Code',
                      j='type',
                      sep='_',
                      suffix='\\w+')
           view  click
Code type             
1    A     3000      3
2    A     2300     23
1    B     1200      5
2    B      300      3
-----------------------
df = pd.DataFrame({"Code":[1,2], "view_A":[3000, 2300], "click_A":[3, 23], 
                   "view_B":[1200, 300], "click_B":[5, 3]})
# pip install pyjanitor
import pandas as pd
import janitor

(df.pivot_longer(index='Code', 
                 names_to=('.value', 'type'), 
                 names_sep='_')
   .set_index(['Code', 'type'])
)

           view  click
Code type
1    A     3000      3
2    A     2300     23
1    B     1200      5
2    B      300      3
-----------------------
df = pd.DataFrame({"Code":[1,2], "view_A":[3000, 2300], "click_A":[3, 23], 
                   "view_B":[1200, 300], "click_B":[5, 3]})
# pip install pyjanitor
import pandas as pd
import janitor

(df.pivot_longer(index='Code', 
                 names_to=('.value', 'type'), 
                 names_sep='_')
   .set_index(['Code', 'type'])
)

           view  click
Code type
1    A     3000      3
2    A     2300     23
1    B     1200      5
2    B      300      3

Community Discussions

Trending Discussions on pandas
  • Installing scipy and scikit-learn on apple m1
  • Error while downloading the requirements using pip install (setup command: use_2to3 is invalid.)
  • Mapping complex JSON to Pandas Dataframe
  • AttributeError: Can't get attribute 'new_block' on &lt;module 'pandas.core.internals.blocks'&gt;
  • How to update pandas DataFrame.drop() for Future Warning - all arguments of DataFrame.drop except for the argument 'labels' will be keyword-only
  • Cannot set up a conda environment with python 3.10
  • ImportError: cannot import name 'ABCIndexClass' from 'pandas.core.dtypes.generic'
  • Merge two pandas DataFrame based on partial match
  • Create a new column in a Pandas DataFrame from existing column names
  • After conda update, python kernel crashes when matplotlib is used
Trending Discussions on pandas

QUESTION

Installing scipy and scikit-learn on apple m1

Asked 2022-Mar-22 at 06:21

The installation on the m1 chip for the following packages: Numpy 1.21.1, pandas 1.3.0, torch 1.9.0 and a few other ones works fine for me. They also seem to work properly while testing them. However when I try to install scipy or scikit-learn via pip this error appears:

ERROR: Failed building wheel for numpy

Failed to build numpy

ERROR: Could not build wheels for numpy which use PEP 517 and cannot be installed directly

Why should Numpy be build again when I have the latest version from pip already installed?

Every previous installation was done using python3.9 -m pip install ... on Mac OS 11.3.1 with the apple m1 chip.

Maybe somebody knows how to deal with this error or if its just a matter of time.

ANSWER

Answered 2021-Aug-02 at 14:33

Please see this note of scikit-learn about

Installing on Apple Silicon M1 hardware

The recently introduced macos/arm64 platform (sometimes also known as macos/aarch64) requires the open source community to upgrade the build configuation and automation to properly support it.

At the time of writing (January 2021), the only way to get a working installation of scikit-learn on this hardware is to install scikit-learn and its dependencies from the conda-forge distribution, for instance using the miniforge installers:

https://github.com/conda-forge/miniforge

The following issue tracks progress on making it possible to install scikit-learn from PyPI with pip:

https://github.com/scikit-learn/scikit-learn/issues/19137

Source https://stackoverflow.com/questions/68620927

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install pandas

To install pandas from source you need Cython in addition to the normal dependencies above. Cython can be installed from PyPI:.

Support

The official documentation is hosted on PyData.org: https://pandas.pydata.org/pandas-docs/stable.

DOWNLOAD this Library from

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

Explore Related Topics

Share this Page

share link
Compare Python Libraries with Highest Quality
Compare Python Libraries with Highest Security
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases

Save this library and start creating your kit

  • © 2022 Open Weaver Inc.