In Pandas, how to return 2 data using resample('D').first()?

Viewed 60

Questions

  • Q1. Why does the float data changes to string data when it gets put into a pd.dataframe? Is there any way to keep it float, rather than changing it to float afterwards with .astype(float)?
  • Q2. How to get 2 data using resample('D').first() method? The method .first() returns only 1 data, while I want 2 data to be returnd. : If it is not possible with .first() method, can you give me an alternative solution?

Code Example

import pandas as pd
import numpy as np
from datetime import datetime

BTC_df = pd.DataFrame(np.array([[1.05,'BTC'],[1.2,'BTC'],[0.9,'BTC']]),
                      columns = ['return','coin'],
                      index = [datetime(2020,5,1,15), datetime(2020,5,2,9,20), datetime(2020,5,3,23,40)])

ETH_df = pd.DataFrame(np.array([[1.1,'ETH'],[0.9,'ETH'],[0.95,'ETH']]),
                      columns = ['return','coin'],
                      index = [datetime(2020,5,1,8,30), datetime(2020,5,2,17,30), datetime(2020,5,3,11,50)])

EOS_df = pd.DataFrame(np.array([[1.3,'EOS'],[0.6,'EOS'],[0.8,'EOS']]),
                      columns = ['return','coin'],
                      index = [datetime(2020,5,1,1,20), datetime(2020,5,2,22,10), datetime(2020,5,3,13,5)])

BTC_df
>>>                  return  coin
2020-05-01 15:00:00   1.05   BTC
2020-05-02 09:20:00   1.2    BTC
2020-05-03 23:40:00   0.9    BTC

##############################################
# Q1. Why does the 'float' changes to 'str'? #
##############################################
BTC_df.loc[datetime(2020,5,2,9,20),'return']
>>>   '1.2'

merged_df = pd.concat([BTC_df,ETH_df,EOS_df])
merged_df.loc[:,'return'] = merged_df.loc[:,'return'].astype(float)  # from 'str' to 'float'
merged_df.loc[:,'time'] = merged_df.index.time  # to preserve hour and minutes

#################################################################
# Q2. How to create the 'desired output' using resample method? #
#################################################################
merged_df.resample('D').first()
>>>        return  coin     time
2020-05-01  1.30    EOS   01:20:00
2020-05-02  1.20    BTC   09:20:00
2020-05-03  0.95    ETH   11:50:00

My desired output is as follows, showing 2 coins with the earliest time :

desired_output_df
>>>         return  coin    time
2020-05-01  1.30    EOS   01:20:00
            1.10    ETH   08:30:00
2020-05-02  1.20    BTC   09:20:00
            0.90    ETH   17:30:00
2020-05-03  0.95    ETH   11:50:00
            0.80    EOS   13:05:00
1 Answers

Q1: this is because you defined the whole data as one array, which can have one type only for all data (string). Define it columnwise like so:

BTC_df = pd.DataFrame({'return': [1.05, 1.2, 0.9], 'coin': ['BTC', 'BTC', 'BTC']},
                      index = [datetime(2020,5,1,15), datetime(2020,5,2,9,20), datetime(2020,5,3,23,40)])

Q2: use

merged_df.resample('D').apply(lambda x: x[:2])
Related