## python – Calculated column with shift

This is the base DataFrame:

g_accessor  number_opened  number_closed
0     49 - 20            3.0            1.0
1     50 - 20            2.0           14.0
2     51 - 20            1.0            6.0
3     52 - 20            0.0            6.0
4      1 - 21            1.0            4.0
5      2 - 21            3.0            5.0
6      3 - 21            4.0           11.0
7      4 - 21            2.0            7.0
8      5 - 21            6.0           10.0
9      6 - 21            2.0            8.0
10     7 - 21            4.0            9.0
11     8 - 21            2.0            3.0
12     9 - 21            2.0            1.0
13    10 - 21            1.0           11.0
14    11 - 21            6.0            3.0
15    12 - 21            3.0            3.0
16    13 - 21            2.0            6.0
17    14 - 21            5.0            9.0
18    15 - 21            9.0           13.0
19    16 - 21            7.0            7.0
20    17 - 21            9.0            4.0
21    18 - 21            3.0            8.0
22    19 - 21            6.0            3.0
23    20 - 21            6.0            1.0
24    21 - 21            3.0            5.0
25    22 - 21            5.0            3.0
26    23 - 21            1.0            0.0

I want to add a calculated new column number_active which relies on previous values. For this I’m trying to use pd.DataFrame.shift(), like this:

# Creating new column and setting all rows to 0
df('number_active') = 0
# Active from previous period
PREVIOUS_PERIOD_ACTIVE = 22
# Calculating active value for first period in the DataFrame, based on `PREVIOUS_PERIOD_ACTIVE`
df.iat(0,3) = (df.iat(0,1) + PREVIOUS_PERIOD_ACTIVE) - df.iat(0,2)
# Calculating all columns using DataFrame.shift()
df('number_active') = (df('number_opened') + df('number_active').shift(1)) - df('number_closed')
# Recalculating first active value as it was overwritten in the previous step.
df.iat(0,3) = (df.iat(0,1) + PREVIOUS_PERIOD_ACTIVE) - df.iat(0,2)

The result:

g_accessor  number_opened  number_closed  number_active
0     49 - 20            3.0            1.0           24.0
1     50 - 20            2.0           14.0           12.0
2     51 - 20            1.0            6.0           -5.0
3     52 - 20            0.0            6.0           -6.0
4      1 - 21            1.0            4.0           -3.0
5      2 - 21            3.0            5.0           -2.0
6      3 - 21            4.0           11.0           -7.0
7      4 - 21            2.0            7.0           -5.0
8      5 - 21            6.0           10.0           -4.0
9      6 - 21            2.0            8.0           -6.0
10     7 - 21            4.0            9.0           -5.0
11     8 - 21            2.0            3.0           -1.0
12     9 - 21            2.0            1.0            1.0
13    10 - 21            1.0           11.0          -10.0
14    11 - 21            6.0            3.0            3.0
15    12 - 21            3.0            3.0            0.0
16    13 - 21            2.0            6.0           -4.0
17    14 - 21            5.0            9.0           -4.0
18    15 - 21            9.0           13.0           -4.0
19    16 - 21            7.0            7.0            0.0
20    17 - 21            9.0            4.0            5.0
21    18 - 21            3.0            8.0           -5.0
22    19 - 21            6.0            3.0            3.0
23    20 - 21            6.0            1.0            5.0
24    21 - 21            3.0            5.0           -2.0
25    22 - 21            5.0            3.0            2.0
26    23 - 21            1.0            0.0            1.0

Oddly, it seems that only the first active value (index 1) is calculated correctly (since the value at index 0 is calculated independently, via df.iat). For the rest of the values it seems that number_closed is interpreted as negative value – for some reason.

## microsoft excel – Is there a formula or function i can use assign a value to column based off the criteria in another cell?

I have a spreadsheet with multiple header columns and and the aim of the spreadsheet is to pick the value on the left hand side and place it in the correct column based on the description on the right hand side. The description on the right hand side has consistent elements which help decide which column it needs to go in like Trials, Supermoto, MX and membership Im just wondering if there is a formula or function that i could set up that would check for this criteria and then allocate the amount on the left hand side to the correct column.

## JSON formula for column to get the earliest date from two or more other date columns

Wanting to use a separate column that defines which date from two other columns is the earliest.

## sharepoint online – Lookup Field to pull in values from Choice Column

I have a Customer Table containing info such as Customer # (Number), Name(Single line of text), Bankruptcy status (Yes/No), Currency (Choice).

I also have a Invoice table which has a lookup column for the Customer #, pulling in the Customer Name.

Now, the scope of the project has made it so I need to pull in the Currency column using the Customer # lookup, which seems like it would’ve been straight forward; however, I don’t see any of the other columns besides the system generated ones (Version, Title, ID, Created, etc.) w

How can I get the lookup column to also include the currency in the Invoice table?

## SharePoint Online: Change “Display Format” to Standard in the “Modified” column name

There is a way, in SharePoint Online, to Change “Display Format” to Standard from “Friendly” in the “Modified” column name?

I would like do this for all sites in the tenant and for all data fields and the last but not least, for all future teams, sites and so on

Thank you

## How to return user email from a custom column “Owner” to an XML from sharepoint online list using CSOM?

How to return user email from a custom column “Owner” to an XML from sharepoint online list using CSOM?

string xmlFile = @"c:tempexport.xml";
Web web = clientContext.Web;
ListCollection listColl = web.Lists;
List list = clientContext.Web.Lists.GetByTitle("Site Pages");
FieldCollection listFields = list.Fields;
clientContext.ExecuteQuery();

ListItemCollection listItems = list.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.ExecuteQuery();

XmlDocument doc = new XmlDocument();
XmlDeclaration declaration = doc.CreateXmlDeclaration("1.0", "ISO-8859-1", null);
doc.AppendChild(declaration);
XmlElement root = doc.CreateElement("SitePages"); //CREATES FIRST ROOT NODE

foreach (ListItem item in listItems)
{

ListItem LItem = listItems.GetById(item.Id);
XmlElement elem = doc.CreateElement("List"); //CREATES CHILD NODE

elem.SetAttribute("DateLastReviewed", Convert.ToString(item("DateLastReviewed")));
elem.SetAttribute("NextReviewDate", Convert.ToString(item("NextReviewDate")));
elem.SetAttribute("ContentOwner", Convert.ToString(item("Owner")));                   // bug exists as it sends Microsoft.SharePoint.Client.FieldUserValue() instead of sending user name /email
elem.SetAttribute("ContentNumber", Convert.ToString(item("ContentNumber")));
elem.SetAttribute("ID", Convert.ToString(item.Id));
root.AppendChild(elem);

}
doc.AppendChild(root);
doc.Save(xmlFile);
Console.WriteLine(" XML file is successfully generated");
}

## MySQL – Adding Computed Column extremely slow

We are trying to add computed column to a table and this operation is taking more than 20 hours to complete:

json_unquote(coalesce(json_extract(int_data,’\$.Item.IRefNum’),json_extract(m_dt,’\$.refId’)))

Database: MySql verison 5.7.
Table rows: 32636254

any input will be apprecitaed.

## sql – How to search to see if values of a column exist in another table as sub-string in BQ

I am familiar with the CONTAINS_SUBSTR function in BigQuery.  Here’s an example from the official BigQuery documentation of how it works (with minor modification):

WITH Recipes AS
(SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL
SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL
SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL
SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham')
SELECT * FROM Recipes;

Recipes:

Breakfast Lunch Dinner
Blueberry pancakes Egg salad sandwich Potato dumplings
Potato pancakes Toasted cheese sandwich Beef stroganoff
Avocado toast Tomato soup Blueberry salmon
Corned beef hash Lentil potato soup Glazed ham
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch), 'potato');

Result:

Breakfast Lunch Dinner
Corned beef hash Lentil potato soup Glazed ham

However, instead of a single STRING literal (potato in the above example), I want to be able to use a table with a single column that contains the keywords that I’m looking for:

Keywords AS
(SELECT 'potato' as ingredient UNION ALL
SELECT 'cheese' UNION ALL
SELECT 'egg')

Desired output:

Breakfast Lunch Dinner
Corned beef hash Lentil potato soup Glazed ham
Potato pancakes Toasted cheese sandwich Beef stroganoff
Blueberry pancakes Egg salad sandwich Potato dumplings

Is there a way to use CONTAINS_SUBSTR or any other function and pass the ingredient column of Keywords as input to it to get the desired output?

Example of a query that I’m looking for (this does not work):

SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch), (SELECT ingredient from Keywords));

## referencing a column in a Google sheets array literal

Is there a way to refer to columns in a literal array like so:

=FILTER({lengthy_arrayformula_1, lengthy_arrayformula_2}, condition referencing column 2 of the array literal in previous argument)

or

=INDEX({lengthy_arrayformula_1, lengthy_arrayformula_2}, row, 2nd col of the literal array)

without creating ‘buffer columns’ that hold intermediate results.

## mysql – Fastest way to find which strings in an array are NOT present in a column

I am searching through MySQL rows in yii. Let’s say I have an array of strings

\$fruits=array("apple", "banana","cherry","date");

I want to search in a db that looks like this:

id  name
1   apple
2   banana

And I want it to return

array("cherry",'date')

What’s the best way to do this? Is there a Yii-specific way to do this? I was going to do it the caveman way where I get all of the names into a php array and then do foreach loop on \$fruits but I think there must be a smarter way.