CODEWITHSUNDEEP
pythonpython-3.xpandasnumpydataframe
Subhojyoti Lahiri
Subhojyoti Lahiri

Reputation: 111

pandas convert some column values to rows

I have a Pandas DataFrame df that looks like this:

enter image description here

I want them to appear in this format:

enter image description here

Losing the SignalName column. I need it in this format because I have to plot time vs SDLxvalues. I think it requires some kind of iterator, I have tried melt but didn't work.

Upvotes: 1

Views: 54

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Try this:

df.set_index(['time','SignalName'])['value'].unstack()

Output:

SignalName  APID1  SLID1  SPID1
time                           
t_1             1      0      2
t_2            11      0     22
t_3             1      0     20

Another option:

df_out = df.set_index(['time','SignalName']).unstack()
df_out.columns = [f'{j}{i}' for i, j in df_out.columns]
df_out.reset_index()

Output:

  time  APID1value  SLID1value  SPID1value
0  t_1           1           0           2
1  t_2          11           0          22
2  t_3           1           0          20

Duplicate values, you need to aggregate time some how, here I am choosing the first value, you can do sum, mean, max, min or any aggregation of your choice.

df.groupby(['time', 'SignalName'])['value'].agg('first').unstack()

Output:

SignalName  APID1  SLID1  SPID1
time                           
t_1             1      0      2
t_2            11      0     22
t_3             1      0     20

Upvotes: 3

Related Questions