Pandas: Combining DataFrames with nested arrays or merging the JSON output

Question

I'm taking a standard data frame and creating various sub-set data frames of summary data with nested arrays. I then need to combine the sub-set data frames in a way that gives me the intended JSON output. (I used MaxU's answer to format most of my code; Convert Pandas Dataframe to nested JSON)

First few rows of my standard data frame (If necessary I could give all 58 rows from this example): df

    ID         PRI_AFF   PRI_DEP      LOA    STATE
0   5571             M              Basic        A
1   5030             T  14700000     Blue        A
2   5030             T  14700000     Blue        A
3   5030             T  14700000     Blue        A
4   4014             T  14700000     Blue        A
5   2230             T  14700000      UFM        A
6   2230             T  14700000      UFM        A
7   2150             F  95011000   Bronze        A
8   2150             F  95011000   Bronze        A
9   2150             F  95011000   Bronze        A
10  2150             F  95011000   Bronze        A

From here I use the following Python:

 PAFF_df = pd.DataFrame(df.groupby(['PRI_DEP','PRI_AFF'])['ID'].nunique().unstack().reset_index().fillna(0))
 LOA_df = pd.DataFrame(df.groupby(['PRI_DEP','LOA'])['ID'].nunique().unstack().reset_index().fillna(0))
 ST_df = pd.DataFrame(df.groupby(['PRI_DEP','STATE'])['ID'].nunique().unstack().reset_index().fillna(0))

 Nested_PAFF_df = (PAFF_df.groupby(['PRI_DEP'], as_index=True)
      .apply(lambda x: x[['A','E','F','L','M','T']].to_dict('r'))
      .reset_index()
      .rename(columns={0:'Primary_Affiliation'}))

 Nested_LOA_df = (LOA_df.groupby(['PRI_DEP'], as_index=True)
      .apply(lambda x: x[['Basic','Blue','Bronze','Invalid','UFM']].to_dict('r'))
      .reset_index()
      .rename(columns={0:'LOA'}))

 Nested_ST_df = (ST_df.groupby(['PRI_DEP'], as_index=True)
      .apply(lambda x: x[['A','E']].to_dict('r'))
      .reset_index()
      .rename(columns={0:'STATE'}))

Which gives me the appropriate nested JSONs using: .to_json(orient='records')

Primary Affiliation JSON:

[{"PRI_DEP":" ","Primary_Affiliation":[{"A":0.0,"E":0.0,"F":0.0,"M":2.0,"L":0.0,"T":0.0}]},{"PRI_DEP":"14700000","Primary_Affiliation":[{"A":0.0,"E":3.0,"F":0.0,"M":1.0,"L":1.0,"T":19.0}]},{"PRI_DEP":"95011000","Primary_Affiliation":[{"A":0.0,"E":0.0,"F":1.0,"M":0.0,"L":0.0,"T":0.0}]},{"PRI_DEP":"Null","Primary_Affiliation":[{"A":0.0,"E":1.0,"F":0.0,"M":0.0,"L":0.0,"T":0.0}]},{"PRI_DEP":"ST010000","Primary_Affiliation":[{"A":1.0,"E":0.0,"F":0.0,"M":0.0,"L":0.0,"T":1.0}]}]

LOA JSON:

[{"PRI_DEP":" ","LOA":[{"Blue":0.0,"UFM":0.0,"Invalid":0.0,"Bronze":1.0,"Basic":1.0}]},{"PRI_DEP":"14700000","LOA":[{"Blue":14.0,"UFM":5.0,"Invalid":1.0,"Bronze":4.0,"Basic":0.0}]},{"PRI_DEP":"95011000","LOA":[{"Blue":0.0,"UFM":0.0,"Invalid":0.0,"Bronze":1.0,"Basic":0.0}]},{"PRI_DEP":"Null","LOA":[{"Blue":0.0,"UFM":0.0,"Invalid":0.0,"Bronze":1.0,"Basic":0.0}]},{"PRI_DEP":"ST010000","LOA":[{"Blue":0.0,"UFM":0.0,"Invalid":1.0,"Bronze":0.0,"Basic":1.0}]}]

Status JSON:

[{"PRI_DEP":" ","STATE":[{"A":2.0,"E":0.0}]},{"PRI_DEP":"14700000","STATE":[{"A":23.0,"E":1.0}]},{"PRI_DEP":"95011000","STATE":[{"A":1.0,"E":0.0}]},{"PRI_DEP":"Null","STATE":[{"A":1.0,"E":0.0}]},{"PRI_DEP":"ST010000","STATE":[{"A":2.0,"E":0.0}]}]

Now I would like to somehow get these all represented in a single JSON by PRI_DEP.

So the desired JSON would be like this (Updated for easier reading):

[{"PRI_DEP":" ",
    "Primary_Affiliation":
        [{"A":0.0,"E":0.0,"F":0.0,"M":2.0,"L":0.0,"T":0.0}],
    "LOA": 
        [{"Blue":0.0,"UFM":0.0,"Invalid":0.0,"Bronze":1.0,"Basic":1.0}],
    "STATE":
        [{"A":2.0,"E":0.0}]},
 {"PRI_DEP":"14700000",
    "Primary_Affiliation": 
        [{"A":0.0,"E":3.0,"F":0.0,"M":1.0,"L":1.0,"T":19.0}],
    "LOA": 
        [{"Blue":14.0,"UFM":5.0,"Invalid":1.0,"Bronze":4.0,"Basic":0.0}],
    "STATE":
        [{"A":23.0,"E":1.0}]}, 
 {"PRI_DEP":"95011000",
    "Primary_Affiliation":
        [{"A":0.0,"E":0.0,"F":1.0,"M":0.0,"L":0.0,"T":0.0}],
    "LOA":
        [{"Blue":0.0,"UFM":0.0,"Invalid":0.0,"Bronze":1.0,"Basic":0.0}],
    "STATE":
        [{"A":1.0,"E":0.0}]},
 {"PRI_DEP":"Null",
    "Primary_Affiliation": 
        [{"A":0.0,"E":1.0,"F":0.0,"M":0.0,"L":0.0,"T":0.0}],
    "LOA":
        [{"Blue":0.0,"UFM":0.0,"Invalid":0.0,"Bronze":1.0,"Basic":0.0}],
    "STATE":
        [{"A":1.0,"E":0.0}]},
 {"PRI_DEP":"ST010000",
    "Primary_Affiliation":
        [{"A":1.0,"E":0.0,"F":0.0,"M":0.0,"L":0.0,"T":1.0}],
    "LOA":
        [{"Blue":0.0,"UFM":0.0,"Invalid":1.0,"Bronze":0.0,"Basic":1.0}],
    "STATE":
        [{"A":2.0,"E":0.0}]}]

I purposefully only put in the first record, but I will update with the the remaining records. There is only a few more. — Drew Aschenbrener, Aug 03 '18 at 16:04

score 0 · Accepted Answer · answered Aug 03 '18 at 18:18

I just kept playing with different ways of combining dataframes, and I think I got the answer.

After the python code in my original post (setting up the nested groups), I did the following:

Group_frames = [Nested_PAFF_df.set_index('PRI_DEP'), Nested_LOA_df.set_index('PRI_DEP'), Nested_ST_df.set_index('PRI_DEP')]
result = pd.concat(Group_frames, axis=1).reset_index()
print(result.to_json(orient='records'))

Pandas: Combining DataFrames with nested arrays or merging the JSON output

1 Answers1