You could use list comprehension with a little modications your list, l, first.
print(df)
a b c
0 1 1 1
1 2 2 2
2 3 3 3
3 4 4 4
4 5 5 5
5 6 6 6
6 7 7 7
7 8 8 8
l = [2,5,7]
l_mod = [0] + l + [max(l)+1]
list_of_dfs = [df.iloc[l_mod[n]:l_mod[n+1]] for n in range(len(l_mod)-1)]
Output:
list_of_dfs[0]
a b c
0 1 1 1
1 2 2 2
list_of_dfs[1]
a b c
2 3 3 3
3 4 4 4
4 5 5 5
list_of_dfs[2]
a b c
5 6 6 6
6 7 7 7
list_of_dfs[3]
a b c
7 8 8 8
Answer from Scott Boston on Stack OverflowYou could use list comprehension with a little modications your list, l, first.
print(df)
a b c
0 1 1 1
1 2 2 2
2 3 3 3
3 4 4 4
4 5 5 5
5 6 6 6
6 7 7 7
7 8 8 8
l = [2,5,7]
l_mod = [0] + l + [max(l)+1]
list_of_dfs = [df.iloc[l_mod[n]:l_mod[n+1]] for n in range(len(l_mod)-1)]
Output:
list_of_dfs[0]
a b c
0 1 1 1
1 2 2 2
list_of_dfs[1]
a b c
2 3 3 3
3 4 4 4
4 5 5 5
list_of_dfs[2]
a b c
5 6 6 6
6 7 7 7
list_of_dfs[3]
a b c
7 8 8 8
I think this is what you need:
df = pd.DataFrame({'a': np.arange(1, 8),
'b': np.arange(1, 8),
'c': np.arange(1, 8)})
df.head()
a b c
0 1 1 1
1 2 2 2
2 3 3 3
3 4 4 4
4 5 5 5
5 6 6 6
6 7 7 7
last_check = 0
dfs = []
for ind in [2, 5, 7]:
dfs.append(df.loc[last_check:ind-1])
last_check = ind
Although list comprehension are much more efficient than a for loop, the last_check is necessary if you don't have a pattern in your list of indices.
dfs[0]
a b c
0 1 1 1
1 2 2 2
dfs[2]
a b c
5 6 6 6
6 7 7 7
python - Pandas split DataFrame according to indices - Stack Overflow
pandas - split data frame based on integer index - Stack Overflow
Split pandas dataframe based on first value in row
You can get at the index you need directly without relying on a counter:
# Get index for 2nd occurrence of pattern in 1st column cutoff = df.loc[(df.index < 40) & (df.iloc[:, 0].str.contains(r'^[4-6][A-z]*[0-9]'))].index[1] # Using loc, not iloc, because we have the actual index df1 = df.loc[:cutoff] df1 = df.loc[cutoff:]
I also added a '^' to your regex, assuming this pattern needs to occur at the start.
More on reddit.compython - Splitting pandas dataframe based on index value - Stack Overflow
Use slice:
In [11]: s = pd.Series([1,2,3,4])
In [12]: s.iloc[::2] # even
Out[12]:
0 1
2 3
dtype: int64
In [13]: s.iloc[1::2] # odd
Out[13]:
1 2
3 4
dtype: int64
Here's some comparisions
In [100]: df = DataFrame(randn(100000,10))
simple method (but I think range makes this slow), but will work regardless of the index (e.g. does not have to be a numeric index)
In [96]: %timeit df.iloc[range(0,len(df),2)]
10 loops, best of 3: 21.2 ms per loop
The following require an Int64Index that is range based (which is easy to get, just reset_index()).
In [107]: %timeit df.iloc[(df.index % 2).astype(bool)]
100 loops, best of 3: 5.67 ms per loop
In [108]: %timeit df.loc[(df.index % 2).astype(bool)]
100 loops, best of 3: 5.48 ms per loop
make sure to give it index positions
In [98]: %timeit df.take(df.index % 2)
100 loops, best of 3: 3.06 ms per loop
same as above but no conversions on negative indicies
In [99]: %timeit df.take(df.index % 2,convert=False)
100 loops, best of 3: 2.44 ms per loop
This winner is @AndyHayden soln; this only works on a single dtype
In [118]: %timeit DataFrame(df.values[::2],index=df.index[::2])
10000 loops, best of 3: 63.5 us per loop
I have a dataframe I need to split in two, where the splitting point is the first value in some row. My df looks like this:
0 1 2 3 4 0 6Vfatl2 NaN NaN NaN NaN 1 Name 123456 7.377354 2.000000 6.005613 ....... 31 Someone 123486 7.158705 2.333333 7.290309 32 6Vfatl4 NaN NaN NaN NaN 33 Person 123488 6.883334 3.666667 6.764028
I want to split the df between lines 31 and 32 (keeping row 32 as part of the second df). I have looked up some solutions on StackOverflow but none of them seemed to work for this particular problem.
I tried this, but isn't there a better way to do it? (And yes, I am SURE I have to cut before the 40th row):
indices = []
for i in range(40):
if re.match(r'[4-6][A-z]*[0-9]',df.iloc[i][0]):
indices.append(i)
df1 = df.iloc[:indices[1]] #Split dataframe into 2 (per class)
df2 = df.iloc[indices[1]:]EDIT: Formatting, (temporary) solution
You add reset_index instead df_test1['index1'] = df_test1.index and for clean df add rename_axis - it remove column name place:
df_test1 = df_test.groupby(['code' , 'year', 'week', 'place'])['vl'].sum() \
.unstack(fill_value=0) \
.reset_index() \
.rename_axis(None, axis=1)
print (df_test1)
code year week region1 region2 region3
0 111.0002.0056 2017 28 0 1 0
1 111.0002.0056 2017 29 1 1 0
2 111.0002.0056 2017 30 0 1 0
3 111.0002.0114 2017 31 0 0 1
4 112.5600.6325 2017 30 0 2 0
5 112.5600.8159 2017 28 0 0 1
6 112.5600.8159 2017 30 0 1 0
7 112.5600.8159 2017 31 0 0 1
8 112.6500.2285 2017 31 0 1 0
Last if necessary change ordering of columns:
#all cols are columns in df_test1
cols = ['code' , 'year', 'week']
df_test1 = df_test1[[x for x in df_test1.columns if x not in cols] + cols]
print (df_test1)
region1 region2 region3 code year week
0 0 1 0 111.0002.0056 2017 28
1 1 1 0 111.0002.0056 2017 29
2 0 1 0 111.0002.0056 2017 30
3 0 0 1 111.0002.0114 2017 31
4 0 2 0 112.5600.6325 2017 30
5 0 0 1 112.5600.8159 2017 28
6 0 1 0 112.5600.8159 2017 30
7 0 0 1 112.5600.8159 2017 31
8 0 1 0 112.6500.2285 2017 31
Or you can try this pd.crosstab
df=df.set_index(['code', 'year', 'week','vl'])
df=pd.crosstab(df.index,df.place).reset_index()
df[['code', 'year', 'week','vl']]=df['row_0'].apply(pd.Series).drop('row_0',axis=1)
Out[32]:
place region1 region2 region3 code year week vl
0 0 1 0 111.0002.0056 2017 28 1
1 1 1 0 111.0002.0056 2017 29 1
2 0 1 0 111.0002.0056 2017 30 1
3 0 0 1 111.0002.0114 2017 31 1
4 0 2 0 112.5600.6325 2017 30 1
5 0 0 1 112.5600.8159 2017 28 1
6 0 1 0 112.5600.8159 2017 30 1
7 0 0 1 112.5600.8159 2017 31 1
8 0 1 0 112.6500.2285 2017 31 1