Convert multiple rows into a single row using pandas

Question

I want to change from this:

+--------+------------+-----------+-----------+-------------+--------------+
| Nominal| IP_Address | MaxTxPower| gsmSecName| CellName    | fingerprint  |
+--------+------------+-----------+-----------+-------------+--------------+
| DU0949 | 172.18.26.1| 15000     | DU0949A   |             |              |
+--------+------------+-----------+-----------+-------------+--------------+
| DU0949 |            | 40000     |           | DU0949U09A0 | DU0949_BB2   |
+--------+------------+-----------+-----------+-------------+--------------+
| LO0004 | 172.18.26.2| 15000     | LO0004D   |             |              |
+--------+------------+-----------+-----------+-------------+--------------+
| LO0004 |            | 40000     |           | LO0004U09A3 | LO0004_BB2   |
----------------------------------------------------------------------------

into this:

+--------+------------+-----------+-----------+------------+--------------+-----------+
| Nominal| IP_Address | MaxTxPower| gsmSecName| CellName   | fingerprint  | MaxTxPower|
+--------+------------+-----------+-----------+------------+--------------+-----------+
| DU0949 | 172.18.26.1| 15000     | DU0949A   | DU0949U09A0| DU0949_BB2   | 40000     |
+--------+------------+-----------+-----------+------------+--------------+-----------+
| LO0004 | 172.18.26.2| 15000     | LO0004D   | LO0004U09A3| LO0004_BB2   | 40000     |
+--------+------------+-----------+-----------+------------+--------------+-----------+

using pandas

I am using the below code but not getting the proper data

import pandas as pd
data = {
    'Nominal': ['DU0949', 'DU0949', 'LO0004', 'LO0004'],
    '2G_IP_Address': ['172.18.26.1', '', '172.18.26.2', ''],
    'MaxTxPower': [15000, 40000, 15000, 40000],
    'gsmSecName': ['DU0949A', '', 'LO0004D', ''],
    'CellName': ['', 'DU0949U09A0', '', 'LO0004U09A3'],
    'fingerprint': ['', 'DU0949_BB2', '', 'LO0004_BB2']
}

df = pd.DataFrame(data)
df = df[(df['IP_Address'] != '') & (df['gsmSecName'] != '')]
df = df.reset_index(drop=True)
df.columns = ['Nominal', 'IP_Address', 'MaxTxPower_1', 'gsmSectorName', 'CellName_1', 'fingerprint_1']
df = df[['Nominal', 'IP_Address', 'MaxTxPower_1', 'gsmSectorName', 'CellName_1', 'fingerprint_1']]
df = df.rename(columns={'MaxTxPower_1': 'MaxTxPower', 'CellName_1': 'CellName', 'fingerprint_1':'fingerprint'})
print(df)

[JFYI](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — Danila Ganchar, Jul 04 '23 at 11:14

score 1 · Answer 1 · answered Jul 04 '23 at 11:32

One way to do it

df[df.IP_Address==''].drop('IP_Address',axis=1).merge(df[df.IP_Address!=''].drop(['gsmSecName', 'CellName', 'fingerprint'], axis=1), on='Nominal')

Assuming that the criteria (I reverse engineered that from your example; you didn't really say anything) being that each 'Nominal' appears twice: once with an IP, another without

Example

import pandas as pd

df = pd.DataFrame({'Nominal':['DU0949', 'DU0949', 'LO0004', 'LO0004'], 'IP_Address':['172.18.26.1', '', '172.18.26.2', ''], 'MaxTxPower':[15000, 40000, 15000, 40000], 'gsmSecName':['', 'DU0949A', '', 'LO0004D'], 'CellName':['', 'stg', '', 'stg2'], 'fingerprint':['', 'f1', '', 'f2']})

df[df.IP_Address==''].drop('IP_Address',axis=1).merge(df[df.IP_Address!=''].drop(['gsmSecName', 'CellName', 'fingerprint'], axis=1), on='Nominal')

Returns

  Nominal  MaxTxPower_x gsmSecName CellName fingerprint   IP_Address  MaxTxPower_y
0  DU0949         40000    DU0949A      stg          f1  172.18.26.1         15000
1  LO0004         40000    LO0004D     stg2          f2  172.18.26.2         15000

score 0 · Accepted Answer · answered Jul 04 '23 at 11:32

You can reshape using stack/unstack and groupby.cumcount to de-duplicate:

out = (df
   .replace('', np.nan).set_index('Nominal').stack().to_frame(name='value')
   .assign(n=lambda d: d.groupby(level=[0, 1]).cumcount())
   .set_index('n', append=True)
   ['value']
   .unstack(level=[-1, -2]).sort_index(level=0, axis=1)
   .droplevel(0, axis=1).reset_index()
)

Output:

  Nominal     CellName   IP_Address MaxTxPower fingerprint gsmSecName MaxTxPower
0  DU0949  DU0949U09A0  172.18.26.1      15000  DU0949_BB2    DU0949A      40000
1  LO0004  LO0004U09A3  172.18.26.2      15000  LO0004_BB2    LO0004D      40000

Thanks a ton for the help, it is working now. – prashant sharma Jul 05 '23 at 09:53 — prashant sharma, Jul 05 '23 at 09:53

Convert multiple rows into a single row using pandas

2 Answers2