0

I have comma separated datasets with more than 50k rows like this:

items
a,e
b,b,d
c
d,e,a,c,b
c,a,c,e
b,d,a,c,e
a,d
d,d

I want to add them to different columns using pandas:

item1 item2 item3   item4   item5
a      e    NaN      NaN    NaN
b      b    d        NaN    NaN
c      NaN  NaN      NaN    NaN
d      e    a          c    b
c      a    c          e    NaN
b      d    a          c    e
a      d    NaN      NaN    NaN
d      d    NaN      NaN    NaN
Shaido
  • 27,497
  • 23
  • 70
  • 73
DrakkarLeon
  • 13
  • 4
  • 1
    Warm welcome to SO. Please use correct upper case letters, e.g. in the beginning of your title and sentences. This would be gentle to your readers. Please read https://stackoverflow.com/help/how-to-ask and https://stackoverflow.com/help/minimal-reproducible-example. Then update your question with code to show us what you have tried so far. – buhtz Aug 02 '22 at 06:55
  • Could this maybe answer your question? https://stackoverflow.com/a/55189021/10557420 – Sabsa Aug 02 '22 at 06:58

2 Answers2

1

A simple solution is to split the column, convert it to a list and then create a new dataframe. Then rename the columns from the default values:

df = pd.DataFrame(df['items'].str.split(',').values.tolist())
df.columns += 1
df = df.add_prefix('item_')

Result:

  item_0 item_1 item_2 item_3 item_4
0      a      e   None   None   None
1      b      b      d   None   None
2      c   None   None   None   None
3      d      e      a      c      b
4      c      a      c      e   None
5      b      d      a      c      e
6      a      d   None   None   None
7      d      d   None   None   None
Shaido
  • 27,497
  • 23
  • 70
  • 73
  • 1
    `str.split` supports expansion. That first line could just be `df = df['items'].str.split(',', expand=True)`. Also `rename` with a renaming function could make it a one-liner `df = df['items'].str.split(',', expand=True).rename(columns=lambda i: f'item{i + 1}')` – Henry Ecker Aug 04 '22 at 01:48
  • Henry's recommendation worked for me. – DrakkarLeon Aug 04 '22 at 12:07
1

You could split your file by \n first like this

data = '''a,e
b,b,d
c
d,e,a,c,b
c,a,c,e
b,d,a,c,e
a,d
d,d'''
lines=data.split("\n")

then create a empty Dataframe

df = pd.DataFrame(columns=['item1','item2','item3','item4','item5'])

and add data line by line

for line in lines:
    line_data = line.split(",")
    line_data += (5-len(line_data))*[None]
    df = pd.concat( [pd.DataFrame([line_data], columns=df.columns), df] , ignore_index=True)

output:

      item1 item2 item3 item4 item5
0      d     d  None  None  None
1      a     d  None  None  None
2      b     d     a     c     e
3      c     a     c     e  None
4      d     e     a     c     b
5      c  None  None  None  None
6      b     b     d  None  None
7      a     e  None  None  None
8      d     d  None  None  None
9      a     d  None  None  None
10     b     d     a     c     e
11     c     a     c     e  None
12     d     e     a     c     b
13     c  None  None  None  None
14     b     b     d  None  None
15     a     e  None  None  None
16     d     d  None  None  None
17     a     d  None  None  None
18     b     d     a     c     e
19     c     a     c     e  None
20     d     e     a     c     b
21     c  None  None  None  None
22     b     b     d  None  None
23     a     e  None  None  None
Hossein Dahaei
  • 11
  • 2