<commit_before># coding=utf8
import codecs
import os

def test(cursor):
	 
	# and `Partner Entity_Sum` is not null
	# cursor.execute("UPDATE flow SET `Exp / Imp`=trim(lower(`Exp / Imp`)), `Spe/Gen/Tot`=trim(lower(`Spe/Gen/Tot`))")
	# cursor.execute("UPDATE `Exp-Imp-Standard` SET `Exp / Imp`=trim(lower(`Exp / Imp`)), `Spe/Gen/Tot`=trim(lower(`Spe/Gen/Tot`))")

	for flow in ("flow_joined",) :
		cursor.execute("""SELECT group_concat(`ID`,';'),group_concat(`Flow`,';'),count(*),group_concat(`spegen`,';'),group_concat(`Reporting Entity_Original Name`,';'),reporting,group_concat(`Partner Entity_Original Name`,';'),partner,Yr,`expimp`,group_concat(`Source`,'|') 				
							from %s 
							WHERE Flow is not null
							GROUP BY Yr,expimp,reporting,partner HAVING count(*)>1
				
							"""%flow)
		lines=list(cursor)
		print len(lines)
		nb_spe_gen=0
		nb_dups=0
		reporting_spegen={}
		reporting_dups={}
		log=u'"ids","nb_dup","original_reportings","reporting","original_partner","partner","year","exp_imp","spe_gen","flows","nb_source","sources"\n'
		for ids,flows,n,group,o_r,r,o_p,p,y,e_i,s in lines:
			if group and "Gen" in group and "Spe" in group:
				nb_spe_gen+=n
				reporting_spegen[r]=reporting_spegen.get(r,0)+n
				spegen=False
			else:
				nb_dups+=n
				reporting_dups[r]=reporting_dups.get(r,0)+n
				spegen=True
			# count sources
			sources=set(s.split("|"))
			log+=u'"%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s"\n'%(ids,n,o_r,r,o_p,p,y,e_i,group,flows,len(sources),"|".join(sources))
		print "# %s"%flow
		print "## Spe/gen Dups"
		print "   number of spe/gen to clean %s"%nb_spe_gen
		print "   reporting countries with spe/gen dups :"
		for k,v in reporting_spegen.iteritems():
			print ("   %s: %s flows"%(k,v)).encode("UTF8")
		print "## dups"
		print "   number of dups to clean %s"%nb_dups
		print "   reporting countries with  dups :"<commit_msg>removed null flow filtering in testing duplicates, those must be filtered out<commit_after># coding=utf8
import codecs
import os

def test(cursor):
	 
	# and `Partner Entity_Sum` is not null
	# cursor.execute("UPDATE flow SET `Exp / Imp`=trim(lower(`Exp / Imp`)), `Spe/Gen/Tot`=trim(lower(`Spe/Gen/Tot`))")
	# cursor.execute("UPDATE `Exp-Imp-Standard` SET `Exp / Imp`=trim(lower(`Exp / Imp`)), `Spe/Gen/Tot`=trim(lower(`Spe/Gen/Tot`))")

	for flow in ("flow_joined",) :
		cursor.execute("""SELECT group_concat(`ID`,';'),group_concat(`Flow`,';'),count(*),group_concat(`spegen`,';'),group_concat(`Reporting Entity_Original Name`,';'),reporting,group_concat(`Partner Entity_Original Name`,';'),partner,Yr,`expimp`,group_concat(`Source`,'|') 				
							from %s 
							GROUP BY Yr,expimp,reporting,partner HAVING count(*)>1
				
							"""%flow)#WHERE Flow is not null
		lines=list(cursor)
		print len(lines)
		nb_spe_gen=0
		nb_dups=0
		reporting_spegen={}
		reporting_dups={}
		log=u'"ids","nb_dup","original_reportings","reporting","original_partner","partner","year","exp_imp","spe_gen","flows","nb_source","sources"\n'
		for ids,flows,n,group,o_r,r,o_p,p,y,e_i,s in lines:
			if group and "Gen" in group and "Spe" in group:
				nb_spe_gen+=n
				reporting_spegen[r]=reporting_spegen.get(r,0)+n
				spegen=False
			else:
				nb_dups+=n
				reporting_dups[r]=reporting_dups.get(r,0)+n
				spegen=True
			# count sources
			sources=set(s.split("|"))
			log+=u'"%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s"\n'%(ids,n,o_r,r,o_p,p,y,e_i,group,flows,len(sources),"|".join(sources))
		print "# %s"%flow
		print "## Spe/gen Dups"
		print "   number of spe/gen to clean %s"%nb_spe_gen
		print "   reporting countries with spe/gen dups :"
		for k,v in reporting_spegen.iteritems():
			print ("   %s: %s flows"%(k,v)).encode("UTF8")
		print "## dups"
		print "   number of dups to clean %s"%nb_dups
		print "   reporting countries with  dups :"
{'fraction_non_alphanumeric': 0.20369887991664495, 'fraction_numerical': 0.0036467830164105238, 'mean_word_length': 5.254071661237785, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5299784', 'n_tokens_mistral': 1661, 'n_tokens_neox': 1486, 'n_words': 258}