Split one dataframe into multiple dataframes with same column header based on values

Question

I have a dataframe that looks like below

+------+------+---+---+---+
| S.No | A    | B | C | D |
+------+------+---+---+---+
| 1    | 0.25 | 2 | 1 | 5 |
+------+------+---+---+---+
| 2    | 1.1  | 4 | 2 | 5 |
+------+------+---+---+---+
| 3    | 1.5  | 6 | 3 | 5 |
+------+------+---+---+---+
| 4    | 0.32 | 3 | 4 | 5 |
+------+------+---+---+---+
| 5    | 1.45 | 5 | 5 | 5 |
+------+------+---+---+---+
| 6    | 1.9  | 7 | 6 | 5 |
+------+------+---+---+---+
| 7    | 0.5  | 3 | 4 | 5 |
+------+------+---+---+---+
| 8    | 1.49 | 5 | 5 | 5 |
+------+------+---+---+---+

I want to split them into 3 dataframes with same column header value name, the split is based on Column A value i.e 1st dataframe should start from 0.25 and end in 1.5, the second dataframe should start from 0.32 and end in 1.9 and 3rd dataframe should start from 0.5 and end in 1.49. i.e when the value in column A is between 0-1, the split should start, They all should retain the same column header value. Expected Output is as follows, Since i am new to this, i dont know how to get this done properly, any help in this would be appreciated.

Dataframe 1:

+------+------+---+---+---+
| S.No | A    | B | C | D |
+------+------+---+---+---+
| 1    | 0.25 | 2 | 1 | 5 |
+------+------+---+---+---+
| 2    | 1.1  | 4 | 2 | 5 |
+------+------+---+---+---+
| 3    | 1.5  | 6 | 3 | 5 |
+------+------+---+---+---+

Dataframe 2:

+------+------+---+---+---+
| S.No | A    | B | C | D |
+------+------+---+---+---+
| 4    | 0.32 | 3 | 4 | 5 |
+------+------+---+---+---+
| 5    | 1.45 | 5 | 5 | 5 |
+------+------+---+---+---+
| 6    | 1.9  | 7 | 6 | 5 |
+------+------+---+---+---+

Dataframe 3:

+------+------+---+---+---+
| S.No | A    | B | C | D |
+------+------+---+---+---+
| 7    | 0.5  | 3 | 4 | 5 |
+------+------+---+---+---+
| 8    | 1.49 | 5 | 5 | 5 |
+------+------+---+---+---+

Are you splitting based on the conditional value of A, or just simply by a choice of row index? — sjc
– sjc, Commented Jan 2, 2020 at 17:42

BENY · Accepted Answer · 2020-01-02 17:43:24Z

4

Let us do cumsum

d={x: y for x , y in df.groupby(df.A.between(0,1).cumsum())}

answered Jan 2, 2020 at 17:43

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roshan Santhosh · Accepted Answer · 2020-01-02 17:45:17Z

1

You start off by identifying the indices where the values are between 0 and 1. This is done with a combination of between and index. Once you have the indices, you can start splitting the dataframe using the iloc method

#Identifies indices based on variable A
splitIndices = df.index[df.A.between(0,1)].tolist()


dfList = []

for i in range(len(splitIndices)-1):
    startIndex = splitIndices[i]
    endIndex = splitIndices[i+1]

    tempDf = df.iloc[startIndex : endIndex]

    #Appends the dataframe subset to the output list
    dfList.append(tempDf.copy())

answered Jan 2, 2020 at 17:45

Roshan Santhosh

6874 silver badges10 bronze badges

1 Comment

windowws Over a year ago

Excellent, does what i expected. Thanks @Roshan Santhosh

anky · Accepted Answer · 2020-01-02 17:54:08Z

According to the explanation you have provided, you include a between condition, eg:

1st dataframe should start from 0.25 and end in 1.5

this means values like 0.32 should be included in the dataframe

With that logic you can do the below:

l=[.25,1.5,.32,1.9,.5,1.49]
r=[(a,b) for a,b in zip(l[::2],l[1::2])]
for i in r:
    r i in r:
    print(df[df['A'].between(*i,inclusive=True)].sort_values('A'))
    print("----------------------------------")

   S.No     A    B    C    D
0   1.0  0.25  2.0  1.0  5.0
3   4.0  0.32  3.0  4.0  5.0
6   7.0  0.50  3.0  4.0  5.0
1   2.0  1.10  4.0  2.0  5.0
4   5.0  1.45  5.0  5.0  5.0
7   8.0  1.49  5.0  5.0  5.0
2   3.0  1.50  6.0  3.0  5.0
----------------------------------
   S.No     A    B    C    D
3   4.0  0.32  3.0  4.0  5.0
6   7.0  0.50  3.0  4.0  5.0
1   2.0  1.10  4.0  2.0  5.0
4   5.0  1.45  5.0  5.0  5.0
7   8.0  1.49  5.0  5.0  5.0
2   3.0  1.50  6.0  3.0  5.0
5   6.0  1.90  7.0  6.0  5.0
----------------------------------
   S.No     A    B    C    D
6   7.0  0.50  3.0  4.0  5.0
1   2.0  1.10  4.0  2.0  5.0
4   5.0  1.45  5.0  5.0  5.0
7   8.0  1.49  5.0  5.0  5.0

Collectives™ on Stack Overflow

Split one dataframe into multiple dataframes with same column header based on values

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related