Robert Vesco

Using R to Extract Excel/csv File of Citation Data from Zotero

Robert Vesco — Fri, 15 Jul 2011 12:57:19 +0000

Zotero is a handy piece of software used to manage citations. However, one of issues I have with it is that there is no convenient way to export citation into excel. There are some folks that have put together some solutions here, here, and here, but they haven't been able to accomplish what I needed. Essentially, want I wanted was a way to review my citations, abstracts and a few other details easily. Two things about my solution. One it uses R, so you probably want to have some experience using that software, and two, you may want to have Zotero closed or make a copy of the sqlite file. Sometimes there are locking issues with the database if you try to access it from two different ways at the same time.


# Purpose: To extract zotero citation data into excel.
# Version: 1.0
# Platform: Written on R 2.11, Win 7, Zotero 2.01, but should work on all platforms.
###############################################################################

############################
## The following has variables you need to replace for your setup
##########################

# Replace with the path to your Zotero sqlite file
dbZot <- "C:\\path\\to\\your\\zotero\\zotero-current.sqlite"

# Put a character of the top level folders you want to extract
# This will include subfolders as well
vTopFolders <- c("TopFolder1","TopFolder2")

##########################

#libraries
require(sqldf)
require(gdata)
require(reshape2)
require(reshape)

# SQL query that gets basic data.
sqlC <- "SELECT
items.itemID,
collections.collectionID,
collections.collectionName,
collections.parentCollectionID,
fields.fieldName,
itemDataValues.value,
creatorData.firstName,
creatorData.lastName,
itemNotes.title,
itemNotes.note
FROM
collectionItems
INNER JOIN items ON (collectionItems.itemID = items.itemID)
INNER JOIN collections ON (collectionItems.collectionID = collections.collectionID)
INNER JOIN itemCreators ON (items.itemID = itemCreators.itemID)
INNER JOIN itemData ON (itemData.itemID = items.itemID)
INNER JOIN itemDataValues ON (itemData.valueID = itemDataValues.valueID)
LEFT OUTER JOIN itemNotes ON (items.itemID = itemNotes.sourceItemID)
INNER JOIN creators ON (itemCreators.creatorID = creators.creatorID)
INNER JOIN creatorData ON (creators.creatorDataID = creatorData.creatorDataID)
INNER JOIN fields ON (itemData.fieldID = fields.fieldID)"

# This sends query to sqlite db
# Notice bug, stringAsFactor=F results in 0 vars, but T with method="raw" gets desired outcome
dfData <- sqldf(sqlC,, stringsAsFactors = F, dbname=dbZot, method="raw")

#This filter the data from sqlite and extracts the ID of the top level folder
vParentID <- unique(dfData[dfData$collectionName %in% vTopFolders,"collectionID"])

#This extracts a mapping of parent-child IDs
dfParentChildID <-  unique(dfData[vParentID %in% dfData$parentCollectionID,c("itemID","collectionID","parentCollectionID","collectionName")])

# Self join
dfParentChildID2 <- merge(dfParentChildID,dfParentChildID,by.x=c("itemID","collectionID"),by.y=c("itemID","parentCollectionID"),all.x=TRUE)

# Creates a field that concat top folder - sub folder
dfParentChildID2 <- transform(dfParentChildID2, top.sub.folder  = paste(collectionName.x,collectionName.y,sep="-"))

# Creates a unique ID
dfParentChildID2$ID <- with(dfParentChildID2,paste(itemID,collectionID.1,collectionID,sep="-"))

# Creates another unique ID for the original dataset from sqlite
dfData$ID <- with(dfData,paste(itemID,collectionID,parentCollectionID,sep="-"))

# merge the datasset
dfData2 <- merge(dfData,dfParentChildID2,by=c("ID"),all=TRUE)

# Get rid of the NA row in data and extract some key variables
dfData3 <- dfData2[!is.na(dfData2$parentCollectionID.x),c("ID","top.sub.folder", "collectionName",
				"fieldName", "value", "firstName", "lastName", "title", "note")]

#rename vars to prevent name conflict later on
dfData3 <- rename.vars(dfData3, from="title",to="notetitle")

# now need to reshape data, but trick was how to aggregate text. Other functions caused issue, but
# max seems to work for some reason.
# This take the fieldName which is the field in zotero that has vars like author, year, etc
# we want to turn this into wide form so that they become columns
dfData4 <- cast(data = dfData3, ID + top.sub.folder + collectionName +
				firstName + lastName + notetitle + note ~ fieldName, value = "value", fun.aggregate=max)

# Make author last name only
dfData4$author <- with(dfData4, lastName)
# Alternative: both names dfData4$author <- with(dfData4, paste(lastName,firstName,sep=","))

# Create essential a group_concat on author names.
# TODO: Fix so that authors are reversed.
dfNames <- ddply(dfData4, .(ID), summarise, authors = paste(author,sep=";",collapse=";"))

# Merge new names back into dataset
dfData5 <- merge(dfData4,dfNames)

# Make the prior data frame unique and extract the vars of interest
dfData6 <- unique(dfData5[,c("ID", "top.sub.folder","authors","publicationTitle","title",
 "abstractNote", "bookTitle", "date", "notetitle", "note")])

# Extract the year from the data
dfData6$date <- substr(as.character(dfData6$date),1,4)

#order by top folder
dfData6 <- dfData6[order(dfData6$top.sub.folder),]

#convert all factor to character
dfData6 <- as.data.frame(lapply(dfData6,as.character),stringsAsFactors=F)

#Combine book-article so that I know what book an article or chapter came from.
dfData6$bookjournal <- with(dfData6, ifelse(is.na(bookTitle), title,paste(bookTitle,title,sep="::")))

# Drop unnecessary Var
dfData7 <- dfData6[,-7]

write.csv(dfData7,"Zotero Citations.csv")
# TODO: find some excel package that doesn't have 256 character limit because
# some abstracts are really long....

Top Words Used in Titles of Top Strategic Management Journals

Robert Vesco — Sat, 02 Apr 2011 19:19:51 +0000

The following table was created from citation data from Academy of Management Review (AMR), Academy of Management Journal (AMJ) , Administrative Science Quarterly (ASQ) , Management Science (MS), Organizational Science (OS) and the Strategic Management Journal (SMJ). The time period is approximately 2000-2008. There's a lot more I would have liked to have done, but alas. The table uses word counts instead of percentages and I didn't combine things like organization + organization(s).

Word Ranking	AMJ		AMR		ASQ		MS		OS		SMJ
1	performance	99	theory	51	organizational	27	performance	52	organizational	90	performance	161
2	effects	60	social	48	performance	19	knowledge	42	performance	59	firm	95
3	management	60	organizational	37	social	19	management	42	knowledge	43	strategic	73
4	organizational	57	corporate	29	organizations	18	strategic	36	learning	36	firms	65
5	role	48	organizations	28	corporate	14	development	31	firm	33	corporate	44
6	research	32	management	27	effects	14	learning	31	effects	32	effects	44
7	firm	29	model	24	network	14	market	31	organization	31	knowledge	44
8	knowledge	29	role	24	firms	13	model	27	role	29	strategy	44
9	social	29	perspective	23	dynamics	11	innovation	25	change	28	market	39
10	firms	25	firm	20	institutional	11	organizational	24	organizations	26	based	37
11	theory	25	research	20	networks	11	time	24	social	24	management	37
12	teams	23	institutional	19	change	10	decision	21	theory	24	competitive	35
13	change	22	knowledge	19	knowledge	10	effects	21	model	20	resource	32
14	behavior	21	change	17	market	10	firm	21	strategic	20	organizational	31
15	relationships	21	based	16	learning	9	based	20	firms	19	business	28
16	network	19	strategic	15	influence	8	dynamic	19	innovation	18	role	28
17	institutional	18	network	14	management	8	theory	18	networks	16	strategies	24
18	process	18	dynamics	13	business	7	strategies	17	based	15	innovation	20
19	corporate	17	organization	13	capital	7	approach	16	corporate	15	environmental	19

A Startup Visa That Works, But Loan Programs For MBAs That Don't

Robert Vesco — Sat, 19 Mar 2011 15:21:09 +0000

A lot has been said about the proposed start up visa legislation that would grant immigrants visas in exchange for starting certain types of businesses here in the US.

This is long overdue and a great development. Let's hope this legislation passes.

That said, there is another source of entrepreneurship that the US is also preventing and that source is MBA students who are saddled with enormous debt. A common comment heard in the hallways of MBA schools is "Yes, I would love to join or start a business, but my hundred plus grand in debt makes this impossible."

So what could be done about this problem?

Nothing, it's not a problem. Those who chose to get an MBA should have known the costs and constraints that it would impose. No need to encourage more moral hazard from MBA students....
Create loan forgiveness programs/scholarships like those offered by both schools and governments specifically for those MBAs (or undergrads) who decide to start or join a startup.
Create loan deferment programs.

While there are a few schools that are offering to repay part of the loan MBA students incur there could be more. And as far as I know, there is no government program that offers loan forgiveness for MBA students who decide to go the start up route. That said, I'm not too sure that would be such a good idea.

Alternatively, perhaps allowing MBA students to defer their MBA loans for a certain number of years would be a better idea. This would allow them to pursue their dream and see if it works out without the immediate fear of having to pay back their massive loans. If their venture doesn't work out, then it's back to the corporate world. They would still be accountable. Now, the government already offers deferment for economic hardships but it is a complicated process to get approval for (and maintain) and it lasts only up to three years. More importantly, it only covers, as far as I know, certain types of loans. Many of the private, non-federal loans, may not be covered. Thus, ideally there would a loan deferment program for anyone who decides to create a qualified startup regardless of where their loans come from.

Right now, there are many MBA students who would love to pursue their startup dreams but who feel their only choice is to work with established companies in order to pay off their loans. This is one more source of constraint to entrepreneurship that is holding this country back. While allowing immigrants to start businesses in this country in exchange for visas is a hugely important policy goal, I would argue that helping our MBAs start businesses would be a close second.

Webscraping with R

Robert Vesco — Wed, 09 Jun 2010 19:25:14 +0000

I'll be giving a talk at the DC Meetup group on "Webscraping with R" on June 24.

If you're interested in learning more about R or webscraping in general come join us.

http://www.meetup.com/R-users-DC/

Top Journals in Management and Strategy

Robert Vesco — Wed, 04 Nov 2009 03:39:55 +0000

Each business school, and each department/sub discipline within that school, has a set of journals that measure faculty performance. On the strategy side of the department of management and organization at U of Maryland's business school these six journals are considered A-level.

Academy of Management Review

The mission of the Academy of Management Review (AMR) is to publish new theoretical insights that advance our understanding of management and organizations. AMR is receptive to a variety of perspectives, including those seeking to improve the effectiveness of, as well as those critical of, management and organizations. Submissions to AMR must extend theory in ways that permit the development of testable knowledge-based claims. To do this, researchers can develop new management and organization theory, significantly challenge or clarify existing theory, synthesize recent advances and ideas into fresh, if not entirely new theory, or initiate a search for new theory by identifying and delineating a novel theoretical problem. The contributions of AMR articles often are grounded in "normal science disciplines" of economics, psychology, sociology, or social psychology as well as nontraditional perspectives, such as the humanities. AMR publishes novel, insightful and carefully crafted conceptual work that challenges conventional wisdom concerning all aspects of organizations and their roles in society.

Academy of Management Journal

The mission of the Academy of Management Journal is to publish empirical research that tests, extends, or builds management theory and contributes to management practice. All empirical methods -- including, but not limited to, qualitative, quantitative, field, laboratory, and combination methods -- are welcome. To be published in AMJ, a manuscript must make strong empirical and theoretical contributions and highlight the significance of those contributions to the management field. Thus, preference is given to submissions that test, extend, or build strong theoretical frameworks while empirically examining issues with high importance for management theory and practice. AMJ is not tied to any particular discipline, level of analysis, or national context.

Strategic Management Journal

The journal publishes original material concerned with all aspects of strategic management. It is devoted to the improvement and further development of the theory and practice of strategic management and it is designed to appeal to both practising managers and academics. Papers acceptable to an editorial board acting as referees are published. The journal also publishes communications in the form of research notes or comments from readers on published papers or current issues. Editorial comments and invited papers on practices and developments in strategic management appear from time to time as warranted by new developments. Overall, SMJ provides a communication forum for advancing strategic management theory and practice. Such major topics as strategic resource allocation; organization structure; leadership; entrepreneurship and organizational purpose; methods and techniques for evaluating and understanding competitive, technological, social, and political environments; planning processes; and strategic decision processes are included in the journal.

Administrative Science Quarterly

ASQ regularly publishes the best theoretical and empirical papers based on dissertations and on the evolving and new work of more established scholars. Look to ASQ for new work from young scholars with fresh views, opening new areas of inquiry, and from more seasoned scholars deepening earlier work and staking out new terrain.

ASQ publishes the best organizational theory papers from a number of disciplines, including organizational behavior and theory, sociology, psychology and social psychology, strategic management, economics, public administration, and industrial relations. Look to ASQ for work that transcends the bounds of particular disciplines to speak to a broad audience.

Organization Science

Organization Science is ranked among the top journals in management by the Social Science Citation Index in terms of impact and is widely recognized in the fields of strategy, management, and organization theory. Organization Science provides one umbrella for the publication of research from all over the world in fields such as organization theory, strategic management, sociology, economics, political science, history, information science, systems theory, communication theory, artificial intelligence, and psychology.

Management Science

Management Science is a scholarly journal that publishes scientific research into the practice of management. Our scope includes articles that address management issues with tools from foundational fields such as computer science, economics, mathematics, operations research, political science, psychology, sociology, and statistics, as well as cross-functional, multidisciplinary research that reflects the diversity of the management science professions. Our interest extends to managerial issues in diverse organizational forms, such as for-profit and nonprofit firms, private and public sector institutions, and formal and informal networks of individuals. We welcome theoretical, empirical, prescriptive, and descriptive contributions.

Creating Opportunities for Sustainable Entrepreneurship

Robert Vesco — Fri, 09 Oct 2009 03:54:37 +0000

Escaping the green prison: Entrepreneurship and the creation of opportunities for sustainable development (possibly gated) is a paper by Desirée F. Pacheco, Thomas J. Dean b, and David S. Payne soon to be published in the Journal of Business Venturing.

It's a fresh paper on the topic of sustainable entrepreneurship that looks at how and why entrepreneurs create changes in society. It's full of real life examples as well as some interesting theory. The emphasis of the paper is on how entrepreneurs themselves, and through business led non-profits, create change with little mention on the role of traditional environmental non-profits. This leaves the paper with the feeling that entrepreneurs have taken the lead role in creating changes in norms, property rights, and so on. I'm not so sure that's the case, but nevertheless, it's nice to read a paper that highlights the collective action of entrepreneurs rather than that of governments or non-profits.

Abstract:

While entrepreneurial activity has been an important force for social and ecological
sustainability; its ef?cacy is dependent upon the nature of market incentives. This limitation
is sometimes explained by the metaphor of the prisoner's dilemma, which we term the green
prison. In this prison, entrepreneurs are compelled to environmentally degrading behavior due
to the divergence between individual rewards and collective goals for sustainable
development. Entrepreneurs, however, can escape from the green prison by altering or
creating the institutions—norms, property rights, and legislation—that establish the incentives
of competitive games. We provide a variety of evidence of such entrepreneurial action and
discuss its implications for theory and practice.

What do We Know about Social Entrepreneurship: An Analysis of Empirical Research

Robert Vesco — Wed, 16 Sep 2009 05:25:02 +0000

What do We Know about Social Entrepreneurship: An Analysis of Empirical Research.

Worth checking out if you want a high-level overview of what's been done in this space and the various schools of thought.

Social Entrepreneurship Research

Robert Vesco — Sat, 12 Sep 2009 15:53:27 +0000

Looking for social entrepreneurship research online can be a challenge. There just doesn't seem to be that many good sources around. To aid in this issue, the University Network, formed by Ashoka, the Skoll Centre, and several other partners, combined their forces to create a resource hub around promoting social entrepreneurship. While there are no shortage of such organizations what caught my attention is that they've created a social entrepreneurship e-journal on SSRN. These are the top categories they've setup so far:

Social Entrepreneurship

Unfortunately, SSRN still doesn't have RSS feeds setup for it's journals, only for specific authors, so you still need to subscribe to their emails to get the latest updates. This is a shame because it makes it harder to sift through research and promote research outside of SSRN. On the upside, you can now comment on SSRN articles.

Dandelions, Institutions, and the Power of the Poor

Robert Vesco — Thu, 10 Sep 2009 23:50:07 +0000

If tomorrow we repealed every tax, respected every property right, and removed all the constraints that bind entrepreneurship and human initiative -- there would still be no guarantee that the poor would realize their power and achieve prosperity. This is because cultural institutions like family, media, schools, and religion are ultimately the true drivers of growth. They are what inspire people to take risks, overcome obstacles, and succeed.

If a country's cultural institutions revere great football players like Pele, then you will get millions of children aspiring to be the next football star. If a country's cultural institutions stress engineers and doctors as the ultimate achievement -- you will get those. The problem is that football players may lift our spirits, engineers may build our bridges, and doctors may cure our ills, but those professions and role models alone cannot help the vast number of poor achieve power and prosperity.

I argue cultural institutions should put forward business development and entrepreneurship as the greatest achievements one can make. When children are asked: Who do you want to be when you grow up? They should say: I want to be an entrepreneur. I want to start my own business, invent the next great thing and employ thousands of people! If this were the dream of every would-be Pele today, tomorrow poverty would begin to appear like polio - largely eradicated. But ask yourselves -- is this what we have today? How many movies, TV shows, or even teachers for that matter, speak highly of business people and entrepreneurship? I think it is no coincidence that by and large my Chinese and Indian friends are aspiring business people and my Western friends are aspiring aid workers and government types. These aspirations are the true leading indicators of future GDP.

Some folks are like dandelions flowers. No matter how thick the concrete or onerous the environment they can break through the barriers. And if they can’t, they will figure out a way how to get around the constraints to power and prosperity. For hundreds of years America was the beneficiary of these dandelions, but this trend is neither sustainable nor desirable. So while changes in cultural institutions are necessary, they are not sufficient. Property rights and legal institutions are needed to achieve the widespread prosperity our species deserves and for which the poor have still been denied. Brazil, China, and India are all examples of countries that improved their cultural institutions while simultaneously improving their economic and legal institutions and because of this, not only do millions of their own poor see increasing opportunities to escape poverty, but even the dandelions that left years ago are returning.

To learn more about the constraints that hold back the poor from achieving power and prosperity, check out PBS’ The Power of the Poor which airs October 8, 10 PM ET (or check your local listing).

A Guide to Professors Teaching and Researching Social Entrepreneurship

Robert Vesco — Thu, 10 Sep 2009 00:29:54 +0000

Ashoka has a nice pdf listing of professors and institutions that teach and research social entrepreneurship.You can get it here