Making wide-to-long transformation based on the values of a column

user_19603419 0
Viewed 35

I need to make a wide-to-long transformation using Pandas.

enter image description here

I tried to make a list (from 1 to the value of the second column) but I got the error below :

Code :

import pandas as pd
df = pd.DataFrame({'Id': ['AA', 'BB', 'CC'], 'Value': [4, 2, 3]})
df['Value'] = df.apply(list(range(1, df['Value'])))
df.explode('Value')

Error :

TypeError: 'Series' object cannot be interpreted as an integer

Do you know you have to fix that or any other suggestions to make this transformation work ?

pandas dataframe python
2 Answers

You can turn Value column into list then explode

out = (df.assign(Value=df['Value'].apply(lambda x: range(1, x+1)))
       .explode('Value', ignore_index=True))

print(out)

   Id Value
0  AA     1
1  AA     2
2  AA     3
3  AA     4
4  BB     1
5  BB     2
6  CC     1
7  CC     2
8  CC     3
user_103151630

There may be a quicker way to do this, but one solution could be as follows.

import pandas as pd
df = pd.DataFrame({'Id': ['AA', 'BB', 'CC'], 'Value': [4, 2, 3]})

output_df = df.reindex(df.index.repeat(df['Value'])).reset_index(drop=True)
output_df['Value'] = output_df.groupby('Id')['Value'].cumcount().add(1)

print(output_df)

   Id  Value
0  AA      1
1  AA      2
2  AA      3
3  AA      4
4  BB      1
5  BB      2
6  CC      1
7  CC      2
8  CC      3
user_184706920
Related