pythonjsonnested

create dataframe from nested list of lists in python?


list is like below -

[[{"1":"","2":"","3":"Jurisdiction of"},{"1":"Name of Subsidiary","2":"","3":"Formation"},{"1":"BA International, L.L.C.","2":"","3":"Delaware"},{"1":"Caribesock, Inc.","2":"","3":"Delaware"},{"1":"Caribetex, Inc.","2":"","3":"Delaware"},{"1":"CASA International, LLC","2":"","3":"Delaware"},{"1":"Ceibena Del, Inc.","2":"","3":"Delaware"},{"1":"Hanes Menswear, LLC","2":"","3":"Delaware"},{"1":"Hanes Puerto Rico, Inc.","2":"","3":"Delaware"},{"1":"Hanesbrands Direct, LLC","2":"","3":"Colorado"},{"1":"Hanesbrands Distribution, Inc.","2":"","3":"Delaware"},{"1":"HBI Branded Apparel Limited, Inc.","2":"","3":"Delaware"},{"1":"HBI Branded Apparel Enterprises, LLC","2":"","3":"Delaware"},{"1":"HBI Playtex BATH LLC","2":"","3":"Delaware"},{"1":"HbI International, LLC","2":"","3":"Delaware"},{"1":"HBI Sourcing, LLC","2":"","3":"Delaware"},{"1":"Inner Self, LLC","2":"","3":"Delaware"},{"1":"Jasper-Costa Rica, L.L.C.","2":"","3":"Delaware"},{"1":"National Textiles, L.L.C.","2":"","3":"Delaware"},{"1":"Playtex Dorado, LLC","2":"","3":"Delaware"},{"1":"Playtex Industries, Inc.","2":"","3":"Delaware"},{"1":"Playtex Marketing Corporation (50% owned)","2":"","3":"Delaware"},{"1":"Seamless Textiles, LLC","2":"","3":"Delaware"},{"1":"UPCR, Inc.","2":"","3":"Delaware"},{"1":"UPEL, Inc.","2":"","3":"Delaware"}],

[{"1":"","2":"","3":"Jurisdiction of"},{"1":"Name of Subsidiary","2":"","3":"Formation"},{"1":"Allende Internacional S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Bali Dominicana, Inc.","2":"","3":"Panama/DR"},{"1":"Bali Dominicana Textiles, S.A.","2":"","3":"Panama/DR"}],[{"1":"","2":"","3":"Jurisdiction of"},{"1":"Name of Subsidiary","2":"","3":"Formation"},{"1":"Bal-Mex S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Canadelle LP","2":"","3":"Canada"},{"1":"Canadelle Holdings Corporation Limited","2":"","3":"Canada"},{"1":"Cartex Manufacturera S. A.","2":"","3":"Costa Rica"},{"1":"Caysock, Inc.","2":"","3":"Cayman Islands"},{"1":"Caytex, Inc.","2":"","3":"Cayman Islands"},{"1":"Caywear, Inc.","2":"","3":"Cayman Islands"},{"1":"Ceiba Industrial, S. de R.L.","2":"","3":"Honduras"},{"1":"Champion Products S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Choloma, Inc.","2":"","3":"Cayman Islands"},{"1":"Confecciones Atlantida S. de R.L.","2":"","3":"Honduras"},{"1":"Confecciones de Nueva Rosita S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Confecciones El Pedregal Inc.","2":"","3":"Cayman Islands"},{"1":"Confecciones El Pedregal S.A. de C.V.","2":"","3":"El Salvador"},{"1":"Confecciones del Valle, S. de R.L. de C.V.","2":"","3":"Honduras"},{"1":"Confecciones Jiboa S.A. de C.V.","2":"","3":"El Salvador"},{"1":"Confecciones La Caleta, Inc.","2":"","3":"Cayman Islands"},{"1":"Confecciones La Herradura S.A. de C.V.","2":"","3":"El Salvador"},{"1":"Confecciones La Libertad, S.A. de C.V.","2":"","3":"El Salvador"},{"1":"DFK International Ltd.","2":"","3":"Hong Kong"},{"1":"Dos Rios Enterprises, Inc.","2":"","3":"Cayman Islands"},{"1":"Hanes Brands Incorporated de Costa Rica, S.A.","2":"","3":"Costa Rica"},{"1":"Hanes Caribe, Inc.","2":"","3":"Cayman Islands"},{"1":"Hanes Choloma, S. de R. L.","2":"","3":"Honduras"},{"1":"Hanes Colombia, S.A.","2":"","3":"Colombia"},{"1":"Hanes de Centro America S.A.","2":"","3":"Guatemala"},{"1":"Hanes de El Salvador, S.A. de C.V.","2":"","3":"El Salvador"},{"1":"Hanes de Honduras S. de R.L. de C.V.","2":"","3":"Honduras"},{"1":"Hanes Dominican, Inc.","2":"","3":"Cayman Islands"},{"1":"Hanes Menswear Puerto Rico, Inc.","2":"","3":"Puerto Rico"},{"1":"Hanes Panama Inc.","2":"","3":"Panama"},{"1":"Hanesbrands Apparel India Private Limited","2":"","3":"India"},{"1":"Hanesbrands Argentina S.A.","2":"","3":"Argentina"},{"1":"Hanesbrands Brasil Textil Ltda.","2":"","3":"Brazil"},{"1":"Hanesbrands Canada NSULC","2":"","3":"Canada"},{"1":"Hanesbrands Dominicana, Inc.","2":"","3":"Cayman Islands"},{"1":"Hanesbrands Europe GmbH","2":"","3":"Germany"},{"1":"Hanesbrands International (Shanghai) Co. Ltd.","2":"","3":"China"},{"1":"Hanesbrands Japan Inc.","2":"","3":"Japan"},{"1":"Hanesbrands Philippines Inc.","2":"","3":"Philippines"},{"1":"Hanesbrands (HK) Limited","2":"","3":"Hong Kong"}],[{"1":"","2":"","3":"Jurisdiction of"},{"1":"Name of Subsidiary","2":"","3":"Formation"},{"1":"Hanesbrands (Thailand) Ltd.","2":"","3":"Thailand"},{"1":"HBI Alpha Holdings, Inc.","2":"","3":"Cayman Islands"},{"1":"HBI Beta Holdings, Inc.","2":"","3":"Cayman Islands"},{"1":"HBI Compania de Servicios, S.A. de C.V.","2":"","3":"El Salvador"},{"1":"HBI RH Mexico, S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"HBI Manufacturing (Thailand) Ltd.","2":"","3":"Thailand"},{"1":"HBI Servicios Administrativos de Costa Rica, S.A.","2":"","3":"Costa Rica"},{"1":"HBI Socks de Honduras, S. de R.L. de C.V.","2":"","3":"Honduras"},{"1":"HBI Sourcing Asia Limited","2":"","3":"Hong Kong"},{"1":"Indumentaria Andina S.A.","2":"","3":"Argentina"},{"1":"Industria Textileras del Este, S. de R.L.","2":"","3":"Costa Rica"},{"1":"Industrias Internacionales de San Pedro S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Inmobiliaria Rinplay, S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"J.E. Morgan de Honduras, S.A.","2":"","3":"Honduras"},{"1":"Jasper Honduras, S.A.","2":"","3":"Honduras"},{"1":"Jogbra Honduras, S.A.","2":"","3":"Honduras"},{"1":"Madero Internacional S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Manufacturera Ceibena S. de R.L.","2":"","3":"Honduras"},{"1":"Manufacturera Comalapa S.A. de C.V.","2":"","3":"El Salvador"},{"1":"Manufacturera de Cartago, S.R.L.","2":"","3":"Costa Rica"},{"1":"Manufacturera San Pedro Sula, S. de R.L.","2":"","3":"Honduras"},{"1":"Monclova Internacional S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Playtex Puerto Rico, Inc.","2":"","3":"Puerto Rico"},{"1":"PT HBI Sourcing Indonesia","2":"","3":"Indonesia"},{"1":"PTX (D.R.), Inc.","2":"","3":"Cayman Islands"},{"1":"Rinplay S. de R.L. de C.V.","2":"","3":"Mexico"},{"1":"Santiago Internacional Textil Limitada (in liquidation)","2":"","3":"Chile"},{"1":"Seamless Puerto Rico, Inc.","2":"","3":"Puerto Rico"},{"1":"Servicios Rinplay, S. De R.L. de C.V.","2":"","3":"Mexico"},{"1":"Servicios de Soporte Intimate Apparel, S de RL","2":"","3":"Costa Rica"},{"1":"SL Sourcing India Private Ltd.\n    (to be renamed HBI Sourcing India Private Ltd.)","2":"","3":"India"},{"1":"SN Fibers (49% owned)","2":"","3":"Israel"},{"1":"Socks Dominicana S.A.","2":"","3":"Dominican Republic"},{"1":"Texlee El Salvador, S.A. de C.V.","2":"","3":"El Salvador"},{"1":"The Harwood Honduras Companies, S. de R.L.","2":"","3":"Honduras"},{"1":"TOS Dominicana, Inc.","2":"","3":"Cayman Islands"}]]

As i have given a line break to give an understanding of data, it is nested list with same headers, how can i convert this list to dataframe with single header and data under it .


Solution

  • First, we will flatten out the list of lists of dictionaries into a list of dictionaries (without any nested lists inside). We will use a list comprehension. Assuming your big list of data is called big_list:

    list_of_dicts = [dictionary for sub_list in big_list for dictionary in sub_list]
    

    I see here that you have your headers/column names in the first and second row of the first list. We have to handle these exceptions manually, so we take them out of the list we just made:

    list_of_dicts = list_of_dicts[2:]
    

    Then we will utilise pd.DataFrame.from_dict to create a big dataframe with one row per dictionary in the big list we just made.

    df = pd.DataFrame.from_dict(list_of_dicts)
    

    We remove the blank middle column created by the {"2":""} entries in all the dictionaries:

    df = df.drop("2", axis=1)
    

    Finally we rename the columns to match your headers:

    df = df.rename(columns={'1':'Name of Subsidiary', '3': 'Jurisdiction of Formation'})
    

    EDIT: I had not noticed that every sub-list has a header. We will do the same thing we did by doing list_of_dicts = list_of_dicts[2:]. However now we will do it for every sub-list in the list comprehension:

    list_of_dicts = [
        dictionary for sub_list in big_list
        for dictionary in sub_list[2:] # We do [2:] to get rid of headers
    ]
    

    Then doing list_of_dicts = list_of_dicts[2:] is no longer necessary.