Pandas: Swapping cells in excel-CodePudding

I have data in excel (3 columns)

A	B	C
KLM: 123	QRS: 345	NOP: 356
NOP: 454	KLM: 123	QRS: 564
NOP: 65	KLM: 423	QRS: 642
QRS: 54	KLM: 523	NOP: 325
QRS: 234	KLM: 123	NOP: 56
KLM: 234	NOP: 5425	QRS: 3425

I am trying to swap the value and get the results shown below. i.e. values starting with "KLM" in one column, "QRS" in second column, followed by "NOP" in third column.

A	B	C
KLM: 123	QRS: 345	NOP: 356
KLM: 123	QRS: 564	NOP: 454
KLM: 423	QRS: 642	NOP: 65
KLM: 523	QRS: 54	NOP: 325
KLM: 123	QRS: 234	NOP: 56
KLM: 234	QRS: 3425	NOP: 5425

any help using pandas or excel would be appreciable. Thanks in advance

CodePudding user response：

Do with np.sort then pass to dataframe

import numpy as np 
out = pd.DataFrame(np.sort(df.values)).iloc[:,[0,2,1]]
Out[346]: 
          0          2          1
0  KLM: 123   QRS: 345   NOP: 356
1  KLM: 123   QRS: 564   NOP: 454
2  KLM: 423   QRS: 642    NOP: 65
3  KLM: 523    QRS: 54   NOP: 325
4  KLM: 123   QRS: 234    NOP: 56
5  KLM: 234  QRS: 3425  NOP: 5425

CodePudding user response：

You can try sorting row wise

Creating data

df = pd.DataFrame({'A': ['KLM: 123', 'NOP: 454', 'NOP: 65', 'QRS: 54', 'QRS: 234', 'KLM: 234'],
                   'B': ['QRS: 345', 'KLM: 123', 'KLM: 423', 'KLM: 523', 'KLM: 123', 'NOP: 5425'],
                   'C': ['NOP: 356', 'QRS: 564', 'QRS: 642', 'NOP: 325', 'NOP: 56', 'QRS: 3425']},
                  )

Sorting

df.values.sort()

Output:

This gives us the expected results :

df
          A          B          C
0  KLM: 123   NOP: 356   QRS: 345
1  KLM: 123   NOP: 454   QRS: 564
2  KLM: 423   NOP: 65    QRS: 642
3  KLM: 523   NOP: 325   QRS: 54
4  KLM: 123   NOP: 56    QRS: 234
5  KLM: 234   NOP: 5425  QRS: 3425