A look into Philadelphia’s Drugs Drunks and Prostitutes. The data is collected by the Philadelphia Police Department. Data can be accessed here https://www.opendataphilly.org/. Old CARTO was used to create visualizations. Click on static maps below for full size.

OpenDataPhilly segments this data by individual years. And its structure changed in 2020. If you are curious enough to download all the files, you can use this c# script to manage them.

DirectoryInfo d = new DirectoryInfo(@"C:\AA_Phillidelphia");
FileInfo [] Files = d.GetFiles("*.csv");
Dictionary dictCrime = new Dictionary();
string output = "";
foreach (FileInfo file in Files)
{
Console.WriteLine("*** " + file.FullName);
string cur_file = file.FullName;
int total = 0;
if (cur_file != @"C:\AA_Phillidelphia\2020.csv" && cur_file != @"C:\AA_Phillidelphia\2021.csv"
&& cur_file != @"C:\AA_Phillidelphia\2022.csv")
{
foreach (string line in System.IO.File.ReadLines(cur_file))
{
string[] parts = line.Split(',');
string crime = parts[10];
string date = parts[4];
string lat = parts[13];
string lon = parts[14];
if (crime.Trim().Contains("Public Drunkenness"))
{
output += date + "," + lat + "," + lon +"\n";
total += 1;
}
if (dictCrime.ContainsKey(crime))
{
int existing = dictCrime[crime];
existing += 1;
dictCrime[crime] = existing;
}
else
dictCrime.Add(crime, 1);
}
}
else
{
foreach (string line in System.IO.File.ReadLines(cur_file))
{
string[] parts = line.Split(',');
string date = parts[7];
string crime = parts[13];
string lat = parts[16];
string lon = parts[17];
if (crime.Trim().Contains("Public Drunkenness"))
{
output += date + "," + lat + "," + lon + "\n";
total += 1;
}
if (dictCrime.ContainsKey(crime))
{
int existing = dictCrime[crime];
existing += 1;
dictCrime[crime] = existing;
}
else
dictCrime.Add(crime, 1);
}
}
foreach (KeyValuePair item in dictCrime.OrderByDescending(key => key.Value))
{
// Console.WriteLine(item.Key + " . . . . . . . . . " + item.Value);
}
Console.WriteLine(total);
}
//save to C:\Airplanes\
StreamWriter streamWrite;
streamWrite = File.AppendText("C:\\Airplanes\\DRUNKS.csv");
streamWrite.WriteLine(output);
streamWrite.Close();