How to convert single column data into multiple columns in python dataframe?

Question

I have a data frame (df) with column col1 which has many rows and there are rows with a common string (Collection of numbers are) and ending with different numbers(001, 002, 005). I want to extract the rows between two string (Collection of numbers are 002 to Collection of numbers are 003) and assign them to new column with same row name (Collection of numbers are 002)

    col1
0   Collection of numbers are 002
1   53
2   20
3   56
4   Collection of numbers are 003
5   236
6   325
7   Collection of numbers are 005
8   96
9   23
10  63

I want to convert the how above data frame to the following format.

0   Collection of numbers are 002   Collection of numbers are 003   Collection of numbers are 005
1   53                              236                              96
2   20                              325                              23
3   56                                                               63

Note: No repeated numbers

can you have several times the same number (like 002)? if yes you want the values to be in the same final column? — Ben.T
– Ben.T, Commented Apr 16, 2020 at 14:55

Umar.H · Accepted Answer · 2020-04-16 15:06:23Z

4

We could try ffill and some basic resphaping using str.split

df['headers'] = df['col1'].str.extract('(Collection.*)').ffill()


df1 = df[~df['col1'].str.contains('Collection')].copy()


df1.groupby('headers').agg(','.join)['col1'].str.split(',',expand=True).T.rename_axis('',axis='columns')

out:

  Collection of numbers are 002 Collection of numbers are 003  \
0                            53                           236   
1                            20                           325   
2                            56                          None   

  Collection of numbers are 005  
0                            96  
1                            23  
2                            63

answered Apr 16, 2020 at 15:06

Umar.H

23.1k7 gold badges50 silver badges94 bronze badges

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

Comments

Ben.T · Accepted Answer · 2020-04-16 15:56:11Z

1

you can use set_index and unstack. I steal the idea of @Datanovice for extracting the name of the future columns and use groupby.cumcount to get the future index numbers:

arrCollection = df['col1'].str.extract('(Collection.*)').ffill()[0].to_numpy()
df_f = df.set_index([df.groupby(arrCollection)['col1'].cumcount()-1,
                     arrCollection])['col1']\
         .unstack().iloc[1:,:]

print (df_f)
  Collection 002 Collection 003 Collection 005
0             53            236             96
1             20            325             23
2             56            NaN             63

Note: the name of the columns will be like in your example, I did not use the exact same input

answered Apr 16, 2020 at 15:56

Ben.T

29.7k6 gold badges39 silver badges57 bronze badges

3 Comments

Umar.H Over a year ago

very clever with cumcount, I wanted to use unstack but couldn't figure it out with my duplicated index (:

Ben.T Over a year ago

@Datanovice thanks, I was a bit lost when creating the name of the columns before I see your str.extract ;) and by the way, you should consider changing your pseudo, you are not really a novice anymore haha

Umar.H Over a year ago

Once I reach 10k I will change it =)

trigonom · Accepted Answer · 2020-04-16 15:26:11Z

in

                    col1
0   c of numbers are 002
1                      1
2                      2
3                      3
4   c of numbers are 003
5                     55
6                     66
7   c of numbers are 005
8                     45
9                     23
10                    12
11                   456
12                    56

for_concat = []
col = []
for i,r in df.iterrows():
    if "numbers" in str(r["col1"]):
        if col:
            for_concat.append(pd.DataFrame(col,columns=[col_name]))
            col_name = r["col1"]
            col = []
        else:
            col_name = r["col1"]
    else:
        col.append(r["col1"])
for_concat.append(pd.DataFrame(col,columns=[col_name]))
out = pd.concat(for_concat, axis =1)

out:

   c of numbers are 002  c of numbers are 003  c of numbers are 005
0                   1.0                  55.0                    45
1                   2.0                  66.0                    23
2                   3.0                   NaN                    12
3                   NaN                   NaN                   456
4                   NaN                   NaN                    56

sempersmile · Accepted Answer · 2020-04-16 15:44:54Z

The answer provided by Datanovic seems good. An alternative solution would be following function:

def extract_columns(df, column, common_string):
    df_list = df[column].tolist()
    df_new = pd.DataFrame()
    row_indices = []
    cols = []
    for ind, elem in enumerate(df_list):
        if common_string in str(elem):
            row_indices.append(ind)
            cols.append(elem)

    row_indices.append(len(df_list))

    for ind, col in enumerate(cols): 
        df_new[col] = pd.Series(df_list[row_indices[ind]+1:row_indices[ind+1]])

    return df_new

So with your example dataframe you will get following result when calling the function extract_columns(df, 'col1', 'Collection of numbers are')

   Collection of numbers are 002  Collection of numbers are 003  Collection of numbers are 005
0                             53                          236.0                             96
1                             20                          325.0                             23
2                             56                            NaN                             63

Collectives™ on Stack Overflow

How to convert single column data into multiple columns in python dataframe?

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related