问:
Can't redirect to stripe checkout url
I'm not able to redirect to checkout url from my cart component, the button wasn't working so I wrapped my component with a Form tag, now it redirects but it says:
"Page not found (404)
Request Method:     POST
Request URL:    http://127.0.0.1:8000/cart/3

Using the URLconf defined in backend.urls, Django tried these URL patterns, in this order:

```<code>admin/
api/
^images/(?P<path>.*)$
</code>```

The current path, cart/3, didn’t match any of these."

And in the console I get "Warning: Invalid value for prop action on  tag. Either remove it from the element, or pass a string or number value to keep it in the DOM. For details"

urls:

```<code>urlpatterns = [
    path('', views.getRoutes, name="get-routes"),
    path('users/register/', views.registerUser, name="register"),
    path('users/login/', TokenObtainPairView.as_view(), name='token_obtain_pair'),
    path('products/', views.getProducts, name="get-products"),
    path('products/<str:pk>', views.getProduct, name="get-product"),
    path('user/profile/', views.getUserProfile, name="get-user-profile"),
    path('users/', views.getUsers, name="get-users"),
    path('search/', ProductFilter.as_view(), name="search-product"),
    path('create-checkout-session/', StripeCheckoutView.as_view()),
]
</code>```

views:

```<code>class StripeCheckoutView(APIView):
    def post(self, request):
        try:
            checkout_session = stripe.checkout.Session.create(
                line_items=[
                    {
                        'currency': 'usd',
                        'price': 'price_1MAtSLJU3RVFqD4TnNYWOxPO',
                        'quantity': 1,
                    },
                ],
                payment_method_types=['card'],
                mode='payment',
                success_url=settings.SITE_URL +
                '/?success=true&session_id={CHECKOUT_SESSION_ID}',
                cancel_url=settings.SITE_URL + '/' + '?canceled=true',
            )
            return redirect(checkout_session.url)
        except:
            return Response(
                {'error': 'Something went wrong when creating stripe checkout session'},
                status=status.HTTP_500_INTERNAL_SERVER_ERROR
            )
</code>```

cartScreen.js:

```<code>import React, { useEffect } from "react";
import { Link } from "react-router-dom";
import {
  Row,
  Col,
  Image,
  ListGroup,
  Button,
  Card,
  Form,
} from "react-bootstrap";
import { useDispatch, useSelector } from "react-redux";
import Message from "../Message";
import { addToCart, removeFromCart } from "../../actions/CartActions";
import { API_URL } from "../../config/config";

function CartSreen({ match, location, history }) {
  const productId = match.params.id;
  const quantity = location.search ? Number(location.search.split("=")[1]) : 1;
  const dispatch = useDispatch();

  const cart = useSelector((state) => state.cart);
  const { cartItems } = cart;

  useEffect(() => {
    if (productId) {
      dispatch(addToCart(productId, quantity));
    }
  }, [dispatch, productId, quantity]);

  const removeFromCartHandler = (id) => {
    dispatch(removeFromCart(id));
  };
  const checkoutHandler = () => {
    history.push(`${API_URL}/api/stripe/create-checkout-session`);
  };
  return (
    <Form action={checkoutHandler} method="POST">
      <Row>
        <Col md={8}>
          <h1>Shopping Cart</h1>
          {cartItems.length === 0 ? (
            <Message variant="info">
              Your cart is empty <Link to="/">Go Back</Link>
            </Message>
          ) : (
            <ListGroup variant="flush">
              {cartItems.map((item) => (
                <ListGroup.Item key={item.product}>
                  <Row>
                    <Col md={2}>
                      <Image src={item.image} alt={item.name} fluid rounded />
                    </Col>
                    <Col md={3}>
                      <Link to={`/product/${item.product}`}>{item.name}</Link>
                    </Col>
                    <Col md={2}>${item.price}</Col>
                    <Col md={3}>
                      <Form.Control
                        as="select"
                        value={item.quantity}
                        onChange={(e) =>
                          dispatch(
                            addToCart(item.product, Number(e.target.value))
                          )
                        }
                      >
                        {[...Array(item.countInStock).keys()].map((x) => (
                          <option key={x + 1} value={x + 1}>
                            {x + 1}
                          </option>
                        ))}
                      </Form.Control>
                    </Col>
                    <Col md={1}>
                      <Button
                        type="button"
                        variant="light"
                        onClick={() => removeFromCartHandler(item.product)}
                      >
                        <i className="fas fa-trash"></i>
                      </Button>
                    </Col>
                  </Row>
                </ListGroup.Item>
              ))}
            </ListGroup>
          )}
        </Col>
        <Col md={4}>
          <Card>
            <ListGroup variant="flush">
              <ListGroup.Item>
                <h2>
                  Subtotal (
                  {cartItems.reduce((acc, item) => acc + item.quantity, 0)})
                  items
                </h2>
                $
                {cartItems
                  .reduce((acc, item) => acc + item.quantity * item.price, 0)
                  .toFixed(2)}
              </ListGroup.Item>
            </ListGroup>
            <ListGroup.Item>
              <Button
                type="submit"
                className="btn-block"
                disabled={cartItems.length === 0}
              >
                Proceed to Checkout
              </Button>
            </ListGroup.Item>
          </Card>
        </Col>
      </Row>
    </Form>
  );
}

export default CartSreen;

</code>```

settings:

```<code>"""
Django settings for backend project.

Generated by 'django-admin startproject' using Django 4.1.3.

For more information on this file, see
https://docs.djangoproject.com/en/4.1/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/4.1/ref/settings/
"""

from datetime import timedelta
from pathlib import Path

# Build paths inside the project like this: BASE_DIR / 'subdir'.
BASE_DIR = Path(__file__).resolve().parent.parent

# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/4.1/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = ''

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = []

# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',

    'app.apps.AppConfig',
    'rest_framework',
    "corsheaders",
    'django_filters',
]
REST_FRAMEWORK = {
    'DEFAULT_AUTHENTICATION_CLASSES': (
        'rest_framework.authentication.SessionAuthentication',
        'rest_framework.authentication.BasicAuthentication',
        'rest_framework_simplejwt.authentication.JWTAuthentication',
        # 'rest_framework.permissions.AllowAny',
    )
}

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
    "corsheaders.middleware.CorsMiddleware",
    "django.middleware.common.CommonMiddleware",
]

...

SIMPLE_JWT = {
    'ACCESS_TOKEN_LIFETIME': timedelta(days=30),
    'REFRESH_TOKEN_LIFETIME': timedelta(days=1),
    'ROTATE_REFRESH_TOKENS': False,
    'BLACKLIST_AFTER_ROTATION': False,
    'UPDATE_LAST_LOGIN': False,

    'ALGORITHM': 'HS256',
    # 'SIGNING_KEY': SECRET_KEY,
    'VERIFYING_KEY': None,
    'AUDIENCE': None,
    'ISSUER': None,
    'JWK_URL': None,
    'LEEWAY': 0,

    'AUTH_HEADER_TYPES': ('Bearer',),
    'AUTH_HEADER_NAME': 'HTTP_AUTHORIZATION',
    'USER_ID_FIELD': 'id',
    'USER_ID_CLAIM': 'user_id',
    'USER_AUTHENTICATION_RULE': 'rest_framework_simplejwt.authentication.default_user_authentication_rule',

    'AUTH_TOKEN_CLASSES': ('rest_framework_simplejwt.tokens.AccessToken',),
    'TOKEN_TYPE_CLAIM': 'token_type',
    'TOKEN_USER_CLASS': 'rest_framework_simplejwt.models.TokenUser',

    'JTI_CLAIM': 'jti',

    'SLIDING_TOKEN_REFRESH_EXP_CLAIM': 'refresh_exp',
    'SLIDING_TOKEN_LIFETIME': timedelta(days=1),
    'SLIDING_TOKEN_REFRESH_LIFETIME': timedelta(days=5),
}

ROOT_URLCONF = 'backend.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'backend.wsgi.application'

# Database
# https://docs.djangoproject.com/en/4.1/ref/settings/#databases

# DATABASES = {
#     'default': {
#         'ENGINE': 'django.db.backends.sqlite3',
#         'NAME': BASE_DIR / 'db.sqlite3',
#     }
# }

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'my_db',
        'USER': 'postgres',
        'PASSWORD': 'django101',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

# Password validation
# https://docs.djangoproject.com/en/4.1/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]

# Internationalization
# https://docs.djangoproject.com/en/4.1/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_TZ = True

# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/4.1/howto/static-files/

STATIC_URL = 'static/'

MEDIA_URL = '/images/'

STATICFILES_DIRS = [
    BASE_DIR / 'static'
]

MEDIA_ROOT = 'static/images'

# Default primary key field type
# https://docs.djangoproject.com/en/4.1/ref/settings/#default-auto-field

DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField'

STRIPE_SECRET_KEY = ""

SITE_URL = "http://127.0.0.1:3000"

CORS_ALLOW_ALL_ORIGINS = True

</code>```

package.json:

```<code>"proxy": "http://127.0.0.1:8000/",
</code>```

Title: Can't redirect to stripe checkout url

Body:
I'm not able to redirect to checkout url from my cart component, the button wasn't working so I wrapped my component with a Form tag, now it redirects but it says:
"Page not found (404)
Request Method:     POST
Request URL:    http://127.0.0.1:8000/cart/3

Using the URLconf defined in backend.urls, Django tried these URL patterns, in this order:

admin/
api/
^images/(?P<path>.*)$

The current path, cart/3, didn’t match any of these."

And in the console I get "Warning: Invalid value for prop action on  tag. Either remove it from the element, or pass a string or number value to keep it in the DOM. For details"

urls:

urlpatterns = [
    path('', views.getRoutes, name="get-routes"),
    path('users/register/', views.registerUser, name="register"),
    path('users/login/', TokenObtainPairView.as_view(), name='token_obtain_pair'),
    path('products/', views.getProducts, name="get-products"),
    path('products/<str:pk>', views.getProduct, name="get-product"),
    path('user/profile/', views.getUserProfile, name="get-user-profile"),
    path('users/', views.getUsers, name="get-users"),
    path('search/', ProductFilter.as_view(), name="search-product"),
    path('create-checkout-session/', StripeCheckoutView.as_view()),
]

views:

class StripeCheckoutView(APIView):
    def post(self, request):
        try:
            checkout_session = stripe.checkout.Session.create(
                line_items=[
                    {
                        'currency': 'usd',
                        'price': 'price_1MAtSLJU3RVFqD4TnNYWOxPO',
                        'quantity': 1,
                    },
                ],
                payment_method_types=['card'],
                mode='payment',
                success_url=settings.SITE_URL +
                '/?success=true&session_id={CHECKOUT_SESSION_ID}',
                cancel_url=settings.SITE_URL + '/' + '?canceled=true',
            )
            return redirect(checkout_session.url)
        except:
            return Response(
                {'error': 'Something went wrong when creating stripe checkout session'},
                status=status.HTTP_500_INTERNAL_SERVER_ERROR
            )

cartScreen.js:

import React, { useEffect } from "react";
import { Link } from "react-router-dom";
import {
  Row,
  Col,
  Image,
  ListGroup,
  Button,
  Card,
  Form,
} from "react-bootstrap";
import { useDispatch, useSelector } from "react-redux";
import Message from "../Message";
import { addToCart, removeFromCart } from "../../actions/CartActions";
import { API_URL } from "../../config/config";

function CartSreen({ match, location, history }) {
  const productId = match.params.id;
  const quantity = location.search ? Number(location.search.split("=")[1]) : 1;
  const dispatch = useDispatch();

  const cart = useSelector((state) => state.cart);
  const { cartItems } = cart;

  useEffect(() => {
    if (productId) {
      dispatch(addToCart(productId, quantity));
    }
  }, [dispatch, productId, quantity]);

  const removeFromCartHandler = (id) => {
    dispatch(removeFromCart(id));
  };
  const checkoutHandler = () => {
    history.push(`${API_URL}/api/stripe/create-checkout-session`);
  };
  return (
    <Form action={checkoutHandler} method="POST">
      <Row>
        <Col md={8}>

Shopping Cart

          {cartItems.length === 0 ? (
            <Message variant="info">
              Your cart is empty <Link to="/">Go Back</Link>
            </Message>
          ) : (
            <ListGroup variant="flush">
              {cartItems.map((item) => (
                <ListGroup.Item key={item.product}>
                  <Row>
                    <Col md={2}>
                      <Image src={item.image} alt={item.name} fluid rounded />
                    </Col>
                    <Col md={3}>
                      <Link to={`/product/${item.product}`}>{item.name}</Link>
                    </Col>
                    <Col md={2}>${item.price}</Col>
                    <Col md={3}>
                      <Form.Control
                        as="select"
                        value={item.quantity}
                        onChange={(e) =>
                          dispatch(
                            addToCart(item.product, Number(e.target.value))
                          )
                        }
                      >
                        {[...Array(item.countInStock).keys()].map((x) => (
                          <option key={x + 1} value={x + 1}>
                            {x + 1}
                          </option>
                        ))}
                      </Form.Control>
                    </Col>
                    <Col md={1}>
                      <Button
                        type="button"
                        variant="light"
                        onClick={() => removeFromCartHandler(item.product)}
                      >

                      </Button>
                    </Col>
                  </Row>
                </ListGroup.Item>
              ))}
            </ListGroup>
          )}
        </Col>
        <Col md={4}>
          <Card>
            <ListGroup variant="flush">
              <ListGroup.Item>

                  Subtotal (
                  {cartItems.reduce((acc, item) => acc + item.quantity, 0)})
                  items

                $
                {cartItems
                  .reduce((acc, item) => acc + item.quantity * item.price, 0)
                  .toFixed(2)}
              </ListGroup.Item>
            </ListGroup>
            <ListGroup.Item>
              <Button
                type="submit"
                className="btn-block"
                disabled={cartItems.length === 0}
              >
                Proceed to Checkout
              </Button>
            </ListGroup.Item>
          </Card>
        </Col>
      </Row>
    </Form>
  );
}

export default CartSreen;

settings:

"""
Django settings for backend project.

Generated by 'django-admin startproject' using Django 4.1.3.

For more information on this file, see
https://docs.djangoproject.com/en/4.1/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/4.1/ref/settings/
"""

from datetime import timedelta
from pathlib import Path

# Build paths inside the project like this: BASE_DIR / 'subdir'.
BASE_DIR = Path(__file__).resolve().parent.parent

# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/4.1/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = ''

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = True

ALLOWED_HOSTS = []

# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',

    'app.apps.AppConfig',
    'rest_framework',
    "corsheaders",
    'django_filters',
]
REST_FRAMEWORK = {
    'DEFAULT_AUTHENTICATION_CLASSES': (
        'rest_framework.authentication.SessionAuthentication',
        'rest_framework.authentication.BasicAuthentication',
        'rest_framework_simplejwt.authentication.JWTAuthentication',
        # 'rest_framework.permissions.AllowAny',
    )
}

MIDDLEWARE = [
    'django.middleware.security.SecurityMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
    "corsheaders.middleware.CorsMiddleware",
    "django.middleware.common.CommonMiddleware",
]

...

SIMPLE_JWT = {
    'ACCESS_TOKEN_LIFETIME': timedelta(days=30),
    'REFRESH_TOKEN_LIFETIME': timedelta(days=1),
    'ROTATE_REFRESH_TOKENS': False,
    'BLACKLIST_AFTER_ROTATION': False,
    'UPDATE_LAST_LOGIN': False,

    'ALGORITHM': 'HS256',
    # 'SIGNING_KEY': SECRET_KEY,
    'VERIFYING_KEY': None,
    'AUDIENCE': None,
    'ISSUER': None,
    'JWK_URL': None,
    'LEEWAY': 0,

    'AUTH_HEADER_TYPES': ('Bearer',),
    'AUTH_HEADER_NAME': 'HTTP_AUTHORIZATION',
    'USER_ID_FIELD': 'id',
    'USER_ID_CLAIM': 'user_id',
    'USER_AUTHENTICATION_RULE': 'rest_framework_simplejwt.authentication.default_user_authentication_rule',

    'AUTH_TOKEN_CLASSES': ('rest_framework_simplejwt.tokens.AccessToken',),
    'TOKEN_TYPE_CLAIM': 'token_type',
    'TOKEN_USER_CLASS': 'rest_framework_simplejwt.models.TokenUser',

    'JTI_CLAIM': 'jti',

    'SLIDING_TOKEN_REFRESH_EXP_CLAIM': 'refresh_exp',
    'SLIDING_TOKEN_LIFETIME': timedelta(days=1),
    'SLIDING_TOKEN_REFRESH_LIFETIME': timedelta(days=5),
}

ROOT_URLCONF = 'backend.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'backend.wsgi.application'

# Database
# https://docs.djangoproject.com/en/4.1/ref/settings/#databases

# DATABASES = {
#     'default': {
#         'ENGINE': 'django.db.backends.sqlite3',
#         'NAME': BASE_DIR / 'db.sqlite3',
#     }
# }

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'my_db',
        'USER': 'postgres',
        'PASSWORD': 'django101',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

# Password validation
# https://docs.djangoproject.com/en/4.1/ref/settings/#auth-password-validators

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]

# Internationalization
# https://docs.djangoproject.com/en/4.1/topics/i18n/

LANGUAGE_CODE = 'en-us'

TIME_ZONE = 'UTC'

USE_I18N = True

USE_TZ = True

# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/4.1/howto/static-files/

STATIC_URL = 'static/'

MEDIA_URL = '/images/'

STATICFILES_DIRS = [
    BASE_DIR / 'static'
]

MEDIA_ROOT = 'static/images'

# Default primary key field type
# https://docs.djangoproject.com/en/4.1/ref/settings/#default-auto-field

DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField'

STRIPE_SECRET_KEY = ""

SITE_URL = "http://127.0.0.1:3000"

CORS_ALLOW_ALL_ORIGINS = True

package.json:

"proxy": "http://127.0.0.1:8000/",


来源: stackexchange

问:
How to get user license assignment from BigQuery export of Google Workspace user data
Google Workspace allows admins to automatically export Workspace user data to BigQuery.

Using the exported BigQuery dataset, I am able to generate a list of users in our Workspace environment with the following BigQuery SQL:

```<code>SELECT user_email, accounts.first_name, accounts.last_name
FROM `account.dataset.usage` LIMIT 1000
</code>```

I would like to be able to query whether each of these users has a Google Workspace license. However, none of the fields I can find display whether a user has a Google Workspace license. I've tried the usage.accounts fields, but none of the license-related fields seem to contain the status of a specific user.

Does anyone know if this is possible from within the Google Workspace Usage or Activity BigQuery

ContentLicense: CC BY-SA 4.0

Body:
Google Workspace allows admins to automatically export Workspace user data to BigQuery.

Using the exported BigQuery dataset, I am able to generate a list of users in our Workspace environment with the following BigQuery SQL:

SELECT user_email, accounts.first_name, accounts.last_name
FROM `account.dataset.usage` LIMIT 1000

I would like to be able to query whether each of these users has a Google Workspace license. However, none of the fields I can find display whether a user has a Google Workspace license. I've tried the usage.accounts fields, but none of the license-related fields seem to contain the status of a specific user.

Does anyone know if this is possible from within the Google Workspace Usage or Activity BigQuery


问:
Add a number to the date sql
I have a date sequence(2020-2025) that I take from a table.

I need to generate a day code for each year. The code consists of 7 entries: year + day:

```<code>2020001, 2020002, ..., 2020365, 2025001, ..., 2025365
</code>```

I tried to implement it by CASE. I'm using Redshift

```<code>SELECT
    CASE
        WHEN EXTRACT(YEAR FROM date_range) = 2020
            THEN EXTRACT(doy from date_range) + 2020000
        WHEN EXTRACT(YEAR FROM date_range) = 2021
            THEN EXTRACT(doy from date_range) + 2021000
        WHEN EXTRACT(YEAR FROM date_range) = 2022
            THEN EXTRACT(doy from date_range) + 2022000
        WHEN EXTRACT(YEAR FROM date_range) = 2023
            THEN EXTRACT(doy from date_range) + 2023000
        WHEN EXTRACT(YEAR FROM date_range) = 2024
            THEN EXTRACT(doy from date_range) + 2024000
        WHEN EXTRACT(YEAR FROM date_range) = 2025
            THEN EXTRACT(doy from date_range) + 2025000
    END AS day_code
FROM
    public.dates_seq;
</code>```

It works and I get what I need. But I think this solution is not optimal. Can anyone suggest or guide to a better approach?

Title: Add a number to the date sql

Body:
I have a date sequence(2020-2025) that I take from a table.

I need to generate a day code for each year. The code consists of 7 entries: year + day:

2020001, 2020002, ..., 2020365, 2025001, ..., 2025365

I tried to implement it by CASE. I'm using Redshift

SELECT
    CASE
        WHEN EXTRACT(YEAR FROM date_range) = 2020
            THEN EXTRACT(doy from date_range) + 2020000
        WHEN EXTRACT(YEAR FROM date_range) = 2021
            THEN EXTRACT(doy from date_range) + 2021000
        WHEN EXTRACT(YEAR FROM date_range) = 2022
            THEN EXTRACT(doy from date_range) + 2022000
        WHEN EXTRACT(YEAR FROM date_range) = 2023
            THEN EXTRACT(doy from date_range) + 2023000
        WHEN EXTRACT(YEAR FROM date_range) = 2024
            THEN EXTRACT(doy from date_range) + 2024000
        WHEN EXTRACT(YEAR FROM date_range) = 2025
            THEN EXTRACT(doy from date_range) + 2025000
    END AS day_code
FROM
    public.dates_seq;

It works and I get what I need. But I think this solution is not optimal. Can anyone suggest or guide to a better approach?


问:
pygame / ALSA error on colab when rendering
I am trying to run a script in colab that rendering a latent vector interpolation (reference github: https://github.com/marian42/shapegan

scroll to the last section --> Using the pretrained DeepSDF model and recreating the latent space traversal animation

After following all steps and running !python3 demo_latent_space.py , I am getting this error on colab. I appreciate all feedback:

```<code>Calculating embedding...
/usr/local/lib/python3.8/dist-packages/sklearn/manifold/_t_sne.py:780: FutureWarning: The default initialization in TSNE will change from 'random' to 'pca' in 1.2.
  warnings.warn(
/usr/local/lib/python3.8/dist-packages/sklearn/manifold/_t_sne.py:790: FutureWarning: The default learning rate in TSNE will change from 200.0 to 'auto' in 1.2.
  warnings.warn(
Calculating clusters...
Calculating trip...
100% 100/100 [00:20<00:00,  4.95it/s]
Rendering...
ALSA lib confmisc.c:767:(parse_card) cannot find card '0'
ALSA lib conf.c:4528:(_snd_config_evaluate) function snd_func_card_driver returned error: No such file or directory
ALSA lib confmisc.c:392:(snd_func_concat) error evaluating strings
ALSA lib conf.c:4528:(_snd_config_evaluate) function snd_func_concat returned error: No such file or directory
ALSA lib confmisc.c:1246:(snd_func_refer) error evaluating name
ALSA lib conf.c:4528:(_snd_config_evaluate) function snd_func_refer returned error: No such file or directory
ALSA lib conf.c:5007:(snd_config_expand) Evaluate error: No such file or directory
ALSA lib pcm.c:2495:(snd_pcm_open_noupdate) Unknown PCM default
Traceback (most recent call last):
  File "demo_latent_space.py", line 160, in <module>
    viewer = MeshRenderer(size=1080, start_thread=False)
  File "/content/drive/MyDrive/EnactiveDesign/final-project/grayboxAI/shapegan/rendering/__init__.py", line 91, in __init__
    self._initialize_opengl()
  File "/content/drive/MyDrive/EnactiveDesign/final-project/grayboxAI/shapegan/rendering/__init__.py", line 264, in _initialize_opengl
    pygame.display.gl_set_attribute(pygame.GL_MULTISAMPLEBUFFERS, 1)
pygame.error: video system not initialized
</code>```

This is the demo_latent_space.py -->

```<code>from util import device, ensure_directory
import scipy.interpolate
import numpy as np
from rendering import MeshRenderer
import torch
from tqdm import tqdm
import cv2
import random
import matplotlib.pyplot as plt
from sklearn.manifold import TSNE
from matplotlib.offsetbox import Bbox
from sklearn.cluster import KMeans

SAMPLE_COUNT = 30 # Number of distinct objects to generate and interpolate between
TRANSITION_FRAMES = 60

USE_VAE = False

SURFACE_LEVEL = 0.011

FRAMES = SAMPLE_COUNT * TRANSITION_FRAMES
progress = np.arange(FRAMES, dtype=float) / TRANSITION_FRAMES

if USE_VAE:
    from model.autoencoder import Autoencoder, LATENT_CODE_SIZE
    vae = Autoencoder()
    vae.load()
    vae.eval()
    print("Calculating latent codes...")

    from datasets import VoxelDataset
    from torch.utils.data import DataLoader

    dataset = VoxelDataset.glob('data/chairs/voxels_32/**.npy')
    dataloader = DataLoader(dataset, batch_size=1000, num_workers=8)

    latent_codes = torch.zeros((len(dataset), LATENT_CODE_SIZE))

    with torch.no_grad():
        position = 0
        for batch in tqdm(dataloader):
            latent_codes[position:position + batch.shape[0], :] = vae.encode(batch.to(device)).detach().cpu()
    latent_codes = latent_codes.numpy()
else:
    from model.sdf_net import SDFNet, LATENT_CODES_FILENAME
    latent_codes = torch.load(LATENT_CODES_FILENAME).detach().cpu().numpy()

    sdf_net = SDFNet()
    sdf_net.load()
    sdf_net.eval()

from shapenet_metadata import shapenet
labels = torch.load('data/labels.to')

print("Calculating embedding...")
tsne = TSNE(n_components=2)
latent_codes_embedded = tsne.fit_transform(latent_codes)
print("Calculating clusters...")
kmeans = KMeans(n_clusters=SAMPLE_COUNT)

indices = np.zeros(SAMPLE_COUNT, dtype=int)
kmeans_clusters = kmeans.fit_predict(latent_codes_embedded)
for i in range(SAMPLE_COUNT):
    center = kmeans.cluster_centers_[i, :]
    cluster_classes = labels[kmeans_clusters == i]
    cluster_class = np.bincount(cluster_classes).argmax()
    dist = np.linalg.norm(latent_codes_embedded - center[np.newaxis, :], axis=1)
    dist[labels != cluster_class] = float('inf')
    indices[i] = np.argmin(dist)

def try_find_shortest_roundtrip(indices):
    best_order = indices
    best_distance = None
    for _ in range(5000):
        candiate = best_order.copy()
        a = random.randint(0, SAMPLE_COUNT-1)
        b = random.randint(0, SAMPLE_COUNT-1)
        candiate[a] = best_order[b]
        candiate[b] = best_order[a]
        dist = np.sum(np.linalg.norm(latent_codes_embedded[candiate, :] - latent_codes_embedded[np.roll(candiate, 1), :], axis=1)).item()
        if best_distance is None or dist < best_distance:
            best_distance = dist
            best_order = candiate

    return best_order, best_distance

def find_shortest_roundtrip(indices):
    best_order, best_distance = try_find_shortest_roundtrip(indices)

    for _ in tqdm(range(100)):
        np.random.shuffle(indices)
        order, distance = try_find_shortest_roundtrip(indices)
        if distance < best_distance:
            best_order = order
    return best_order

print("Calculating trip...")
indices = find_shortest_roundtrip(indices)
indices = np.concatenate((indices, indices[0][np.newaxis]))

SIZE = latent_codes.shape[0]

stop_latent_codes = latent_codes[indices, :]

colors = np.zeros((labels.shape[0], 3))
for i in range(labels.shape[0]):
    colors[i, :] = shapenet.get_color(labels[i])

spline = scipy.interpolate.CubicSpline(np.arange(SAMPLE_COUNT + 1), stop_latent_codes, axis=0, bc_type='periodic')
frame_latent_codes = spline(progress)

color_spline = scipy.interpolate.CubicSpline(np.arange(SAMPLE_COUNT + 1), colors[indices, :], axis=0, bc_type='periodic')
frame_colors = color_spline(progress)
frame_colors = np.clip(frame_colors, 0, 1)

frame_colors = np.zeros((progress.shape[0], 3))
for i in range(SAMPLE_COUNT):
    frame_colors[i*TRANSITION_FRAMES:(i+1)*TRANSITION_FRAMES, :] = np.linspace(colors[indices[i]], colors[indices[i+1]], num=TRANSITION_FRAMES)

embedded_spline = scipy.interpolate.CubicSpline(np.arange(SAMPLE_COUNT + 1), latent_codes_embedded[indices, :], axis=0, bc_type='periodic')
frame_latent_codes_embedded = embedded_spline(progress)
frame_latent_codes_embedded[0, :] = frame_latent_codes_embedded[-1, :]

width, height = 40, 40

PLOT_FILE_NAME = 'tsne.png'
ensure_directory('images')

margin = 2
range_x = (latent_codes_embedded[:, 0].min() - margin, latent_codes_embedded[:, 0].max() + margin)
range_y = (latent_codes_embedded[:, 1].min() - margin, latent_codes_embedded[:, 1].max() + margin)

plt.ioff()

def create_plot(index, resolution=1080, filename=PLOT_FILE_NAME, dpi=100):
    frame_color = frame_colors[index, :]
    frame_color = (frame_color[0], frame_color[1], frame_color[2], 1.0)

    size_inches = resolution / dpi

    fig, ax = plt.subplots(1, figsize=(size_inches, size_inches), dpi=dpi)
    ax.set_position([0, 0, 1, 1])
    plt.axis('off')
    ax.set_xlim(range_x)
    ax.set_ylim(range_y)

    ax.plot(frame_latent_codes_embedded[:, 0], frame_latent_codes_embedded[:, 1], c=(0.2, 0.2, 0.2, 1.0), zorder=1, linewidth=2)
    ax.scatter(latent_codes_embedded[:, 0], latent_codes_embedded[:, 1], c=colors[:SIZE], s = 10, zorder=0)
    ax.scatter(frame_latent_codes_embedded[index, 0], frame_latent_codes_embedded[index, 1], facecolors=frame_color, s = 200, linewidths=2, edgecolors=(0.1, 0.1, 0.1, 1.0), zorder=2)
    ax.scatter(latent_codes_embedded[indices, 0], latent_codes_embedded[indices, 1], facecolors=colors[indices, :], s = 140, linewidths=1, edgecolors=(0.1, 0.1, 0.1, 1.0), zorder=3)

    fig.savefig(filename, bbox_inches=Bbox([[0, 0], [size_inches, size_inches]]), dpi=dpi)
    plt.close(fig)

frame_latent_codes = torch.tensor(frame_latent_codes, dtype=torch.float32, device=device)

print("Rendering...")
viewer = MeshRenderer(size=1080, start_thread=False)

def render_frame(frame_index):
    viewer.model_color = frame_colors[frame_index, :]
    with torch.no_grad():
        if USE_VAE:
            viewer.set_voxels(vae.decode(frame_latent_codes[frame_index, :]))
        else:
            viewer.set_mesh(sdf_net.get_mesh(frame_latent_codes[frame_index, :], voxel_resolution=128, sphere_only=True, level=SURFACE_LEVEL))
    image_mesh = viewer.get_image(flip_red_blue=True)

    create_plot(frame_index)
    image_tsne = plt.imread(PLOT_FILE_NAME)[:, :, [2, 1, 0]] * 255

    image = np.concatenate((image_mesh, image_tsne), axis=1)

    cv2.imwrite("images/frame-{:05d}.png".format(frame_index), image)

for frame_index in tqdm(range(SAMPLE_COUNT * TRANSITION_FRAMES)):
    render_frame(frame_index)
    frame_index += 1

print("\n\nUse this command to create a video:\n")
print('ffmpeg -framerate 30 -i images/frame-%05d.png -c:v libx264 -profile:v high -crf 19 -pix_fmt yuv420p video.mp4')
</code>```

Body:
I am trying to run a script in colab that rendering a latent vector interpolation (reference github: https://github.com/marian42/shapegan

scroll to the last section --> Using the pretrained DeepSDF model and recreating the latent space traversal animation

After following all steps and running !python3 demo_latent_space.py , I am getting this error on colab. I appreciate all feedback:

Calculating embedding...
/usr/local/lib/python3.8/dist-packages/sklearn/manifold/_t_sne.py:780: FutureWarning: The default initialization in TSNE will change from 'random' to 'pca' in 1.2.
  warnings.warn(
/usr/local/lib/python3.8/dist-packages/sklearn/manifold/_t_sne.py:790: FutureWarning: The default learning rate in TSNE will change from 200.0 to 'auto' in 1.2.
  warnings.warn(
Calculating clusters...
Calculating trip...
100% 100/100 [00:20<00:00,  4.95it/s]
Rendering...
ALSA lib confmisc.c:767:(parse_card) cannot find card '0'
ALSA lib conf.c:4528:(_snd_config_evaluate) function snd_func_card_driver returned error: No such file or directory
ALSA lib confmisc.c:392:(snd_func_concat) error evaluating strings
ALSA lib conf.c:4528:(_snd_config_evaluate) function snd_func_concat returned error: No such file or directory
ALSA lib confmisc.c:1246:(snd_func_refer) error evaluating name
ALSA lib conf.c:4528:(_snd_config_evaluate) function snd_func_refer returned error: No such file or directory
ALSA lib conf.c:5007:(snd_config_expand) Evaluate error: No such file or directory
ALSA lib pcm.c:2495:(snd_pcm_open_noupdate) Unknown PCM default
Traceback (most recent call last):
  File "demo_latent_space.py", line 160, in <module>
    viewer = MeshRenderer(size=1080, start_thread=False)
  File "/content/drive/MyDrive/EnactiveDesign/final-project/grayboxAI/shapegan/rendering/__init__.py", line 91, in __init__
    self._initialize_opengl()
  File "/content/drive/MyDrive/EnactiveDesign/final-project/grayboxAI/shapegan/rendering/__init__.py", line 264, in _initialize_opengl
    pygame.display.gl_set_attribute(pygame.GL_MULTISAMPLEBUFFERS, 1)
pygame.error: video system not initialized

This is the demo_latent_space.py -->

from util import device, ensure_directory
import scipy.interpolate
import numpy as np
from rendering import MeshRenderer
import torch
from tqdm import tqdm
import cv2
import random
import matplotlib.pyplot as plt
from sklearn.manifold import TSNE
from matplotlib.offsetbox import Bbox
from sklearn.cluster import KMeans

SAMPLE_COUNT = 30 # Number of distinct objects to generate and interpolate between
TRANSITION_FRAMES = 60

USE_VAE = False

SURFACE_LEVEL = 0.011

FRAMES = SAMPLE_COUNT * TRANSITION_FRAMES
progress = np.arange(FRAMES, dtype=float) / TRANSITION_FRAMES

if USE_VAE:
    from model.autoencoder import Autoencoder, LATENT_CODE_SIZE
    vae = Autoencoder()
    vae.load()
    vae.eval()
    print("Calculating latent codes...")

    from datasets import VoxelDataset
    from torch.utils.data import DataLoader

    dataset = VoxelDataset.glob('data/chairs/voxels_32/**.npy')
    dataloader = DataLoader(dataset, batch_size=1000, num_workers=8)

    latent_codes = torch.zeros((len(dataset), LATENT_CODE_SIZE))

    with torch.no_grad():
        position = 0
        for batch in tqdm(dataloader):
            latent_codes[position:position + batch.shape[0], :] = vae.encode(batch.to(device)).detach().cpu()
    latent_codes = latent_codes.numpy()
else:
    from model.sdf_net import SDFNet, LATENT_CODES_FILENAME
    latent_codes = torch.load(LATENT_CODES_FILENAME).detach().cpu().numpy()

    sdf_net = SDFNet()
    sdf_net.load()
    sdf_net.eval()

from shapenet_metadata import shapenet
labels = torch.load('data/labels.to')

print("Calculating embedding...")
tsne = TSNE(n_components=2)
latent_codes_embedded = tsne.fit_transform(latent_codes)
print("Calculating clusters...")
kmeans = KMeans(n_clusters=SAMPLE_COUNT)

indices = np.zeros(SAMPLE_COUNT, dtype=int)
kmeans_clusters = kmeans.fit_predict(latent_codes_embedded)
for i in range(SAMPLE_COUNT):
    center = kmeans.cluster_centers_[i, :]
    cluster_classes = labels[kmeans_clusters == i]
    cluster_class = np.bincount(cluster_classes).argmax()
    dist = np.linalg.norm(latent_codes_embedded - center[np.newaxis, :], axis=1)
    dist[labels != cluster_class] = float('inf')
    indices[i] = np.argmin(dist)

def try_find_shortest_roundtrip(indices):
    best_order = indices
    best_distance = None
    for _ in range(5000):
        candiate = best_order.copy()
        a = random.randint(0, SAMPLE_COUNT-1)
        b = random.randint(0, SAMPLE_COUNT-1)
        candiate[a] = best_order[b]
        candiate[b] = best_order[a]
        dist = np.sum(np.linalg.norm(latent_codes_embedded[candiate, :] - latent_codes_embedded[np.roll(candiate, 1), :], axis=1)).item()
        if best_distance is None or dist < best_distance:
            best_distance = dist
            best_order = candiate

    return best_order, best_distance

def find_shortest_roundtrip(indices):
    best_order, best_distance = try_find_shortest_roundtrip(indices)

    for _ in tqdm(range(100)):
        np.random.shuffle(indices)
        order, distance = try_find_shortest_roundtrip(indices)
        if distance < best_distance:
            best_order = order
    return best_order

print("Calculating trip...")
indices = find_shortest_roundtrip(indices)
indices = np.concatenate((indices, indices[0][np.newaxis]))

SIZE = latent_codes.shape[0]

stop_latent_codes = latent_codes[indices, :]

colors = np.zeros((labels.shape[0], 3))
for i in range(labels.shape[0]):
    colors[i, :] = shapenet.get_color(labels[i])

spline = scipy.interpolate.CubicSpline(np.arange(SAMPLE_COUNT + 1), stop_latent_codes, axis=0, bc_type='periodic')
frame_latent_codes = spline(progress)

color_spline = scipy.interpolate.CubicSpline(np.arange(SAMPLE_COUNT + 1), colors[indices, :], axis=0, bc_type='periodic')
frame_colors = color_spline(progress)
frame_colors = np.clip(frame_colors, 0, 1)

frame_colors = np.zeros((progress.shape[0], 3))
for i in range(SAMPLE_COUNT):
    frame_colors[i*TRANSITION_FRAMES:(i+1)*TRANSITION_FRAMES, :] = np.linspace(colors[indices[i]], colors[indices[i+1]], num=TRANSITION_FRAMES)

embedded_spline = scipy.interpolate.CubicSpline(np.arange(SAMPLE_COUNT + 1), latent_codes_embedded[indices, :], axis=0, bc_type='periodic')
frame_latent_codes_embedded = embedded_spline(progress)
frame_latent_codes_embedded[0, :] = frame_latent_codes_embedded[-1, :]

width, height = 40, 40

PLOT_FILE_NAME = 'tsne.png'
ensure_directory('images')

margin = 2
range_x = (latent_codes_embedded[:, 0].min() - margin, latent_codes_embedded[:, 0].max() + margin)
range_y = (latent_codes_embedded[:, 1].min() - margin, latent_codes_embedded[:, 1].max() + margin)

plt.ioff()

def create_plot(index, resolution=1080, filename=PLOT_FILE_NAME, dpi=100):
    frame_color = frame_colors[index, :]
    frame_color = (frame_color[0], frame_color[1], frame_color[2], 1.0)

    size_inches = resolution / dpi

    fig, ax = plt.subplots(1, figsize=(size_inches, size_inches), dpi=dpi)
    ax.set_position([0, 0, 1, 1])
    plt.axis('off')
    ax.set_xlim(range_x)
    ax.set_ylim(range_y)

    ax.plot(frame_latent_codes_embedded[:, 0], frame_latent_codes_embedded[:, 1], c=(0.2, 0.2, 0.2, 1.0), zorder=1, linewidth=2)
    ax.scatter(latent_codes_embedded[:, 0], latent_codes_embedded[:, 1], c=colors[:SIZE], s = 10, zorder=0)
    ax.scatter(frame_latent_codes_embedded[index, 0], frame_latent_codes_embedded[index, 1], facecolors=frame_color, s = 200, linewidths=2, edgecolors=(0.1, 0.1, 0.1, 1.0), zorder=2)
    ax.scatter(latent_codes_embedded[indices, 0], latent_codes_embedded[indices, 1], facecolors=colors[indices, :], s = 140, linewidths=1, edgecolors=(0.1, 0.1, 0.1, 1.0), zorder=3)

    fig.savefig(filename, bbox_inches=Bbox([[0, 0], [size_inches, size_inches]]), dpi=dpi)
    plt.close(fig)

frame_latent_codes = torch.tensor(frame_latent_codes, dtype=torch.float32, device=device)

print("Rendering...")
viewer = MeshRenderer(size=1080, start_thread=False)

def render_frame(frame_index):
    viewer.model_color = frame_colors[frame_index, :]
    with torch.no_grad():
        if USE_VAE:
            viewer.set_voxels(vae.decode(frame_latent_codes[frame_index, :]))
        else:
            viewer.set_mesh(sdf_net.get_mesh(frame_latent_codes[frame_index, :], voxel_resolution=128, sphere_only=True, level=SURFACE_LEVEL))
    image_mesh = viewer.get_image(flip_red_blue=True)

    create_plot(frame_index)
    image_tsne = plt.imread(PLOT_FILE_NAME)[:, :, [2, 1, 0]] * 255

    image = np.concatenate((image_mesh, image_tsne), axis=1)

    cv2.imwrite("images/frame-{:05d}.png".format(frame_index), image)

for frame_index in tqdm(range(SAMPLE_COUNT * TRANSITION_FRAMES)):
    render_frame(frame_index)
    frame_index += 1

print("\n\nUse this command to create a video:\n")
print('ffmpeg -framerate 30 -i images/frame-%05d.png -c:v libx264 -profile:v high -crf 19 -pix_fmt yuv420p video.mp4')


问:
Retrieving VAT information from vies in Excel
Currently our accounting department is verifying VAT information from our business partners manually by checking vies. (https://ec.europa.eu/taxation_customs/vies/#/vat-validation)
I would like to simplify this process a bit by including the check in our Excel form. I know there are a few add-ins available, but I would like to try this via a macro.

I found this Excel where there is already a macro which can show a result in a pass/fail way, but it does not show any additional info. The result should include

is the vat number valid (pass / fail)

name registered

address registered
If possible, it would need to check how many rows were filled and only check for those, instead of always checking the first 5000 rows (even when blank).

I only know the very basics of macros, so if anyone can help, it would be greatly appreciated!

```<code>Option Explicit

#If VBA7 And Win64 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Const mTIN_ACTIV    As String = "Yes, valid VAT number"
Const mTIN_VOID     As String = "No, VAT number is void"
Const mTIN_BAD_NUMBER As String = "Invalid VAT number"
Const mTIN_UNVERIFIED As String = "Unverified"

Sub Test()
  Dim i As Long
  Dim objXml As Object
  Dim Tm As Single

  Application.Cursor = xlWait
  Tm = Timer
  Set objXml = CreateObject("MSXML2.XMLHTTP")

  For i = 1 To 5000
    'in column A - Country ID
    'in column B - VAT number (without country id)
    'in column C - verification result
    Cells(i, 3).Value = Check_VIES(Cells(i, 1).Value, Cells(i, 2).Value, objXml)
    DoEvents
  Next i

  Application.Cursor = xlDefault
  MsgBox "Efficiency: " & Round((i - 1) * 60# / (Timer - Tm), 0) & " IDs/min"
End Sub

Function Check_VIES(sCountryCode As String, sTINNumber As String, objXml As Object) As Variant

    Dim Soap        As String
    Dim strResponse As String
    Dim strErr      As String
    Dim iErrCounter As Long

    Soap = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' " & _
           "xmlns:urn='urn:ec.europa.eu:taxud:vies:services:checkVat:types'>"
    Soap = Soap & "<soapenv:Header/>"
    Soap = Soap & "<soapenv:Body>"
    Soap = Soap & "<urn:checkVatApprox>"
    Soap = Soap & " <urn:countryCode>" & sCountryCode & "</urn:countryCode>"
    Soap = Soap & " <urn:vatNumber>" & sTINNumber & "</urn:vatNumber>"
    Soap = Soap & " </urn:checkVatApprox>"
    Soap = Soap & "</soapenv:Body>"
    Soap = Soap & "</soapenv:Envelope>"

    With objXml
OnceAgain:
        On Error GoTo ErrHndl
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
        .Send (Soap)
        While .ReadyState <> 4: DoEvents: Wend
        strResponse = .ResponseText

        If InStr(1, strResponse, "<faultstring>") > 0 Then
            strErr = UCase(Split(Split(strResponse, "<faultstring>")(1), "</faultstring>")(0))
            Select Case strErr
                Case "INVALID_INPUT"
                    Check_VIES = mTIN_BAD_NUMBER
                Case "GLOBAL_MAX_CONCURRENT_REQ", "MS_MAX_CONCURRENT_REQ", "TIMEOUT"
                    iErrCounter = iErrCounter + 1
                    If iErrCounter > 4 Then
                        Check_VIES = mTIN_UNVERIFIED
                    Else
                        If iErrCounter > 0 Then
                        End If
                        Sleep 200
                        GoTo OnceAgain
                    End If
                Case "SERVICE_UNAVAILABLE"
                    Check_VIES = mTIN_UNVERIFIED
                Case "MS_UNAVAILABLE"
                    Check_VIES = mTIN_UNVERIFIED
                Case "FAULT OCCURRED WHILE PROCESSING."
                    Check_VIES = mTIN_UNVERIFIED
            End Select
        Else
            On Error Resume Next
            If InStr(1, strResponse, "<ns2:traderName xmlns:xsi=""http://www.w3.org/2001/") > 0 Then
                Check_VIES = CBool(Split(Split(strResponse, "<ns2:valid>")(1), "</ns2:valid>")(0))
                Check_VIES = IIf(Check_VIES, mTIN_ACTIV, mTIN_VOID)
            ElseIf Split(Split(strResponse, "<ns2:traderName>")(1), "</ns2:traderName>")(0) = "---" Then
                Check_VIES = mTIN_BAD_NUMBER
            Else
                Check_VIES = CBool(Split(Split(strResponse, "<ns2:valid>")(1), "</ns2:valid>")(0))
                Check_VIES = IIf(Check_VIES, mTIN_ACTIV, mTIN_VOID)
            End If
        End If
    End With
    Exit Function
ErrHndl:
    Check_VIES = mTIN_UNVERIFIED
End Function
</code>```

Body:
Currently our accounting department is verifying VAT information from our business partners manually by checking vies. (https://ec.europa.eu/taxation_customs/vies/#/vat-validation)
I would like to simplify this process a bit by including the check in our Excel form. I know there are a few add-ins available, but I would like to try this via a macro.

I found this Excel where there is already a macro which can show a result in a pass/fail way, but it does not show any additional info. The result should include

is the vat number valid (pass / fail)

name registered

address registered
If possible, it would need to check how many rows were filled and only check for those, instead of always checking the first 5000 rows (even when blank).

I only know the very basics of macros, so if anyone can help, it would be greatly appreciated!

Option Explicit

#If VBA7 And Win64 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Const mTIN_ACTIV    As String = "Yes, valid VAT number"
Const mTIN_VOID     As String = "No, VAT number is void"
Const mTIN_BAD_NUMBER As String = "Invalid VAT number"
Const mTIN_UNVERIFIED As String = "Unverified"

Sub Test()
  Dim i As Long
  Dim objXml As Object
  Dim Tm As Single

  Application.Cursor = xlWait
  Tm = Timer
  Set objXml = CreateObject("MSXML2.XMLHTTP")

  For i = 1 To 5000
    'in column A - Country ID
    'in column B - VAT number (without country id)
    'in column C - verification result
    Cells(i, 3).Value = Check_VIES(Cells(i, 1).Value, Cells(i, 2).Value, objXml)
    DoEvents
  Next i

  Application.Cursor = xlDefault
  MsgBox "Efficiency: " & Round((i - 1) * 60# / (Timer - Tm), 0) & " IDs/min"
End Sub

Function Check_VIES(sCountryCode As String, sTINNumber As String, objXml As Object) As Variant

    Dim Soap        As String
    Dim strResponse As String
    Dim strErr      As String
    Dim iErrCounter As Long

    Soap = "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/' " & _
           "xmlns:urn='urn:ec.europa.eu:taxud:vies:services:checkVat:types'>"
    Soap = Soap & "<soapenv:Header/>"
    Soap = Soap & "<soapenv:Body>"
    Soap = Soap & "<urn:checkVatApprox>"
    Soap = Soap & " <urn:countryCode>" & sCountryCode & "</urn:countryCode>"
    Soap = Soap & " <urn:vatNumber>" & sTINNumber & "</urn:vatNumber>"
    Soap = Soap & " </urn:checkVatApprox>"
    Soap = Soap & "</soapenv:Body>"
    Soap = Soap & "</soapenv:Envelope>"

    With objXml
OnceAgain:
        On Error GoTo ErrHndl
        .Open "POST", "http://ec.europa.eu/taxation_customs/vies/services/checkVatService", False
        .Send (Soap)
        While .ReadyState <> 4: DoEvents: Wend
        strResponse = .ResponseText

        If InStr(1, strResponse, "<faultstring>") > 0 Then
            strErr = UCase(Split(Split(strResponse, "<faultstring>")(1), "</faultstring>")(0))
            Select Case strErr
                Case "INVALID_INPUT"
                    Check_VIES = mTIN_BAD_NUMBER
                Case "GLOBAL_MAX_CONCURRENT_REQ", "MS_MAX_CONCURRENT_REQ", "TIMEOUT"
                    iErrCounter = iErrCounter + 1
                    If iErrCounter > 4 Then
                        Check_VIES = mTIN_UNVERIFIED
                    Else
                        If iErrCounter > 0 Then
                        End If
                        Sleep 200
                        GoTo OnceAgain
                    End If
                Case "SERVICE_UNAVAILABLE"
                    Check_VIES = mTIN_UNVERIFIED
                Case "MS_UNAVAILABLE"
                    Check_VIES = mTIN_UNVERIFIED
                Case "FAULT OCCURRED WHILE PROCESSING."
                    Check_VIES = mTIN_UNVERIFIED
            End Select
        Else
            On Error Resume Next
            If InStr(1, strResponse, "<ns2:traderName xmlns:xsi=""http://www.w3.org/2001/") > 0 Then
                Check_VIES = CBool(Split(Split(strResponse, "<ns2:valid>")(1), "</ns2:valid>")(0))
                Check_VIES = IIf(Check_VIES, mTIN_ACTIV, mTIN_VOID)
            ElseIf Split(Split(strResponse, "<ns2:traderName>")(1), "</ns2:traderName>")(0) = "---" Then
                Check_VIES = mTIN_BAD_NUMBER
            Else
                Check_VIES = CBool(Split(Split(strResponse, "<ns2:valid>")(1), "</ns2:valid>")(0))
                Check_VIES = IIf(Check_VIES, mTIN_ACTIV, mTIN_VOID)
            End If
        End If
    End With
    Exit Function
ErrHndl:
    Check_VIES = mTIN_UNVERIFIED
End Function


问:
Firebase Spinner - Update Spinner Text on Selected Item
I've seen similar Q&A on this and played around with several variations.  I'm trying to update the Spinner text, but nothing happens.

In the onCreate method I have two spinners:
1.) DCAccount2 (XML ID)
2.) NumofPayments(XML ID)

DC Account2 successfully pulls the query data from Firebase database and populates the drop down with the correct values I want to see.  But when I select the value I want, nothing happens.  The Spinner text is not updated.?.?

```<code>Spinner DC_Account_Spr = findViewById(R.id.DCAccount2);
DC_Account_Spr.setOnItemSelectedListener(this);
ArrayAdapter<String> DC_Array_Adapter = new ArrayAdapter<>(getApplicationContext(), android.R.layout.simple_spinner_item, user_debit_acct_DL);
DC_Array_Adapter.setDropDownViewResource(android.R.layout.simple_spinner_item);
DC_Account_Spr.setAdapter(DC_Array_Adapter);

Spinner Num_Pymts_Spr = findViewById(R.id.NumofPayments);
Num_Pymts_Spr.setOnItemSelectedListener(this);
ArrayAdapter<CharSequence> Num_Pymts_adapter = ArrayAdapter.createFromResource(this, R.array.Num_Payments, com.google.android.material.R.layout.support_simple_spinner_dropdown_item);
Num_Pymts_adapter.setDropDownViewResource(com.google.android.material.R.layout.support_simple_spinner_dropdown_item);
Num_Pymts_Spr.setAdapter(Num_Pymts_adapter);
</code>```

Other relevant methods:

```<code>@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long l) {
    switch(parent.getId()) {
        case (R.id.DCAccount2):
            //This is where I think I am not capturing the selected item properly
            String Account_Selected_Str = parent.getItemAtPosition(position).toString();
            Toast.makeText(getApplicationContext(), Account_Selected_Str, Toast.LENGTH_LONG).show();
        case (R.id.NumofPayments):
            String Num_Payments_Str = parent.getItemAtPosition(position).toString();
            Toast.makeText(getApplicationContext(), Num_Payments_Str, Toast.LENGTH_LONG).show();
    }
}

@Override
public void onNothingSelected(AdapterView<?> adapterView) {
    return;
}

</code>```

I've tried using setSelection and several variables, but still no luck

Body:
I've seen similar Q&A on this and played around with several variations.  I'm trying to update the Spinner text, but nothing happens.

In the onCreate method I have two spinners:
1.) DCAccount2 (XML ID)
2.) NumofPayments(XML ID)

DC Account2 successfully pulls the query data from Firebase database and populates the drop down with the correct values I want to see.  But when I select the value I want, nothing happens.  The Spinner text is not updated.?.?

Spinner DC_Account_Spr = findViewById(R.id.DCAccount2);
DC_Account_Spr.setOnItemSelectedListener(this);
ArrayAdapter<String> DC_Array_Adapter = new ArrayAdapter<>(getApplicationContext(), android.R.layout.simple_spinner_item, user_debit_acct_DL);
DC_Array_Adapter.setDropDownViewResource(android.R.layout.simple_spinner_item);
DC_Account_Spr.setAdapter(DC_Array_Adapter);

Spinner Num_Pymts_Spr = findViewById(R.id.NumofPayments);
Num_Pymts_Spr.setOnItemSelectedListener(this);
ArrayAdapter<CharSequence> Num_Pymts_adapter = ArrayAdapter.createFromResource(this, R.array.Num_Payments, com.google.android.material.R.layout.support_simple_spinner_dropdown_item);
Num_Pymts_adapter.setDropDownViewResource(com.google.android.material.R.layout.support_simple_spinner_dropdown_item);
Num_Pymts_Spr.setAdapter(Num_Pymts_adapter);

Other relevant methods:

@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long l) {
    switch(parent.getId()) {
        case (R.id.DCAccount2):
            //This is where I think I am not capturing the selected item properly
            String Account_Selected_Str = parent.getItemAtPosition(position).toString();
            Toast.makeText(getApplicationContext(), Account_Selected_Str, Toast.LENGTH_LONG).show();
        case (R.id.NumofPayments):
            String Num_Payments_Str = parent.getItemAtPosition(position).toString();
            Toast.makeText(getApplicationContext(), Num_Payments_Str, Toast.LENGTH_LONG).show();
    }
}

@Override
public void onNothingSelected(AdapterView<?> adapterView) {
    return;
}

I've tried using setSelection and several variables, but still no luck


问:
there is something wrong with my iteration while loop
I want to write a code to store the fractional value of minerals in a special sample. the sum of fractions can't be greater or less than 1. so, I want to write a while loop to store a correct fractions value. I wrote this:

```<code>N=int(input('Enter the number of pure monomineralic lithologic constituents: '))
S=0
while S != 1:
    litho=[]
    frac=[]
    i=0
    while i<=N-1:
        litho.append(input('Please enter the name of pure monomineralic lithologic constituent: '))
        frac.append(float(input('Enter the fraction of ' + litho[i] + ': ')))
        i+=1
    if sum(frac) != 1:
        print(Back.RED + 'You have to enter the values of the fractions in such a way that their sum is equal to one.')
    S=sum(frac)
Xi=pd.DataFrame(
    {'Lithology': litho,
     'Fraction': frac,
    })
</code>```

but if you enter the wrong values, the red background is repeating for a new loop. Does anyone know what's happened?

Body:
I want to write a code to store the fractional value of minerals in a special sample. the sum of fractions can't be greater or less than 1. so, I want to write a while loop to store a correct fractions value. I wrote this:

N=int(input('Enter the number of pure monomineralic lithologic constituents: '))
S=0
while S != 1:
    litho=[]
    frac=[]
    i=0
    while i<=N-1:
        litho.append(input('Please enter the name of pure monomineralic lithologic constituent: '))
        frac.append(float(input('Enter the fraction of ' + litho[i] + ': ')))
        i+=1
    if sum(frac) != 1:
        print(Back.RED + 'You have to enter the values of the fractions in such a way that their sum is equal to one.')
    S=sum(frac)
Xi=pd.DataFrame(
    {'Lithology': litho,
     'Fraction': frac,
    })

but if you enter the wrong values, the red background is repeating for a new loop. Does anyone know what's happened?


问:
D3.js V4 Wide-Input Stacked Area Chart Not Writing Values
I am adapting a wide input stacked area chart as seen here: https://d3-graph-gallery.com/graph/stackedarea_wideinput.html using my own data.

The only changes that I really thought I needed to make were updating the x axis to Date format and inputting my data, but when I do so, the chart doesn't show up (the axes do, but not the actual data). I've included my  data (https://raw.githubusercontent.com/atowey-uchi/data_for_final/main/trips.csv) and code below. Any ideas as to why it isn't showing?

Also, I am using D3 Version 4.

JS:

```<code>// set the dimensions and margins of the graph
var margin_area = {top: 20, right: 30, bottom: 30, left: 55},
    width_area = 460 - margin_area.left - margin_area.right,
    height_area = 400 - margin_area.top - margin_area.bottom;

// append the svg object to the body of the page
var svg_area = d3.select("#my_dataviz")
  .append("svg")
    .attr("width", width_area + margin_area.left + margin_area.right)
    .attr("height", height_area + margin_area.top + margin_area.bottom)
  .append("g")
    .attr("transform",
          "translate(" + margin_area.left + "," + margin_area.top + ")");
var parseDate = d3.timeParse("%Y/%m/%d");
// Parse the Data
d3.csv("https://raw.githubusercontent.com/atowey-uchi/data_for_final/main/trips.csv", function(data) {

    data.forEach(function(d) {
        d.year = parseDate(d.year);
    });

  // List of groups = header of the csv files
  var keys = data.columns.slice(1)
  console.log(keys)

  var maxDateVal = d3.max(data, function(d){
    var vals = d3.keys(d).map(function(key){ return key !== "date" ? d[key] : 0 });
    return d3.sum(vals);
  });
  console.log(maxDateVal)
  console.log(height_area)

  // Add X axis
  var x = d3.scaleTime()
    .domain(d3.extent(data, function(d) { return d.year; }))
    .range([ 0, width_area ]);
  svg_area.append("g")
    .attr("transform", "translate(0," + height_area + ")")
    .call(d3.axisBottom(x).ticks());

  // Add Y axis
  var y = d3.scaleLinear()
    .domain([0, maxDateVal])
    .range([height_area, 0]);
  svg_area.append("g")
    .call(d3.axisLeft(y).tickFormat(d3.format(".2s")));

  // color palette
  var color = d3.scaleOrdinal(d3.schemeCategory10)

    //stack the data?
  var stackedData = d3.stack()
    .keys(keys)
    (data)
    console.log("This is the stack result: ", stackedData);

  // Show the areas
  svg_area
    .selectAll("mylayers")
    .data(stackedData)
    .enter()
    .append("path")
      .style("fill", function(d) { console.log(d.key) ; return color(d.key); })
      .attr("d", d3.area()
        .x(function(d, i) { return x(d.data.year); })
        .y0(function(d) { return y(d[0]); })
        .y1(function(d) { return y(d[1]); })
    )

})
</code>```

HTML:

```<code><!DOCTYPE html>
<meta charset="utf-8">

<!-- Load d3.js -->
<script src="https://d3js.org/d3.v4.js"></script>

<!-- Create a div where the graph will take place -->
<div id="my_dataviz"></div>
</code>```

I have tried playing around with the axes and the fill, but I cannot get it to show up.

Body:
I am adapting a wide input stacked area chart as seen here: https://d3-graph-gallery.com/graph/stackedarea_wideinput.html using my own data.

The only changes that I really thought I needed to make were updating the x axis to Date format and inputting my data, but when I do so, the chart doesn't show up (the axes do, but not the actual data). I've included my  data (https://raw.githubusercontent.com/atowey-uchi/data_for_final/main/trips.csv) and code below. Any ideas as to why it isn't showing?

Also, I am using D3 Version 4.

JS:

// set the dimensions and margins of the graph
var margin_area = {top: 20, right: 30, bottom: 30, left: 55},
    width_area = 460 - margin_area.left - margin_area.right,
    height_area = 400 - margin_area.top - margin_area.bottom;

// append the svg object to the body of the page
var svg_area = d3.select("#my_dataviz")
  .append("svg")
    .attr("width", width_area + margin_area.left + margin_area.right)
    .attr("height", height_area + margin_area.top + margin_area.bottom)
  .append("g")
    .attr("transform",
          "translate(" + margin_area.left + "," + margin_area.top + ")");
var parseDate = d3.timeParse("%Y/%m/%d");
// Parse the Data
d3.csv("https://raw.githubusercontent.com/atowey-uchi/data_for_final/main/trips.csv", function(data) {

    data.forEach(function(d) {
        d.year = parseDate(d.year);
    });

  // List of groups = header of the csv files
  var keys = data.columns.slice(1)
  console.log(keys)

  var maxDateVal = d3.max(data, function(d){
    var vals = d3.keys(d).map(function(key){ return key !== "date" ? d[key] : 0 });
    return d3.sum(vals);
  });
  console.log(maxDateVal)
  console.log(height_area)

  // Add X axis
  var x = d3.scaleTime()
    .domain(d3.extent(data, function(d) { return d.year; }))
    .range([ 0, width_area ]);
  svg_area.append("g")
    .attr("transform", "translate(0," + height_area + ")")
    .call(d3.axisBottom(x).ticks());

  // Add Y axis
  var y = d3.scaleLinear()
    .domain([0, maxDateVal])
    .range([height_area, 0]);
  svg_area.append("g")
    .call(d3.axisLeft(y).tickFormat(d3.format(".2s")));

  // color palette
  var color = d3.scaleOrdinal(d3.schemeCategory10)

    //stack the data?
  var stackedData = d3.stack()
    .keys(keys)
    (data)
    console.log("This is the stack result: ", stackedData);

  // Show the areas
  svg_area
    .selectAll("mylayers")
    .data(stackedData)
    .enter()
    .append("path")
      .style("fill", function(d) { console.log(d.key) ; return color(d.key); })
      .attr("d", d3.area()
        .x(function(d, i) { return x(d.data.year); })
        .y0(function(d) { return y(d[0]); })
        .y1(function(d) { return y(d[1]); })
    )

})

HTML:

<!DOCTYPE html>
<meta charset="utf-8">

I have tried playing around with the axes and the fill, but I cannot get it to show up.


问:
Facebook graph api - permissions to post to my feed? publish_actions - deprecated
Previously i've used publish_actions permission to post to my feed.
So i've used long-lived user token and make request to facebook:

```<code>$fb->post('/me/feed', array('name'=>'Test','message'=>'Test'), $userLongLivedToken);
</code>```

But after permission deprecation i found only pages_manage_posts, but that's for pages... How i can post to my feed?

Body:
Previously i've used publish_actions permission to post to my feed.
So i've used long-lived user token and make request to facebook:

$fb->post('/me/feed', array('name'=>'Test','message'=>'Test'), $userLongLivedToken);

But after permission deprecation i found only pages_manage_posts, but that's for pages... How i can post to my feed?


问:
prevent addeventlistener from running again for a certain time
how to prevent javascript event from running again for a certain time after it's run</p>


问:
How can I use R to access files that are within folders, that are in other folders, that are in my working directory?
I need to write a code that allows me to access these files and get information from them. Here's an example of the pathway: Main folder (working directory) -> subject folders -> measurement folders -> excel file. Is there a way I can write some sort of loop that allows me to access the excel files for all of the different measurements from all of the subjects?

already tried listing the files and importing them with a basic for-loop but no success. Not sure how to proceed or if I'm on the right track at all. Note: I don't want to combine them into one dataframe, rather have individual dataframes.


问:
Two functions having scheduler and running parallelly using ProcessPoolExecuter
I am trying to execute two functions parallelly using multiprocessing module in another function and each function have schedule module running once the functions are called. When I run the multiprocessing module function I am able to execute only one function, but I want both of the functions to execute parallelly.

I have tried the following:

```<code>def func1():
     schedule.every().minute.at(":10").until("23:00").do(job1)
     while True:
        schedule.run_pending()
        time.sleep(1)

def func2():
     schedule.every().minute.at(":10").until("23:00").do(job2)
     while True:
        schedule.run_pending()
        time.sleep(1)

def func3():
    with concurrent.futures.ProcessPoolExecutor() as executer:
        executer.submit(func1)
        executer.submit(func2)
</code>```

Only func1 is executing all the time. I have also tried with threading module, but results are the same. Please help how to solve this problem.

Body:
I am trying to execute two functions parallelly using multiprocessing module in another function and each function have schedule module running once the functions are called. When I run the multiprocessing module function I am able to execute only one function, but I want both of the functions to execute parallelly.

I have tried the following:

def func1():
     schedule.every().minute.at(":10").until("23:00").do(job1)
     while True:
        schedule.run_pending()
        time.sleep(1)

def func2():
     schedule.every().minute.at(":10").until("23:00").do(job2)
     while True:
        schedule.run_pending()
        time.sleep(1)

def func3():
    with concurrent.futures.ProcessPoolExecutor() as executer:
        executer.submit(func1)
        executer.submit(func2)

Only func1 is executing all the time. I have also tried with threading module, but results are the same. Please help how to solve this problem.


问:
Is there a way to link emphasis/highlights between charts in a group in ECharts?
I want to make two plots in ECharts, one scatter plot and one line plot. Each point in the scatter plot has a corresponding line in the line plot. When i mouseover a point or a line I want both the point in the scatter plot and the corresponding line in line plot to be emphasized.

Thus far i have tried grouping the charts but i only manage to link the tooltips and the legends.


问:
Mautic can't add custom field, fails after upgrade to 4.4.5
I upgraded Mautic from v2 to v3 to v4.4.5 and the upgrade went well and email automations are working. But after trying to add a custom field to the Contacts I keep getting this error:

```<code>An exception occurred while executing 'ALTER TABLE leads ADD created DATE DEFAULT NULL': SQLSTATE[42S22]: Column not found: 1054 Unknown column '`mautic`.`l`.`email`' in 'GENERATED ALWAYS'
</code>```

I tried running these commands in case a migrations wasn't migrated but its not working

docker-compose run app php bin/console mautic:update:apply
docker-compose run app php bin/console mautic:update:apply --finish
docker-compose run app php bin/console cache:clear
```

Any ideas?

Body:
I upgraded Mautic from v2 to v3 to v4.4.5 and the upgrade went well and email automations are working. But after trying to add a custom field to the Contacts I keep getting this error:

An exception occurred while executing 'ALTER TABLE leads ADD created DATE DEFAULT NULL': SQLSTATE[42S22]: Column not found: 1054 Unknown column '`mautic`.`l`.`email`' in 'GENERATED ALWAYS'

I tried running these commands in case a migrations wasn't migrated but its not working

docker-compose run app php bin/console mautic:update:apply
docker-compose run app php bin/console mautic:update:apply --finish
docker-compose run app php bin/console cache:clear

Any ideas?


问:
Is it possible to plot a variable in matplotlib?
For my upcoming exam project I want to analyze Donald Trump rally speeches and find out what the top 10 most common words are, and plot it by using matplotlib. I've currently run in to some trouble while trying to plot my variable "lst" where all the data is stored. Is there a way to make this work?

This is the current code I've been running - and trying to plt.plot(lst) doesn't yield great resulsts at all..

So far I've been typing it in manually, as seen at the bottom of the code.

Is there anything I'm missing?

Thank you so much in advance!

(I'm very new to python and coding in general!)

```<code>import pandas as pd
import operator
import matplotlib.pyplot as plt
import numpy as np

df_1 = pd.read_table("Trump_speeches/BattleCreekDec19_2019.txt")
df_2 = pd.read_table("Trump_speeches/BemidjiSep18_2020.txt")
df_3 = pd.read_table("Trump_speeches/CharlestonFeb28_2020.txt")
df_4 = pd.read_table("Trump_speeches/CharlotteMar2_2020.txt")
df_5 = pd.read_table("Trump_speeches/CincinnatiAug1_2019.txt")
df_6 = pd.read_table("Trump_speeches/ColoradorSpringsFeb20_2020.txt")
df_7 = pd.read_table("Trump_speeches/DallasOct17_2019.txt")
df_8 = pd.read_table("Trump_speeches/DesMoinesJan30_2020.txt")
df_9 = pd.read_table("Trump_speeches/FayettevilleSep9_2019.txt")
df_10 = pd.read_table("Trump_speeches/FayettevilleSep19_2020.txt")
df_11 = pd.read_table("Trump_speeches/FreelandSep10_2020.txt")
df_12 = pd.read_table("Trump_speeches/GreenvilleJul17_2019.txt")
df_13 = pd.read_table("Trump_speeches/HendersonSep13_2020.txt")
df_14= pd.read_table("Trump_speeches/HersheyDec10_2019.txt")

df = df_1 + df_2 + df_3 + df_4 + df_5 + df_6 + df_7 + df_8 + df_9 + df_10 + df_11 + df_12 + df_13 + df_14

def stopwords():
    return ["","a","about","above","after","again","against",.....]

def tokenizer(text, lower=True, stopword=True):
    if stopword:
        stopwordlist = stopwords()
        tokens = [token for token in text.lower().split(' ') if token not in stopwordlist]
    elif lower:
        tokens = text.lower().split()
    else:
        tokens = text.split()
    return tokens

def word_count(text, sort = True, stopword=True):
    counter = dict()
    for word in tokenizer(text, stopword=stopword):
        counter.setdefault(word, 0)
        counter[word] = counter[word] + 1
    if sort:
        counter = dict(sorted(counter.items(), key=operator.itemgetter(1), reverse=True))
    return counter

wc = word_count(' '.join(df))
wc_items = wc.items()
lst = list(wc_items)[:10]
print('[INFO] Top 10 most common words in Donald Trump rally speeches:')
print(lst)

x = ["said", "great", "people", "right?", "lot", "american", "good", "love", "years", "people"]
y = [757, 693, 571, 353, 311, 267, 257, 244, 208, 203]

</code>```

This is the current code I've been running - and trying to plt.plot(lst) doesn't yield great resulsts at all..

Body:
For my upcoming exam project I want to analyze Donald Trump rally speeches and find out what the top 10 most common words are, and plot it by using matplotlib. I've currently run in to some trouble while trying to plot my variable "lst" where all the data is stored. Is there a way to make this work?

This is the current code I've been running - and trying to plt.plot(lst) doesn't yield great resulsts at all..

So far I've been typing it in manually, as seen at the bottom of the code.

Is there anything I'm missing?

Thank you so much in advance!

(I'm very new to python and coding in general!)

import pandas as pd
import operator
import matplotlib.pyplot as plt
import numpy as np

df_1 = pd.read_table("Trump_speeches/BattleCreekDec19_2019.txt")
df_2 = pd.read_table("Trump_speeches/BemidjiSep18_2020.txt")
df_3 = pd.read_table("Trump_speeches/CharlestonFeb28_2020.txt")
df_4 = pd.read_table("Trump_speeches/CharlotteMar2_2020.txt")
df_5 = pd.read_table("Trump_speeches/CincinnatiAug1_2019.txt")
df_6 = pd.read_table("Trump_speeches/ColoradorSpringsFeb20_2020.txt")
df_7 = pd.read_table("Trump_speeches/DallasOct17_2019.txt")
df_8 = pd.read_table("Trump_speeches/DesMoinesJan30_2020.txt")
df_9 = pd.read_table("Trump_speeches/FayettevilleSep9_2019.txt")
df_10 = pd.read_table("Trump_speeches/FayettevilleSep19_2020.txt")
df_11 = pd.read_table("Trump_speeches/FreelandSep10_2020.txt")
df_12 = pd.read_table("Trump_speeches/GreenvilleJul17_2019.txt")
df_13 = pd.read_table("Trump_speeches/HendersonSep13_2020.txt")
df_14= pd.read_table("Trump_speeches/HersheyDec10_2019.txt")

df = df_1 + df_2 + df_3 + df_4 + df_5 + df_6 + df_7 + df_8 + df_9 + df_10 + df_11 + df_12 + df_13 + df_14

def stopwords():
    return ["","a","about","above","after","again","against",.....]

def tokenizer(text, lower=True, stopword=True):
    if stopword:
        stopwordlist = stopwords()
        tokens = [token for token in text.lower().split(' ') if token not in stopwordlist]
    elif lower:
        tokens = text.lower().split()
    else:
        tokens = text.split()
    return tokens

def word_count(text, sort = True, stopword=True):
    counter = dict()
    for word in tokenizer(text, stopword=stopword):
        counter.setdefault(word, 0)
        counter[word] = counter[word] + 1
    if sort:
        counter = dict(sorted(counter.items(), key=operator.itemgetter(1), reverse=True))
    return counter

wc = word_count(' '.join(df))
wc_items = wc.items()
lst = list(wc_items)[:10]
print('[INFO] Top 10 most common words in Donald Trump rally speeches:')
print(lst)

x = ["said", "great", "people", "right?", "lot", "american", "good", "love", "years", "people"]
y = [757, 693, 571, 353, 311, 267, 257, 244, 208, 203]

This is the current code I've been running - and trying to plt.plot(lst) doesn't yield great resulsts at all..


问:
Dynamic filtering of child entity collection
Simple project to solve business task:

```<code>@Entity
@Table(name = "parent")
public class Parent {
    @Id
    @SequenceGenerator(name = "SEQ_GEN", sequenceName = "SEQ_JUST_FOR_TEST", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_GEN")
    @Column(name = "id", nullable = false)
    private Long id;
    @Column(name = "name", nullable = false)
    private String name;
    @OneToMany(fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.PERSIST})
    private List<Child> childs;
</code>```

repository method:

```<code>@Query("select parent from Parent parent"
        + " join parent.childs childs "
        + " where parent.id = :id "
        + " and :search is null or childs.name = :search ")
Page<Parent>getPageById(Pageable pageable, @Param("id")Long id, @Param("search") String search);
</code>```

and controller:

```<code>@GetMapping("/parents/{id}/{search}")
public ResponseEntity<Page<Parent> >parents(Pageable pageable, @PathVariable(value = "id") final String id,
        @PathVariable(value = "search") final String search) {
    Page<Parent>parentPage = parentRepository.getPageById(pageable, Long.getLong(id), search);
   return new ResponseEntity<>(parentPage, HttpStatus.OK);
</code>```

Is it possible to filter child collection and retrieve only childs with specific attribute, here it's a name for brievety?

Body:
Simple project to solve business task:

@Entity
@Table(name = "parent")
public class Parent {
    @Id
    @SequenceGenerator(name = "SEQ_GEN", sequenceName = "SEQ_JUST_FOR_TEST", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_GEN")
    @Column(name = "id", nullable = false)
    private Long id;
    @Column(name = "name", nullable = false)
    private String name;
    @OneToMany(fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.PERSIST})
    private List<Child> childs;

repository method:

@Query("select parent from Parent parent"
        + " join parent.childs childs "
        + " where parent.id = :id "
        + " and :search is null or childs.name = :search ")
Page<Parent>getPageById(Pageable pageable, @Param("id")Long id, @Param("search") String search);

and controller:

@GetMapping("/parents/{id}/{search}")
public ResponseEntity<Page<Parent> >parents(Pageable pageable, @PathVariable(value = "id") final String id,
        @PathVariable(value = "search") final String search) {
    Page<Parent>parentPage = parentRepository.getPageById(pageable, Long.getLong(id), search);
   return new ResponseEntity<>(parentPage, HttpStatus.OK);

Is it possible to filter child collection and retrieve only childs with specific attribute, here it's a name for brievety?


问:
Possible bug in Spring Cloud Stream's handling of LateBinding Kafka Stream outputs (causing message loss)
I may have encountered a bug in Spring Cloud Stream’s handling of late-binding Kafka Stream outputs that manifests when the broker is transiently unavailable during application startup.

From my empirical testing, any output binding that is a not-yet-bound LateBinding at the time the KafkaStreams object is instantiated is not included in the Topology object passed to Kafka, even if the broker is subsequently brought up.

After the broker is brought up, the application successfully starts and consumes messages, including publishing them to internal topics, but it never publishes them to the sink node; this effectively results in message loss.

If this does not sound like WAI, I can create a minimal repro. (My current repro is reliable, but includes likely-extraneous streams, joins, etc.)

As a non-minimal example, I have a function consuming two KTables and outputting a KStream, whose topology (logged by StreamsBuilderFactoryBean) includes:

```<code>   Processor: KTABLE-TOSTREAM-0000000020 (stores: [])
      --> KSTREAM-SINK-0000000021
      <-- KTABLE-FK-JOIN-OUTPUT-0000000018
    Sink: KSTREAM-SINK-0000000021 (topic: my_output_topic)
      <-- KTABLE-TOSTREAM-0000000020
</code>```

Launching the same application while the broker is down yields an identical topology, except that the output sink is omitted:

```<code>   Processor: KTABLE-TOSTREAM-0000000020 (stores: [])
      --> none
      <-- KTABLE-FK-JOIN-OUTPUT-0000000018
</code>```

To engage in a bit of uninformed speculation:

A comment in StreamsBuilderFactoryManager seems to imply that it is necessary to defer constructing KafkaStreams until all listener bindings have completed:

```<code> * Iterate through all {@link StreamsBuilderFactoryBean} in the application context and
 * start them. As each one completes starting, register the associated KafkaStreams object
 * into {@link InteractiveQueryService}.
 *
 * This {@link SmartLifecycle} class ensures that the bean created from it is started very
 * late through the bootstrap process by setting the phase value closer to
 * Integer.MAX_VALUE. This is to guarantee that the {@link StreamsBuilderFactoryBean} on a
 * {@link org.springframework.cloud.stream.annotation.StreamListener} method with multiple
 * bindings is only started after all the binding phases have completed successfully.
</code>```

It seems (cursorily) plausible that the use of a LateBinding inadvertently violates this ordering constraint.

Speculating even further: After reconnection, any time the input topic is repartitioned, all of the internal topics are re-provisioned, but not the output topic; maybe this accounts for why the output topic does not recover on reconnection, but the input topics do?

I will happily provide additional information or a minimal repro, but I first wanted to see if this is entirely WAI / operator error, or if this sounds like a potential bug. Thanks!

Body:
I may have encountered a bug in Spring Cloud Stream’s handling of late-binding Kafka Stream outputs that manifests when the broker is transiently unavailable during application startup.

From my empirical testing, any output binding that is a not-yet-bound LateBinding at the time the KafkaStreams object is instantiated is not included in the Topology object passed to Kafka, even if the broker is subsequently brought up.

After the broker is brought up, the application successfully starts and consumes messages, including publishing them to internal topics, but it never publishes them to the sink node; this effectively results in message loss.

If this does not sound like WAI, I can create a minimal repro. (My current repro is reliable, but includes likely-extraneous streams, joins, etc.)

As a non-minimal example, I have a function consuming two KTables and outputting a KStream, whose topology (logged by StreamsBuilderFactoryBean) includes:

   Processor: KTABLE-TOSTREAM-0000000020 (stores: [])
      --> KSTREAM-SINK-0000000021
      <-- KTABLE-FK-JOIN-OUTPUT-0000000018
    Sink: KSTREAM-SINK-0000000021 (topic: my_output_topic)
      <-- KTABLE-TOSTREAM-0000000020

Launching the same application while the broker is down yields an identical topology, except that the output sink is omitted:

   Processor: KTABLE-TOSTREAM-0000000020 (stores: [])
      --> none
      <-- KTABLE-FK-JOIN-OUTPUT-0000000018

To engage in a bit of uninformed speculation:

A comment in StreamsBuilderFactoryManager seems to imply that it is necessary to defer constructing KafkaStreams until all listener bindings have completed:

 * Iterate through all {@link StreamsBuilderFactoryBean} in the application context and
 * start them. As each one completes starting, register the associated KafkaStreams object
 * into {@link InteractiveQueryService}.
 *
 * This {@link SmartLifecycle} class ensures that the bean created from it is started very
 * late through the bootstrap process by setting the phase value closer to
 * Integer.MAX_VALUE. This is to guarantee that the {@link StreamsBuilderFactoryBean} on a
 * {@link org.springframework.cloud.stream.annotation.StreamListener} method with multiple
 * bindings is only started after all the binding phases have completed successfully.

It seems (cursorily) plausible that the use of a LateBinding inadvertently violates this ordering constraint.

Speculating even further: After reconnection, any time the input topic is repartitioned, all of the internal topics are re-provisioned, but not the output topic; maybe this accounts for why the output topic does not recover on reconnection, but the input topics do?

I will happily provide additional information or a minimal repro, but I first wanted to see if this is entirely WAI / operator error, or if this sounds like a potential bug. Thanks!


问:
How to Auto generate QR code in google sheets
I have generated my qr url in my google sheet , but when a new response is received it does not generate the qr url how should I auto generate it

I tried using =arrayformula(https://chart.googleapis.com/chart.chs=200x200&cht=qr&chl=“&ENCODEURL($B2&”,”&$L2))

But I am not getting my result when a new response is received


问:
SASS and Specificity
I was refactoring code this afternoon and I realized something in peculiar that I was hoping someone could answer. I noticed the following...

```<code>&__main ul a {
....
};
</code>```

equates too specificity (0, 0, 2) which makes sense. However....

```<code> &__main &__example a {
...
}
</code>```

equates to specificity (0, 0, 1) which doesn't make sense bc doesn't the above snippet equate to...

```<code>.div__main .div__example a {
...
}
</code>```

which should be specificty (0, 1, 1)? Becasuse I tried the snippet below and I get the specificity which I'm looking for...

```<code>&__main .div__example a {
....
}
</code>```

Can someone please explain to me please because I'm confused as to why the compiler wouldn't take specificity into account when using an & to refer to a parent class? Because when you break it down to regular CSS it should, no? (edited)

Title: SASS and Specificity

Body:
I was refactoring code this afternoon and I realized something in peculiar that I was hoping someone could answer. I noticed the following...

&__main ul a {
....
};

equates too specificity (0, 0, 2) which makes sense. However....

 &__main &__example a {
...
}

equates to specificity (0, 0, 1) which doesn't make sense bc doesn't the above snippet equate to...

.div__main .div__example a {
...
}

which should be specificty (0, 1, 1)? Becasuse I tried the snippet below and I get the specificity which I'm looking for...

&__main .div__example a {
....
}

Can someone please explain to me please because I'm confused as to why the compiler wouldn't take specificity into account when using an & to refer to a parent class? Because when you break it down to regular CSS it should, no? (edited)


问:
how to make a colormap of an existing dataframe by adding additional dataset?
I have a dataframe look like a matrix, in which values varies from 0-15, I want to represent another dataset as another dimension of the existing data. This new data set has to give the colormap to the current heatmap plot. How to do it? I am giving a sample code below,

```<code>import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
import seaborn as sns

#first data
df = pd.read_csv('/Linux/Case_study/IncandMass.csv', delimiter=",", header=None,skiprows=1)
df.info()
</code>```
```<code>Out[64]:
         0         1         2     ...      1436      1437      1438
174  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
173  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
172  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
171  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
170  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
..        ...       ...       ...  ...       ...       ...       ...
9    0.037891  0.051185  0.026712  ...  0.042928  0.055719  0.051363
8    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
7    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
6    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
5    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000

[170 rows x 1439 columns]
</code>```
```<code>#Plotting
fig, ax = plt.subplots(figsize=(12, 7))
# Displaying dataframe as an heatmap
sns.heatmap(df, cmap="jet", linewidths=0.0)
</code>```

Here I imported my second dataset with the same shape

```<code>num = pd.read_csv('/Linux/Case_study/Incandnum.csv', delimiter=",", header=None,skiprows=1)
df.info()
</code>```

I want to use this second dataset (num) for the colormap to use in the heatmap of the first dataset(df). How to do that?

Updated
The annot giving such outputs

```<code>sns.heatmap(da, cmap="jet", linewidths=0.0,annot = num)
</code>```

I tried with other colormaps as well, it giving distorted images

Body:
I have a dataframe look like a matrix, in which values varies from 0-15, I want to represent another dataset as another dimension of the existing data. This new data set has to give the colormap to the current heatmap plot. How to do it? I am giving a sample code below,

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
import seaborn as sns

#first data
df = pd.read_csv('/Linux/Case_study/IncandMass.csv', delimiter=",", header=None,skiprows=1)
df.info()

Out[64]:
         0         1         2     ...      1436      1437      1438
174  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
173  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
172  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
171  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
170  0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
..        ...       ...       ...  ...       ...       ...       ...
9    0.037891  0.051185  0.026712  ...  0.042928  0.055719  0.051363
8    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
7    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
6    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000
5    0.000000  0.000000  0.000000  ...  0.000000  0.000000  0.000000

[170 rows x 1439 columns]

#Plotting
fig, ax = plt.subplots(figsize=(12, 7))
# Displaying dataframe as an heatmap
sns.heatmap(df, cmap="jet", linewidths=0.0)

Here I imported my second dataset with the same shape

num = pd.read_csv('/Linux/Case_study/Incandnum.csv', delimiter=",", header=None,skiprows=1)
df.info()

I want to use this second dataset (num) for the colormap to use in the heatmap of the first dataset(df). How to do that?

Updated
The annot giving such outputs

sns.heatmap(da, cmap="jet", linewidths=0.0,annot = num)

I tried with other colormaps as well, it giving distorted images


问:
Prisma - How to fix Migration file after invalid column data value, and migration failed
Updated a column and put in an incorrect value for the varchar length.  This caused the migration file to fail and now it wants to reset the Database and wipe everything out.

```<code>Error: P3006

Migration `20221205190350_migration_name_here` failed to apply cleanly to the shadow database.
Error code: P3018
Error:
  A migration failed to apply. New migrations cannot be applied before the error is
  recovered from. Read more about how to resolve migration issues in a production
  database: https://pris.ly/d/migrate-resolve

Migration name: 20221205190350_migration_name_here

Database error code: 1074

Database error:
  Column length too big for column 'cardJson' (max = 16383); use BLOB or TEXT instead

Please check the query number 1 from the migration file.
</code>```

That is not an option.

I tried following the documentation to rollback and fix the migration, but it still wants to reset everything.

```<code>prisma migrate resolve --rolled-back "20221205190350_migration_name_here"
</code>```

Then I updated it to a correct value in the SQL migration file and the schema file and tried to run it but it says it has already been applied (so not sure what the purpose of rolling it back then is?)

I need a way to adjust this migration file so the DB doesn't get reset.  The table itself is empty so that could be dropped if that would help but I do not think any migrations are going to run unless I can find a way to fix this file.

Body:
Updated a column and put in an incorrect value for the varchar length.  This caused the migration file to fail and now it wants to reset the Database and wipe everything out.

Error: P3006

Migration `20221205190350_migration_name_here` failed to apply cleanly to the shadow database.
Error code: P3018
Error:
  A migration failed to apply. New migrations cannot be applied before the error is
  recovered from. Read more about how to resolve migration issues in a production
  database: https://pris.ly/d/migrate-resolve

Migration name: 20221205190350_migration_name_here

Database error code: 1074

Database error:
  Column length too big for column 'cardJson' (max = 16383); use BLOB or TEXT instead

Please check the query number 1 from the migration file.

That is not an option.

I tried following the documentation to rollback and fix the migration, but it still wants to reset everything.

prisma migrate resolve --rolled-back "20221205190350_migration_name_here"

Then I updated it to a correct value in the SQL migration file and the schema file and tried to run it but it says it has already been applied (so not sure what the purpose of rolling it back then is?)

I need a way to adjust this migration file so the DB doesn't get reset.  The table itself is empty so that could be dropped if that would help but I do not think any migrations are going to run unless I can find a way to fix this file.


问:
Save password in browser for other domain
I'm looking for best solution for embedded device provisioning:

Connect to device working in AP mode

Configure SSID, Password for WiFi and access password.

Restart device to STA mode

Redirect browser to new address (with mDNS)

Now user should login to device with access password from point 2.

Is it possible to store "access password" in browser, so user will not need to reenter it ?


问:
connect to existing kernal to google collab
As an engineer and biostatistician, I code primarily in python and R. Within the last six months, I have decided, for small projects, it is fastest to share code with others through Google Drive and Google Colaboratory. The convenient access to shared cloud storage cannot be beat for small, informal projects. Avoiding issues of shared server access, working directory updates, and Git confusion saves time, particularly when collaborators have varying levels of comfort/fluency with data science and programming language.

enter image description here


问:
Spring JPA Query containing subquery that maps to Object
I'm not sure what I am doing it right or necessary, but it made sense in my head.
Scenario:
I have a large, relation Oracle DB, where there are many columns(Up to 130). I want to use JPA to generate the query and map the data back to simple DTOs that only have a few columns that I need. Currently we are using EntityManager to do this, but it feels a little clunky as the Inner joins are causing duplicate rows that need to be sorted.

Sample - None working code

```<code> @Query("select DISTINCT new com.jackb.PersonDto(p.name ,p.age, +
            "( select new com.jackb.CommentsDto(c.comment, c.date) from Comment c WHERE p.key = c.key )) " +
            "from People p ")

</code>```

I keep getting the error "getDataType() is not supported by ConstructorNode!"
I expected that I am doing something that is not support or allowed in Hibernate.
When I want an individual column it works well, but I cant convert to my DTO within another DTO.

Body:
I'm not sure what I am doing it right or necessary, but it made sense in my head.
Scenario:
I have a large, relation Oracle DB, where there are many columns(Up to 130). I want to use JPA to generate the query and map the data back to simple DTOs that only have a few columns that I need. Currently we are using EntityManager to do this, but it feels a little clunky as the Inner joins are causing duplicate rows that need to be sorted.

Sample - None working code

 @Query("select DISTINCT new com.jackb.PersonDto(p.name ,p.age, +
            "( select new com.jackb.CommentsDto(c.comment, c.date) from Comment c WHERE p.key = c.key )) " +
            "from People p ")

I keep getting the error "getDataType() is not supported by ConstructorNode!"
I expected that I am doing something that is not support or allowed in Hibernate.
When I want an individual column it works well, but I cant convert to my DTO within another DTO.


问:
Running a cell twice in jupyter and keep the count the same
I am changing the question completely because it was not expressed properly before:

Lets say I have a huge df with millions of rows and I want to replace a special characters ä and ö in Column. I do this:

```<code>print(df['Column'].str.contains('ä|ö', regex=True).sum())
umlaut_dict = {'ä':'ae','ö':'oe'}
df.Column.replace(umlaut_dict, regex=True, inplace=True)
print(df['Column'].str.contains('ä|ö', regex=True).sum())
</code>```

The first time it works like a charm, shows the number of characters to replace and then confirms if all have been replaced. When the same cell is ran a 2nd time it shows 2 0s.

My question is:

Are these things I need to worry or the whole notebook is supposed to run once?

Is there a way to keep the original number without creating a copy of the df before transformation?

Body:
I am changing the question completely because it was not expressed properly before:

Lets say I have a huge df with millions of rows and I want to replace a special characters ä and ö in Column. I do this:

print(df['Column'].str.contains('ä|ö', regex=True).sum())
umlaut_dict = {'ä':'ae','ö':'oe'}
df.Column.replace(umlaut_dict, regex=True, inplace=True)
print(df['Column'].str.contains('ä|ö', regex=True).sum())

The first time it works like a charm, shows the number of characters to replace and then confirms if all have been replaced. When the same cell is ran a 2nd time it shows 2 0s.

My question is:

Are these things I need to worry or the whole notebook is supposed to run once?

Is there a way to keep the original number without creating a copy of the df before transformation?