Fairly new to programming and python in general. Learning as I go. Breaking thing, ALOT, but learning ALOT.
What I want to do, which I have not been able to figure out is create a CSV with certain columns. Currently, one of the columns, after I organize the data, is a date-timestamp, which I need to be separated into 2 columns one for date(yyyy/mm/dd) and one for time (HH:MM:SS 24-hour time). The code looks like this:
activities = api.get_activities(
date='2021-03-09',
direction='asc',
)
df = pd.DataFrame([activity._raw for activity in activities])
print('created dataframe')
id = df["id"]
activity_type = df["activity_type"]
transaction_time = df["transaction_time"]
type = df["type"]
price = df["price"]
qty = df["qty"]
side = df["side"]
symbol = df["symbol"]
leaves_qty = df["leaves_qty"]
order_id = df["order_id"]
cum_qty = df["cum_qty"]
df[['date', 'time']] = df.transaction_time.str.strip('Z').str.split('T', expand=True)
header = ["date","time","symbol","qty","price","side"]
df.to_csv('daily_journal_export.csv', columns = header, index = False)
print('Completed')
When exported I get something that looks like this:
transaction_time,symbol,qty,price,side 2021-03-09T15:03:06.0925Z,XNET,5059,6.21,buy 2021-03-09T15:03:06.888595Z,XNET,718,6.21,buy 2021-03-09T15:03:09.474924Z,CLNE,3661,11.65,buy 2021-03-09T15:05:13.957144Z,SOL,1728,12.68,buy 2021-03-09T15:57:53.094945Z,SOL,753,13.33,sell 2021-03-09T15:57:53.118822Z,XNET,5777,6.09,sell 2021-03-09T15:57:53.220454Z,CLNE,3661,11.78,sell 2021-03-09T15:57:53.613874Z,SOL,975,13.33,sell
The transaction_time looks like this that needs to be separated and converted:
- Date column: (yyyy/mm/dd)
- Time column: (HH:MM:SS 24-hour time)
2021-03-09T15:03:06.0925Z
Any help would be greatly appreciated.
Advertisement
Answer
You can use str.split() on the timestamps with the expand param (2a below), or use .dt.date and .dt.time (2b below).
- Load the sample data:
>>> import pandas as pd
>>> df = pd.read_csv('daily_journal_export.csv')
transaction_time symbol qty price side
0 2021-03-09T15:03:06.0925Z XNET 5059 6.21 buy
1 2021-03-09T15:03:06.888595Z XNET 718 6.21 buy
2 2021-03-09T15:03:09.474924Z CLNE 3661 11.65 buy
3 2021-03-09T15:05:13.957144Z SOL 1728 12.68 buy
4 2021-03-09T15:57:53.094945Z SOL 753 13.33 sell
5 2021-03-09T15:57:53.118822Z XNET 5777 6.09 sell
6 2021-03-09T15:57:53.220454Z CLNE 3661 11.78 sell
7 2021-03-09T15:57:53.613874Z SOL 975 13.33 sell
2a. Strip the decimals, split by T, and expand to separate columns:
>>> df[['date', 'time']] = df.transaction_time.str.replace(r'..*', '').str.split('T', expand=True)
>>> df = df.drop('transaction_time', axis=1)
symbol qty price side date time
0 XNET 5059 6.21 buy 2021-03-09 15:03:06
1 XNET 718 6.21 buy 2021-03-09 15:03:06
2 CLNE 3661 11.65 buy 2021-03-09 15:03:09
3 SOL 1728 12.68 buy 2021-03-09 15:05:13
4 SOL 753 13.33 sell 2021-03-09 15:57:53
5 XNET 5777 6.09 sell 2021-03-09 15:57:53
6 CLNE 3661 11.78 sell 2021-03-09 15:57:53
7 SOL 975 13.33 sell 2021-03-09 15:57:53
2b. Alternatively, you can use .dt.date and .dt.time instead of 2a’s string splitting method:
>>> df['date'] = pd.to_datetime(df.transaction_time).dt.date
>>> df['time'] = pd.to_datetime(df.transaction_time.str.replace(r'..*', '')).dt.time
>>> df = df.drop('transaction_time', axis=1)
symbol qty price side date time
0 XNET 5059 6.21 buy 2021-03-09 15:03:06
1 XNET 718 6.21 buy 2021-03-09 15:03:06
2 CLNE 3661 11.65 buy 2021-03-09 15:03:09
3 SOL 1728 12.68 buy 2021-03-09 15:05:13
4 SOL 753 13.33 sell 2021-03-09 15:57:53
5 XNET 5777 6.09 sell 2021-03-09 15:57:53
6 CLNE 3661 11.78 sell 2021-03-09 15:57:53
7 SOL 975 13.33 sell 2021-03-09 15:57:53
- If you want to move
dateandtimeback to the front:
>>> columns = df.columns.to_list()[-2:] + df.columns.to_list()[:-2]
>>> df = df[columns]
date time symbol qty price side
0 2021-03-09 15:03:06 XNET 5059 6.21 buy
1 2021-03-09 15:03:06 XNET 718 6.21 buy
2 2021-03-09 15:03:09 CLNE 3661 11.65 buy
3 2021-03-09 15:05:13 SOL 1728 12.68 buy
4 2021-03-09 15:57:53 SOL 753 13.33 sell
5 2021-03-09 15:57:53 XNET 5777 6.09 sell
6 2021-03-09 15:57:53 CLNE 3661 11.78 sell
7 2021-03-09 15:57:53 SOL 975 13.33 sell