CODEWITHSUNDEEP
pythonpandas
Ali
Ali

Reputation: 466

combining two rows of dataframe

Let us say that we have the following dataframe:

df = pd.DataFrame(
    data={
        'from': [103, 102, 104, 105],
        'to': [104, 105, 103, 102],
        'id': [1] * 4,
        'p': [415, 1203.11, -414.35, -1197.37],
        'q': [0, -395.44, 62.23, 489.83]
    })

or 

     from      to  id        p       q
0     103     104   1   415.00    0.00
1     102     105   1  1203.11 -395.44
2     104     103   1  -414.35   62.23
3     105     102   1 -1197.37  489.83

The goal is to combine the rows that have the same from and to values. In the example above, rows 0 and 2, and rows 1 and 3, needs to be combined.

The output is supposed to be as follows:

   from      to  id        p       q       p1      q1
0   103     104   1   415.00    0.00  -414.35   62.23
1   102     105   1  1203.11 -395.44 -1197.37  489.83

Of course, the following is also acceptable:

     from   to  id        p       q       p1      q1
0     104  103   1  -414.35   62.23   415.00    0.00
1     105  102   1 -1197.37  489.83  1203.11 -395.44

Any help is appreciated :)

Upvotes: 1

Views: 59

Answers (2)

Allen Qin
Allen Qin

Reputation: 19947

Another solution:

#sort from and to first
df[['from', 'to']]=np.sort(df[['from', 'to']])
(
    df.groupby(['from', 'to'])
    #groupby and concatenate all q and q in the same group to 1 row
    .apply(lambda x:  x[['p','q']].values.reshape(1,-1)[0])
    #convert the list of p and q to a DataFrame
    .pipe(lambda x: pd.DataFrame(x.tolist(), index=x.index))
    #rename the columns
    .rename(columns=lambda x: f'p{x//2}')
    .reset_index()
)

    from    to      p0      p0      p1          p1
0   103     104     415.00  0.00    -414.35     62.23
1   444     999230  1203.11 -395.44 -1197.37    489.83

Upvotes: 1

jezrael
jezrael

Reputation: 862671

First sorting both columns from and to by numpy.sort, then create counter Series by GroupBy.cumcount, reshape by DataFrame.set_index and DataFrame.unstack with sorting second level by DataFrame.sort_index, last flatten MultiIndex with f-strings and convert Multiindex in index to columns by DataFrame.reset_index:

df[['from','to']] = np.sort(df[['from','to']], axis=1)
g = df.groupby(['from','to']).cumcount()

df = df.set_index(['from','to','id', g]).unstack().sort_index(level=1, axis=1)
df.columns = [f'{a}{b}' for a, b in df.columns]
df = df.reset_index()
print(df)
   from      to  id       p0      q0       p1      q1
0   103     104   1   415.00    0.00  -414.35   62.23
1   444  999230   1  1203.11 -395.44 -1197.37  489.83

Upvotes: 2

Related Questions