pandas converting single row into column

Question

I am trying to convert a single column into row and get get corresponding values of that row into its columns.

I tried using pivot table but it gives me the nulls for all other columns where there is no specific corresponding values, but it don't want NaN in the data frame.

I have below data frame df:

week          value            
1       1234
1       5678
1       9856
1       5486
1       4569
2       1234
2       5869
2       4865
2       5589

I tried

df1 = df.pivot(columns='week',values='value')

This code returns the data frame with NaN, I want to eliminate all the NaN and get values of each week.the result is shown below. I have added both expected output and the actual output.

The expected result is:

1       2
1234   1234   
5678   5869
9856   4865
5486   5589
4569   NaN

The actual result is

1       2
1234   NaN
5678   NaN
9856   NaN
5486   NaN
4569   1234   
NaN     5869
NaN     4865
NaN     5589

I want to remove NaN and align values which belongs to that particular week.

Quang Hoang · Accepted Answer · 2019-04-11 16:19:24Z

1

Not the cleanest way, but this get the work done:

df = pd.DataFrame({'week':[1]*4 + [2]*5 + [3]*7, 'value':np.arange(16)})

df.groupby('week').value.apply(list).apply(pd.Series).T

output:

+------+-----+-----+------+
| week |  1  |  2  |  3   |
+------+-----+-----+------+
|    0 | 0.0 | 4.0 | 9.0  |
|    1 | 1.0 | 5.0 | 10.0 |
|    2 | 2.0 | 6.0 | 11.0 |
|    3 | 3.0 | 7.0 | 12.0 |
|    4 | NaN | 8.0 | 13.0 |
|    5 | NaN | NaN | 14.0 |
|    6 | NaN | NaN | 15.0 |
+------+-----+-----+------+

edited Apr 11, 2019 at 16:19

answered Apr 11, 2019 at 15:14

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alok Over a year ago

Thanks. Weeks are not just 1 and 2 they are multiple. Also the values x1, x2 are not just 4 they might differ from week to week. week 1 might have 1000 values and week 2 might have 500 values and so on

Quang Hoang Over a year ago

It still works, will fill the missing 500 values in week 2 with NaN. See edit for details:

Alok Over a year ago

Thanks. What i was to referring to was the values are not sequence of numbers they are random. I have edited the data frame accordingly. Thanks

Quang Hoang Over a year ago

Still weeks 1 and 2 both have 4, what do you expect if week 1 has 1000 and week 2 has 500 like you said above?

Alok Over a year ago

That was just a reference, we don't know the numbers of values that we have corresponding to the weeks. If you see the main dataframe week1 has four values, but in actual dataframe it has almost 1000 values and we dont know that number. We are just making that week as a column and list all the values under that week1 and the same for week2 and so on. Thanks.

|

Collectives™ on Stack Overflow

pandas converting single row into column

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related