logo
Tags down

shadow

Find first N non null values in each row


By : Gifts Less Ordinary
Date : September 14 2020, 09:00 PM
will be helpful for those in need If I have a pandas dataframe like this: , You can use:
code :
df.mask(df.notna().cumsum(axis=1).gt(5))


Share : facebook icon twitter icon

Using ==Null in Linq not working to find null values


By : john busciglio
Date : March 29 2020, 07:55 AM
help you fix your problem There is a simple mismatch between the post model class and the database table. I get back the data using the following model for post:
code :
public post {
    [Key]
    [Required]
    [Column(Order = 0)]
    public long post_id { get; set; }

    [Required]
    [Column(Order = 1)]
    public long thread_id { get; set; }

    //[Required]
    [Column(Order = 2)]
    public long? parent_post { get; set; }

    [Required]
    [Column(Order = 3)]
    public long creator { get; set; }

    //[Required]
    [Column(Order = 4)]
    public DateTime? create_date { get; set; }

    //[Required]
    [Column(Order = 5)]
    public DateTime? update_date { get; set; }

    //[Required]
    [Column(Order = 6)]
    public string post_text { get; set; }
}

Create an array with NULL values/0 and find array length excluding null/0


By : Andrius Skučas
Date : March 29 2020, 07:55 AM
it helps some times
I want to find the number of columns in a range in each row which has non-null and >0 value ...
code :
SELECT *,
  (SELECT COUNT(1) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(
        TO_JSON_STRING(t), r'"col\d+":(.*?)[,}]')     
      ) value
    WHERE NOT value IN ('null', '0')
  ) AS non_null_0_count
FROM `project.dataset.table` t
#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 col1, 0 col2, 0 col3, 0 col4, 0 col5 UNION ALL
  SELECT 2, 4, 5, 0, 0, 0 UNION ALL
  SELECT 3, NULL, NULL, NULL, NULL, NULL
)
SELECT *,
  (SELECT COUNT(1) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(
        TO_JSON_STRING(t), r'"col\d+":(.*?)[,}]')     
      ) value
    WHERE NOT value IN ('null', '0')
  ) AS non_null_0_count
FROM `project.dataset.table` t
Row id  col1    col2    col3    col4    col5    non_null_0_count     
1   1   1       0       0       0       0       1    
2   2   4       5       0       0       0       2    
3   3   null    null    null    null    null    0      
#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 abc, 0 xyz, 0 qwe, 0 asd, 0 zxc UNION ALL
  SELECT 2, 4, 5, 0, 0, 0 UNION ALL
  SELECT 3, NULL, NULL, NULL, NULL, NULL
)
SELECT *,
  (SELECT COUNT(1) 
    FROM UNNEST(REGEXP_EXTRACT_ALL(
        TO_JSON_STRING(t), r'"(?:abc|xyz|qwe|asd|zxc)":(.*?)[,}]')
      ) value
    WHERE NOT value IN ('null', '0') 
  ) AS non_null_0_count
FROM `project.dataset.table` t
Row id  abc     xyz     qwe     asd     zxc     non_null_0_count     
1   1   1       0       0       0       0       1    
2   2   4       5       0       0       0       2    
3   3   null    null    null    null    null    0      
#standardSQL
SELECT *, 
  (
    SELECT COUNT(1)
    FROM (
      SELECT col1 col UNION ALL 
      SELECT col2 UNION ALL 
      SELECT col3 UNION ALL 
      SELECT col4 UNION ALL 
      SELECT col5
    )
    WHERE NOT col IS NULL AND col != 0
  ) AS non_null_0_count
FROM `project.dataset.table` t

Filling null values in the table using previous non null values. Each column has few null values?


By : IMMPrabath
Date : March 29 2020, 07:55 AM
I hope this helps you . As largely commented, you do need a column to order the dataset. As your data comes from a CSV file, you can, for example, edit the file before loading it to add an autoincremented row number.
Assuming that you have this column (id) in place, here is a SQLServer solution for the question of filling NULL values with the first preceeding non-NULL value in the same column.
code :
SELECT
    t.* ,
    MAX(CASE WHEN FromCompany IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpFromCompany,
    MAX(CASE WHEN Container   IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpContainer,
    MAX(CASE WHEN Numbers     IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpNumbers,
    MAX(CASE WHEN ToCompany   IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpToCompany,
    MAX(CASE WHEN Location    IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpLocation
FROM mytable t
id | FromCompany | Container   | Numbers | ToCompany     | Location    | grpFromCompany | grpContainer | grpNumbers | grpToCompany | grpLocation
-: | :---------- | :---------- | ------: | :------------ | :---------- | -------------: | -----------: | ---------: | -----------: | ----------:
 1 | DISCOVERY   | HALU 330308 |       5 | MAGNA CHARGE  | St-Laurent  |              1 |            1 |          1 |            1 |           1
 2 | null        | ATSU 827944 |       0 | LEEZA DIST.   | null        |              1 |            2 |          2 |            2 |           1
 3 | null        | null        |       4 | null          | null        |              1 |            2 |          3 |            2 |           1
 4 | COLUMBIA    | CAIU 807457 |       3 | La Cie Canada | Baie D'Urfe |              4 |            4 |          4 |            4 |           4
 5 | null        | null        |       6 | null          | null        |              4 |            4 |          5 |            4 |           4
 6 | null        | null        |       0 | null          | null        |              4 |            4 |          6 |            4 |           4
WITH mycte AS (
    SELECT
        t.* ,
        MAX(CASE WHEN FromCompany IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpFromCompany,
        MAX(CASE WHEN Container   IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpContainer,
        MAX(CASE WHEN Numbers     IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpNumbers,
        MAX(CASE WHEN ToCompany   IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpToCompany,
        MAX(CASE WHEN Location    IS NOT NULL THEN id END) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS grpLocation
    FROM mytable t
)
SELECT 
   id,
   (SELECT FromCompany FROM mytable WHERE id = grpFromCompany) AS FromCompany,
   (SELECT Container   FROM mytable WHERE id = grpFromCompany) AS Container,
   (SELECT Numbers     FROM mytable WHERE id = grpNumbers) AS Numbers,
   (SELECT ToCompany   FROM mytable WHERE id = grpToCompany) AS ToCompany,
   (SELECT Location    FROM mytable WHERE id = grpLocation) AS Location
FROM mycte 

GO
id | FromCompany | Container   | Numbers | ToCompany     | Location   
-: | :---------- | :---------- | ------: | :------------ | :----------
 1 | DISCOVERY   | HALU 330308 |       5 | MAGNA CHARGE  | St-Laurent 
 2 | DISCOVERY   | HALU 330308 |       0 | LEEZA DIST.   | St-Laurent 
 3 | DISCOVERY   | HALU 330308 |       4 | LEEZA DIST.   | St-Laurent 
 4 | COLUMBIA    | CAIU 807457 |       3 | La Cie Canada | Baie D'Urfe
 5 | COLUMBIA    | CAIU 807457 |       6 | La Cie Canada | Baie D'Urfe
 6 | COLUMBIA    | CAIU 807457 |       0 | La Cie Canada | Baie D'Urfe

Regular expression to find both non-null and NULL values


By : Matthew Marshall
Date : March 29 2020, 07:55 AM
will be helpful for those in need SQL Server doesn't support Regex, only it's own (more limited) pattern matching.
Normally I wouldn't recommend this answer, however, as you have a leading wild card already performance will be out the window. Assuming you want '%' to return every row, you could do:
code :
DECLARE @Filter = '%';

SELECT *
FROM TableA
WHERE ISNULL(ColumnA,'|') LIKE @Filter;

How to find a column with null and not null values in same table


By : hongsonghak
Date : March 29 2020, 07:55 AM
it fixes the issue I have a table where I need to find a column with null and not null values. for e.g. The column has a value today but becomes null tomorrow. , Use LEAD():
code :
SELECT *
FROM (
    SELECT 
        t.*, 
        LEAD(LastChangedDate) OVER(PARTITION BY AccountNumber ORDER BY EffectiveDate) LastChangedDateLead
    FROM test t
) x
WHERE LastChangedDate IS NULL OR LastChangedDateLead IS NULL
AccountNumber | EffectiveDate       | LastChangedDate     | LastChangedDateLead
------------: | :------------------ | :------------------ | :------------------
      1234567 | 30/06/2019 00:00:00 | 14/09/2018 00:00:00 | null               
      1234567 | 31/07/2019 00:00:00 | null                | null
Related Posts Related Posts :
  • How to multiply 2 unequal dataframes based on column values?
  • how can i get only one of the href using beautifulsoup if there are two href in the same line?
  • How to save a video using OpenCV VideoWriter to a specific directory -- python
  • Can URLField() in django load a localhost path?
  • Converting float numbers to binary
  • Transfer learning for ConvLstm ucf101 to hmdb51
  • How to perform assignment destructuring using the walrus operator in Python
  • Given a sentence in english with a blank space, how can I estmate in python the probability of a specific word to fit in
  • Retain tooltip of previous layer in Altair
  • A function that returns an unsorted list of with Numbers ranging from 100 to 1,000 and do not need to all be unique
  • Output shown in Jupyter Notebook but not in Pycharm
  • Correct classification of passenger
  • How do I run 100 python files 2 minutes apart?
  • Django server stops immediatly after login into admin page
  • django_rest_swagger - 'staticfiles' is not a registered tag library. Must be one of:
  • How to scrape stats from NBA website with python
  • Inconsistent dtype inference for mixed floats and strings
  • Proper way to typecheck object parameter
  • How to check if elements of a vector lies between two vectors in Numpy?
  • Why am I getting an error called 'expected string or bytes like object' when I try to use the re.sub expression in my co
  • Fast and efficient way to convert string into integer
  • scraping "View more" button with scrapy
  • ValueError: x and y must have same first dimension, but have shapes (101,) and (1,)
  • Python - Bug while appending
  • Saving multiple plots as one image
  • Incorrect data types for a transposed mixed type DataFrame
  • Appending to one list in recursive function
  • Having an issue running Python Code in terminal
  • Sort items in a list according to the number in the item
  • Faster way of iterating through lists in Python
  • Variable indexed by an indexed Set with Pyomo
  • Python: Calling a function from another function
  • Use a variable to describe an index range/slice for array / list indexing
  • Pandas extract data into a string from a column
  • Convert a RAW image to TIFF in python but keep the metadata
  • Python. How to compare two texts excluding some blocks from comparison
  • Regular expression <.*?>
  • Scrapy Crawlspider not crawling is it the RegEx?
  • How to provide default enum value with pybind11?
  • Tensorflow sports predictions
  • File dependency in Airflow
  • Add leading numbers to Numbers in Dataframe
  • how to return the class function in python?
  • Why does this python generator act like this?
  • Run time while loop condition
  • Set both condition and break in python for statement
  • Why does torch.from_numpy require a different byte ordering while matplotlib doesn't?
  • How can I bind more than one IP without using multithreading?
  • Keep marker in dropdown menu tkinter after new selection
  • Dropping row in pandas if 2 columns in the same row have NAN value in it
  • How to turn a column of lists in pandas to a sparse DataFrame of the unique values in Python
  • I need to pass variable from my python script to a remote server
  • Comparing 2 lists with ref to position of element?
  • String formatting in Python for today standards
  • How to add new Category in the CNN for Attendance by AI
  • Preserving Comments after an Array
  • Python converts from strings of word into list of word
  • How can you write almost type safe code in Python
  • List files in Google Cloud Virtual Machines before SCP
  • Comparing List / CSV File Python
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk