Build a data frame from the existing data frame which has the distinct id

Admin 1
Viewed 39

I have a data frame like in the figure.

I have multiple id with different columns.

I want to build another dataframe that only has the distinct id and also the dataframes has all the columns for that id.

could you help me with that? Thank you so much

import pandas as pd
import numpy as np


df = pd.DataFrame()
df['id'] = [2, 3, 3,2,1]
df['c2'] = [1, 10, 6, 7, 4]
df['c3'] = [11, 23, 9, 10,23]
print(df)

dfn = pd.DataFrame()
dfn['id'] = [2, 3,1]
dfn['c2'] = [1, 10, 4]
dfn['c3'] = [11, 23, 23]
dfn['c4'] = [7, 6,  0]
dfn['c5'] = [10, 9,  0]
dfn

The first one is that I have and the second one is that I want

pandas python
2 Answers

You can use set_index / unstack at starting point of the transformation to flatten your dataframe. You can execute the following code step-by-step to follow the transformation to your final dataframe:

out = (
    df.set_index('id').rename_axis(columns='col')
      .stack().rename('val').reset_index()
      .assign(col=lambda x: x.groupby('id').cumcount().add(2))
      .pivot_table('val', 'id', 'col', fill_value=0, sort=False)
      .add_prefix('c').reset_index().rename_axis(columns=None)
)

Output:

>>> out
   id  c2  c3  c4  c5
0   2   1  11   7  10
1   3  10  23   6   9
2   1   4  23   0   0
user_152399510

Here is a different approach:

df.filter(regex='c\d+', axis=1) \
    .groupby(df['id'], sort=False).apply(np.ravel).apply(pd.Series) \
    .rename(lambda x: f"c{x + 2}", axis=1).reset_index().fillna(0)

Output:

   id    c2    c3   c4    c5
0   1   4.0  23.0  0.0   0.0
1   2   1.0  11.0  7.0  10.0
2   3  10.0  23.0  6.0   9.0
user_93773660
Related