Re-arrange rows values

Question

I have 100 columns and 200 rows. Each value contains "First", "Second", "King", or "Queen" concatenate with other random string and separated by _. Note: All of these are concatenated with other values. Every row has "First" once, but others can be multiple.

Below is an example:

I want to re-arrange the values of each row as per my conditions:

"First" must always be in the first column.
"Second" Must come after "First"
"King" Must come after "Second"
"Queen" Must come after "King"

Desired output:

I tried to do a for loop to iterate each row but I don't know how to switch values or replace like my requirement.

And all rows will always have each of those items? (no duplicates, no missing values etc...?) — Jon Clements, Apr 28 '19 at 11:49
Difficult to make a general solution if these are only 4 of the 200 rows — Erfan, Apr 28 '19 at 11:50
@JonClements No, sometimes a row could have only "First", sometimes "First" and "Second", and sometimes all of them. I want the code to be flexible with such case. — M.A, Apr 28 '19 at 11:50
Okay... should probably do a few more examples of such cases then... Can there be more than one "First"? If so... then obviously First can't always go where it's supposed to etc... And if there's no First, then does that column end up missing and the rest get filled in order or... ? — Jon Clements, Apr 28 '19 at 11:52
@JonClements There is only one "First" in every row. "Second", "King" and "Queen" can be multiple. — M.A, Apr 28 '19 at 11:53
So "First" is *always* guaranteed to appear once and only once for all rows? — Jon Clements, Apr 28 '19 at 11:55
The example input doesnt have a `Queen` in the second row but the output has. How is that? — anky, Apr 28 '19 at 11:57
@JonClements Yes, but there 1 issue. I need the code to search in the value like contains. Because each of my values are like this "First83382" "Second8282', "King9292". So I want to search into the string and if found, it will re-arrange accordingly. — M.A, Apr 28 '19 at 12:01
@anky_91 Yes i did the output manually and forgot to write it :/ I edited it. Regarding your code, but 1 more modification please. Could you make it "contains" ? My values are like this "King2912", "Queen384829". I actually made them that way to label each value. I forgot to mention this in my quesiton. thank you — M.A, Apr 28 '19 at 12:03
@M.A it'd be good if you could [edit] your question so it's clear instead of in comments here and on the answer... — Jon Clements, Apr 28 '19 at 12:05
@JonClements Thank you Jon. I've edited my question accordingly. This is my first time posting here, please bear with me. Thank you. — M.A, Apr 28 '19 at 12:22
@M.A no worries... one thing I would suggest though is that posting images makes it somewhat difficult for someone to just copy/paste the info so they can work on your dataframe... It'd be good if you could copy/paste the result of `your_df.head(5).to_dict()` into a code block, then everyone can just pick it up and run with it... it's also worth looking at: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples. Thanks :) — Jon Clements, Apr 28 '19 at 12:55

score 2 · Accepted Answer · answered Apr 28 '19 at 11:53

2

Try with:

l=['First','Second','King','Queen']
d=dict(zip(l,range(len(l))))
#{'First': 0, 'Second': 1, 'King': 2, 'Queen': 3}
df=pd.DataFrame(np.sort(df.replace(d),axis=1),columns=df.columns,
                index=df.index).replace({v:k for k,v in d.items()})
print(df)

         A       B       C     ETC
1    First  Second  Second  Second
2    First  Second  Second    King
3    First  Second    King   Queen
ETC  First  Second  Second    King

answered Apr 28 '19 at 11:53

anky

74,114
11
41
70

Anky you're awesome but 1 more modification please. Could you make it "contains" ? My values are like this "King2912", "Queen384829". I actually made them that way to label each value. I forgot to mention this in my quesiton. thank you – M.A Apr 28 '19 at 11:57
I get this error "'<' not supported between instances of 'float' and 'str'" although the columns are str types. What do you think the reason of this? – M.A Apr 28 '19 at 12:44
@M.A do you have blanks (`NaN`) or any float columns in the df? if so exclude them, for `NaN` you can fill them with something and convert the df to string `df=df.astype(str)` , or drop them if you want – anky Apr 28 '19 at 12:50
@M.A Glad I could help. Happy coding. :) – anky Apr 29 '19 at 17:26

Re-arrange rows values

1 Answers1