CODEWITHSUNDEEP
pythonpandas
azw
azw

Reputation: 23

Collapse rows in pandas dataframe dataset

I'm new to Pandas and am attempting to create a denormalized flat dataset and would like to gauge whether it’s even possible. I’m starting with two dataframes, a parent and child, that can conceptually be joined on a single column (‘PID’).

Here is the parent dataframe:

parentData = [(1,’A’,100), (2,’B’,200)]
parentCols = [‘PID’, ‘PATTR1’, ‘PATTR1’]
parentDf = pd.DataFrame.from_records(parentData, columns=parentCols)

Parent Dataframe
     PID  PATTR1  PATTR2
0      1       A     100
1      2       B     200

Here is the child dataframe:

childData = [(201,1,’AA’,2100), (202,2,’BB’,2200), (203,2,’CC’,2300)]
childCols = [‘CID’, ‘PID’, ‘CATTR1’, ‘CATTR1’]
childDf = pd.DataFrame.from_records(childData, columns=childCols)

Child Dataframe
     CID  PID  PATTR1  PATTR2
0    201    1      AA    2100
1    202    2      BB    2200
2    203    2      CC    2300

Here’s the merge of the parent and the child:

mergedDf = parentDf.merge(childDf, left_on=’PID’, right_on=’PID’, how=’outer’)

Parent merged with Child dataframe
     PID  PATTR1  PATTR2  CID  CATTR1  CATTR2
0      1       A     100  201      AA    2100
1      2       B     200  202      BB    2200
2      2       B     200  203      CC    2300

And here’s what the desired output is:

                          | ????                 | ????
     PID  PATTR1  PATTR2  | CID  CATTR1  CATTR2  | CID  CATTR1  CATTR2
0      1       A     100  | 201      AA    2100  |
1      2       B     200  | 202      BB    2200  | 203      CC    2300

After searching and reading through the merge, reshaping, etc. sections of the Pandas API docs, I wasn’t sure if the desired output is possible or not.

Thanks in advance for any advice and/or help, it is greatly appreciated.

Upvotes: 2

Views: 237

Answers (1)

BENY
BENY

Reputation: 323276

After you get the mergedDf , we create a new para 'G' and using unstack(PS: this is long to wide question )

mergedDf.assign(G=mergedDf.groupby('PID').cumcount())\
     .set_index(['PID','PATTR1','PATTR2','G'])\
       .unstack().swaplevel(0,1,1)\
               .sort_index(1,level=0)
Out[218]: 
G                      0                     1               
                  CATTR1  CATTR2    CID CATTR1  CATTR2    CID
PID PATTR1 PATTR2                                            
1   A      100        AA  2100.0  201.0   None     NaN    NaN
2   B      200        BB  2200.0  202.0     CC  2300.0  203.0

Upvotes: 1

Related Questions