How to reorder strings inside Pandas data frame and insert new ones

user_12228905 0
Viewed 173

I have a data frame that looks like below. Data type of Output is string.

ID  Output
1   ab 1, bc 2, ac 5, at 0, abc 0 
2   ab 0, ac 5, at 0
3   ac 5, bc 0, atn 0

As you can see, in row2, bc is skipped while the overall order stays the same. However, in row3, the order differs. How do I first insert the missing categories and then reorder the strings in the data frame? In other words, how may I get an intermediate data frame that looks like this:

ID  Output
1   ab 1, bc 2, ac 5,  at 0,  abc 0, atn  
2   ab 0, bc,   ac 5,  at 0, abc,   atn
3   ab,   bc 0, ac 5,  at,   abc,   atn 0

So eventually I can perform the below operation:

x = df['Output'].str.split(",",expand=True,)
x.columns = x.iloc[0, :].str.extract(r"^(.*)\s+")[0]
x = x.apply(lambda x: x.str.replace(r"^(.*\s+)", ""))
df=pd.concat([df, x], axis=1)

To reach this ideal data frame:

ID  ab    bc   ac   at    abc    atn
1   1     2    5    0     0      None
2   0     None 5    0     None   None
3   None  0    5    None  None    0
pandas dataframe python
2 Answers

We can skip the part where the intermediate dataframe is created and directly approach to create the resulting dataframe. Here, we can use str.findall to find the token pairs e.g. (ab, 1), (bc, 2) etc. which can be used to create records corresponding to each row, where each record contains the column names as keys and corresponding values, then we can easily create the resulting dataframe from these records.

pd.DataFrame([*df['Output'].str.findall(r'(\S+)\s(\d+)').map(dict)], dtype=float)

    ab   bc   ac   at  abc  atn
0  1.0  2.0  5.0  0.0  0.0  NaN
1  0.0  NaN  5.0  0.0  NaN  NaN
2  NaN  0.0  5.0  NaN  NaN  0.0
user_128331660

Another option:

  • Split column Output by comma into list;
  • explode Output column;
  • Split Output column by whitespace;
  • pivot the table;

Code:

df = df.assign(Output = df.Output.str.split(', ')).explode('Output')
pd.concat([df.ID, df.Output.str.split(' ', expand=True)], axis=1) \
  .pivot('ID', 0, 1)

#0    ab  abc ac   at  atn   bc
#ID                            
#1     1    0  5    0  NaN    2
#2     0  NaN  5    0  NaN  NaN
#3   NaN  NaN  5  NaN    0    0
user_49834500
Related