Python, move Excel file

You have time series statistics.

The time series statistics consist of a separate column of dates and data.

The data movement we want to implement using Python is as above.

This is the format in which a specific month is fixed and then filled in 12.

You want to move the Excel data for the purpose of representing it in the above graph form.

After reading Excel data through Python, simple data movement is possible, but it is not easy to organize the above data.

I have to calculate the monthly fixed value and the average value for 5 years, so I don't think it'll be possible with the normal pivot function, is there a good way? Since it is a season concept, the 2019 time series also includes 2020 data.

I can't figure it out no matter how many times I search it'

Thank you.

python excel

2022-09-20 22:19

2 Answers

https://hashcode.co.kr/questions/10557/%ED%8C%8C%EC%9D%B4%EC%8D%AC-csv%ED%8C%8C%EC%9D%BC-%EA%B0%80%EC%A0%B8%EC%99%80%EC%84%9C-numpy%EB%A1%9C-%EC%B5%9C%EB%8C%93%EA%B0%92-%EB%B6%84%EC%82%B0-%EA%B5%AC%ED%95%98%EA%B8%B0

If you look at what I answered here, there is a Pandas code that converts daily data to monthly. When fixed on a monthly basis, the year_month is converted to a key, so 2020 data are included in the time series marked in 2019I don't think it'll be a problem to do it. For your information, you need to specify whether the aggregation rule should be max or sum

With the monthly DataFrame obtained like this, it doesn't seem difficult to get an average of 5 years, but to draw the above picture with matplotlib.

I don't know what the source data looks like, and I don't know what rules to apply when converting to monthly, so I'm just giving you a link first Haha

2022-09-20 22:19

import panda as pd
import numpy as np
import sys
from openpyxl import load_workbook  
sys.path.append("C:\\pytest")
df = pd.read_excel('C:\\pytest\\brazil.xlsx')

# Check Data
df.head()

# Separate year and month for pivot application
df['Month'] = df['date'].map(lambda x: x.month)
df['Year'] = df['date'].map(lambda x: x.year)
df1 = df.copy()
df1

# Apply Pivot to create a table by month
table = df1.pivot_table(values = 'value', index = 'Year', columns='Month')
table

# To change the order of table columns
table = table[[10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9]]
table

# Separating tables for seasonal applications
table1 = table[[10, 11, 12]]
table1
table2 = table[[1, 2, 3, 4, 5, 6, 7, 8, 9]]
table2

# Work to paste tables in the same year
table2.index
table2['Year1'] = table2.index - 1

table2.reset_index
table2.set_index('Year1', inplace = True)
table2

# Working on pasting tables
table_result = pd.concat([table1, table2], axis = 1)
table_result

# Remove missing values
table_result1 = table_result.copy().dropna(how = 'all')
table_result1

table_result2 = table_result.copy().dropna()
table_result2

# Just five years to get the five-year average
table_result3 = table_result2.iloc[-5:]
table_result3

# Obtain and insert a 5-year average
table_result1.loc['5average'] = table_result3.mean(axis = 0)

# Confirmation of results
table_result1

I'm sharing my own solution(?) even though it's an ignorant method just in case someone needs it. I'm not used to Plgram, so I don't think it's a neat method, but I got the result I wanted.

2022-09-20 22:19

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