Expand, transpose and de-aggregate a dataset using python

user_5942100 0
Viewed 172

I have a dataset, df, where there are several categories with number counts for each category. I would like to transform this set and show a distinct count for each category. I would also like to add a new 'count' column. The newly generated counts go off of the date column. Example how many distinct alphas are there in q1 for ID A: 2 and so on.

Data

ID  date    alpha   beta    gamma   
A   q1      2       2       1   
B   q1      2       2       3   
C   q1              
D   q2      2

Desired

ID  type    date    count           
A   alpha   q1      alpha1          
A   alpha   q1      alpha2          
A   beta    q1      beta1           
A   beta    q1      beta2           
A   gamma   q1      gamma1          
B   alpha   q1      alpha1          
B   alpha   q1      alpha2          
B   beta    q1      beta1           
B   beta    q1      beta2           
B   gamma   q1      gamma1          
B   gamma   q1      gamma2          
B   gamma   q1      gamma3          
C
D   alpha   q2      alpha1
D   alpha   q2      alpha2

Doing

I believe I may have to use the code below, in addition to the melt function.

df.melt(id_vars= ['ID', 'date'],
        var_name = 'type',
        value_name = 'count'

However, I am not able to expand the values into distinct counts

Any suggestions will be helpful. I am still researching this.

pandas numpy python
3 Answers

Melt the dataframe

df=pd.melt(df.replace("",0), id_vars=['ID','date'], value_vars=['alpha', 'beta', 'gamma']).sort_values(by='ID')

Repeat rows by values of the column value

newdf = (pd.DataFrame(np.repeat(df.values,df.value.astype(int),axis=0))).rename(columns={0:'ID',1:'date',2:'type',3:'count'})

Update Count to have a concat of type and count of type in each ID

 newdf=newdf.assign(count=newdf['type']+(newdf.groupby(['ID','type'])['type'].cumcount()+1).astype(str))
    

    ID date   type   count
0   A   q1  alpha  alpha1
1   A   q1  alpha  alpha2
2   A   q1   beta   beta1
3   A   q1   beta   beta2
4   A   q1  gamma  gamma1
5   B   q1  alpha  alpha1
6   B   q1  alpha  alpha2
7   B   q1   beta   beta1
8   B   q1   beta   beta2
9   B   q1  gamma  gamma1
10  B   q1  gamma  gamma2
11  B   q1  gamma  gamma3
12  D   q2  alpha  alpha1
13  D   q2  alpha  alpha2
user_89869750

Uses pandas version 1.2.4 ...

First we melt, then get rid of nulls, since they are not needed in the final output.

Next move the dataframe into a series by setting as index and squeezing.

This allows us to repeat the series with the count.

After that we then get the count per group, before merging them into one column

temp = (df.melt(['ID', 'date'],  
                ignore_index = False, 
                value_name = 'count')
          .dropna()
          .sort_index()
          .set_index(['ID', 'date', 'variable'], append = True)
          .squeeze())

(temp.repeat(temp)
     .reset_index(['ID', 'date', 'variable'])
     .assign(count = lambda df: df.variable + 
                                df.groupby(['ID', 'variable'])
                                  .variable.cumcount()
                                  .add(1)
                                  .astype(str))
     .reset_index(drop = True))


   ID date variable   count
0   A   q1    alpha  alpha1
1   A   q1    alpha  alpha2
2   A   q1     beta   beta1
3   A   q1     beta   beta2
4   A   q1    gamma  gamma1
5   B   q1    alpha  alpha1
6   B   q1    alpha  alpha2
7   B   q1     beta   beta1
8   B   q1     beta   beta2
9   B   q1    gamma  gamma1
10  B   q1    gamma  gamma2
11  B   q1    gamma  gamma3
12  D   q2    alpha  alpha1
13  D   q2    alpha  alpha2
user_71757130

Another way:

df = df.set_index(['ID', 'date']).stack().astype(int).apply(range).explode().reset_index(
    name='count').assign(count=lambda x: x.level_2 + (x['count'] + 1).astype(str))
user_154380330
Related