#Create SuperfileOrg
df1 = pd.read_excel('C:\pytest\\Data_org_superfile.xlsx',sheet_name='name')
Data_Org = {}
for Freq in set(df1['Freq']):
Data_Org[Freq] = {}
for i,NAME in tqdm(enumerate(df1[df1['Freq']==Freq]['NAME'])):
df=pd.read_excel ('C:\pytest\superfile_ver1_2020314.xlsm',sheet_name=Freq)
all_data = []
for x in range(0,len(df.columns),2):
temp_df = []
temp_df = df[[df.columns[x], df.columns[x+1]]]
temp_df = temp_df.fillna(method='bfill').dropna()
temp_df.columns = ['Date', 'Data']
all_data.append(temp_df)
Data_Org[Freq][df1[df1['Freq']==Freq].iloc[i]['NAME']]= all_data[i]
Data_Org
df1 is shown below.
Hi, everyone. To make a dictionary of various daily price data in Excel, I have written the above code and used it.
There are data that have a history of daily price data since the 1970s It takes four minutes to read the data above. The total number of prices is only 21.
We can modify the code above more efficiently to reduce data read time Is there?
Thank you.
python loops pandas
with pd.ExcelFile ("Superfile").xlsm") as xls:
for freq in uniq_freqs:
df_sheet = pd.read_excel(xls, sheet_name=freq)
# ...
for i, name in enumerate(names):
# ...
# ...
# ...
Like this, read the file once, and read it by sheet. It might be a little faster.
I recommend you to read the basic book of Pandas.
© 2024 OneMinuteCode. All rights reserved.