[Solved] Forward fi...
 
Notifications
Clear all

[Solved] Forward fill missing values with limit in clickhosue

0 Posts
1 Users
0 Likes
1 Views
0
Topic starter
AJM Asks: Forward fill missing values with limit in clickhosue
Given some sensor data in a clickhouse table with values every 30 seconds but also some data gaps, I would like to resample the data to 30 seconds and then forward fill the gaps but only if the gaps is shorter than a certain limit.

(similar to method_2 in Python here: Using fillna() selectively in pandas).

Raw data:

Code:
+------------+---------+-------------------+-----+
|equipment_id|sensor_id|datetime           |value|
+------------+---------+-------------------+-----+
|123         |456      |2021-02-05 09:18:46|63   |
|123         |456      |2021-02-05 09:19:07|64   |
|123         |456      |2021-02-05 09:18:12|63   |
|123         |456      |2021-02-05 09:22:11|63   |
|123         |456      |2021-02-05 09:22:38|65   |
|123         |456      |2021-02-05 09:24:22|65   |
|123         |456      |2021-02-05 09:24:53|65   |
|123         |456      |2021-02-05 09:24:58|66   |
|123         |456      |2021-02-05 09:26:38|62   |
|123         |456      |2021-02-05 09:27:13|82   |
|123         |456      |2021-02-05 09:27:52|76   |
+------------+---------+-------------------+-----+

Following code already resamples to 30 seconds, but fills all gaps with 0:

Code:
SELECT
  equipment_id,
  sensor_id,
  datetime,
  value
FROM table_name
WHERE
  datetime > '2021-01-05 09:18:00' AND
  datetime < '2021-01-05 09:28:00' AND
  sensor_id IN (456) AND
  equipment_id = 123
GROUP BY equipment_id, sensor_id, toStartOfInterval(datetime, INTERVAL 30 second) AS datetime
ORDER BY datetime WITH FILL STEP 30

Output:

Code:
+------------+------------+-------------------+-----+
|equipment_id|sensor_id   |datetime           |value|
+------------+------------+-------------------+-----+
|123         |456         |2021-01-05 09:18:30|63   |
|123         |456         |2021-01-05 09:19:00|64   |
|0           |0           |2021-01-05 09:19:30|0    |
|0           |0           |2021-01-05 09:20:00|0    |
|0           |0           |2021-01-05 09:20:30|0    |
|0           |0           |2021-01-05 09:21:00|0    |
|0           |0           |2021-01-05 09:21:30|0    |
|123         |456         |2021-01-05 09:22:00|63   |
|123         |456         |2021-01-05 09:22:30|65   |
|0           |0           |2021-01-05 09:23:00|0    |
|0           |0           |2021-01-05 09:23:30|0    |
|123         |456         |2021-01-05 09:24:00|65   |
|123         |456         |2021-01-05 09:24:30|65.5 |
|0           |0           |2021-01-05 09:25:00|0    |
|0           |0           |2021-01-05 09:25:30|0    |
|0           |0           |2021-01-05 09:26:00|0    |
|123         |456         |2021-01-05 09:26:30|62   |
|123         |456         |2021-01-05 09:27:00|82   |
|123         |456         |2021-01-05 09:27:30|76   |
+------------+------------+-------------------+-----+

My desired output with forward filled gaps up to a length of 3 rows (or 90 seconds) would be:

Code:
+------------+------------+-------------------+--------------+--------------+
|equipment_id|sensor_id   |datetime           |original_value|desired_value |
+------------+------------+-------------------+--------------+--------------+
|123         |456         |2021-01-05 09:18:30|63            |63            |
|123         |456         |2021-01-05 09:19:00|64            |64            |
|123         |456         |2021-01-05 09:19:30|0             |null          |
|123         |456         |2021-01-05 09:20:00|0             |null          |
|123         |456         |2021-01-05 09:20:30|0             |null          |
|123         |456         |2021-01-05 09:21:00|0             |null          |
|123         |456         |2021-01-05 09:21:30|0             |null          |
|123         |456         |2021-01-05 09:22:00|63            |63            |
|123         |456         |2021-01-05 09:22:30|65            |65            |
|123         |456         |2021-01-05 09:23:00|0             |65            |
|123         |456         |2021-01-05 09:23:30|0             |65            |
|123         |456         |2021-01-05 09:24:00|65            |65            |
|123         |456         |2021-01-05 09:24:30|65.5          |65.5          |
|123         |456         |2021-01-05 09:25:00|0             |65.5          |
|123         |456         |2021-01-05 09:25:30|0             |65.5          |
|123         |456         |2021-01-05 09:26:00|0             |65.5          |
|123         |456         |2021-01-05 09:26:30|62            |62            |
|123         |456         |2021-01-05 09:27:00|82            |82            |
|123         |456         |2021-01-05 09:27:30|76            |76            |
+------------+------------+-------------------+--------------+--------------+

Can this be done in clickhouse?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

Unreplied Posts

[Solved] Cannot find HTC One in ADB, SD card not mounted, incorrect kernel for installed ROM, no backup

user3600453 Asks: Cannot find HTC One in ADB, SD card not mounted, incorrect kernel for installed ROM, no backup
Pretty much what the title says. I have messed up big time. Because I am switching carriers, I have to switch back to the stock ROM. It is stuck at the loading screen, most likely because of a kernel issue. My stupid self didn’t remember to make a backup.

Because of this, all I have access to is the boot loader. I cannot side load anything because ADB does not recognize my device.

I am in the biggest pickle I’ve ever been in with electronics. Anyone who can fix this is going to win the entire internet.

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.
 
 
 

[Solved] Can two differant wireless providers(t-mobile&then Verizon wireless)legally prevent me from useing ussd & mmi codes on devices i own but run

Adam Lay Asks: Can two differant wireless providers(t-mobile&then Verizon wireless)legally prevent me from useing ussd & mmi codes on devices i own but run there ser
Unable to run ussd or mmi codes,message says wireless provider prohibits

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] transfer/send error cases

mrNick Asks: transfer/send error cases
I have a (noob) question on the usage of transfer and send. I have seen that transfer is equivalent to

Code:
 require(x.send(k));

When I use send() I know that I have to check whether there was an error during the execution and then handle it like:

Code:
   if(!x.send(y)) {
    //...
   }

1) In case there is an error, do I have to write revert() inside the block? To be more specific, since send allows the usage of 2300 gas, in case changes are made inside the receiver contract fallback function, are they reverted automatically?

2) If I use transfer() I don’t have to check for errors right? If something goes wrong all code executed is reverted and the remaining gas refunded(I think).

Cheers!

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Parity – Permission Denied, Cannot write version file

Barney Chambers Asks: Parity – Permission Denied, Cannot write version file
I am trying to run a Parity daemon. Here is my shell command:

sudo parity --db-path . --port=20000 --ws-port=20001 --jsonrpc-port=20002 --jsonrpc-apis web3,parity,parity_accounts,personal,eth daemon parity.pid

with or without sudo permissions, I am receiving the error message:

Error upgrading parity data: CannotWriteVersionFile(Os { code: 13, kind: PermissionDenied, message: "Permission denied" })

I have also given 777 permissions to my db-path folder with the (spicy and dangerous) command

sudo chmod 777 -R .

I am still receiving this error. What am I missing?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] How to insert a payable function in an NFT contract

kelan237 Asks: How to insert a payable function in an NFT contract
I am creating a contract and I’ll like to get paid for my NFT’s in an ERC20 token other than Ether. Can someone recommend an implementation? I tried this implementation:

Code:
function getNft(string memory _name) external payable {
        require(msg.value == 1 ether, "not enough fund");
        _mint(_name);
        payable(owner()).transfer(1 ether);
}

PS: In this example I tried with ether but when deploying on Remix it never works; there is never enough fund. Thanks

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] data is not updating on page after routing to same url with different params

Rohit Kumar Asks: data is not updating on page after routing to same url with different params
Basically I have a notification component that show notification on click of icon in header. When we click the icon on header, it opens a popup and shows a list of notification. On clicking any one of the notification, it routes the user to the component of that particular notification, like on clicking notification , it takes user to that route : profile/guest/{guestid}/alerts/{alertid}. But when we click on another notification from the above mentioned route , it chnages the route params but does not reload the data of the new alert and shows the data of the old route only.

Note: data that are shown are fetched from route resolve. And as the component is already loaded , it doesn’t call ngOnInit or constructor function when we click on another notification. But when we refresh the page , it show correct data according to the updated route.

I tried implementing different router config solution that reloads the resolves data. such as runGuardsAndResolvers along with onSameUrlNavigation.

I also tried calling the functions that sets data, in other angular component life cycle hooks such as ngAfterViewInit or ngAfterViewChecked.

I tried a few more solutions, but all of them were of no use.

Code:
 Notification.component.ts :-(inside header component in shared module)
/**
   * * Callback when user clicks on visit button in notification modal
   * @param notification : notification 
   */
  navigateToGuestOrCustomer(notification: notification) {
    let routePath =
      notification.model == ALERT_MODEL.GUEST ? "guest" : "customers";
    let routeParamId = notification.detail_id;
    let alertId = notification.id;
    this.router.navigate([`/profile/${routePath}/${routeParamId}/alerts/${alertId}`]);
  }


edit-alert.component.ts(inside profile module in guest component>alert component)

  ngOnInit() {
    this.fetchRouteData();
    this.setGuestId();
    this.setGuestName();
  }

  /**
   * * get guest name from route resolve
   */
  setGuestName(): void {
    let guestData = this.route.parent.snapshot.data["editGuestInfo"];
    this.guestName = guestData["name"];
  }

  formData: any;
  alertListForGuest = [];
  /**Fetch Data from Resolve */
  fetchRouteData() {
    this.formData = this.route.snapshot.data["alertDetailForGuest"];
    this.alertListForGuest = this.route.snapshot.data["alertListForGuest"];
    this.alertListForGuest.push(this.formData.alert);
  }

expected result : after clicking on other notification , route parameters should change and show the desired page with the updated data.

actual result : only the route is getting changed , not the data.

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] ModuleNotFoundError: No module named ‘appium’ (yes I have appium, adk, and appium-python-client)

samman Asks: ModuleNotFoundError: No module named ‘appium’ (yes I have appium, adk, and appium-python-client)
I’m trying to get started up using appium, and have gotten this error when I try to import appium in python:

Code:
C:UsersSams PC>python
Python 3.8.4 (tags/v3.8.4:dfa645a, Jul 13 2020, 16:46:45) [MSC v.1924 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import appium
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'appium'

To cover what I have:

Appium:

Code:
C:UsersSams PC>appium
[Appium] Welcome to Appium v1.17.1
[Appium] Appium REST http interface listener started on 0.0.0.0:4723

Appium-python-client

Code:
C:UsersSams PC>pip install appium-python-client
Requirement already satisfied: appium-python-client in c:userssams pcappdatalocalprogramspythonpython37libsite-packages (1.0.2)
Requirement already satisfied: selenium<4,>=3.14.1 in c:userssams pcappdatalocalprogramspythonpython37libsite-packages (from appium-python-client) (3.141.0)
Requirement already satisfied: urllib3 in c:userssams pcappdatalocalprogramspythonpython37libsite-packages (from selenium<4,>=3.14.1->appium-python-client) (1.25.7)

C:UsersSams PC>

I have installed and setup Android Studio:

Code:
C:ProgramDataMicrosoftWindowsStart MenuProgramsAndroid Studio>dir
 Volume in drive C has no label.
 Volume Serial Number is C08F-2C8E

 Directory of C:ProgramDataMicrosoftWindowsStart MenuProgramsAndroid Studio

07/15/2020  06:26 PM    <DIR>          .
07/15/2020  06:26 PM    <DIR>          ..
07/15/2020  06:26 PM             1,181 Android Studio.lnk
               1 File(s)          1,181 bytes
               2 Dir(s)  30,455,201,792 bytes free

I’ve looked at all the other similar questions, but I don’t quite understand what I’m missing since I’ve downloaded everything required from the looks of it.

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] process.browser does not exist

softshipper Asks: process.browser does not exist
I am trying to implement Swipeable Drawer from Material-UI. For performance reason, I would like to implement

Code:
const iOS = process.browser && /iPad|iPhone|iPod/.test(navigator.userAgent);
<SwipeableDrawer disableBackdropTransition={!iOS} disableDiscovery={iOS} />

as recommended on the page.

I have created a component as follows:

Code:
const iOS = process.browser && /iPad|iPhone|iPod/.test(navigator.userAgent)
const useStyles = makeStyles((theme: Theme) =>
    createStyles({
        icon: {
            marginLeft: "auto",
            color: "#505050"
        },
    }),
)


export default function MobileNav() {

    const classes = useStyles()

    return (
        <React.Fragment>
            <IconButton
                className={classes.icon}
                color="inherit"
                aria-label="Open navigation"
                edge="end"
            >
                <MenuRoundedIcon fontSize="large"/>
            </IconButton>
        </React.Fragment>
    )
}

and the compiler complains:

Code:
Property 'browser' does not exist on type 'Process'.  TS2339

     5 | 
     6 | 
  >  7 | const iOS = process.browser && /iPad|iPhone|iPod/.test(navigator.userAgent)
       |                     ^
     8 | const useStyles = makeStyles((theme: Theme) =>
     9 |     createStyles({
    10 |         icon: {

What is missing?

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Does Selenium work with Java 16? The jars are all there and yet the imports don’t work

Tom Sherrin Asks: Does Selenium work with Java 16? The jars are all there and yet the imports don’t work
Learning Selenium from a relatively-beginner Java background. Downloaded the JARs and imported them to Eclipse as per all the guides, but they don’t have any bearing on the functionality of the code. Searched the web high and low, but can’t find anything unless I’m going crazy. Screenshot attached to show the referenced libraries as well as the code: code and jars

Code itself is:

Code:
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
public class Firefox {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        
        System.setProperty("webdriver.gecko.driver","C:\geckodriver.exe");
        WebDriver driver = new FirefoxDriver();
        //comment the above 2 lines and uncomment below 2 lines to use Chrome
        //System.setProperty("webdriver.chrome.driver","G:\chromedriver.exe");
        //WebDriver driver = new ChromeDriver();
        
        String baseUrl = "http://demo.guru99.com/test/newtours/";
        String expectedTitle = "Welcome: Mercury Tours";
        String actualTitle = "";

        // launch Fire fox and direct it to the Base URL
        driver.get(baseUrl);

        // get the actual value of the title
        actualTitle = driver.getTitle();

        /*
         * compare the actual title of the page with the expected one and print
         * the result as "Passed" or "Failed"
         */
        if (actualTitle.contentEquals(expectedTitle)){
            System.out.println("Test Passed!");
        } else {
            System.out.println("Test Failed");
        }
       
        //close Fire fox
        driver.close();

    }

}

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

[Solved] Substitute removed rows by duplicating existing rows in order to hit a defined value target number

Phoenix Islands Asks: Substitute removed rows by duplicating existing rows in order to hit a defined value target number
Given the following dataframe

Code:
date         city        customer_name     customer_id     purchase_amount
2021-01-01   A               X                 A1                10
2021-01-01   A               Y                 A2                20
2021-01-01   A               Z                 A3                15
2021-01-01   A               Z                 A4               100
...
2021-03-03   B               AA                A4                40

I have 2 scenarios:

Scenario A: No change to dataset

Scenario B:

I would like to remove all customer_name which start by Z for date, city combination and i want to replace these rows which have been removed by the same number of rows for that date city combination, however the rows which are chosen should have an average value (for purchase amount column) as close as possible to a defined threshold.

Below is the code I wrote:

Code:
dates = df["date"].unique()
cities = df["city"].unique()
final_df = []
for date in dates:
    for city in cities:
        dff = df.copy()
        dff = dff[dff["date"]=="date"]
        dff = dff[dff["city"]=="city"]
        if scenario_name == "B":
            removal_count = dff[dff["customer_name"].str.startswith("Z")]
            removal_count = removal_count["customer_id"].count()
            dff = dff[~dff["customer_name"].str.startswith("Z")]
            threshold = dff["purchase_amount"].mean()
            df_add = dff.sample(removal_count, random_state = 8, replace = True)
            dff = pd.concat([dff,df_add])
            final_df.append(dff)

I believe the above code a part of it but does not have the threshold logic for df_add and I would like some guidance as how we can add it.

Using the dataset at the start, let’s say we remove third and fourth row and my threshold is 15. Then I would want my dataframe to have 2 random rows duplicated (here row 1 and 2) so it is possible to reach 15 (purchase_amount).

If the duplicated row cannot reach 15 then it should reach the closest number to 15 (can be more or less). 1 and 2 are relevant here become they are in the “correct” sequential number in order to reach 15, but the rows which are being picked by the code should be any rows from the filtered dataset as long as it is possible to get close to 15 using the average of X rows.

Apologize if it is not clear…

Ten-tools.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your response here to help other visitors like you. Thank you, Ten-tools.

 

 


 

Share: