Pandas Dataframe - (Column re structure)

Question

I have a dataframe that has n number of columns. These contain letters, the amount of letters a column contains varies and a letter can appear in various amounts of columns. I need the code for a pandas dataframe to convert the sheet to columns starting with the letters, the rows should contain the numbers of the columns that that letter was in.

Link to example problem

```
  ABCDEF
```

ABDE. 11 1 BBCC -> 2 2 EFB. 3 3 4 4

The image describes my problem better. Thank you in advance for any help.

Can you convert that image to actual text data and share please? — Joe Ferndz, Feb 21 '21 at 17:50
I attempted to convert it to text data. I can do the question in a text file if that would help also? — Dammon, Feb 21 '21 at 21:04

jezrael · Accepted Answer · 2021-02-21T18:29:03.923

Use DataFrame.stack with DataFrame.reset_index for reshape, then DataFrame.sort_values and aggregate lists, last create DataFrame by constructor with transpose:

s=df.stack().reset_index(name='a').sort_values('level_1').groupby('a')['level_1'].agg(list)

df1 = pd.DataFrame(s.tolist(), index=s.index).T
print (df1)
a     a  b     c     d     e     f
0     1  1     1     1     3     2
1     3  3     2     4     4  None
2  None  4  None  None  None  None

Or use GroupBy.cumcount for counter and reshape by DataFrame.pivot:

df2 = df.stack().reset_index(name='a').sort_values('level_1')

df2['g'] = df2.groupby('a').cumcount()

df2 = df2.pivot('g','a','level_1')
print (df2)
a    a  b    c    d    e    f
g                            
0    1  1    1    1    3    2
1    3  3    2    4    4  NaN
2  NaN  4  NaN  NaN  NaN  NaN

Last if necessary remove index and columns names:

df1 = df1.rename_axis(index=None)
df2 = df2.rename_axis(index=None, columns=None)

Pandas Dataframe - (Column re structure)

1 Answers1