Neat poster here (PDF) by Roxanne Johnson indicating when to consider Python for data projects, when to stick with Excel, how to find self-help resources if you delve into Python, etc.
Large page and small print, so zoom in.
Wednesday, June 28, 2017
Wednesday, May 24, 2017
Job-Hunting Advice For Career Changers -- Talking About Transferable Skills
I'm mentoring a student worker as she searches for jobs after finishing her graduate degree at our university. She came to graduate school full-time to learn skills in a new field, which means she has recently learned a number of new programming languages in an academic setting, rather than on the job. Unsure how to keep recruiters on the phone and find some way to make her several years of work experience before and during graduate school "count" towards a job using these programming languages, she asked me if I had any advice. Here are some tips I gave her.
- The written job description is gold to you.
- The bottom half usually describes the technical skills you're afraid of seeming too new at. Don't get too wrapped up in it.
- The top half usually describes the job itself. It's where a career-changer like you will find the most "source material" for talking about why you're qualified to do the job.
- You wish they would "give you a chance" to "learn on the job," right?
- Well, they won't. Not if you put it like that.
- But if you point out how you've done things just like that, only different, but pretty much the same, and HERE'S HOW -- well, now you sound like someone ready to "jump into the deep end of the pool," don't you?
Welcome to the world of "selling your transferable skills."
- The "what this position does" "top half" of a job description is where you find out exactly what you need to say your prior experience is "just like," and where you'll figure out the difference between your prior experience and the future job (so that you can explain what other transferable skills you have that address the difference).
- You wish they would "give you a chance" to "learn on the job," right?
- If you don't have a written job description because a recruiter is summarizing a job orally and focuses on the "bottom half," confidently direct them to tell you about about the "top half" so you can direct the rest of the phone call from a position of strength.
- Example: "Ah, yes, I have worked with those technologies in my graduate coursework, and I spent a year at ___ and three years at ___ doing related work with [related technology or transferable experience/skills] -- that might be a good fit. Can you tell me more about the job responsibilities and day-to-day work?"
- Rehearse, rehearse, rehearse. Practice makes perfect.
- Treat this like you would ask a child to treat a role in a "school play."
- Memorize the lines
- Look in the mirror and practice the lines -- master your projection of confidence and your ability to remember your lines while looking someone in the eyes / get over any sense of disgust at watching yourself.
- If you have time, look in the mirror again and practice making your lines your own -- practice being easy and relaxed and you while delivering them.
- Treat this like you would ask a child to treat a role in a "school play."
- Of course, you have to write your own lines -- you're the playwright as well. But you already know what the play is going to be about -- it's going to be about the job in the advertisement and about you! So write that script!
- Print a copy of the job description and a copy of your resume. Maybe grab some spare paper if there isn't a lot of room in the margins.
- Look through the "job duties" and TRANSLATE them from the way they're written to "something your grandmother would understand when she asks what you do for a living."
- Look through the "job duties" and find the IMPLIED job duties. (For example, if you have to "deliver" something, implied transferable skills & experiences might be "ahead of schedule" or "to very important people." Another example -- if it talks about "different" people you'll be working with ... implied is, "You'll be pulled in a million different directions and need to be able to handle it politely." If you've done that before ... yay, you have experience in something they need!)
- Now, for all the plain-English and implied real job duties, figure out what you've already done, at school or at work, and write notes to yourself.
- Finally, go through your notes and put together little stories about how great at the job you'd be, mentioning that you see they need the position to do ____, and how that sounds like the time in ____ when you _____ed.
- Repeat until you're out of such stories -- then start this process over for the next job advertisement!
- You might want to have a few different versions of any story that's too technical -- the "highly technical" (but still 60 seconds or less) version and the "explain it to a 6-year-old in 15 seconds or less" version. Always start by sharing the "explain it to a 6-year-old, 15 seconds or less" version of a story, only going into the "highly technical" version if pressed for more details.
- When talking about how you have ___ experience on the job and ___ experience at school, don't leave it to the interviewer to infer that the two can be added together into a competence. This is YOUR JOB and is why you're rehearsing. If you'd like, you can start with phrases like, "In my experience" or "Given my experiences on the job and in my coursework," which have 3 advantages:
- You don't have to worry about seeming like you're being contradictory -- it's just your opinion. So it's polite / deferential.
- It's hard for someone to challenge the accuracy of. It's just your opinion. So it's confident (because it's hard to challenge).
- It brings attention to the fact that you have experience and that you can put all of your experience together into one big package that's useful to them.
- This same process can also be used when deciding what to write in a cover letter -- only you have to be much, much more concise and not tell as many stories.
- Finally, if you're worried about professional "word choice" when answering difficult questions, read Alison Green's 'Ask A Manger' advice blog religiously. Eventually, you'll pick up on her style, which I think is excellent.
Good luck!
Python CSV Example - Back-Filling Nearby Zip Code Distances
Today I used the following small Python script to add a bunch of distances to a new field called "Miles_From_Us__c" on 40,000 pre-existing Contact records.
The process was as follows:
- Export "SELECT ID, MAILINGPOSTALCODE, MILES_FROM_US__C WHERE MAILINGPOSTALCODE != NULL AND MAILINGPOSTALCODE != '' AND MILES_FROM_US__C = NULL" from our Salesforce org to a CSV file on my hard drive at "c:\tempexample\contactzipextract.csv" using the "Apex Data Loader" software.
- Run the Python script in this blog post instead of bashing my head against the wall with Excel.
- Import the CSV file on my hard drive at "c:\tempexample\contactzipupdate.csv" back into our Salesforce org as an "UPDATE" operation using the "Apex Data Loader" software.
import pandas
zips = ['11111','22222','33333','44444']
zipstodist = {'11111':0, '22222':1, '33333':9, '44444':21}
df = pandas.read_csv('c:\\tempexample\\contactzipextract.csv', dtype='object')
df = df[df['MAILINGPOSTALCODE'].str[:5].isin(zips)]
df['MILES_FROM_US__C'] = df['MAILINGPOSTALCODE'].str[:5].apply(lambda x : zipstodist[x])
df[['ID','MILES_FROM_US__C']].to_csv('c:\\tempexample\\contactzipupdate.csv', index=0)
Here's an explanation of what my script is doing, line by line, for people new to Python:
- Says that I'll be using the "Pandas" plug-in to Python.
- Says which U.S. postal codes ("zip codes") I care about as a Python "list" that I give a nickname of "zips."
- I got this from the first column of a CSV file of "zip codes of interest" that a consultant sent one of my users.
- Defines the "distance from us," in miles, of each zip code of interest, as a Python "dict" that I give a nickname of "zipstodist."
- I got this from the first and second columns of a CSV file of "zip codes of interest" that a consultant sent one of my users.
- I could have cross-checked the two CSV files in Python, but I had already transformed the CSV data into this list style when writing some Salesforce Apex code for future maintenance, so copying/pasting it into Python was easier in this case.
- Reads my CSV file into Python as a Python "Pandas dataframe" that I give a nickname of "df."
- Filters out any rows whose first 5 characters of the value in the "MAILINGPOSTALCODE" column aren't among my zip codes of interest.
- Sets each row's"MILES_FROM_US__C" to the distance corresponding to the first 5 characters of that row's "MAILINGPOSTALCODE" value.
- Exports the "ID" & "MILES_FROM_US__C" columns of my filtered, transformed dataset out to a new CSV file on my hard drive.
Tuesday, April 25, 2017
A Brief UPDATE Script: Oracle SQL vs. Salesforce Apex+SOQL
In a copy of Salesforce using EnrollmentRx, we are capturing the details of every submission-from-a-student on a table attached to "Contact" (PK-FK) called "Touch_Point__c."
When such a "Touch_Point__c" record is created, if it is the first created for a given "Contact," a trigger copies its "Lead_Source__c" value over to the corresponding "Contact" record's "LeadSource" field.
Midway through an advertising campaign, a decision was made to change the string used for a certain departmental landing page's "Lead_Source__c" value from "Normal Welcome Page" to "Landing Page."
We'd caught up on back-filling "Lead_Source__c" values on old "Touch_Point__c" table records.
However, we hadn't yet back-filled the corresponding "LeadSource" fields on "Contact" in the case where such "Touch_Point__c" records had been the first in existence for a given "Contact."
(We wanted to leave "Contact" records alone where none of the altered-after-the-fact "Touch_Point__c" were actually the first "Touch_Point__c" record for the "Contact.")
I wrote a little script that's the equivalent of a complex "UPDATE" statement and am sharing it here for colleagues from the Oracle SQL world.
(Please excuse any typos or inefficiencies -- the real data set was small, so I didn't care about performance, and I didn't actually run the Oracle.)
Here's some Oracle SQL that I believe would've done the job, if Salesforce were a normal Oracle database:
UPDATE Contact
SET LeadSource = (
SELECT
Lead_Source__c
FROM Touch_Point__c
INNER JOIN (
SELECT Contact__c, MIN(CreatedDate) AS MIN_CR_DT
FROM Touch_Point__c
GROUP BY Contact__c
) qEarliestTP
ON Touch_Point__c.Contact__c = qEarliestTP.Contact__c AND Touch_Point__c.CreatedDate = qEarliestTP.MIN_CR_DT
WHERE Contact.Id = Touch_Point__c.Contact__c
AND Lead_Source__c='Landing Page'
AND Dept_Name__c='Math'
AND utm_source__c is not null
AND extract(year from CreatedDate) >= extract(year from current_date)
)
WHERE Contact.Id IN (
SELECT
Touch_Point__c.Contact__c
FROM Touch_Point__c
INNER JOIN (
SELECT Contact__c, MIN(CreatedDate) AS MIN_CR_DT
FROM Touch_Point__c
GROUP BY Contact__c
) qEarliestTP
ON Touch_Point__c.Contact__c = qEarliestTP.Contact__c AND Touch_Point__c.CreatedDate = qEarliestTP.MIN_CR_DT
WHERE Contact.Contact__c = Touch_Point__c.Contact__c
AND Lead_Source__c='Landing Page'
AND Dept_Name__c='Math'
AND utm_source__c is not null
AND extract(year from CreatedDate) >= extract(year from current_date)
)
AND Contact.LeadSource='Normal Welcome Page'
Here's the Salesforce Apex code (with embedded SOQL) I wrote to do the job instead, since Salesforce doesn't give you a full-on SQL-type language.**
// Loop through every record in the "Touch_Point__c" table, setting it aside in a map, keyed by its foreign key to a the "Contact," if it is the earliest-created "Touch_Point__c" for that Contact
Map<Id, Touch_Point__c> cIDsToEarliestTP = new Map<Id, Touch_Point__c>();
List<Touch_Point__c> allTps = [
SELECT Id, Contact__c, CreatedDate
FROM Touch_Point__c
ORDER BY Contact__c, CreatedDate ASC
];
for (Touch_Point__c tp : allTPs) {
// The "ORDER BY" in allTPs should make this logic short-circuit at the first half of the "IF," but 2nd half will dummy-check if the list is, for some reason, out of order.
if (!cIDsToEarliestTP.containsKey(tp.Contact__c) || cIDsToEarliestTP.get(tp.Contact__c).CreatedDate > tp.CreatedDate) {
cIDsToEarliestTP.put(tp.Contact__c, tp);
}
}
// Loop through every "landing page visit"-typed record in the "Touch_Point__c" table, updating a modified in-memory copy of the record in the "Contact" table it references to a list of "Contact" records called "csToUpdate" ONLY IF the "landing page"-related TouchPoint is also the "earliest-created" TouchPoint for that Contact record -- then call a DML operation on that in-memory list to persist it to the database.
List<Contact> csToUpdate = new List<Contact>();
List<Touch_Point__c> mathLandingTPs = [
SELECT Id, Contact__c, Lead_Source__c, Contact__r.LeadSource, Dept_Name__c, utm_source__c, CreatedDate
FROM Touch_Point__c
WHERE Lead_Source__c='Landing Page'
AND Contact__r.LeadSource='Normal Welcome Page'
AND Dept_Name__c='Math'
AND utm_source__c<>null
AND CreatedDate>=THIS_YEAR
];
for (Touch_Point__c tp : mathLandingTPs ) {
if (cIDsToEarliestTP.containsKey(tp.Contact__c) && cIDsToEarliestTP.get(tp.Contact__c).Id == tp.Id) {
csToUpdate.add(new Contact(Id=tp.Contact__c, LeadSource=tp.Lead_Source__c));
}
}
UPDATE csToUpdate;
Oracle programmers, I imagine your colleagues might yell at you if you used PL/SQL to hand-iterate over smaller SQL queries in Oracle rather than using native SQL do the work for you. In Salesforce, that's simply the way it's done.
**Note that more complex code might be required -- e.g. you might have to run the same code several times with a row-count limit on it -- since Salesforce is pretty picky about the performance of triggers fired in response to a DML statement. (A normal Oracle database has its limits, too, of course, but they're likely far less strict if it's your own in-house database than with Salesforce.)
Tuesday, April 4, 2017
Python for Salesforce Administrators - Introduction to XML and JSON
XML and JSON are like each other, but not like CSV
We've talked about how useful Python can be for processing table-style data stored in "CSV" plain-text files.
The key properties of table-style data are that:
- The "table" always has a certain number of columns
- Every single row in the table has the exact same set of "columns" (the exact same "keys") as every other row and as the table at large
- Every single row in the table is capable of having a value in each of these columns ... a "blank" placeholder still needs to be indicated if it doesn't.
- Every single row in the table can only have one value in each of these columns. (If you have only one "First Name" column, no row can have two first names.)
- Each conceptual "item" in the data is represented by a "row"
- Each conceptual "item" (row) can have no more than 1 level of "key-value" properties (a "column header" being the "key" and a given cell beneath a column header, in a specific row, being the "value").
Example: There's no such thing as having the notion of a "Name" that breaks down at a lower level into "First Name" and "Last Name."
Sure, the database you're exporting a "CSV" file from might have an automatically computed / "formula" field called "Name" that is just a space-separated merge of "First Name" and "Last Name." But the exported "CSV" file itself -- a plain-text representation of your data -- will show "Name," "First Name," and "Last Name" as independent "columns" whose values are at an equal "level" to each other.
There are other styles of data that can be stored in plain-text files as well.
The two main problems with table-style data that alternative textual representations of data try to get around are:
- Giving each conceptual "item" in the data key-value properties that are "nested" inside each other
- Letting each conceptual "item" in the data have "keys" that have nothing to do with the "keys" that other conceptual "items" in the data have
A plain-text file where punctuation indicates the start/end of each conceptual "item" in the data, and where the "keys" (and their values) inside of each conceptual "item" are also indicated by careful use of punctuation, can handle both of these requirements.
The two most common formats today are "XML" and "JSON." Plain-text exports of your current Salesforce configuration are often formatted in either of these styles.
- In both formats, conceptual "items" can have more conceptual "items" nested inside of them.
- In both formats, there is a way (in XML, 2) of defining the "keys and their values" possessed by each conceptual "item"
- In XML, the word for the thing representing a conceptual "item" is an "element."
- In JSON, the word for the thing representing a conceptual "item" is an "object."
- Despite the "element" vs. "object" linguistic difference, XML and JSON represent the same type of data (nested data where each conceptual "item" gets to define its own "keys" and specify values for them).
We'll have a lot of examples in this post.
- To view my XML examples graphically, paste them here and click "Tree View".
- To view my JSON examples graphically, paste them here and click "Tree View".
XML
The punctuation that XML uses to define the beginning and end of an "element" is a "tagset." It looks like this:
<Person></Person>
As you can see, it's the same word, surrounded by less-than and greater-than signs, with the one indicating the "end" of the element starting with a forward-slash.
Each piece in the greater-than or less-than signs is considered a "tag," hence "tagset" for the notion of including them both (kind of like "parenthesis" versus "a set of parentheses").
The fact that the tagset exists in your text file means that it exists as a conceptual "item" in your data.
It doesn't matter whether or not there's anything typed between the tags (after the first greater-than, before the last less-than). This is now a conceptual item that "exists" in your data, simply because the tagset exists.
If it doesn't have anything between the tags, you can think of it a little like a row full of nothing but commas in a CSV file. It's there ... it's just blank.
(In fact, there's even a fancy shortcut for typing such tagsets: this single tag is equivalent to a tagset with nothing in the middle like the one above -- note the forward-slash before the greater-than sign:)
<Person/>
Note that already, though, our "blank" element has one big thing different about it than a row in a CSV file does: it has a name! Rows don't have names. We'll come back to this, but this is why XML has two ways of indicating an element's "keys and their values." By giving each element a name, XML allows the element itself to be used as a "key" definition for the larger context inside of which the element finds itself.
Here's an example:
<Shirt> <Color></Color> <Fabric></Fabric> </Shirt>
There are 3 conceptual "items," or "elements," in this data, each of which has a name.
All 3 can stand alone as "elements" in the grammar of XML. Analogy:
You can write an English sentence that has multiple complete sentences inside of it; to write a sentence with multiple complete sentences inside, simply separate the two with semicolons.
However, the fact that the elements named "color" and "fabric" are nested between the tags of the element named "shirt" means that they are also indicating that this particular shirt has keys named "color" and "fabric" (the values to both of which are currently blank).
The line breaks and tabs aren't necessary in XML (even for saying where "color" stops and "fabric" begins), but they help humans read XML.
Now might be a good time to show you the other way of indicating that a particular shirt has a "color" and "fabric," but that their values are blank:
<Shirt Color="" Fabric=""></Shirt>Or, in shortcut notation, since there's now nothing inside the "Shirt" tagset:
<Shirt Color="" Fabric=""/>
Note that this isn't always treated EXACTLY the same as our nested-tag example when it comes to programming languages that read XML. Some software might argue that there's more of a "nothingness" in the nested-tags example (it truly doesn't have a color), and there's more of a "value without any letters in it"-ness in the inside-the-Shirt-opening-tag example. Just sometimes, though, and that's often you, the programmer, deciding to make that distinction.
The big difference, though, is that in this case, "color" and "fabric" are not standalone elements.
They are "attributes" of the element named "Shirt".
You can't put more standalone "elements" between the quotes after the "=" of an "attribute." You're done. Only a plain-text value can go there.
You can't do this:
<Shirt Color="" Fabric="<Washable></Washable>"></Shirt>
But you can do this:
<Shirt> <Color></Color> <Fabric> <Washable></Washable> </Fabric> </Shirt>
You also can't give any element more than one "attribute" of the same name, whereas you can nest as many same-named "elements" inside of an element as you like.
You can't do this:
<Shirt Color="" Color="" Fabric=""></Shirt>
But you can do this:
<Shirt> <Color></Color> <Color></Color> <Fabric> <Washable></Washable> </Fabric> </Shirt>
Those are the main differences between the two ways XML gives you to define key-value pairs on an element.
- Attributes are, by definition, "the end of the line" when it comes to the "key" definitions attached to an element (and can't conflict with each others' names)
- The names of elements nested inside an element also serve as "key" definitions for the outer element, but they're "fuzzier" than attributes.
This is usually considered a good thing.
This desire for "fuzziness" & "repeatability" & "nested-ness" is one of the two reasons people choose a "nested" data format instead of a table-style format to represent their data.
A word of warning: this is also valid code:
<Shirt Color="" Fabric=""> <Color></Color> <Color></Color> <Fabric> <Washable></Washable> </Fabric> </Shirt>
A human might look at the shirt above and think it has 3 colors and 2 fabrics. It's probably better to think of it the way the computer thinks of it -- that the shirt above has 1 color attribute, 1 fabric attribute, 2 full-on elements nested within it each named "color," and 1 full-on element nested within it named "fabric."
Now let's give our shirt some "values!"
First of all, it's essential to remember that in a way, all these example' elements "keys" already had values. The values for the "keys" were just blank, or they were other elements**.
**(Think about the examples where an element named "washable" was nested inside of an element named "fabric" which was nested inside of an element named "shirt." The value for the "shirt" element's "fabric" key wasn't exactly blank -- the value for that key was more like: "an element called 'washable.'")
But when I say "values" for keys like "color" or "fabric" or "washable," you're probably thinking about things like the word "blue" or the word "red" or the word "leather" or the word "yes" or the word "no." So let's talk about those.
In XML, any given "element" can have exactly 0 or 1 plain-text "value" (like "leather" or "blue") between the tags that show where its boundaries are.
The only other thing that can go "inside" the element besides its (optional) plain-text "value" is more elements.
Here's a really simple element with a plain-text value:
<Shape> Rectangle </Shape>
It's not common for the "outer-most" elements in an XML-formatted piece of data to have values--especially because valid XML always has just 1 outermost element. (If you don't care, you can just make up a name like "RootElement" for the tagset that holds all the "elements" you actually think of as your data.).
But even sometimes "2nd-outer-most" elements don't have values. Particularly when they represent some sort of abstract real-world object with a lot of complexity that you want to capture, they have 0 values but a lot of elements nested inside them.
Although you could describe a fleet of cars like this:
<RootElement> <Car> First car's Vehicle Identification Number here </Car> <Car> Second car's Vehicle Identification Number here </Car> </RootElement>
The above code implies that the conceptual "items" that you've given names of "car" truly are their Vehicle Identification Numbers. Yet they're really not, are they? They're heavy chunks of steel taking up space in the real world. There isn't really a plain-text value that captures what they are. Therefore, you won't really see a lot of XML like that. Although the word "Car" is, technically, a "key" to each "element's" "value," in this case, it doesn't quite make sense to give "Car" a "value."
Here's a more realistic way of writing the data, using nested elements to show that each car has 1 "key" of "VIN" and that the "value" for that "VIN" key is filled in on both cars:
<RootElement> <Car> <VIN> First car's Vehicle Identification Number here </VIN> </Car> <Car> <VIN> Second car's Vehicle Identification Number here </VIN> </Car> </RootElement>
Here's another realistic way of expressing the same idea, only using attributes to show each car's key & values:
<RootElement> <Car VIN="First car's Vehicle Identification Number here"> </Car> <Car VIN="Second car's Vehicle Identification Number here"> </Car> </RootElement>
Or, for short (using attributes):
<RootElement> <Car VIN="First car's Vehicle Identification Number here"/> <Car VIN="Second car's Vehicle Identification Number here"/> </RootElement>
In this little example, we're actually just dealing with multiple conceptual "items," each of which has the exact same keys as each other, and which has just 1 value per key, so remember that table-style (CSV) data could've easily represented the same data -- in this case, we've just got a 1-column CSV file:
"VIN" "First car's Vehicle Identification Number here" "Second car's Vehicle Identification Number here"
I digress -- but it's good to recognize what's going on in your data, and which types of plain-text files are capable of representing it.
Going back to our shirt example, let's say that our data set includes just 1 shirt, that it's "blue and red and green" and made out of "leather and cotton" and that the leather isn't washable but the cotton is.
Our XML representation of our data might look like this:
<Shirt> <Color> Blue </Color> <Color> Red </Color> <Color> Green </Color> <Fabric> Leather <Washable> No </Washable> </Fabric> <Fabric> Cotton <Washable> Yes </Washable> </Fabric> </Shirt>
There isn't really a good way to represent that concept of what traits the shirt possesses in a single row of a CSV file, is there? This is where XML and JSON shine!
Read the XML above carefully. What you have is:
- 1 element with a name of "shirt" that has 0 plain-text "values," but has 5 more elements nested inside of it
- 3 elements with a name of "color" (nested inside the one named "shirt"), none of which have any elements nested inside of them, but each of which have 1 plain-text value
- 2 elements with a name of "fabric" (nested inside the one named "shirt"), each of which have exactly 1 plain-text value, and each of which also have 1 more element nested inside of them
- 2 elements with a name of "washable" (nested inside various ones named "fabric"), none of which have any elements nested inside of them, but each of which have 1 plain-text value
There also isn't really a good way to represent this shirt using "attributes" on the "shirt" tagset (because it has multiple colors and multiple fabrics, and because the fabrics have nested elements of their own). However, since each "fabric" only has exactly 1 "washable" key & value, you could use attributes for that as follows:
<Shirt> <Color> Blue </Color> <Color> Red </Color> <Color> Green </Color> <Fabric Washable="No"> Leather </Fabric> <Fabric Washable="Yes"> Cotton </Fabric> </Shirt>
The choice is up to you, depending on which way you think it's easier to fetch/modify the values using code and which way you think it's easier for humans to read.
Another choice that's up to you is whether "Leather" is what the fabric truly is (the way "blue" is an adjective and therefore describes what the color truly is), or whether the notion of a fabric is too fuzzy in the real world to capture in a single word (like with our car) and should've been a key-value pair with a key like "name."
It's the same choice we had to make when deciding whether a car was its VIN or whether it had a VIN.
Outer-ward elements representing complex concepts usually just have key-value pairs (like with our car or our shirt examples).
For elements at "deeper" levels of nesting, you'll need to decide whether they "are" something (the optional 1 plain-text value they get) or whether they merely "have" things (nested elements & attributes).
That's a judgment call for you to make based on how your data is going to be used. All organization involves judgment calls trading flexibility against simplicity.
When it comes to writing software to process XML someone else already wrote, it's good to be able to recognize which judgment call they made (because the programming-language commands for extracting the two styles of writing key-value pairs are different).
JSON
The punctuation that JSON uses to define the beginning and end of an "object" is a set of "curly braces." It looks like this:
{}
- Q: Hey waiddaminute -- that "object" doesn't have a name! I thought you said JSON "objects" and XML "elements" were pretty equivalent in terms of both being representations of conceptual "items" in the same style of organizing data!
- A: Similar. But not the same. Good catch. JSON doesn't give objects a name. Nor do they get an optional "plain-text value" standing apart from anything nested inside of them. JSON objects look pretty different from XML elements.
Also, you can't just put JSON objects back-to-back the way you can put XML elements back-to-back; this isn't valid JSON:
{}
{}
Instead, you have to put them inside square-brackets and separate them with commas (remember not to put a comma after the last one, since nothing comes next--easy copy/paste mistake when you're putting each one on its own line). This is how you show 2 JSON objects at the same level as each other:
[
{},
{}
]
Note that the line breaks and tabs, however, are still for human benefit only.
Also, you don't have to include them inside any sort of "RootElement" container. That right there is valid JSON.
But getting back to our "JSON objects don't have names" problem ... what's the equivalent of this XML in JSON?
<Person/>
There isn't an exact translation, but one representation could be:
{
"type": "Person"
}
In other words, you're making up an "attribute" (a "key") for the JSON object, calling it "type," and giving it a "value" of "Person" (yup, JSON objects have attributes, and like XML element attributes, you can only use a given attribute-name once!) You could have called it anything -- "type" is nothing special.
Similarly, either this XML:
<Shirt> <Color></Color> <Fabric></Fabric> </Shirt>
Or this XML:
<Shirt Color="" Fabric=""></Shirt>
Might become this JSON:
{
"type" : "Shirt",
"Color" : null,
"Fabric" : null
}
(Though, getting back to that thing I mentioned earlier about whether an empty tagset is somehow "emptier" than an empty attribute set of quotes, you might argue that the values of this JSON object's "Color" & "Fabric" attributes/keys should be two quotes in a row, rather than the special keyword 'null'. More than I want to get into right now, but software than processes JSON would see the two differently -- null is emptier than the empty-quotes.)
The biggest difference from XML that the lack of names in JSON introduces is this notion of having to make up your own keyword for the name if you really think it needs a name.
Also, as far as how-to-type-JSON, "attribute" names & values on a JSON object are separated from each other with a colon, and there needs to be a comma between attribute name-and-value pairs. (Again, don't forget not to put a comma after the last attribute name-value pair!)
Furthermore, attribute names are often inside quotes in JSON, and the value can be something that doesn't have quotes around it (we haven't gotten there yet).
Sometimes your data might not need names. If you have a bunch of conceptual "items" back-to-back at the same level of nesting, and none of them need a "plain-text value" representing what they truly are, and all of them just have key-value pairs describing what they "have," JSON is a lot shorter to type. (Especially if you take out all the tabs & line breaks I'm putting in to make this blog readable. JSON authors love to take out line breaks & tabs -- if you run into such code, paste it here and click "Beautify" to read it more easily ... just make sure the "code" you're punching in isn't confidential company information!) Consider this example.
Here's some XML representing a fleet of cars, each of which have different sets of key-value traits we care about tracking (we'll use "attribute" style and "tagset-with-nothing-inside shorthand here), but all of which are cars.
<RootElement> <Car color="blue" trim="chrome" trunk="hatchback"/> <Car appeal="sporty" doors="2"/> <Car doors="4" color="red" make="Ford"/> </RootElement>
Maybe we already know they're all cars based on the context of our data. A JSON equivalent, without forcing each one to have a silly attribute like "type" (with a value of "car"), could be:
[
{
"color" : "blue",
"trim" : "chrome",
"trunk" : "hatchback"
},
{
"appeal" : "sporty",
"doors" : "2"
},
{
"doors" : "4",
"color" : "red",
"make" : "Ford"
}
]
So far, JSON doesn't look much more concise. But that note I made about "attribute values in JSON don't have to be in double-quotes" earlier is where its power really lies. Let's take a look at a shirt with two colors (blue, red) and 1 fabric (nylon). Here's the XML:
<Shirt> <Color>blue</Color> <Color>red</Color> <Fabric>nylon</Fabric> </Shirt>
And here's some similar JSON (note that I didn't bother to force it to be called "shirt" and that I decided that saying "colors" would make more sense than "color"):
{
"Colors" : ["blue","red"],
"Fabric" : "nylon"
}
We're using the same square-brackets-and-commas notation to make a list out of "blue" and "red" that we used to put a bunch of JSON objects together into a data set full of cars. The entirety of the set of brackets is the value of this JSON object's attribute/key called "colors."
As you can see, XML and JSON get pretty different when it comes to writing down the fact that a conceptual "item" in your data set has multiple "keys" all with the same name, each with a different value.
- XML works like I just described it (multiple key-pair values, where the key names are the same as each other). JSON doesn't allow that. That's something special to XML. If you like that about XML, there you go--use XML. :-)
- In JSON, you just give your object one "key" (maybe making the name of that "key" a plural noun) and you shove all those values into a list.
So, to recap the kinds of "value" you can give an attribute/"key" belonging to a JSON "object" (conceptual item):
- We've seen that the "value" for a given attribute/"key" of a JSON object can be a piece of plain text (put it between double-quotes).
- We've seen it be the keyword 'null' (no quotes around it).
- We've seen it be a list (square-brackets, with commas separating multiple values).
Note that you can certainly just have one value inside the square brackets, or they can even be empty if the list is empty. Just keep in mind that it's still a list of values to a computer, even if it's empty or a list of size 1. - We're about to see that it can be another JSON "object" (we're about to nest things, just like we did in XML!)
Let's go back to our data set that includes just 1 shirt, which is "blue and red and green" and made out of "leather and cotton," where the leather isn't washable but the cotton is.
A JSON representation of our data might look like this:
{
"type" : "Shirt",
"colors" : ["blue","red","green"],
"fabrics" :
[
{
"type" : "Leather",
"washable" : "No"
},
{
"type" : "Cotton",
"washable" : "Yes"
}
]
}
As a reminder, here was a short XML version of the same shirt:
<Shirt> <Color> Blue </Color> <Color> Red </Color> <Color> Green </Color> <Fabric Washable="No"> Leather </Fabric> <Fabric Washable="Yes"> Cotton </Fabric> </Shirt>
What I notice the most is:
- JSON is awkward when conceptual "items" in our data need to have attributes and a meaningful name of their own (like "shirt" and "leather" and "cotton")
- XML is overly wordy by making conceptual "items" out of things that already were true "attributes" of what we humans really think of as our data's conceptual "items" (like "color" -- we're probably not really thinking of it as an "item" in our data set, because "color" is no more complicated than its value(s) -- it doesn't have any other traits about the color that we need to describe).
What's In It For You?
In the end, as a Salesforce administrator, what matters most is being able to recognize which kind of data the Salesforce servers have given you (or in which format the servers expect to receive data from you).
You don't exactly get to argue with Salesforce about which format they should have picked.
- When you're picking apart data Salesforce sent you, you just need to know how to "parse" the file to extract the data of interest to you.
- When you're composing a file to send Salesforce, you'll model it after an existing file they sent you (or technical documentation) to figure out exactly how they want you to arrange the details.
In future posts, we'll talk about writing Python code that can do both of these tasks.
- Understanding the relationship between the textual representation of the data and what it means will be crucial to those exercises.
- Understanding when you're looking at data that can be made "flat" and "consistent from one conceptual item in the data to the next" (that is, when you can interpret it like a table/CSV) and when you're looking at data that really doesn't have those characteristics is crucial as well. Remember how I "diverted" and pointed out that our fleet of cars with nothing but VINs could have just as easily been a 1-column CSV file? Ideally, you want to be able to make that kind of commentary, too, about entire XML/JSON files or about fragments of them.
Remember to play with the graphical viewers (XML, JSON)!
Hopefully, this blog post will help you with both tasks by better understanding what the example data says and how it's shaped when you see it.
Table of Contents
- Introduction & Table of Contents
- Before You Begin Programming
- Installing Python 3 on Windows
- First Scripts - Import/Export CSVs, Filter Out Rows
- Rename/Drop/Add Columns
- Recognizing Pandas DataFrames vs. Series (Important!)
- Fancier Row-Filtering and Data-Editing
- Combining Multiple Tables
- Filtering Rows By Maximum Date Per Group
- Introduction to XML and JSON
Wednesday, December 21, 2016
Python Miscellany - Exploring A 1-Million-Row Task Table Export
Mass-mailing-system e-mail histories have put us over our Salesforce data storage limits.
Various admin tools say it's the task table hogging all the space. Indeed, when I exported the entire tasks table as a CSV via Apex Data Loader, it was a gigabyte large on my hard drive. So this was obviously the table that we needed to delete some records out of (archiving their CSV-export values on a fileserver we own), but it sure wasn't going to be an efficient file to play with in Excel.
This post brings you along on some of the miscellaneous play I did with the file. I'll be describing output, rather than showing it to you, as this is real company data and I'd rather not take the time to clean it up with fake data for public display.
First, I executed the following statements:
import pandas
df = pandas.read_csv('C:\\example\\tasks.csv', encoding='latin1', dtype='object')
Then I commented out those lines (put a "#" sign at the beginning of each row) because that 2nd line of code had taken a LONG time to run, even in Python. I knew that Spyder, my "IDE" that came with "WinPython," picks up where the last code left off when you hit the "Run" button again, so in further experimentation, I was simply careful not to accidentally overwrite the value of "df" (that way, I could keep executing code against it).
The next thing I did was export the first 5 lines of DF to CSV, so they WOULD be openable in Excel, just in case I wanted to.
df.head().to_csv('C:\\example\\tasks-HEAD.csv', index=0, quoting=1)
I also checked the row count in my Task table, which is how I knew the CSV file had 1 million lines (I exported it yesterday and forgot the result from Apex Data Loader by today):
print(len(df.index))
Next, I interested myself in the subject headings, since I knew that Marketo & Pardot always started the Subject line of mass e-mails in distinctive ways. I just couldn't remember what those ways were.
For starters, I was curious how many distinct subject headings were to be found among the 50 million rows.
print(len(df['SUBJECT'].unique()))
7,000, it turns out.
Next, I wanted to skim through those 7,000 values and see if I could remember what the Marketo-related ones were called.
Just like filtering an Excel spreadsheet and clicking the down-arrow-icon that shows you all the unique values in alphabetical order gets a little useless when you have too many long, similar values, a Python output console isn't ideal for reading lots of data at once.
Therefore, I exported the data into a 7,000-line, 1-column CSV file (which would open easily & read well in a text editor like Notepad).
pandas.DataFrame(df['SUBJECT'].sort_values().unique()).to_csv('C:\\example\\task-uniquesubjects.csv', index=0, header=False)
Aha: Pardot used "Pardot List Email:" and Marketo used "Was Sent Email:" at the beginning of all subject lines in records that I considered candidates for deletion if they were over 2 years old.
(Note: if an error message comes up that says, "AttributeError: 'Series' object has no attribute 'sort_values'," your version of the Pandas plugin to Python is too old for the ".sort_values()" command against Series-typed data. Replace that part of the code with ".sort(inplace=False)".
I have two different business units willing to let me delete old e-mail copies, but they have different timeframes. One is willing to let me delete anything of theirs over 2 years old. The other is willing to let me delete anything of theirs over 1 year old. So now I needed to comb through the raw data matching my two subject heading patterns, looking for anything that would indicate business unit.
print(df[(df['SUBJECT'].str.startswith('Was Sent Email:') | df['SUBJECT'].str.startswith('Pardot List Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=2)))].head())
Unfortunately, nothing in any of the other columns gave me a hint which business unit had been responsible for sending the e-mail.
I knew that we were using Marketo long before Pardot ... if I just deleted Marketo outbound e-mails over 2 years old (the least common denominator between the two business units' decisions), how many rows would I be deleting?
print(len(df[(df['SUBJECT'].str.startswith('Was Sent Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=2)))]))
Hmmm. 85,000. Out of 1 million. Well ... not a bad start (about 85MB out of 1GB)...but I need to find more.
Come to think of it, how many is "both Pardot and Marketo, more than a year old," period?
print(len(df[(df['SUBJECT'].str.startswith('Was Sent Email:') | df['SUBJECT'].str.startswith('Pardot List Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=1)))]))
200,000. Out of 1 million. So now we're talking about getting rid of 200MB of data (the table is about 1GB), which is probably enough to buy us some time with Salesforce.
So our happy medium is somewhere in the middle, and we probably can't do much better than getting rid of 200MB of data given the business units' requests.
Maybe, though, it wouldn't be a bad idea to just start with the low-hanging fruit and get rid of that first 80MB of data or so (Marketo >2 years old).
Let's dive just a little deeper into what I think is "Marketo 2-Year-Old e-mail copies" to make absolutely sure that that's what they are.
First ... I when I printed the "head()" of Marketo e-mails, I noticed that there seemed to be some diversity in a custom field called "ACTIVITY_REPORT_DEPT__C." Let's see what we've got in there:
tempmksbj2yo = df[(df['SUBJECT'].str.startswith('Was Sent Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=2)))]
print(tempmksbj2yo['ACTIVITY_REPORT_DEPT__C'].unique())
"ERP API", "Marketo Sync," & "Student Worker
How many do we have of each?
tempmksbj2yo = df[(df['SUBJECT'].str.startswith('Was Sent Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=2)))]
print(tempmksbj2yo.groupby('ACTIVITY_REPORT_DEPT__C').size())
Good to know - there are just a few dozen rows that aren't "Marketo Sync." In future logic (as below), I'll further specify that a "Marketo" e-mail has this "Marketo Sync" trait.
Now let's take our records we want to delete from Salesforce and get them ready for doing so by exporting the raw data to CSV (for archiving on an on-premise file server) and by exporting the record IDs to a different CSV (for putting into "Apex Data Loader" as a DELETE operation). Let's also export to CSV what remains, to make it easier to pick up where we left off hunting for more deleteable records (this ".to_csv()" operation takes a LONG time to execute because it's a huge file!). And before doing all that (commented out the last line to keep it from running when first verifying this, since it takes so long to run), let's also verify whether the "remaining" file has the same size as the original file minus our "to delete" file.
mk2yologic = (df['SUBJECT'].str.startswith('Was Sent Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=2))) & (df['ACTIVITY_REPORT_DEPT__C'] == 'Marketo Sync')
mk2yo = df[mk2yologic]
remaining = df[~mk2yologic]
print('df size is ' + str(len(df.index)))
print('mk2yo size is ' + str(len(mk2yo.index)))
print('df - mk2yo size is ' + str(len(df.index) - len(mk2yo.index)))
print('remaining size is ' + str(len(remaining.index)))
mk2yo.to_csv('C:\\example\\task-marketo-2yo-or-more-deleting.csv', index=0, quoting=1)
mk2yo['ID'].to_csv('C:\\example\\task-marketo-2yo-or-more-idstodelete.csv', index=0, header=True)
remaining.to_csv('C:\\example\\task-remaining-after-removing-marketo-2yo-or-more.csv', index=0, quoting=1)
Next, it's on out of Python-land for a while, and into Apex trigger-editing (there's one from an AppExchange plugin that it helps to disable when deleting this many Tasks) and using the "Apex Data Loader" to execute the DELETE operation.
P.S. That didn't help enough. You know where I found another problem? E-mails that went out to a business unit's entire mailing list. 1 task per recipient per e-mail. Here's how I came up with a list of the (10) Subject-ActivityDate combinations that had the largest number of records:
rmn = df[~((df['SUBJECT'].str.startswith('Was Sent Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=1))) & (df['ACTIVITY_REPORT_DEPT__C'] == 'Marketo Sync'))]
print(rmn.groupby(['SUBJECT','ACTIVITYDATE']).size().order(ascending=True).reset_index(name='count').query('count>10000'))
Those 10 mailings represent 250,000 of our 1 million "Task" records, as verified here:
rmn = df[~((df['SUBJECT'].str.startswith('Was Sent Email:')) & (pandas.to_datetime(df['ACTIVITYDATE']) < (pandas.to_datetime('today')-pandas.DateOffset(years=1))) & (df['ACTIVITY_REPORT_DEPT__C'] == 'Marketo Sync'))]
print(rmn.groupby(['SUBJECT','ACTIVITYDATE']).size().order(ascending=True).reset_index(name='count').query('count>10000')['count'].sum())
Time to talk with some business users about whether this data really needs to live in Salesforce, even though it's recent...
Thursday, December 8, 2016
Python for Salesforce Administrators - Filtering Rows By Maximum Date Per Group
I had a special request to show off Python for filtering a CSV file to only leave behind the "most recent activity" for a given "Account" record. Happy to oblige!
However, I can't explain as well as I've been doing in other examples exactly what's going on here. It's a bit of black magic to me, but it works. Follow along.
Preparing A CSV
Now I'm going to open Notepad and make myself a CSV file (when I do File->Save As, change the "Save as File Type" from .TXT to "All Files (*.*)") and save the file as "c:\tempexamples\sample3.csv".
Here's what that file contains (representing 5 columns worth of fields & 7 rows worth of data):
"Actv Id","Actv Subject","Acct Name","Contact Name","Actv Date" "02848v","Sent email","Costco","James Brown","10/9/2014" "vsd8923j","Phone call","Gallaudet University","Maria Hernandez","3/8/2016" "3289vd09","Sent email","United States Congress","Tamika Page","7/9/2016" "das90","Lunch appointment","United States Congress","Tamika Page","3/4/2015" "vad0923","Sent email","Salesforce","Leslie Andrews","4/28/2013" "dc89a","Phone call","Costco","Sheryl Larson","5/29/2016" "adf8o32","Conference invitation","Salesforce","Leslie Andrews","2/7/2015" "fa9s3","Breakfast appointment","Costco","James Brown","9/3/2014" "938fk3","Phone call","United States Congress","Shirley Chisholm","7/9/2016"
If I run this code (note that I've got a special "read_csv" line that specifies to treat 'Actv Date' as a sortable date):
import pandas
df = pandas.read_csv('C:\\tempexamples\\sample3.csv', dtype=object, parse_dates=['Actv Date'])
print(df)
The output looks like this:
Actv Id Actv Subject Acct Name Contact Name Actv Date
0 02848v Sent email Costco James Brown 2014-10-09
1 vsd8923j Phone call Gallaudet University Maria Hernandez 2016-03-08
2 3289vd09 Sent email United States Congress Tamika Page 2016-07-09
3 das90 Lunch appointment United States Congress Tamika Page 2015-03-04
4 vad0923 Sent email Salesforce Leslie Andrews 2013-04-28
5 dc89a Phone call Costco Sheryl Larson 2016-05-29
6 adf8o32 Conference invitation Salesforce Leslie Andrews 2015-02-07
7 fa9s3 Breakfast appointment Costco James Brown 2014-09-03
8 938fk3 Phone call United States Congress Shirley Chisholm 2016-07-09
Grouping and Filtering
Firstly, I'm going to show you the final code.
import pandas
df = pandas.read_csv('C:\\tempexamples\\sample3.csv', dtype=object, parse_dates=['Actv Date'])
groupingByAcctName = df.groupby('Acct Name')
groupedDataFrame = groupingByAcctName.apply(lambda x: x[x['Actv Date'] == x['Actv Date'].max()])
outputdf = groupedDataFrame.reset_index(drop=True)
print(outputdf)
The output looks like this:
Actv Id Actv Subject Acct Name Contact Name Actv Date
0 dc89a Phone call Costco Sheryl Larson 2016-05-29
1 vsd8923j Phone call Gallaudet University Maria Hernandez 2016-03-08
2 adf8o32 Conference invitation Salesforce Leslie Andrews 2015-02-07
3 3289vd09 Sent email United States Congress Tamika Page 2016-07-09
4 938fk3 Phone call United States Congress Shirley Chisholm 2016-07-09
(Exporting back to CSV instructions here, under the 5th program, "Writing To CSV.")
Note that "United States Congress" still has 2 records, because two things happened on the same date. This particular script I gave you doesn't "toss a coin" and break the tie between e-mailing Tamika and calling Shirley. It just leaves both "most recent" records in place.
(See the end of this post for tie-breaking.)
I can spot-check that it works because when line 4 ends with "max()" (as in most-recent activity), the result for Costco is Sheryl Larson's activity in May 2016. If I change it to "min()" (as in least-recent activity), the result for Costco is James Brown's activity in September 2014.
Variation - Multi-Column Group
If I change ".groupby('Acct Name')" at the end of line 3 in the previous example to ".groupby(['Acct Name', 'Contact Name'])" and leave line 4 as "max," I get the following output (note 2 Costco rows - one for James, one for Sheryl, but with James's October 2014 activity as his most recent):
Actv Id Actv Subject Acct Name Contact Name Actv Date
0 02848v Sent email Costco James Brown 2014-10-09
1 dc89a Phone call Costco Sheryl Larson 2016-05-29
2 vsd8923j Phone call Gallaudet University Maria Hernandez 2016-03-08
3 adf8o32 Conference invitation Salesforce Leslie Andrews 2015-02-07
4 938fk3 Phone call United States Congress Shirley Chisholm 2016-07-09
5 3289vd09 Sent email United States Congress Tamika Page 2016-07-09
What's Really Going On?
Line 3, "df.groupby('Acct Name')," produces data that we can save with a nickname ("groupingByAcctName"), but that doesn't really print well (try it - try adding a "print(groupingByAcctName)" line to the script). However, trying to print it does tell us that it's a "type" of data called a "DataFrameGroupBy." (Just like there are "types" of data called "DataFrames" & "Series.")
Line 4 applies a "lambda" function to our cryptic data that we stored under the nickname "groupingByAcctName."
The output, which we give a nickname of "groupedDataFrame," is just a "DataFrame." (I can tell by running the line "print(type(groupedDataFrame))".)
We've seen the ".apply(lambda ...)" operation before, in "Fancier Row-Filtering and Data-Editing."
It turns out that it can also be tacked onto the end of "DataFrameGroupBy"-typed data, not just "Series"-typed data.
When ".apply(lambda ...)" is tacked onto Series-type data, it does whatever's inside the "lambda ..." to each item in the Series and spits out a new Series of the same length with contents that have been transformed accordingly.
A DataFrameGroupBy can be thought of kind of like a "Series that holds DataFrames" (each mini-DataFrame being a row of the original DataFrame that falls into the "group").
When ".apply(lambda ...)" is tacked onto DataFrameGroupBy-type data, it does whatever's inside the "lambda ..." to each DataFrame in the DataFrameGroupBy and spits out a new DataFrame that jams the results back together (although, as we'll see later, the labeling is a bit complex).
In this case, there were 4 unique 'Acct Name' values. What we did to each mini-DataFrame inside was say, "Just show the rows of this mini-DataFrame that have a maximal 'Actv Date.' One 'Acct Name' had a tie on 'Actv Date,' so we actually ended up with 5 rows in the output DataFrame (which we then stored under the nickname "groupedDataFrame").
Our "CodeThatMakesAValueGoHere" was "x[x['Actv Date'] == x['Actv Date'].max()]"
What's going here is that "x" is a DataFrame.
"lambda x : ..." means, "Use 'x' as a placeholder for each of whatever is in the thing we're running this '.apply(lambda ...)' on."
If we were to use our original straight-from-the-CSV DataFrame and say "print(df[df['Actv Date'] == df['Actv Date'].max()])" we would get 2 rows: the tie for "most recent activity in the whole original DataFrame" (the July 10, 2016 activities).
However, now we're saying "Do the same thing, but within each of the mini-DataFrames that have already been broken up into unique 'Actv Date' clumps."
This code:
import pandas
df = pandas.read_csv('C:\\tempexamples\\sample3.csv', dtype=object, parse_dates=['Actv Date'])
groupingByAcctName = df.groupby('Acct Name')
groupedDataFrame = groupingByAcctName.apply(lambda x: x[x['Actv Date'] == x['Actv Date'].max()])
print(groupedDataFrame)
Produces this output:
Actv Id Actv Subject Acct Name Contact Name Actv Date
Acct Name
Costco 5 dc89a Phone call Costco Sheryl Larson 2016-05-29
Gallaudet University 1 vsd8923j Phone call Gallaudet University Maria Hernandez 2016-03-08
Salesforce 6 adf8o32 Conference invitation Salesforce Leslie Andrews 2015-02-07
United States Congress 2 3289vd09 Sent email United States Congress Tamika Page 2016-07-09
8 938fk3 Phone call United States Congress Shirley Chisholm 2016-07-09
Here we have something with "rows" and "columns," with its rows "numbered" by Pandas - it is indeed a DataFrame, even though it's got some weird groupings and gaps. (Although note how the row-numbers have been preserved from the original DataFrame! And note how to the left of the numbers there are labels that seem to group the numbers ... I said that DataFrame row-numbering was "complicated," didn't I?)
In case you're curious, here's output from the same code, only with the "grouping by both account and contact name" variation mentioned earlier.
Actv Id Actv Subject Acct Name Contact Name Actv Date
Acct Name Contact Name
Costco James Brown 0 02848v Sent email Costco James Brown 2014-10-09
Sheryl Larson 5 dc89a Phone call Costco Sheryl Larson 2016-05-29
Gallaudet University Maria Hernandez 1 vsd8923j Phone call Gallaudet University Maria Hernandez 2016-03-08
Salesforce Leslie Andrews 6 adf8o32 Conference invitation Salesforce Leslie Andrews 2015-02-07
United States Congress Shirley Chisholm 8 938fk3 Phone call United States Congress Shirley Chisholm 2016-07-09
Tamika Page 2 3289vd09 Sent email United States Congress Tamika Page 2016-07-09
Before we can export our DataFrame into something that looks like our original CSV file (which is pretty much everything to the right of the row-numbers), we need to strip off that far-left "Acct Name" and the row-numbers, plus strip out the extra line between the column headers to the right and their data.
That's where the ".reset_index(drop=True)" command comes into play. Tacked onto a DataFrame, it produces a new DataFrame with all the row-numbers reset to "0, 1, 2..." and no labels to the left, plus no whitespace above the data. To make the code easier to read, I saved this new DataFrame as "outputdf."
And that's the end of the dirty details.
Breaking Ties
Afterthought: if you want to break ties so you have exactly 1 output row per 'Acct Name', here's how you do it:
- Figure out if there's anything else that's a "secondary tiebreaker."
- Once you run out of "secondary tiebreakers," pick an arbitrary single record with ".head(n=1)" or ".tail(n=1)" (depending on whether you want the first or the last record).
- Making a final tie-break of "Oh, just pick the first line" is a common pattern in data processing when your business requirements say it's vital not to allow any ties to come out of an aggregation.
We have to change our approach to designing our code a bit to accommodate these extra steps. Here's the pattern:
- Break our original DataFrame up into a "Series of DataFrames grouped by (whatever we're computing the Max/Min among)"
(a.k.a. a "DataFrameGroupBy")
The code for this is ".groupBy(...)" - Run an ".apply(lambda ...)" operation on the result of the previous step. For each mini-DataFrame being processed by the ".apply(lambda ...)" operation:
- Sort the rows of the mini-DataFrame first by the "primary sorter column," then by the "secondary sorter column," and so on (".sort_values(...)"). This produces a new mini-DataFrame with all the same rows, but sorted as specified.
(Note - "ascending" is the default sort for each column with ".sort_values(...)", but you can specify on a column-by-column basis. I've shown that in the example code, but you can leave the ", ascending = ..." code out if you simply want an "ascending" sort across the board.) - Produce a new mini-DataFrame that contains just the first/last row of the mini-DataFrame that came out of the previous step (depending on whether your sorting operation put the min/max you're looking for at the beginning or the end of the mini-DataFrame). The code for this is ".head(n=1)" or ".tail(n=1)".
- Sort the rows of the mini-DataFrame first by the "primary sorter column," then by the "secondary sorter column," and so on (".sort_values(...)"). This produces a new mini-DataFrame with all the same rows, but sorted as specified.
- Strip the extra labels off the resulting big DataFrame so that it's ready for export back to CSV (".reset_index(drop=True)")
Afterthought: if you want to break ties by LastModifiedDate, just do it all over again. I've created a new "sample4.csv" as an example:
"Actv Id","Actv Subject","Acct Name","Contact Name","Actv Date","Mod Date" "02848v","Sent email","Costco","James Brown","10/9/2014","10/10/2016 10:00:00" "vsd8923j","Phone call","Gallaudet University","Maria Hernandez","3/8/2016","4/13/2016 14:23:04" "3289vd09","Sent email","United States Congress","Tamika Page","7/9/2016","7/10/2016 9:35:36" "das90","Lunch appointment","United States Congress","Tamika Page","3/4/2015","3/5/2015 13:01:01" "vad0923","Sent email","Salesforce","Leslie Andrews","4/28/2013","9/30/2015 10:04:58" "dc89a","Phone call","Costco","Sheryl Larson","5/29/2016","6/1/2016 11:38:00" "adf8o32","Conference invitation","Salesforce","Leslie Andrews","2/7/2015","2/8/2015 08:36:00" "fa9s3","Breakfast appointment","Costco","James Brown","9/3/2014","9/4/2014 07:35:00" "938fk3","Phone call","United States Congress","Shirley Chisholm","7/9/2016","7/10/2016 08:34:20"
Here's some code to do this:
(Click here for playable sample...do not type your own company's data into this link!)
import pandas
df = pandas.read_csv('C:\\tempexamples\\sample4.csv', dtype=object, parse_dates=['Actv Date', 'Mod Date'])
groupingByAcctName = df.groupby('Acct Name')
groupedDataFrame = groupingByAcctName.apply(lambda x : x.sort_values(['Actv Date','Mod Date'], ascending=[True,True]).tail(n=1))
outputdf = groupedDataFrame .reset_index(drop=True)
print(outputdf)
Or a more concise version of the code, where I strung multiple lines together and didn't stop to give them nicknames (this form gets easier to read as your code gets longer):
import pandas
df = pandas.read_csv('C:\\tempexamples\\sample4.csv', dtype=object, parse_dates=['Actv Date', 'Mod Date'])
print(df.groupby('Acct Name').apply(lambda x : x.sort_values(['Actv Date','Mod Date'], ascending=[True,True]).tail(n=1)).reset_index(drop=True))
And here's the output text:
Actv Id Actv Subject Acct Name Contact Name Actv Date Mod Date
0 dc89a Phone call Costco Sheryl Larson 2016-05-29 2016-06-01 11:38:00
1 vsd8923j Phone call Gallaudet University Maria Hernandez 2016-03-08 2016-04-13 14:23:04
2 adf8o32 Conference invitation Salesforce Leslie Andrews 2015-02-07 2015-02-08 08:36:00
3 3289vd09 Sent email United States Congress Tamika Page 2016-07-09 2016-07-10 09:35:36
Again, I can spot-check it because the tie-break between the "United States Congress" is that Tamika's July 2016 activity record was modified about an hour after Shirley's.
- If I change "ascending=[True,True]" to "ascending=[True,False]" (sort 'Actv Date' ascending but 'Mod Date' descending, then pick the last record in the group) the tie-break goes to Shirley's record instead.
- Similarly, if I change "ascending=[True,True]" to "ascending=[False,True]" (sort 'Actv Date' descending but 'Mod Date' ascending, then pick the last record in the group), I'm saying I want the least recent 'Actv Date', but I still want ties broken by most recent 'Mod Date.'
- And if I change "ascending=[True,True]" to "ascending=[False,False]" (sort both dates descending, then pick the last record in the group), I'm saying I truly want the least recent record in the group.
(In this case, "[False,True]" & "[False,False]" produce the same results because I don't have any ties for "oldest activity in the group" on 'Actv Date' that need breaking by 'Mod Date'. Tamika's March 4, 2015 record is the oldest for Congress, etc.)
Table of Contents
- Introduction & Table of Contents
- Before You Begin Programming
- Installing Python 3 on Windows
- First Scripts - Import/Export CSVs, Filter Out Rows
- Rename/Drop/Add Columns
- Recognizing Pandas DataFrames vs. Series (Important!)
- Fancier Row-Filtering and Data-Editing
- Combining Multiple Tables
- Filtering Rows By Maximum Date Per Group
- Introduction to XML and JSON
Subscribe to:
Posts (Atom)