I am going to provide you one code. Please check and fix this. In the csv file, the rows represent the hours and the columns represent the dates...

import pandas as pd

import numpy as np

from datetime import datetime

def convert$\_$to$\_$int$($value$)$:

return int$($value$)$

def replace$\_$negatives$\_$with$\_$averages$($df$)$:

rows, cols $=$ df$.$shape

for row in range$($rows$)$:

row$\_$values $=[]$

for col in range$($cols$)$:

value $=$ df$[$col$][$row$]$

if isinstance$($value$,($int$))$ and value $!=-1$:

row$\_$values.append$($value$)$

for col in range$($cols$)$:

if df$[$col$][$row$]==-1$:

next$\_$value $=7$

valid$\_$values $=[]$

new$\_$col $=$ col $+$ next$\_$value

while new$\_$col $<$ cols:

value $=$ df$[$new$\_$col$][$row$]$

if isinstance$($value$,($int$,$ float$))$ and value $!=-1$:

valid$\_$values.append$($value$)$

new$\_$col $=$ new$\_$col $+$ next$\_$value

avg $=$ sum$($valid$\_$values$)/$ len$($valid$\_$values$)$

for col$\_1$ in range$($col$,$ cols, next$\_$value$)$:

if df$[$col$\_1][$row$]==-1$:

df$[$col$\_1][$row$]=$ avg

def process$\_$csv$($file$\_$path, output$\_$path$=$'output.csv$\text{'})$:

processed$\_$data $=\{\}$

df $=$ pd$.$read$\_$csv$($file$\_$path, header$=$None$)$

data $=$ df$.$copy$()$

for col in data.columns:

processed$\_$column $=$ data$[$col$][1$:$]$

processed$\_1=$pd$.$DataFrame$($processed$\_$column$)$

date $=$ str$($processed$\_$column$[1])$

if date $=="---"$:

processed$\_$column$[1]=-2$

# blank$\_$date $=$ processed$\_$column.iloc$[0]$

processed$\_$column$[2$:$]=-1$

processed$\_$data$[$col$]=$ processed$\_$column.tolist$()$

else:

day$\_$index $=$ datetime.strptime$($date$,\text{'}\%$d$/\%$m$/\%$Y$\text{'}).$weekday$()$

processed$\_$column$[1]=$ day$\_$index

processed$\_$data$[$col$]=$ processed$\_$column.tolist$()$

processed$\_$df $=$ pd$.$DataFrame$($processed$\_$data$)$

processed$\_$df$.$fillna$(-1,$ inplace$=$True$)$

processed$\_$df $=$ processed$\_$df$.$map$($convert$\_$to$\_$int$)$

pd$.$set$\_$option$(\text{'}$display$.$max$\_$columns', None$)$

processed$\_$df$.$to$\_$csv$(\text{'}$first$-$pre.csv$\text{'},$ index$=$False, header$=$False$)$

new$\_$df $=$ processed$\_$df$.$transpose$()$

# data$\_$dict$=[]$

for idx in range$($len$($new$\_$df$))$:

temp$=$new$\_$df$[0][$idx$]$

if temp $==-2$:

prec $=$ new$\_$df$[0][$idx $-1]$

if prec $<6$:

missing$\_$days $=$ list$($range$($prec $+1,7))$

for i in missing$\_$days:

new$\_$rows $=$ pd$.$DataFrame$(\{0$: i$\},$ index$=[0])$

# Split the DataFrame into two parts

part$\_$before $=$ new$\_$df$[$:idx$]$

part$\_$after $=$ new$\_$df$[$idx:$]$

new$\_$df $=$ pd$.$concat$([$part$\_$before, new$\_$rows, part$\_$after$]).$reset$\_$index$($drop$=$True$)$

# Move the index forward to account for the new row

idx $=$ idx $+1$

next $=$ new$\_$df$[0][$idx $+1]$

if next $>0$: # Fill days from week start until next

missing$\_$days$\_1=$ list$($range$(0,$ next$))$

for i in missing$\_$days$\_1$:

# Split the DataFrame into two parts: before and after the current position

part$\_$before $=$ new$\_$df$[$:idx $+1]$

part$\_$after $=$ new$\_$df$[$idx $+1$:$]$

# Create a new DataFrame with the missing day

new$\_$row $=$ pd$.$DataFrame$(\{0$: i$\},$ index$=[0])$

new$\_$df $=$ pd$.$concat$([$part$\_$before, new$\_$row, part$\_$after$]).$reset$\_$index$($drop$=$True$)$

# Move the index forward to account for the new row

idx $=$idx $+1$

idx $=$ idx $+1$

for i in range$($len$($new$\_$df$))$:

last$\_$value $=$ new$\_$df$[0][$len$($new$\_$df$)-1]$

for i in range$($last$\_$value $+1,7)$:

new$\_$row $=$ pd$.$DataFrame$(\{0$: i$\},$index$=[0])$

new$\_$df $=$ pd$.$concat$([$new$\_$df$,$ new$\_$row$]).$reset$\_$index$($drop$=$True$)$

print$($new$\_$df$)$

# new$\_$df$.$to$\_$csv$(\text{'}$second$\_$pre.csv$\text{'})$

new$\_$df $=$ pd$.$DataFrame$($new$\_$df$).$transpose$()$

new$\_$df$.$fillna$(-1,$ inplace$=$True$)$

new$\_$df $=$ new$\_$df$.$map$($convert$\_$to$\_$int$)$

new$\_$df$.$to$\_$csv$(\text{'}$final$.$csv$\text{'})$

if $($new$\_$df$[0]==-2).$any$()$:

new$\_$df $=$ new$\_$df$.$drop$($columns$=[-2])$

week$\_$labels $=[]$

week$\_$counter $=1$

for i$,$ col in enumerate$($new$\_$df$.$columns$)$:

day $=$ new$\_$df$[$col$][0]$

if i $!=0$ and day $==0$:

week$\_$counter $+=1$

week$\_$label $=$ f'week$\_\{$week$\_$counter$\}\text{'}$

week$\_$labels.append$($week$\_$label$)$

week$\_$labels$\_$df $=$ pd$.$DataFrame$([$week$\_$labels$],$ columns$=$new$\_$df$.$columns$)$

combined$\_$df $=$ pd$.$concat$([$week$\_$labels$\_$df$,$ new$\_$df$])$

combined$\_$df$.$reset$\_$index$($drop$=$True, inplace$=$True$)$

# for row in combined$\_$df$[1$:$]$:

# combined$\_$df$[$row$]=$ combined$\_$df$[$row$].$apply$($convert$\_$to$\_$int$)$

replace$\_$negatives$\_$with$\_$averages$($combined$\_$df$)$

final$\_$df $=$ combined$\_$df

print$("$final$\_$df is:$")$

final$\_$df$[1$:$]=$ final$\_$df$[1$:$].$astype$($int$)$

print$("$final df$[1]$ is:$",$ final$\_$df$[1$:$])$

exit$(0)$

final$\_$df$.$to$\_$csv$(\text{'}$output$.$csv$\text{'},$ index$=$None, header$=$ None$)$

if $\_\_$name$\_\_==\text{'}\_\_$main$\_\_\text{'}$:

input$\_$file $=$ 'centova$\_$dir$\_$stadio.csv$\text{'}$ # Input file path

output$\_$path $=$ 'output.csv$\text{'}$ # Output file path

result $=$ process$\_$csv$($input$\_$file, output$\_$path$)$

print$("$Processed DataFrame:"$)$

print$($result$)$

Now if you understand the logic you will know that I am trying to replace the negatives $(-1)$ with the average o same hours for same days in weekday values $($ ex$,$ take all monday $7$ pm data and make the average and if there is $-1$ in any monday $7$ pm$,$ replace the $-1$ with the average$).$

in the replace negatives with average function, I want to create two empty matrix of sum and count and then I am going to provide you one pseudo code. Can you please adjust the code by using this?

First create these two matrixes..

then,

for row in $(2,26)$:

for col in cols:

if df$[$col$][$row$]!=-1$ then

sum$[$col$\%7][$row$]<---$sum$[$col$\%7][$row$]+$df$[$col$][$row$]$

count$[$col$\%7][$row$]++$

for row

for col

if df$[$col$][$row$]==1$ then

df$[$col$][$row$]<----$sum$[$col

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!

Q:

#Please Answer in python Codeblock Read the .csv file cereal_names.csv as a data frame called cereal_names and output the first five rows. Create a new data frame called cereals_data2 that combines...

Q:

For the 2 CSV files, please email me: mjong at terpmail dot umd dot edu. It won't let me attach here. Overview: In this week, you have studied additional Python language syntax including File I/O,...

Q:

Please follow the instructions carefully. Write all your code in a Code cell, and your explanations in a Markdown cell. Make sure that your code compiles correctly either by selecting a given cell...

Q:

*PYTHON COURSE* Can someone please help me answer this question for my class. My professor mentioned it using pandas and I honestly dont know what that is. There are a lot of pictures that I believe...

Q:

Mistakenly wrote the accounting in subject but the subject is Data science Tusk Eee Purchases In this Exercise you will be given some Fake Dus about compucho done through Amana! Feel free to defence...

Q:

The resulting bar chart shows that when HMK is the AR Clerk and FKL is the Cash Receipts Clerk, CT is the GL Accounting Clerk for $226,851 of current AR balances. However, there are $25,352 of...

Q:

The resulting bar chart shows that when HMK is the AR Clerk and FKL is the Cash Receipts Clerk, CT is the GL Accounting Clerk for $226,851 of current AR balances. However, there are $25,352 of...

Q:

The resulting bar chart shows that when HMK is the AR Clerk and FKL is the Cash Receipts Clerk, CT is the GL Accounting Clerk for $226,851 of current AR balances. However, there are $25,352 of...

Q:

*PYTHON COURSE* Can someone please help me answer this question for my class. My professor mentioned it using pandas and I honestly dont know what that is. There are a lot of pictures that I believe...

Q:

Please help, Python coding on Jupiter. Code the problems below using Jupyter Notebook. Provide a label for each problem using a comment. Your code solutions must be solved within your notebook in the...

Q:

About % of the area is between z = -3.5 and z = 3.5 (or within 3.5 standard deviations of the mean). Find the indicated area under the curve of the standard normal distribution, then convert it to a...

Q:

On Sunday 26th August 1928, May Donoghue sat in a caf with a friend. The friend ordered and paid for some ginger beer, which came in a bottle made from dark opaque glass. Donoghue drank some of the...

Q:

The principal asset - backed securities market for non - institutional investors is mortgaged - backed securities . Question 1 2 options: TrueFalse

Q:

Which of the following tools can be used to group or organize customer requirements to achieve improved definition and understanding of them? A Affinity Diagram B Multi-voting C Brainstorming D Fishbo

Previous Question Next Question