python-3.x 如何在数据框中将月份值转换为日期值？

我想将月份（BP，RE）值转换为日期（BP，RE）值，方法是将月份值除以 Dataframe 中该月的相应天数，但一个名为“质量参数”的类别除外。我想将名为“质量参数”的类别的日值显示为与月值相同的值（例如：如果1月份值是45，我想显示从1月1日到1月31日的值为45。每个月都一样）。
我使用下面的代码将月份数据拆分为日期数据。在此情况下，不处理异常（类别名为“质量参数”）：

distillery_excel_data_upper = distillery_excel_data.apply(lambda x: x.astype(pd.StringDtype()).str.upper())
distillery_excel_data_upper['Date'] = pd.to_datetime(distillery_excel_data_upper['Year'] + distillery_excel_data_upper['Month'], format='%Y%B')
excel_date = pd.to_datetime(distillery_excel_data_upper['Date'])
daywise_excel_data = (distillery_excel_data_upper.assign(Daily=[pd.date_range(*x, freq='D') for x in zip(excel_date, excel_date.add(pd.offsets.MonthEnd()))]).explode("Daily"))
daywise_excel_data["BP"] = daywise_excel_data["BP"].apply(pd.to_numeric)
daywise_excel_data["RE"] = daywise_excel_data["RE"].apply(pd.to_numeric)

daywise_excel_data[['BP', 'RE']] = daywise_excel_data[['BP', 'RE']].div(
daywise_excel_data.groupby(["Date", "Category", "Parameter", "Region", "Factory", "UOM"])['Date'].transform("count"), axis=0)
daywise_excel_data = daywise_excel_data.drop(['Date', 'Year', 'Month'], axis=1)

daywise_excel_data.rename(columns={'Daily': 'date'}, inplace=True)

以上代码适用于所有类别。在此BP中，RE、类别、参数、地区、工厂、计量单位、年份和月份是数据框列。
下面显示了输入 Dataframe ：

Year  Month   Region    Factory  Category         Parameter UOM    BP       RE
0   2023  April  Tamil Nadu   A     Production            ac     L    30.0     60.0
1   2023  April  Tamil Nadu   B     Quality Parameter     acc    L    45.0     20.0
2   2023  April  Karnataka    C     Quality Parameter      bc    L    23.0     50.0
3   2023  May    Karnataka    C     Quality Parameter      bc    L     0.0     43.0
4   2023  June   Karnataka    C     Quality Parameter      bc    L    10.0     56.0
5   2023  June   Karnataka    C     Production             bc    L    60.0     90.0

所需输出：

Year  Month    Date     Region    Factory  Category         Parameter UOM      BP     RE
0 2023  April 2023-04-01 Tamil Nadu   A     Production            ac     L       1.0   2.0
0 2023  April 2023-04-02 Tamil Nadu   A     Production            ac     L       1.0   2.0
.. ..   ..       ..       ..          ..       ..     .. .. .. .. 
.. ..   ..       ..       ..          ..       ..     .. .. .. .. 
0 2023  April 2023-04-30 Tamil Nadu   A     Production            ac     L       1.0   2.0
1 2023  April 2023-04-01 Tamil Nadu   B     Quality Parameter     acc    L       45.0  20.0
1 2023  April 2023-04-02 Tamil Nadu   B     Quality Parameter     acc    L       45.0  20.0
.. ..   ..       ..       ..          ..       ..     .. .. .. .. 
.. ..   ..       ..       ..          ..       ..     .. .. .. ..

1 2023  April 2023-04-30 Tamil Nadu   B     Quality Parameter     acc    L       45.0  20.0
2 2023  April 2023-04-01 Karnataka    C     Quality Parameter     acc    L       23.0  50.0
2 2023  April 2023-04-02 Karnataka    C     Quality Parameter     acc    L       23.0  50.0
.. ..   ..       ..       ..          ..       ..     .. .. .. .. 
.. ..   ..       ..       ..          ..       ..     .. .. .. ..
2 2023  April 2023-04-30 Karnataka    C     Quality Parameter     acc    L       23.0  50.0
3 2023  May   2023-05-01 Karnataka    C     Quality Parameter     acc    L       0.0   43.0
3 2023  May   2023-05-02 Karnataka    C     Quality Parameter     acc    L       0.0   43.0
.. ..   ..       ..       ..          ..       ..     .. .. .. .. 
.. ..   ..       ..       ..          ..       ..     .. .. .. ..
3 2023  May   2023-05-31 Karnataka    C     Quality Parameter     acc    L       0.0   43.0
4 2023  June  2023-05-01 Karnataka    C     Quality Parameter     acc    L       10.0  56.0
4 2023  June  2023-05-02 Karnataka    C     Quality Parameter     acc    L       10.0  56.0
.. ..   ..       ..       ..          ..       ..     .. .. .. .. 
.. ..   ..       ..       ..          ..       ..     .. .. .. ..
4 2023  June  2023-05-30 Karnataka    C     Quality Parameter     acc    L       10.0  56.0
5 2023  June  2023-05-01 Karnataka    C     Production        acc    L       2.0    3.0
5 2023  June  2023-05-02 Karnataka    C     Production        acc    L       2.0    3.0
.. ..   ..       ..       ..          ..       ..     .. .. .. .. 
.. ..   ..       ..       ..          ..       ..     .. .. .. ..
5 2023  June  2023-05-30 Karnataka    C     Production        acc    L       2.0    3.0

谁能提出一个解决方案来实现这一结果。

用途：

#create datetime column like 3rd column
df.insert(2, 'Date', pd.to_datetime(df['Year'].astype(str) + df['Month'], format='%Y%B'))

#get number of rows per month
df['days'] = df['Date'].dt.days_in_month

#repeat rows by months
df1 = df.loc[df.index.repeat(df['days'])]

#add counter to days
df1['Date'] += pd.to_timedelta(df1.groupby(level=0).cumcount(), 'd')

#create default index
df1 = df1.reset_index(drop=True)

#test if not equal Quality Parameter
m = df1['Category'].ne('Quality Parameter')

#divide number of days for all rows match condition
df1.loc[m, ['BP', 'RE']] = df1[['BP', 'RE']].div(df1.pop('days'), axis=0)

print (df1.head())
   Year  Month       Date      Region Factory    Category Parameter UOM   BP  \
0  2023  April 2023-04-01  Tamil Nadu       A  Production        ac   L  1.0   
1  2023  April 2023-04-02  Tamil Nadu       A  Production        ac   L  1.0   
2  2023  April 2023-04-03  Tamil Nadu       A  Production        ac   L  1.0   
3  2023  April 2023-04-04  Tamil Nadu       A  Production        ac   L  1.0   
4  2023  April 2023-04-05  Tamil Nadu       A  Production        ac   L  1.0   

    RE  
0  2.0  
1  2.0  
2  2.0  
3  2.0  

print (df1.iloc[50:56])
    Year  Month       Date      Region Factory           Category Parameter  \
50  2023  April 2023-04-21  Tamil Nadu       B  Quality Parameter       acc   
51  2023  April 2023-04-22  Tamil Nadu       B  Quality Parameter       acc   
52  2023  April 2023-04-23  Tamil Nadu       B  Quality Parameter       acc   
53  2023  April 2023-04-24  Tamil Nadu       B  Quality Parameter       acc   
54  2023  April 2023-04-25  Tamil Nadu       B  Quality Parameter       acc   
55  2023  April 2023-04-26  Tamil Nadu       B  Quality Parameter       acc   

   UOM    BP    RE  
50   L  45.0  20.0  
51   L  45.0  20.0  
52   L  45.0  20.0  
53   L  45.0  20.0  
54   L  45.0  20.0  
55   L  45.0  20.0

编辑：在您的解决方案中，可以用途：

m = daywise_excel_data['Category'].ne('Quality Parameter')
daywise_excel_data.loc[m, ['BP', 'RE']] = daywise_excel_data[['BP', 'RE']]
                           .div(daywise_excel_data.df['Date'].dt.days_in_month, axis=0)

python-3.x 如何在数据框中将月份值转换为日期值？

1条答案

相关问题

热门标签

最新问答