Combining rows in pandas DataFrame by iterating

Question

How can I achieve the expected result from the following DataFrame

 df
            col_1             col_2    col_3
     0  Non-Saved    www.google.com   20,567
     1             www.facebook.com      
     2             www.linkedin.com      
     3      Saved     www.Quora.com    6,337
     4                www.gmail.com

Expected result:

            col_1              col_2    col_3
     0  Non-Saved     www.google.com   20,567
                    www.facebook.com
                    www.linkedin.com
     1  Saved          www.Quora.com    6,337
                       www.gmail.com

From 5 rows to 2 rows by merging the empty strings in col_1 and col_3. Also, concatenating values in col_2 into one cell. Can anyone help me with an user-defined function to do this?

score 2 · Accepted Answer · answered Oct 11 '17 at 18:25

2

Let's try:

df = df.apply(lambda x: x.str.strip()).replace('',np.nan)

df.groupby(df.col_1.ffill())\
  .agg({'col_2': lambda x: ' '.join(x) ,'col_3':'first'})\
  .reset_index()

Output:

       col_1                                             col_2   col_3
0  Non-Saved  www.google.com www.facebook.com www.linkedin.com  20,567
1      Saved                       www.Quora.com www.gmail.com   6,337

answered Oct 11 '17 at 18:25

Scott Boston

147,308
15
139
187

df = df.apply(lambda x: x.str.strip()).replace('',np.nan) Gives an error: `AttributeError: ('Can only use .str accessor with string values, which use np.object_ dtype in pandas', u'occurred at index NumberOfRequests')` – Preetesh Gaitonde Oct 11 '17 at 18:27
Yes, but see this [SO Post](https://stackoverflow.com/a/34324332/6361531) – Scott Boston Oct 11 '17 at 18:29
You can try '\n'.join(x) – Scott Boston Oct 11 '17 at 18:30
`col_1 object` `col_2 object` `col_3 int64` `dtype: object` – Preetesh Gaitonde Oct 11 '17 at 18:32
I have a slight tricky condition here -> [Combine_rows...](https://stackoverflow.com/questions/46698514/combining-rows-in-pandas-dataframe-by-comparing-multiple-columns). Would you mind taking a look once. Meanwhile I am trying as well. – Preetesh Gaitonde Oct 11 '17 at 22:48

Combining rows in pandas DataFrame by iterating

1 Answers1

Linked