Pandas - Creating a Dataframe from a list of Dataframes based on some conditions

Question

I have a list of dataframes. The list might contain 3 or more dataframes. Number of rows in each dataframe can differ from one another. The dataframes look the following.

I have to loop through the list and create a single dataframe out of these based on the following conditions.

If both Line and Point of two dataframes match, create a new Res column having the value of Res of the second dataframe; value of Res of second dataframe being inserted in the same row.
If both Line and Point of two dataframes do not match, insert a new row, with the value of Res of second dataframe in the new Res column.

For the above mentioned sample dataframes, the resultant dataframe should look like the following:

I have tried merging etc but I am not able to do this in Pandas. Please help me to solve this. Thanks.

did you try merge ?

BENY
– BENY

2018-01-03 17:31:15 +00:00
Commented Jan 3, 2018 at 17:31 — BENY
– BENY, Commented Jan 3, 2018 at 17:31

BENY · Accepted Answer · 2018-01-03 17:38:06Z

1

Data input:

df1=pd.DataFrame({'Line':[1,1,1],'Point':[2,3,4],'Res':[1,2,3]})

df2=pd.DataFrame({'Line':[1,2,3],'Point':[2,3,4],'Res':[1,2,3]})

df3=pd.DataFrame({'Line':[3,3,3],'Point':[4,3,4],'Res':[1,2,3]})

We using reduce merge outer

l=[df1,df2,df3]

import functools


functools.reduce(lambda left,right: pd.merge(left,right,on=['Line','Point'],how='outer'), l)
Out[228]: 
   Line  Point  Res_x  Res_y  Res
0     1      2    1.0    1.0  NaN
1     1      3    2.0    NaN  NaN
2     1      4    3.0    NaN  NaN
3     2      3    NaN    2.0  NaN
4     3      4    NaN    3.0  1.0
5     3      4    NaN    3.0  3.0
6     3      3    NaN    NaN  2.0

answered Jan 3, 2018 at 17:38

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BENY Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ just make up some :-)

NixMan Over a year ago

Thanks a lot. Worked like a charm.

cs95 · Accepted Answer · 2018-01-03 19:45:34Z

1

Using Wen's data, first perform concatenation using pd.concat, and then groupby and reshape.

df_list = [df1, df2, df3]

i = [df.rename(columns={'Res' : 'Res{}'.format(i)}) for i, df in enumerate(df_list, 1)]

df = pd.concat(df_list)\
       .groupby(['Line', 'Point'], sort=False)\
       .max()\
       .reset_index()

df

   Line  Point  Res1  Res2  Res3
0     1      2   1.0   1.0   NaN
1     1      3   2.0   NaN   NaN
2     1      4   3.0   NaN   NaN
3     2      3   NaN   2.0   NaN
4     3      4   NaN   3.0   3.0
5     3      3   NaN   NaN   2.0

answered Jan 3, 2018 at 19:45

cs95

406k106 gold badges745 silver badges798 bronze badges

Collectives™ on Stack Overflow

Pandas - Creating a Dataframe from a list of Dataframes based on some conditions

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related