I have some data from an assignment for Lab 4 for GEO 410 Urban Geography.
In[]:=
mydata=ImportFile,"Dataset",HeaderLines->1
Out[]=
In[]:=
Query[GroupBy[#"RACE"&],ReverseSort@*CountsBy[#"BIRTHPLACE"&]][mydata]
Out[]=
What is the birthplace with the most residents for each race?
In[]:=
Query[GroupBy[#"RACE"&],TakeLargestBy[Identity,1]@*CountsBy[#"BIRTHPLACE"&]][mydata]
Out[]=
What is the birthplace with the least residents for each race?
In[]:=
Query[GroupBy[#"RACE"&],TakeSmallestBy[Identity,1]@*CountsBy[#"BIRTHPLACE"&]][mydata]
Out[]=
I’m trying to figure out how to get multiple values for the last one. There is a tie for no data.
In[]:=
Query[GroupBy[#"RACE"&],Values@*CountsBy[#"BIRTHPLACE"&]][mydata]
Out[]=
My code Query[GroupBy[#”RACE”&],TakeSmallestBy[Identity,1]@*CountsBy[#”BIRTHPLACE”&]][mydata] returns only one key but I want it to return all keys with the lowest value.
In[]:=
Query[GroupBy[#"RACE"&],Normal@*CountsBy[#"BIRTHPLACE"&]][mydata]["NO DATA"]//Normal
Out[]=
{CANADA1,VIRGINIA1,TEXAS4,IRELAND1,PENNSYLVANIA1,OHIO1}
I would like something this
In[]:=
Dataset[<|"NO DATA"-><|{"CANADA","VIRGINIA","IRELAND","PENNSYLVANIA","OHIO"}->1|>|>]
Out[]=
Instead of my output based on my solution with Query[GroupBy[#”RACE”&],TakeSmallestBy[Identity,1]@*CountsBy[#”BIRTHPLACE”&]][mydata].
In[]:=
Dataset[<|"NO DATA"-><|"CANADA"->1|>|>]
Out[]=
Here is what RankedMax and RankedMin returns. I’m not sure if there is something with tie breaking with TakeLargestBy.
In[]:=
Query[GroupBy[#"RACE"&],RankedMax[Values[#],1]&@*CountsBy[#"BIRTHPLACE"&]][mydata]
Out[]=
In[]:=
Query[GroupBy[#"RACE"&],RankedMin[Values[#],1]&@*CountsBy[#"BIRTHPLACE"&]][mydata]
Out[]=
In[]:=
Association[Query[GroupBy[#"RACE"&],Normal@*CountsBy[#"BIRTHPLACE"&]][mydata]["NO DATA"]//Normal]
Out[]=
CANADA1,VIRGINIA1,TEXAS4,IRELAND1,PENNSYLVANIA1,OHIO1
In[]:=
Map[<|"count"->#|>&][Association[Query[GroupBy[#"RACE"&],Normal@*CountsBy[#"BIRTHPLACE"&]][mydata]["NO DATA"]//Normal]]
Out[]=
CANADAcount1,VIRGINIAcount1,TEXAScount4,IRELANDcount1,PENNSYLVANIAcount1,OHIOcount1
In[]:=
SortBy[#count&][Map[<|"count"->#|>&][Association[Query[GroupBy[#"RACE"&],Normal@*CountsBy[#"BIRTHPLACE"&]][mydata]["NO DATA"]//Normal]]]
Out[]=
CANADAcount1,IRELANDcount1,OHIOcount1,PENNSYLVANIAcount1,VIRGINIAcount1,TEXAScount4
In[]:=
GroupBy[#count&][Map[<|"count"->#|>&][Association[Query[GroupBy[#"RACE"&],Normal@*CountsBy[#"BIRTHPLACE"&]][mydata]["NO DATA"]//Normal]]]
Out[]=
1CANADAcount1,VIRGINIAcount1,IRELANDcount1,PENNSYLVANIAcount1,OHIOcount1,4TEXAScount4
The primary key would be race, birthplace. This could be put in first normal form by using an integer valued id. The table above is not in first normal form. This table is in first normal form with a key.
The minimum is not necessarily unique. For example in the list {1,2,-1} by absolute value there are 2 minimums, 1 and -1. This means minimum is a many to one operation. This was one of the complexities in designing the PositionSmallest and PositionLargest function.