I need to make a wide-to-long transformation using
I'm looking for a way to have this kind of output :
I tried to make a list (from 1 to the value of the second column) but I got the error below :
df['pos'] = df.apply(list(range(1, df['Value'])))
df.explode('pos')
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 ?
CodePudding user response:
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
CodePudding user response:
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