Split an aggregated value into a distinct count in python, returning new rows

user_5942100 0
Viewed 98

I have data in an excel file, df that holds aggregated values per ID. I am looking to break this down to its distinct count and create a new record for each.

Data

    A    B    C

    2    3    1

Desired

 count   ID

  1      A01

  1      A02
  
  1      B01

  1      B02

  1      B03

  1      C01

Doing:

import pandas as pd
from numpy.random import randint

df = pd.DataFrame(columns=['A', 'B', 'C'])
     for i in range(5):
     df.loc[i] = ['ID' + str(i)] + list(randint(10, size=2))

I am thinking I can go about it this way, however, this is not stacking all the necessary IDs, consecutively.

Any suggestion or advice will be appreciated.

pandas numpy python
2 Answers

Let's try melt to reshape the data, reindex + repeat to duplicate the rows, and groupby + cumcount + zfill to create the suffixes:

import pandas as pd

df = pd.DataFrame({'A': {0: 2}, 'B': {0: 3}, 'C': {0: 1}})

# Melt Table Into New Form
df = df.melt(col_level=0, value_name='count', var_name='ID')
# Repeat Based on Count
df = df.reindex(df.index.repeat(df['count']))
# Set Count To 1
df['count'] = 1

# Add Suffix to Each ID
df['ID'] = df['ID'] + (
    (df.groupby('ID').cumcount() + 1)
        .astype(str)
        .str.zfill(2)
)

# Reorder Columns
df = df[['count', 'ID']]

print(df)

df:

   count   ID
0      1  A01
0      1  A02
1      1  B01
1      1  B02
1      1  B03
2      1  C01
user_154978880

Do you want this?

df = pd.DataFrame([[f"{k}{str(i+1).zfill(2)}" for i in range(v)]
              for k, v in df.to_dict('records')[0].items()]).stack().reset_index(drop=True).to_frame().rename(columns = {0:'ID'})
df['count'] = 1

Another option:

import numpy as np
df = df.melt()
new_df = (pd.DataFrame(np.repeat(df.variable, df.value))
        .assign(count=1))
new_df.variable = new_df.variable + (new_df.groupby('variable').cumcount() + 1).astype(str).str.zfill(2)
user_154380330
Related