Pandas split row in multiple row

user_13595465 0
Viewed 82

Consider below a DataFrame with header and one row as input.. The input row needs to divided into two separate rows based on space between values or second value needs to go to second row.. So in the output we need two rows..

Input1:

 Age  Gender Coverage-Status Total-Paid Benefit-Date Outstanding-Reserve Waiver-Reserve Coverage-Code
 31 26  M F    AC CC           10,000     2/15/2011      NaN                4,743         081 010

Output Required:

Age Gender Coverage-Status Total-Paid Benefit-Date Outstanding-Reserve Waiver-Reserve Coverage Code
31   M     AC              10,000     2/15/2011      NaN                4,743         081 
26   F     CC                                                                         010

i'm stuck at this right now,Is this possible?

i'm trying something like this :

ad['Age'] = ad.Age.str.split(expand = True).stack()

but this one doesn't seem to work...

pandas numpy python
2 Answers

Sorry, not enough points to comment. You could first split the dataframe column-wise, then split the row where the columns have duplicates, then re-join to the saved dataframe i.e.

input1_a = input1[['Total-Paid', 'Benefit-Date', 'Outstanding-Reserve', 'Waiver-Reserve']].copy()
input1_b = input1[['Age', 'Gender' ,'Coverage-Status','Coverage-Code']].copy()

Yatu's dropped the previous answer, but there is a function here which splits multiple columns. zouweilin commented on Sep 6, 2018 . Not as elegant as Yatu's solution but...

https://gist.github.com/jlln/338b4b0b55bd6984f883

Then

input1_new = pd.concat([input1_a,input1_b], axis = 1)

Then re-order columns

input1_new = input1_new[['Age', 'Gender' ,'Coverage-Status','Total-Paid', 'Benefit-Date', 'Outstanding-Reserve', 'Waiver-Reserve', 'Coverage-Code']]
user_142354860

Try:

# iterate ONLY over columns subjected to split - I assumed it's all columns
for col in df.columns:
    df[col]=df[col].str.split(" ")
res = df.stack().explode().reset_index(level=0, drop=True).to_frame()

res["id"] = res.groupby(level=0).cumcount()
res = res.set_index("id", append=True).unstack(level=0)
res.columns = res.columns.droplevel()

Outputs:

   Age Benefit-Date Coverage-Code Coverage-Status Gender Outstanding-Reserve  \
id                                                          
0   31  2/15/2011    081           AC              M      NaN
1   26  NaN          010           CC              F      NaN

   Total-Paid Waiver-Reserve
id
0   10,000     4,743
1   NaN        NaN
user_116101860
Related