How can I return multiple columns from an array of ids?

I have a two tables like the following:

workshops table
+----+--------------+------------+
| id |     name     | option_ids |
+----+--------------+------------+
|  1 | Conversation | {2,3}      |
+----+--------------+------------+

options table
+----+-----------+---------------+
| id |    day    |     time      |
+----+-----------+---------------+
|  1 | Monday    | 13:00 - 14:00 |
|  2 | Tuesday   | 12:00 - 13:00 |
|  3 | Wednesday | 11:00 - 12:00 |
+----+-----------+---------------+

I want to construct a Query where I retrieve the workshop name and also all the information for option_ids 2 and 3.

I would like to eventually construct a JSON like so:

{
    name: "Conversation",
    options: [
        { day: "Tuesday", time: "12:00 - 13:00" }, 
        { day: "Wednesday", time: "11:00 - 12:00" }
    ]

}

python – Calculating Aggregate based on condition within a Columns in Pandas Dataframe

I am new to python so please help me.

I have a Data Frame which have

df

        id   DelegateID  status     emailaddress  ownername   Key   
        a     c           pending    a.gmail.com   as          1
        a     c           pending    a.gmail.com   as         2
        a     c           submitted  a.gmail.com   as         3
        a                 submitted  a.gmail.com   as         4
        b                 pending    b.gmail.com    bs         1
        b                 pending    b.gmail.com   bs         2
        b                 pending    b.gmail.com   bs         3
        b                 pending    b.gmail.com   bs         4
        b                 pending    b.gmail.com    bs         1
        c                 submitted  c.gmail.com   bs         1
        c                 submitted  c.gmail.com   bs         3
        c                 submitted  c.gmail.com   bs         4

I want to calculate the Count of status based on following condition.(I am stuck for the second part)

1.If Delgate ID is blank count the status of Key column based on status for each ID.(Code below)

  1. If Delegate ID is not blank then show the Delegatcount for that Userid and completedkey & remaining key needs to recalculate based on Delegate ID status.

For example -:

Above the ID = a has delegate ID = c so for Delegate count for c is changed to 3 and since out of those three delegated key 2 are pending 1 is submitted so the Completed key for the c would be 4 and Remainingkey would be 2 .

Total Key = count (Key) where status(pending)

CompletedKey = count (Key) where status(submitted)

Expected df_summary

       id    status    emailaddress  ownername   TotalKey   CompletedKey  RemainingKey  DelegC 
        a     pending  a.gmail.com   as            4           2              2           
        b     pending  b.gmail.com   bs            5           0              4
        c     submitted c.gmail.com  cs            3           4              2           3

Code Part 1 -:

 df.astype({'status': pd.CategoricalDtype(('pending', 'submitted'), ordered=True)}) 
  .merge(df.groupby('id')
           .agg(TotalKey=('Key', 'count'),
                CompletedKey=('status', lambda x: sum(x == 'submitted')), 
                RemainingKey=('status', lambda x: sum(x == 'pending'))),
         on='id') 
  .sort_values('status') 
  .drop_duplicates('id') 
  .drop(columns='Key')

postgresql – Must an index cover all columns to be used in a merge?

PostgreSQL can perform an index scan in both cases, but it prefers a sequential scan and a sort in the first case, because it thinks that will be faster. In the second case, an index-only scan is possible and faster.

You could of course add all table columns to the index, but that is usually unreasonable and may even exceed the size limit for an index entry.

If you have reason to believe that PostgreSQL is doing the wrong thing, you can try if the query is faster with enable_seqscan set to off. If yes, perhaps you have to adjust random_page_cost to match your hardware.

sql server 2012 – MS SQL How to pivot rows of 2 columns into multiple new columns using 1 pivot

I was wondering if it’s possible to improve the pivot I used to cater the values of two columns into multiple new columns using 1 pivot instead of using 2 and joining them.

I need the value of 2 of my columns, Date and Amount to be pivoted based on Type.
I already did a single pivot separately on Date and Amount column then used inner join to combine them.

WORKING QUERY: http://sqlfiddle.com/#!18/34054/3

I used the pivot method mentioned here for the working query: Pivot rows into multiple columns

SELECT
    aa.subscriber_id, 
    aa.msisdn,
    aa.name,
    aa.age,
    bb.TOPUP as topup_date,
    aa.TOPUP as topup_amount,
    bb.VOICE as voice_date,
    aa.VOICE as voice_amount,
    bb.SMS as sms_date,
    aa.SMS as sms_amount
FROM
  (SELECT *
  FROM (
      SELECT distinct  
    a.subscriber_id, 
    a.msisdn,
    a.name,
    a.age, 
    a.transaction_type, 
    a.amount
      FROM trans a
      INNER JOIN
      (
        SELECT max(transaction_date) mxdate, subscriber_id, transaction_type
        FROM trans
        WHERE CAST(transaction_date as DATE) BETWEEN '2019-01-01' and '2019-01-31'
        GROUP BY subscriber_id,transaction_type

      ) b
        ON a.subscriber_id = b.subscriber_id
        AND a.transaction_date = b.mxdate
        AND a.transaction_type = b.transaction_type
  ) AS SourceTable PIVOT( max(amount) FOR (transaction_type) IN ((TOPUP), 
  (VOICE),
  (SMS))) AS PivotTable) aa
INNER JOIN
  (SELECT *
  FROM (
      SELECT distinct  
    a.subscriber_id, 
    a.msisdn,
    a.name,
    a.age, 
    a.transaction_type, 
    a.transaction_date
      FROM trans a
      INNER JOIN
      (
        SELECT max(transaction_date) mxdate, subscriber_id, transaction_type
        FROM trans
        WHERE CAST(transaction_date as DATE) BETWEEN '2019-01-01' and '2019-01-31'
        GROUP BY subscriber_id,transaction_type

      ) b
        ON a.subscriber_id = b.subscriber_id
        AND a.transaction_date = b.mxdate
        AND a.transaction_type = b.transaction_type
  ) AS SourceTable PIVOT( max(transaction_date) FOR (transaction_type) IN ((TOPUP), 
  (VOICE),
  (SMS))) AS PivotTable) bb
  ON aa.subscriber_id = bb.subscriber_id
  AND aa.msisdn = bb.msisdn
  AND aa.name = bb.name
  AND aa.age = bb.age

formulas – Google Sheets: SUM of the LAST value – apply across multiple columns

Use Case: I have multiple financial portfolios that cannot be connected to via API. I want to track my total balance across portfolios. I do not want to tally every portfolio every day – instead, I want to update individual portfolios ocassionally, and have a running “up-to-date total”

enter image description here

This would be easy if Google Sheets had a REDUCE function. Here’s what I’ve tried:

=ArrayFormula(SUM(INDEX(B3:D,COUNTA(B3:D))))

I’ve also tried removing SUM and trying to just print the last values of each column using ArrayFormula.
I have also tried using =ArrayFormula(LOOKUP(2,1/(B3:D>0),B3:D)) as an alternative to get the last value of each column.

  1. How can I calculate the sum of last value across multiple columns?
  2. Why does GSheets not have a REDUCE function?

Thank you

ux designer – Adding more columns, even when the table looks full

If the columns are important to the business and provide useful information, one option to free up some room is to simplify the way you’re presenting the date range.

enter image description here
This can make it more readable, and you now have some extra space.

Another option is to have the big View button appear on hover, and save another column.

python – How to loop and append pd columns + groupby logic

I’m trying to loop over an api endpoint to extract json data and then append the data to a pandas dataframe while add certain groupby logic. I’m stuck on the appending and for sure the groupby.

My code

tickers_list = ('BTC', 'ETH')

#setup 
tsym = "USD"
s=requests.session()
url = "https://min-api.cryptocompare.com/data/v2/histoday?"
api_key = "test12345"


#creating empty df
df_cc = pd.DataFrame()

#columns to extract from json
cols = ('time', 'close', 'high', 'low')

#Request for historical OHLC data for tickers from Cryptocompare
for i in range(len(tickers_list)):
    res = s.get(url+"fsym="+tickers_list(i)+"&tsym="+tsym +"&limit=10&api_key="+api_key).content

# load json in pd style
    data = json.loads(res)
#normalize it and extract data path
    df_cc_(i) = pd.json_normalize(data('Data'), record_path='Data')(cols)
    df_cc_(i) = df.astype({'time': 'datetime64(s)'}).set_index('time')
#trying to append data to a df     
    df_cc = df_cc.join(df_cc(i))

Sample json data for BTC

b'{“Response”:”Success”,”Message”:””,”HasWarning”:false,”Type”:100,”RateLimit”:{},”Data”:{“Aggregated”:false,”TimeFrom”:1629936000,”TimeTo”:1630800000,”Data”:({“time”:1629936000,”high”:49358.2,”low”:46456.68,”open”:48996.44,”volumefrom”:34276.36,”volumeto”:1618461768.41,”close”:46852.22,”conversionType”:”direct”,”conversionSymbol”:””},{“time”:1630022400,”high”:49166.31,”low”:46376.81,”open”:46852.22,”volumefrom”:27872.76,”volumeto”:1331637984.84,”close”:49088.1,”conversionType”:”direct”,”conversionSymbol”:””}

Expected output (example data)

           Close.       High.        LOW
           BTC    ETH.  BTC.   ETH.  BTC.  ETH
2021-09-05. 49000 3800. 49500. 3900. 48900.3700
2021-09-04. 49500.3900  49600. 4000. 49200 3800

python – Append Columns C and D to A and B in Pandas Dataframe (pandas)

i have a data frame which is 12 columns by 12 rows. i want to append columns C and D, E and F, G and H and I and J to columns A & B making it 2×72. when i try to do it with:

df = dffvstats.iloc(:,(0,1))
df2 = dffvstats.iloc(:,(2,3))
df.append(df2)

it gives me:

             0            1              2      3
0        Index  DJIA S&P500            NaN    NaN
1   Market Cap     2521.74B            NaN    NaN
2       Income       86.80B            NaN    NaN
3        Sales      347.15B            NaN    NaN
4      Book/sh         3.87            NaN    NaN
5      Cash/sh         3.76            NaN    NaN
6     Dividend         0.88            NaN    NaN
7   Dividend %        0.57%            NaN    NaN
8    Employees       147000            NaN    NaN
9   Optionable          Yes            NaN    NaN
10   Shortable          Yes            NaN    NaN
11       Recom         1.90            NaN    NaN
0          NaN          NaN            P/E  30.09
1          NaN          NaN    Forward P/E  27.12
2          NaN          NaN            PEG   1.53
3          NaN          NaN            P/S   7.26
4          NaN          NaN            P/B  39.70
5          NaN          NaN            P/C  40.87
6          NaN          NaN          P/FCF  31.35
7          NaN          NaN    Quick Ratio   1.00
8          NaN          NaN  Current Ratio   1.10
9          NaN          NaN        Debt/Eq   1.89
10         NaN          NaN     LT Debt/Eq   1.65
11         NaN          NaN          SMA20  3.47%

anyone know how to do this?

sql server 2012 – How to make multiple columns based on information on one field

I have a query that lists all product that is produced and the product that was consumed to make that product. What I want is to create a query or subquery that summarize the produced and consumed.
The produced is TRSTYP_0 = 5
The Consumed is TRSTYP_0 = 6

Basically, I want to add to the table that has the quality Consumed.
and another column Quantity Produced.

The SQL Code so far:

SELECT VCRNUM_0 AS 'Tranx Number',
   ACTQTY_0 AS 'Eaches',
   CREDAT_0 As 'Create Date',
    CAST(CONVERT(NVARCHAR(12), LIVE.STOJOU.CRETIM_0) AS INT) AS 'Production Time',
    CONVERT(varchar, LIVE.STOJOU.CREDAT_0,101) AS 'Production Date',
   CRETIM_0 AS 'Create Time',
   CREUSR_0 AS 'User',
   ITMREF_0 AS 'Item Number',
   VCRNUMORI_0 AS 'Work Order',
   VCRTYPORI_0,
   VCRTYPREG_0,
   TRSTYP_0, 
   LOT_0 As Lot,
   LOC_0,
  SLO_0 As 'Sub Lot',
   PCU_0 As Units,
   SERNUM_0 as 'Serial Number',
   STA_0 As Status
  

FROM LIVE.STOJOU

WHERE
LIVE.STOJOU.VCRTYPREG_0 <> 10
AND LIVE.STOJOU.VCRTYP_0 = 10
AND LIVE.STOJOU.TRSTYP_0 = 19
OR LIVE.STOJOU.TRSTYP_0 = 5
OR LIVE.STOJOU.TRSTYP_0 = 6
AND (CREDAT_0) >= ’01-01-2018′
Table 1

what I want the result to look like

7 – Adding conditional comparison of two columns in relationship

I am trying to put value comparision while creating relationship. When i give direct value that works perfectly fine. but i wanted to pass column name with alias to get required results.

This is working fine

$this->query->add_where(
        $this->options('group'),
        db_and()
          ->condition("class.end_time", 1625047346 , '<=')
    );

output
WHERE ((( (class.end_time <= 1625047346) ))AND ……………

This is not working. it takes alias.column as value

$this->query->add_where(
        $this->options('group'),
        db_and()
          ->condition("class.end_time", "class1.end_time" , '<=')
    );

output
WHERE ((( (class.end_time <= ‘class.end_time’) ))AND ……………