Combine Columns in Pandas-CodePudding

Let's say I have the following Pandas dataframe. It is what it is and the input can't be changed.

df1 = pd.DataFrame(np.array([['a', 1,'e', 5],
                             ['b', 2, 'f', 6],
                             ['c', 3, 'g', 7],
                             ['d', 4, 'h', 8]]))
df1.columns = [1,1,2,2]

See how the columns have the same name? The output I want is to have columns with the same name combined (not summed or concatenated), meaning the second column 1 is added to the end of the first column 1, like so:

df2 = pd.DataFrame(np.array([['a', 'e'], 
                             ['b','f'], 
                             ['c', 'g'], 
                             ['d', 'h'], 
                             [1,5],
                             [2,6],
                             [3,7],
                             [4,8]]))
df2.columns = [1,2]

How do I do this? I can do it manually, except I actually have like 10 column titles, about 100 iterations of each title, and several thousand rows, so it takes forever and I have to redo it with each new dataset.

CodePudding user response：

You can use a dictionary whose default value is list and loop through the dataframe columns. Use the column name as dictionary key and append the column value to the dictionary value.

from collections import defaultdict
d = defaultdict(list)

for i, col in enumerate(df1.columns):
    d[col].extend(df1.iloc[:, i].values.tolist())

df = pd.DataFrame.from_dict(d, orient='index').T

print(df)

   1  2
0  a  e
1  b  f
2  c  g
3  d  h
4  1  5
5  2  6
6  3  7
7  4  8

CodePudding user response：

Try with groupby and explode:

output = df1.groupby(level=0, axis=1).agg(lambda x: x.values.tolist()).explode(df1.columns.unique().tolist())

>>> output
   1  2
0  a  e
0  1  5
1  b  f
1  2  6
2  c  g
2  3  7
3  d  h
3  4  8

Edit:

To reorder the rows, you can do:

output = output.assign(order=output.groupby(level=0).cumcount()).sort_values("order",ignore_index=True).drop("order",axis=1)

>>> output
   1  2
0  a  e
1  b  f
2  c  g
3  d  h
4  1  5
5  2  6
6  3  7
7  4  8