DEV Community: Rolando Gómez Tabar

How to use React useSyncExternalStore hook and RxJS together

Rolando Gómez Tabar — Sun, 19 Jun 2022 23:39:52 +0000

After discovering the useSubscription a while ago and using along with RxJS, it required at least two statements to use it with Rx.BehaviorSubject, create the subscription object and then call useSubscription(subscription):

// create the subscription object
const subscription = useMemo(
  () => ({
    getCurrentValue: () => behaviorSubject.getValue(),
    subscribe: callback => {
      const subscription = behaviorSubject.subscribe(callback);
      return () => subscription.unsubscribe();
    }
  }),

  // Re-subscribe any time the behaviorSubject changes
  [behaviorSubject]
);
// call the hook
const value = useSubscription(subscription);

See the details here https://www.npmjs.com/package/use-subscription.

With this new useSyncExternalStore hook, you have it simpler and integrated in React since version 18.1.0:

const counter$ = new BehaviorSubject(0);

function useCounter() {
  const count = useSyncExternalStore(
    useCallback(
      (callback) => {
        const subscription = counter$.subscribe(callback);
        return () => subscription.unsubscribe();
      },
      [counter$]
    ),
    () => counter$.getValue()
  );
  return count;
}

As we saw, you still have to wrap the subscribe handler function in a useCallback hook, to prevent the re-subscription and unsubscribe on every change or render.

See a the full example here: https://codesandbox.io/s/react18-2-rxjs-s3b781.

Flatten Pandas dataframe MultiIndex

Rolando Gómez Tabar — Thu, 21 Oct 2021 13:31:17 +0000

Context

A few days ago, I had to extract a data from Oracle Database using SQL, and then PIVOT a long set of values in a column, to multiple columns. The sintax to do this, requires to write every single value you want to be as a column, not one but twice to avoid the default quotes.

See reference
https://www.oracle.com/technical-resources/articles/database/sql-11g-pivot.html

There are a few options like pivoting using PIVOT XML, or even more recent to build a JSON column using JSON_ARRAYAGG and JSON_OBJECT to get dynamic columns or any value as an attribute, but still not so straightforward.

First try:

Lets try with a python notebook (I use VSCode notebook).
First import pandas:



import pandas as pd

We will use a random free dataset:



# source file from https://www.kaggle.com/gregorut/videogamesales
df = pd.read_csv('./vgsales.csv')
df

Using the function pivot_table we will transponse the values of the column Genre to be a column for every value in the dataset.



pivot_df = df.pivot_table(index=['Year', 'Publisher'], columns=['Genre'], values=['Global_Sales'], aggfunc=np.sum, fill_value=0)
pivot_df

Now let's reset_index() to "try" flattening the indexes.



mi_pivot_df = pivot_df.reset_index()
mi_pivot_df

Ook, not sure, but, lets try to export to an Excel, with index=False to avoid showing the index column to the left.



pivot_df.to_excel('./global_sales_by_publishers_genres.xlsx', index=False)

So, it's not implemented. Although, it does work when you remove the index=False part. However it will show the index column to the left and the two column indexes, which I don't want to be there.

Second try

I searched the solution to flat the most, but no answer was full, complete enough, and free of errors. I collected all, and by try and error, got here, a working solution. I also works after using pivot function:



flat_index_pivot_df = pivot_df['Global_Sales'].reset_index()
flat_index_pivot_df

Now you can get a clean Excel Sheet, free of MultiIndex.



flat_index_pivot_df.to_excel('./global_sales_by_publishers_genres.xlsx', index=False)

Do you know another solution?

Using Recursive Common Table Expressions to fill missing continuous data | Oracle SQL

Rolando Gómez Tabar — Tue, 07 Sep 2021 20:01:00 +0000

Context

I have a table which contains the value of a currency in others currencies by date, but only working dates.
I need the data for every date since the begining of a source table, being that the missing data for a day should be the previous available one.

Sample data:

As you can see, the dates are not continuous.

First try:

WITH srct(fecha) AS (
    SELECT MIN(fecha) fecha FROM BCRD_TC_OTRAS_MON
     UNION ALL
    SELECT fecha + 1 fecha FROM srct WHERE fecha + 1 <= (SELECT MAX(fecha) fecha FROM BCRD_TC_OTRAS_MON)
)
SELECT a.fecha,
    NVL(dolar_can, LAG(dolar_can, 1) OVER(ORDER BY a.fecha)) dolar_can,
    NVL(franco_sui, LAG(franco_sui, 1) OVER(ORDER BY a.fecha)) franco_sui,
    NVL(euro, LAG(euro, 1) OVER(ORDER BY a.fecha)) euro,
    NVL(dolar_usd, LAG(dolar_usd, 1) OVER(ORDER BY a.fecha)) dolar_usd
FROM
    srct a
        LEFT JOIN
    BCRD_TC_OTRAS_MON b ON b.fecha = a.fecha
ORDER BY 1

Results:

This approach takes the data from previous date, but it does not work for more than 1 day of continuous missing data.
It could work if you use COALESCE and multiple LAG functions, but at the end, the query would not look elegant.

Second try:

WITH srct(fecha, dolar_can, franco_sui, euro, dolar_usd) AS (
    SELECT fecha, dolar_can, franco_sui, euro, dolar_usd FROM BCRD_TC_OTRAS_MON WHERE fecha = (SELECT MIN(fecha) fecha FROM BCRD_TC_OTRAS_MON)
     UNION ALL
    SELECT a.fecha + 1 fecha,
        NVL(b.dolar_can, a.dolar_can), NVL(b.franco_sui, a.franco_sui),
        NVL(b.euro, a.euro), NVL(b.dolar_usd, a.dolar_usd)
    FROM srct a
            LEFT JOIN
        BCRD_TC_OTRAS_MON b ON a.fecha + 1 = b.fecha
    WHERE a.fecha + 1 <= (SELECT MAX(fecha) fecha FROM BCRD_TC_OTRAS_MON)
)
SELECT fecha, dolar_can, franco_sui, euro, dolar_usd
  FROM srct
ORDER BY 1

Results:

Using this approach you will get every date from srct and the previous available row data from the BCRD_TC_OTRAS_MON table for every missing date.

I hope it could help.

Thanks for reading.