Split a pandas dataframe based on values of multiple rows

philbeet 0
Viewed 28

I have two large dataframes : salepricedf which stores the sale price, time sold, and date sold of a warehouse item, and dataanalysisdf, which stores all bid/ask price updates of auction.

I want to conduct an analysis on item in salepricedf of how their price fluctuates until the end of our dataset. I would like to split dataanalysisdf by multiple rows, of date and time.

I have attempted to do this by using the following code:

dataanalysisdf = dataanalysisdf[dataanalysisdf.loc[dataanalysisdf['Date']==date].index[0]:]
dataanalysisdf= dataanalysisdf[dataanalysisdf.loc[dataanalysisdf['Time']==time.index[0]:]

but this does not work. It skips the date + 1, and it doesnt even listen to the time.

NOTE

it should be noted that my dates and times are not actual datetime objects. They are integers of the format YYMMDD and HHMMSS.

pandas python
1 Answers

You can use pd.cut to split your dataframe into multiple dataframes.

import pandas as pd

df = pd.DataFrame({'date': ['2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05'],
                   'time': ['00:00:00', '00:00:00', '00:00:00', '00:00:00', '00:00:00'],
                   'value': [1, 2, 3, 4, 5]})

df['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time'])

#split df into multiple df
dfs = [group for _, group in df.groupby(pd.cut(df.datetime, bins=3))]

#print first df
print(dfs[0])

Output:

   date        time          value        datetime
0  2018-01-01  00:00:00      1 2018-01-01 00:00:00
1  2018-01-02  00:00:00      2 2018-01-02 00:00:00
2  2018-01-03  00:00:00      3 2018-01-03 00:00:00
Linus O. Linhof0
Related