Python specific sheet price data resample

import panda as pd
import numpy as np
import datetime

import sys
from openpyxl import load_workbook  
sys.path.append("C:\\pytest")
df= pd.read_excel('C:\\pytest\\dailyprice.xlsx', sheet_name='dprice')

No_List = ["Date", "Year", "Month"]

df.head()

df.columns

for x in range(0,len(df.columns),2):
    temp_df = df[[df.columns[x], df.columns[x+1]]]
    temp_df = temp_df.fillna(method='bfill').dropna()
    temp_df.columns = ['Date', 'Data']
    temp_df['Date'] = pd.to_datetime(temp_df['Date']).apply(lambda x: x.date())
    print(temp_df)

Hello.

I'm collecting daily price data on a specific sheet, and I want to get the average weekly and monthly price, so I'm going to convert it to Python resample and move the data to a separate sheet.

The df consists of the above data, and there are two columns of date and price data on one sheet, so we divided it into date and data using the for statement. I have a question here.

Thank you.

python pandas dataframe

2022-09-21 11:49

2 Answers

If it's me, I'll divide the original df vertically by two columns and make three data frames before I'll work on it.

It's very simple to divide vertically. Use iloc. This is an example below.


>>> df = pd.DataFrame({ "date1":[ '1990-01-02', '1990-01-03', '1990-01-04'] ,
            "prof":[198,198, 198],
            "date2":[ '1990-01-02', '1990-01-03', '1990-01-04'],
            "buth":[196.5, 196.5, 196.5],
            "date3":[ '1991-01-02', '1991-01-03', '1991-01-04'], "naf":[ 206.5, 206.5, 205] })
>>> df
        date1  prof       date2   buth       date3    naf
0  1990-01-02   198  1990-01-02  196.5  1991-01-02  206.5
1  1990-01-03   198  1990-01-03  196.5  1991-01-03  206.5
2  1990-01-04   198  1990-01-04  196.5  1991-01-04  205.0
>>> df_prof = df.iloc[:,0:2]
>>> df_prof
        date1  prof
0  1990-01-02   198
1  1990-01-03   198
2  1990-01-04   198
>>> df_buth = df.iloc[:,2:4]
>>> df_buth
        date2   buth
0  1990-01-02  196.5
1  1990-01-03  196.5
2  1990-01-04  196.5
>>> df_naf = df.iloc[:,4:6]
>>> df_naf
        date3    naf
0  1991-01-02  206.5
1  1991-01-03  206.5
2  1991-01-04  205.0

After creating propane, butane, and naphtha data frames in this division, for each data frame,

2022-09-21 11:49

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)
    print(all_data) 

index_data = all_data[0].set_index('Date')
index_data.index = pd.to_datetime(index_data.index)
df_w1 = index_data.resample('w').mean()

for x in range(1,len(all_data)):
    index_data = all_data[x].set_index('Date')
    index_data.index = pd.to_datetime(index_data.index)
    df_w2 = index_data.resample('w').mean()
    df_total = pd.concat([df_w1,df_w2], axis = 1)
    df_w1 = df_total

df_w1

Hello.

The result value was obtained by modifying the question code as below, but the date index cannot be separated and integrated with concat.

Is there any other way?

2022-09-21 11:49

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656