Convert a python dataframe with multiple rows into one row using python pandas?

Question

Having the following dataframe,

df = pd.DataFrame({'device_id' : ['0','0','1','1','2','2'],
               'p_food'    : [0.2,0.1,0.3,0.5,0.1,0.7],
               'p_phone'   : [0.8,0.9,0.7,0.5,0.9,0.3]
              })
print(df)

output:

  device_id  p_food  p_phone
0         0     0.2      0.8
1         0     0.1      0.9
2         1     0.3      0.7
3         1     0.5      0.5
4         2     0.1      0.9
5         2     0.7      0.3

How to achieve this transformation?

df2 = pd.DataFrame({'device_id' : ['0','1','2'],
                   'p_food_1'    : [0.2,0.3,0.1],
                   'p_food_2'    : [0.1,0.5,0.7],
                   'p_phone_1'   : [0.8,0.7,0.9],                    
                   'p_phone_2'   : [0.9,0.5,0.3]
                  })
print(df2)

Output:

  device_id  p_food_1  p_food_2  p_phone_1  p_phone_2
0         0       0.2       0.1        0.8        0.9
1         1       0.3       0.5        0.7        0.5
2         2       0.1       0.7        0.9        0.3

I try to achieve it use groupby,apply,agg...
But I still can't achieve this transformation.

Update
My final Code:

df.drop_duplicates('device_id', keep='first').merge(df.drop_duplicates('device_id', keep='last'),on='device_id')

I appreciated su79eu7k's and A-Za-z's time and effort.
Words are not enough to express my gratitude.

Possible duplicate of Long to wide data. Pandas

Arya McCarthy
– Arya McCarthy

2017-05-02 02:56:26 +00:00
Commented May 2, 2017 at 2:56 — Arya McCarthy
– Arya McCarthy, Commented May 2, 2017 at 2:56
Thank you provide another answer for me.

Dondon Jie
– Dondon Jie

2017-05-02 03:23:05 +00:00
Commented May 2, 2017 at 3:23 — Dondon Jie
– Dondon Jie, Commented May 2, 2017 at 3:23

Vaishali · Accepted Answer · 2017-05-02 03:34:36Z

6

If you are still looking for an answer using groupby

df = df.groupby('device_id')['p_food', 'p_phone'].apply(lambda x: pd.DataFrame(x.values)).unstack().reset_index()
df.columns = df.columns.droplevel()
df.columns = ['device_id','p_food_1', 'p_food_2', 'p_phone_1','p_phone_2']

You get

    device_id   p_food_1    p_food_2    p_phone_1   p_phone_2
0   0           0.2         0.1         0.8         0.9
1   1           0.3         0.5         0.7         0.5
2   2           0.1         0.7         0.9         0.3

answered May 2, 2017 at 3:34

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

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

1 Comment

Dondon Jie Over a year ago

Good job! Thank you for your help!

su79eu7k · Accepted Answer · 2017-05-02 03:13:49Z

2

df_m = df.drop_duplicates('device_id', keep='first')\
         .merge(df, on='device_id')\
         .drop_duplicates('device_id', keep='last')\
         [['device_id', 'p_food_x', 'p_food_y', 'p_phone_x', 'p_phone_y']]\
         .reset_index(drop=True)

print(df_m)

  device_id  p_food_x  p_food_y  p_phone_x  p_phone_y
0         0       0.2       0.1        0.8        0.9
1         1       0.3       0.5        0.7        0.5
2         2       0.1       0.7        0.9        0.3

edited May 2, 2017 at 3:13

answered May 2, 2017 at 3:01

su79eu7k

7,3664 gold badges36 silver badges42 bronze badges

Collectives™ on Stack Overflow

Convert a python dataframe with multiple rows into one row using python pandas?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related