$media_url_count[$datepart][$url] = 1;
        } else {
            $media_url_count[$datepart][$url]++;
        }
    }
    mysql_free_result($sqlresults);
}
// write csv results
$filename = get_filename_for_export("mediaFrequency");
$csv = new CSV($filename, $outputformat);
$csv->writeheader(array('interval', 'media url', 'frequency'));
foreach ($media_url_count as $datepart => $url_count) {
    arsort($url_count);
    foreach ($url_count as $url => $count) {
        $csv->newrow();
        $csv->addfield($datepart);
        $csv->addfield($url);
        $csv->addfield($count);
        $csv->writerow();
    }
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your File</legend>';
echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>

    </body>
</html>
            } else {
                if (array_key_exists($word, $positiveSentiments) === false) {
                    $positiveSentiments[$word] = 0;
                }
                $positiveSentiments[$word]++;
            }
            $wordValues[$word] = $sentimentValue;
        }
    }
}
mysql_free_result($rec);
$csv->writeheader(array('word', 'count', 'sentistrength'));
arsort($positiveSentiments);
foreach ($positiveSentiments as $word => $val) {
    $csv->newrow();
    $csv->addfield($word);
    $csv->addfield($val);
    $csv->addfield($wordValues[$word]);
    $csv->writerow();
}
arsort($negativeSentiments);
foreach ($negativeSentiments as $word => $val) {
    $csv->newrow();
    $csv->addfield($word);
    $csv->addfield($val);
    $csv->addfield($wordValues[$word]);
    $csv->writerow();
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your File</legend>';
Example #3
0
        <?php 
validate_all_variables();
$filename = get_filename_for_export("hostHashtag");
$csv = new CSV($filename, $outputformat);
$collation = current_collation();
$sql = "SELECT COUNT(LOWER(h.text COLLATE {$collation})) AS frequency, LOWER(h.text COLLATE {$collation}) AS hashtag, u.domain COLLATE {$collation} AS domain FROM ";
$sql .= $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_hashtags h, " . $esc['mysql']['dataset'] . "_urls u ";
$where = "t.id = h.tweet_id AND h.tweet_id = u.tweet_id AND u.url_followed !='' AND ";
$sql .= sqlSubset($where);
$sql .= " GROUP BY u.domain COLLATE {$collation}, LOWER(h.text COLLATE {$collation}) ORDER BY frequency DESC";
//print $sql." - <br>";
$sqlresults = mysql_unbuffered_query($sql);
$csv->writeheader(array("frequency", "hashtag", "domain"));
while ($res = mysql_fetch_assoc($sqlresults)) {
    $csv->newrow();
    $csv->addfield($res['frequency']);
    $csv->addfield($res['hashtag']);
    $csv->addfield($res['domain']);
    $csv->writerow();
    $urlHashtags[$res['domain']][$res['hashtag']] = $res['frequency'];
}
mysql_free_result($sqlresults);
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your spreadsheet (CSV) file</legend>';
echo '<p><a href="' . str_replace("#", urlencode("#"), str_replace("\"", "%22", $filename)) . '">' . $filename . '</a></p>';
echo '</fieldset>';
$gexf = new Gexf();
$gexf->setTitle("URL-hashtag " . $filename);
$gexf->setEdgeType(GEXF_EDGE_UNDIRECTED);
$gexf->setCreator("tools.digitalmethods.net");
Example #4
0
        <?php 
validate_all_variables();
$collation = current_collation();
$filename = get_filename_for_export("urlUser");
$csv = new CSV($filename, $outputformat);
$sql = "SELECT COUNT(LOWER(t.from_user_name COLLATE {$collation})) AS frequency, LOWER(t.from_user_name COLLATE {$collation}) AS username, u.url_followed AS url, u.domain AS domain, u.error_code AS status_code FROM ";
$sql .= $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_urls u ";
$where = "t.id = u.tweet_id AND u.url_followed !='' AND ";
$sql .= sqlSubset($where);
$sql .= " GROUP BY u.url_followed, LOWER(t.from_user_name) ORDER BY frequency DESC";
$sqlresults = mysql_query($sql);
$csv->writeheader(array("frequency", "user", "url", "domain", "status_code"));
while ($res = mysql_fetch_assoc($sqlresults)) {
    $csv->newrow();
    $csv->addfield($res['frequency']);
    $csv->addfield($res['username']);
    $csv->addfield($res['url']);
    $csv->addfield($res['domain']);
    $csv->addfield($res['status_code']);
    $csv->writerow();
    $urlUsernames[$res['url']][$res['username']] = $res['frequency'];
    $urlDomain[$res['url']] = $res['domain'];
    $urlStatusCode[$res['url']] = $res['status_code'];
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your spreadsheet (CSV) file</legend>';
echo '<p><a href="' . str_replace("#", urlencode("#"), str_replace("\"", "%22", $filename)) . '">' . $filename . '</a></p>';
echo '</fieldset>';
$userUniqueUrls = array();
$langset = $esc['mysql']['dataset'] . '_lang';
$sql = "SELECT * FROM " . $esc['mysql']['dataset'] . "_tweets t inner join {$langset} l on t.id = l.tweet_id ";
$sql .= sqlSubset();
$sqlresults = mysql_query($sql);
$filename = get_filename_for_export("fullExportLang");
$csv = new CSV($filename, $outputformat);
$csv->writeheader(explode(',', $header));
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        $csv->newrow();
        if (preg_match("/_urls/", $sql)) {
            $id = $data['tweet_id'];
        } else {
            $id = $data['id'];
        }
        $csv->addfield($id);
        $csv->addfield(strtotime($data["created_at"]));
        $fields = array('created_at', 'from_user_name', 'from_user_lang', 'text', 'source', 'location', 'geo_lat', 'geo_lng', 'from_user_tweetcount', 'from_user_followercount', 'from_user_friendcount', 'from_user_realname', 'to_user_name', 'in_reply_to_status_id', 'quoted_status_id', 'from_user_listed', 'from_user_utcoffset', 'from_user_timezone', 'from_user_description', 'from_user_url', 'from_user_verified', 'filter_level');
        foreach ($fields as $f) {
            $csv->addfield(isset($data[$f]) ? $data[$f] : '');
        }
        $csv->addfield($data['name']);
        $csv->addfield($data['code']);
        $csv->addfield($data['reliable'] == true ? 1 : 0);
        $csv->addfield($data['bytes']);
        $csv->addfield($data['percent']);
        if (isset($_GET['includeUrls']) && $_GET['includeUrls'] == 1) {
            $urls = $expanded = $followed = $domain = "";
            $sql2 = "SELECT url, url_expanded, url_followed, domain FROM " . $esc['mysql']['dataset'] . "_urls WHERE tweet_id = " . $data['id'];
            $rec2 = mysql_query($sql2);
            if (mysql_num_rows($rec2) > 0) {
Example #6
0
mysql_query("set names utf8");
$sql = "SELECT id, text FROM " . $esc['mysql']['dataset'] . "_tweets t ";
$sql .= sqlSubset();
$sqlresults = mysql_unbuffered_query($sql);
$debug = '';
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        $text = $data["text"];
        preg_match_all('/(https?:\\/\\/[^\\s]+)|([\\p{L}][\\p{L}]+)/u', $text, $matches, PREG_PATTERN_ORDER);
        foreach ($matches[0] as $word) {
            if (preg_match('/(https?:\\/\\/)/u', $word)) {
                continue;
            }
            $word = strtolower($word);
            $csv->newrow();
            $csv->addfield(trim($word));
            $csv->addfield("{'ids': [" . $data['id'] . "]}");
            $csv->writerow();
        }
    }
    mysql_free_result($sqlresults);
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your File</legend>';
echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>

    </body>
</html>
Example #7
0
$sql .= sqlSubset();
$sql .= " AND h.tweet_id = t.id ";
$sql .= "GROUP BY datepart, h.from_user_name";
//print $sql . "<br>";
flush();
$rec = mysql_unbuffered_query($sql);
$tweetsWithhashtags = array();
while ($res = mysql_fetch_assoc($rec)) {
    $tweetsWithhashtags[$res['datepart']][$res['from_user_name']] = $res['count'];
}
mysql_free_result($rec);
$csv->writeheader(explode(',', "date,from_user_id,from_user_name,from_user_lang,from_user_tweetcount (all time user queries),from_user_followercount,from_user_friendcount,from_user_listed,from_user_utcoffset,from_user_verified,tweets in data set,retweets by user, mentioning,mentioned,total nr of hashtags,nr of tweets with hashtags"));
foreach ($array as $date => $user_array) {
    foreach ($user_array as $user => $a) {
        $csv->newrow();
        $csv->addfield($date);
        $csv->addfield($a["from_user_id"]);
        $csv->addfield($a["from_user_name"]);
        $csv->addfield($a["from_user_lang"]);
        $csv->addfield($a["from_user_tweetcount"]);
        $csv->addfield($a["from_user_followercount"]);
        $csv->addfield($a["from_user_friendcount"]);
        $csv->addfield($a["from_user_listed"]);
        $csv->addfield($a["from_user_utcoffset"]);
        $csv->addfield($a["from_user_verified"]);
        $csv->addfield($a["tweetcount"]);
        if (isset($retweets[$date][$user])) {
            $csv->addfield($retweets[$date][$user]);
        } else {
            $csv->addfield(0);
        }
Example #8
0
function generate($what, $filename)
{
    global $tsv, $network, $esc, $titles, $database, $interval, $outputformat;
    require_once __DIR__ . '/CSV.class.php';
    // initialize variables
    $tweets = $times = $from_user_names = $results = $urls = $urls_expanded = $hosts = $hashtags = array();
    $csv = new CSV($filename, $outputformat);
    $collation = current_collation();
    // determine interval
    $sql = "SELECT MIN(t.created_at) AS min, MAX(t.created_at) AS max FROM " . $esc['mysql']['dataset'] . "_tweets t ";
    $sql .= sqlSubset();
    //print $sql . "<bR>";
    $rec = mysql_query($sql);
    $res = mysql_fetch_assoc($rec);
    // get frequencies
    if ($what == "hashtag") {
        $results = frequencyTable("hashtags", "text");
    } elseif ($what == "urls") {
        $results = frequencyTable("urls", "url_followed");
    } elseif ($what == "hosts") {
        $results = frequencyTable("urls", "domain");
    } elseif ($what == "mention") {
        $results = frequencyTable("mentions", "to_user");
        // get other things
    } else {
        // @todo, this could also use database grouping
        $sql = "SELECT id,text COLLATE {$collation} as text,created_at,from_user_name COLLATE {$collation} as from_user_name FROM " . $esc['mysql']['dataset'] . "_tweets t ";
        $sql .= sqlSubset();
        // get slice and its min and max time
        $rec = mysql_query($sql);
        if ($rec && mysql_num_rows($rec) > 0) {
            while ($res = mysql_fetch_assoc($rec)) {
                $tweets[] = $res['text'];
                $ids[] = $res['id'];
                $times[] = $res['created_at'];
                $from_user_names[] = strtolower($res['from_user_name']);
            }
        }
        // extract desired things ($what) and group per interval
        foreach ($tweets as $key => $tweet) {
            $time = $times[$key];
            switch ($interval) {
                case "hourly":
                    $group = strftime("%Y-%m-%d %Hh", strtotime($time));
                    break;
                case "weekly":
                    $group = strftime("%Y %u", strtotime($time));
                    break;
                case "monthly":
                    $group = strftime("%Y-%m", strtotime($time));
                    break;
                case "yearly":
                    $group = strftime("%Y-%m", strtotime($time));
                    break;
                case "overall":
                    $group = "overall";
                    break;
                case "custom":
                    $group = groupByInterval(strftime("%Y-%m-%d", strtotime($time)));
                    break;
                default:
                    $group = strftime("%Y-%m-%d", strtotime($time));
                    // default daily
            }
            switch ($what) {
                //case "hashtag":
                //    foreach ($hashtags as $hashtag)
                //        $results[$group][] = $hashtag;
                //    break;
                //case "mention": // @todo, mentions might be taken from own table
                //    $stuff = get_replies($tweet);
                //    foreach ($stuff as $thing)
                //        $results[$group][] = $thing;
                //    break;
                case "user":
                    $results[$group][] = $from_user_names[$key];
                    break;
                case "user-mention":
                    $stuff = get_replies($tweet);
                    foreach ($stuff as $thing) {
                        $results[$group]['mentions'][] = $thing;
                    }
                    $results[$group]['users'][] = $from_user_names[$key];
                    //var_dump($results);
                    break;
                case "retweet":
                    $results[$group][] = $tweet;
                    // TODO, write stemming function
                    break;
                    //case "urls":
                    //    if (isset($urls_expanded[$ids[$key]]))
                    //        $results[$group][] = $urls_expanded[$ids[$key]];
                    //    break;
                    //case "hosts":
                    //    if (isset($urls_expanded[$ids[$key]]))
                    //        $results[$group][] = $hosts[$ids[$key]];
                    //    break;
                //case "urls":
                //    if (isset($urls_expanded[$ids[$key]]))
                //        $results[$group][] = $urls_expanded[$ids[$key]];
                //    break;
                //case "hosts":
                //    if (isset($urls_expanded[$ids[$key]]))
                //        $results[$group][] = $hosts[$ids[$key]];
                //    break;
                default:
                    break;
            }
        }
        // count frequency of occurence of thing, per interval
        if ($what != "user-mention") {
            foreach ($results as $group => $things) {
                $counted_things = array_count_values($things);
                arsort($counted_things);
                $results[$group] = $counted_things;
            }
        }
    }
    // network output for users
    if ($what == "user-mention") {
        foreach ($results as $group => $things) {
            $tmp_mentions = array_count_values($things['mentions']);
            $tmp_users = array_count_values($things['users']);
            $counted_things = array();
            // add all from_user_names
            foreach ($tmp_users as $user => $count) {
                if (isset($tmp_mentions["@" . $user])) {
                    $counted_things[$user] = $tmp_mentions["@" . $user] . "," . $count;
                } else {
                    $counted_things[$user] = "0," . $count;
                }
            }
            // add all users which were replied but not in the set
            foreach ($tmp_mentions as $user => $count) {
                $user = str_replace("@", "", $user);
                if (!isset($counted_things[$user])) {
                    $counted_things[$user] = $count . ",0";
                }
            }
            ksort($counted_things);
            $results[$group] = $counted_things;
        }
        if (isset($titles[$what])) {
            if (!empty($esc['shell']['query'])) {
                $q = " with search " . $esc['shell']['query'];
            } else {
                $q = "";
            }
            $csv->writeheader(array($titles[$what] . $q . " from " . $esc['date']["startdate"] . " to " . $esc['date']["enddate"]));
        }
        $csv->writeheader(array("date", "user", "mentions", "tweets"));
        foreach ($results as $group => $things) {
            foreach ($things as $thing => $count) {
                $csv->newrow();
                $csv->addfield($group);
                $csv->addfield($thing);
                $exp = explode(",", $count);
                // unpack what we packed
                $csv->addfield($exp[0]);
                $csv->addfield($exp[1]);
                $csv->writerow();
            }
        }
        // write tsv output
    } elseif (in_array($what, $tsv) !== false) {
        ksort($results);
        // construct file
        if (isset($titles[$what])) {
            if (!empty($esc['shell']['query'])) {
                $q = " with search " . $esc['shell']['query'];
            } else {
                $q = "";
            }
            $csv->writeheader(array($titles[$what] . " for " . $esc['shell']['datasetname'] . $q . " from " . $esc['date']["startdate"] . " to " . $esc['date']["enddate"]));
        }
        if ($what == "urls") {
            $csv->writeheader(array("date", "frequency", "tweetedurl"));
        } elseif ($what == "hosts") {
            $csv->writeheader(array("date", "frequency", "domain", "name"));
        } else {
            $csv->writeheader(array("date", "frequency", $what));
        }
        foreach ($results as $group => $things) {
            arsort($things);
            foreach ($things as $thing => $count) {
                if (empty($thing)) {
                    continue;
                }
                if ($count < $esc['shell']['minf']) {
                    continue;
                }
                $csv->newrow();
                $csv->addfield($group);
                $csv->addfield($count);
                $csv->addfield($thing);
                $csv->writerow();
            }
        }
    } else {
        die('no valid output format found');
    }
    $csv->close();
}
Example #9
0
        $fullresults[$res['datepart']] = array();
    }
    $fullresults[$res['datepart']]['totalphrases'] = $res['cnt'];
}
// Get the measured phrases per bin (query C)
$rec = mysql_query($sql_query_c);
while ($res = mysql_fetch_assoc($rec)) {
    if (!array_key_exists($res['datepart'], $fullresults)) {
        $fullresults[$res['datepart']] = array();
    }
    $fullresults[$res['datepart']]['measuredbin'] = $res['cnt'];
}
foreach ($dateparts as $datepart) {
    if (!array_key_exists($datepart, $fullresults)) {
        $csv->newrow();
        $csv->addfield($esc['mysql']['dataset']);
        $csv->addfield($datepart);
        $csv->addfield(-1);
        // report a minus 1 for a datepart with missing ratelimit information
        $csv->writerow();
    } else {
        $row = $fullresults[$datepart];
        if (!array_key_exists('ratelimited', $row) || !array_key_exists('measuredbin', $row) || !array_key_exists('totalphrases', $row)) {
            // TODO/TEST: this cannot occur I think
            continue;
        }
        // Now: calculate the estimate using our formula
        $estimate = round($row['ratelimited'] * $row['measuredbin'] / $row['totalphrases'], 2);
        $csv->newrow();
        $csv->addfield($esc['mysql']['dataset']);
        $csv->addfield($datepart);
while ($res = mysql_fetch_assoc($rec)) {
    if (!isset($hashtagMentions[$res['h1']][$res['u']])) {
        $hashtagMentions[$res['h1']][$res['u']] = 0;
    }
    $hashtagMentions[$res['h1']][$res['u']]++;
}
mysql_free_result($rec);
foreach ($hashtagMentions as $hashtag => $mentions) {
    $hashtagDistinctMentions[$hashtag] = count($mentions);
    $hashtagMentions[$hashtag] = array_sum($mentions);
}
// user-hashtag stats: hashtag, nr. of mentions, nr. of users participating (option: nr. of mentions)
$csv->writeheader(array("hashtag", "nr of tweets with hashtag", "distinct users for hashtag", "distinct mentions with hashtag", "total mentions with hashtag", "nr of tweets in selection", "nr of users in selection"));
foreach ($hashtagCount as $hashtag => $count) {
    $csv->newrow();
    $csv->addfield($hashtag);
    $csv->addfield($count);
    $csv->addfield($hashtagDistinctUsers[$hashtag]);
    $csv->addfield(isset($hashtagDistinctMentions[$hashtag]) ? $hashtagDistinctMentions[$hashtag] : 0);
    $csv->addfield(isset($hashtagMentions[$hashtag]) ? $hashtagMentions[$hashtag] : 0);
    $csv->addfield($nrOfTweets);
    $csv->addfield($nrOfUsers);
    $csv->writerow();
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your CSV File</legend>';
echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>
Example #11
0
    $sql .= "ORDER BY RAND() LIMIT " . $samplesize;
} else {
    $sql .= " ORDER BY id";
}
// loop over results and write to file
#print $sql."<br>";
$sqlresults = mysql_query($sql);
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        $csv->newrow();
        if (preg_match("/_urls/", $sql)) {
            $id = $data['tweet_id'];
        } else {
            $id = $data['id'];
        }
        $csv->addfield($id);
        $csv->addfield(strtotime($data["created_at"]));
        $fields = array('created_at', 'from_user_name', 'text', 'filter_level', 'possibly_sensitive', 'withheld_copyright', 'withheld_scope', 'truncated', 'retweet_count', 'favorite_count', 'lang', 'to_user_name', 'in_reply_to_status_id', 'quoted_status_id', 'source', 'location', 'geo_lat', 'geo_lng', 'from_user_id', 'from_user_realname', 'from_user_verified', 'from_user_description', 'from_user_url', 'from_user_profile_image_url', 'from_user_utcoffset', 'from_user_timezone', 'from_user_lang', 'from_user_tweetcount', 'from_user_followercount', 'from_user_friendcount', 'from_user_favourites_count', 'from_user_listed', 'from_user_withheld_scope', 'from_user_created_at');
        foreach ($fields as $f) {
            $csv->addfield(isset($data[$f]) ? $data[$f] : '');
        }
        if (array_search("urls", $exportSettings) !== false || array_search("media", $exportSettings) !== false) {
            $urls = $expanded = $followed = $domain = $error = $media = $media_ids = $media_urls = $media_type = $photo_width = $photo_height = $photo_resize = $indice_start = $indice_end = array();
            // lookup urls
            if (array_search("urls", $exportSettings) !== false) {
                $sql2 = "SELECT * FROM " . $esc['mysql']['dataset'] . "_urls WHERE tweet_id = " . $id;
                #print $sql2."<br>";
                $rec2 = mysql_query($sql2);
                if (mysql_num_rows($rec2) > 0) {
                    while ($res2 = mysql_fetch_assoc($rec2)) {
                        $urls[] = $res2['url'];
function variabilityOfAssociationProfiles($filename, $series, $keywordToTrack, $ap)
{
    global $outputformat;
    if (empty($series) || empty($keywordToTrack)) {
        die('not enough data');
    }
    $filename = get_filename_for_export("variability", "_variabilityOfAssociationProfiles");
    $csv = new CSV($filename, $outputformat);
    // group per slice
    // per keyword
    // 	get associated words (depth 1) per slice
    // 	get frequency, degree, ap variation (calculated on cooc frequency), words in, words out, ap keywords
    $degree = array();
    foreach ($series as $time => $cw) {
        $cw = $cw->getCowords();
        foreach ($cw as $word => $cowords) {
            foreach ($cowords as $coword => $frequency) {
                // save how many time slices the word appears
                $words[$word][$time] = 1;
                $words[$coword][$time] = 1;
                // keep track of degree per word per time slice
                if (array_key_exists($word, $degree) === false) {
                    $degree[$word] = array();
                }
                if (array_key_exists($coword, $degree) === false) {
                    $degree[$coword] = array();
                }
                if (array_key_exists($time, $degree[$word]) === false) {
                    $degree[$word][$time] = 0;
                }
                if (array_key_exists($time, $degree[$coword]) === false) {
                    $degree[$coword][$time] = 0;
                }
                $degree[$word][$time]++;
                $degree[$coword][$time]++;
            }
        }
    }
    // count nr of time slices the words appears in
    foreach ($words as $word => $times) {
        $documentsPerWords[$word] = count($times);
    }
    // calculate similarity and changes
    foreach ($ap as $word => $times) {
        $times_keys = array_keys($times);
        for ($i = 1; $i < count($times_keys); $i++) {
            $im1 = $i - 1;
            $v1 = $times[$times_keys[$im1]];
            $v2 = $times[$times_keys[$i]];
            $cos_sim[$word][$times_keys[$i]] = cosineSimilarity($v1, $v2);
            $change_out[$word][$times_keys[$i]] = change($v1, $v2);
            $change_in[$word][$times_keys[$i]] = change($v2, $v1);
            $stable[$word][$times_keys[$i]] = array_intersect(array_keys($v1), array_keys($v2));
        }
    }
    // @todo, frequency
    $csv->writeheader(array("key", "time", "degree", "similarity", "associational profile", "change in", "change out", "stable"));
    foreach ($ap as $word => $times) {
        foreach ($times as $time => $profile) {
            if (isset($change_in[$word][$time])) {
                $inc = "";
                foreach ($change_in[$word][$time] as $w => $c) {
                    $inc .= "{$w} ({$c}), ";
                }
                $inc = substr($inc, 0, -2);
            } else {
                $inc = "";
            }
            if (isset($change_out[$word][$time])) {
                $outc = "";
                foreach ($change_out[$word][$time] as $w => $c) {
                    $outc .= "{$w} ({$c}), ";
                }
                $outc = substr($outc, 0, -2);
            } else {
                $outc = "";
            }
            if (isset($stable[$word][$time])) {
                $stablec = array();
                foreach ($stable[$word][$time] as $w) {
                    $stablec[] = $w;
                }
                $stablec = implode(", ", $stablec);
            } else {
                $stablec = "";
            }
            $prof = "";
            foreach ($profile as $w => $c) {
                $prof .= "{$w} ({$c}), ";
            }
            $prof = substr($prof, 0, -2);
            if (isset($degree[$word][$time])) {
                $deg = $degree[$word][$time];
            } else {
                $deg = "";
            }
            if (isset($cos_sim[$word][$time])) {
                $cs = $cos_sim[$word][$time];
            } else {
                $cs = "";
            }
            $csv->newrow();
            $csv->addfield($word);
            $csv->addfield($time);
            $csv->addfield($deg);
            $csv->addfield($cs);
            $csv->addfield($prof);
            $csv->addfield($inc);
            $csv->addfield($outc);
            $csv->addfield($stablec);
            $csv->writerow();
        }
    }
    $csv->close();
    echo '<fieldset class="if_parameters">';
    echo '<legend>Your co-hashtag variability File</legend>';
    echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
    echo '</fieldset>';
}
Example #13
0
    $res['source'] = preg_replace("/<.+>/U", "", $res['source']);
    $res['source'] = preg_replace("/,/", "_", $res['source']);
    $res['source'] = preg_replace("/[ \\s\t]+/", " ", $res['source']);
    $res['source'] = trim($res['source']);
    if (!isset($array[$res['datepart']][$res['source']])) {
        $array[$res['datepart']][$res['source']] = 0;
    }
    $array[$res['datepart']][$res['source']] += $res['count'];
}
mysql_free_result($sqlresults);
$csv->writeheader(array("date", "source", "count"));
foreach ($array as $date => $sources) {
    arsort($sources);
    foreach ($sources as $source => $freq) {
        $csv->newrow();
        $csv->addfield($date);
        $csv->addfield($source);
        $csv->addfield($freq);
        $csv->writerow();
    }
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Source stats</legend>';
echo '<p><a href="' . str_replace("#", urlencode("#"), str_replace("\"", "%22", $filename)) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>

    </body>
</html>
Example #14
0
$header = "start,end";
$csv->writeheader(explode(',', $header));
// make query
$sql = "SELECT * FROM tcat_error_gap WHERE type = '" . mysql_real_escape_string($bin_type) . "' and\n                                                   start >= '" . mysql_real_escape_string($_GET['startdate']) . "' and end <= '" . mysql_real_escape_string($_GET['enddate']) . "'";
// loop over results and write to file
$sqlresults = mysql_query($sql);
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        // the query bin must have been active during the gap period, if we want to report it as a possible gap
        $sql2 = "SELECT count(*) as cnt FROM tcat_query_bins_phrases WHERE querybin_id = {$bin_id} and\n                                                            starttime <= '" . $data["end"] . "' and (endtime >= '" . $data["start"] . "' or endtime is null or endtime = '0000-00-00 00:00:00')";
        $sqlresults2 = mysql_query($sql2);
        if ($sqlresults2) {
            if ($data2 = mysql_fetch_assoc($sqlresults2)) {
                if ($data2['cnt'] > 0) {
                    $csv->newrow();
                    $csv->addfield($data["start"]);
                    $csv->addfield($data["end"]);
                    $csv->writerow();
                }
            }
        }
    }
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your File</legend>';
echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>

    </body>
    $sentiment[$res['tid']]['neg'] = $res['negative'];
    $sentiment[$res['tid']]['desc'] = $res['explanation'];
}
mysql_free_result($rec);
$sql = "SELECT * FROM " . $esc['mysql']['dataset'] . "_tweets t ";
$sql .= sqlSubset();
$sqlresults = mysql_unbuffered_query($sql);
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        $csv->newrow();
        if (preg_match("/_urls/", $sql)) {
            $id = $data['tweet_id'];
        } else {
            $id = $data['id'];
        }
        $csv->addfield($id);
        if (isset($sentiment[$id])) {
            $csv->addfield($sentiment[$id]['desc']);
            $csv->addfield($sentiment[$id]['pos']);
            $csv->addfield($sentiment[$id]['neg']);
        } else {
            for ($n = 0; $n < 3; $n++) {
                $csv->addfield('');
            }
        }
        $csv->writerow();
    }
    mysql_free_result($sqlresults);
}
$csv->close();
echo '<fieldset class="if_parameters">';
Example #16
0
    $array[$res['datepart']]['tweetcount'][$res['from_user_id']] = $res['from_user_tweetcount'];
}
mysql_free_result($sqlresults);
if (!empty($array)) {
    foreach ($array as $date => $ar) {
        $stats[$date]['followercount'] = stats_summary($ar['followercount']);
        $stats[$date]['friendcount'] = stats_summary($ar['friendcount']);
        $stats[$date]['tweetcount'] = stats_summary($ar['tweetcount']);
    }
}
// @todo: aantal retweets
$csv->writeheader(array("date", "what", "min", "max", "avg", "Q1", "median", "Q3", "25%TrimmedMean"));
foreach ($stats as $date => $datestats) {
    foreach ($datestats as $what => $stat) {
        $csv->newrow();
        $csv->addfield($date);
        $csv->addfield($what);
        $csv->addfield($stat['min']);
        $csv->addfield($stat['max']);
        $csv->addfield($stat['avg']);
        $csv->addfield($stat['Q1']);
        $csv->addfield($stat['median']);
        $csv->addfield($stat['Q3']);
        $csv->addfield($stat['truncatedMean']);
        $csv->writerow();
    }
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>User stats</legend>';
echo '<p><a href="' . str_replace("#", urlencode("#"), str_replace("\"", "%22", $filename)) . '">' . $filename . '</a></p>';
Example #17
0
        <h1>TCAT :: Export mentions</h1>

        <?php 
validate_all_variables();
$filename = get_filename_for_export('mentionExport');
$csv = new CSV($filename, $outputformat);
$csv->writeheader(array('tweet_id', 'user_from_id', 'user_from_name', 'user_to_id', 'user_to_name', 'mention_type'));
$sql = "SELECT t.id as id, t.text as text, m.from_user_id as user_from_id, m.from_user_name as user_from_name, m.to_user_id as user_to_id, m.to_user as user_to_name FROM " . $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_mentions m ";
$sql .= sqlSubset();
$sql .= " AND m.tweet_id = t.id ORDER BY id";
$sqlresults = mysql_unbuffered_query($sql);
$out = "";
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        $csv->newrow();
        $csv->addfield($data['id'], 'integer');
        $csv->addfield($data['user_from_id'], 'integer');
        $csv->addfield($data['user_from_name'], 'string');
        $csv->addfield($data['user_to_id'], 'integer');
        $csv->addfield($data['user_to_name'], 'string');
        $csv->addfield(detect_mention_type($data['text'], $data['user_to_name']), 'string');
        $csv->writerow();
    }
    mysql_free_result($sqlresults);
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your File</legend>';
echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>
Example #18
0
        $hashtagcount = $numTweetsWithHashtag[$date];
    }
    if (isset($numTweetsWithMentions[$date])) {
        $mentioncount = $numTweetsWithMentions[$date];
    }
    if (isset($numTweetsWithMedia[$date])) {
        $mediacount = $numTweetsWithMedia[$date];
    }
    if (isset($numretweets[$date])) {
        $retweetcount = $numretweets[$date];
    }
    if (isset($numReplies[$date])) {
        $replycount = $numReplies[$date];
    }
    $csv->newrow();
    $csv->addfield($date);
    $csv->addfield($tweetcount);
    $csv->addfield($linkcount);
    $csv->addfield($hashtagcount);
    $csv->addfield($mentioncount);
    $csv->addfield($mediacount);
    $csv->addfield($retweetcount);
    $csv->addfield($replycount);
    $csv->writerow();
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Tweet stats</legend>';
echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>
Example #19
0
        <?php 
validate_all_variables();
$collation = current_collation();
$filename = get_filename_for_export("urlHashtag");
$csv = new CSV($filename, $outputformat);
$sql = "SELECT COUNT(LOWER(h.text COLLATE {$collation})) AS frequency, LOWER(h.text COLLATE {$collation}) AS hashtag, u.url_followed AS url, u.domain AS domain, u.error_code AS status_code FROM ";
$sql .= $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_hashtags h, " . $esc['mysql']['dataset'] . "_urls u ";
$where = "t.id = h.tweet_id AND h.tweet_id = u.tweet_id AND u.url_followed !='' AND ";
$sql .= sqlSubset($where);
$sql .= " GROUP BY u.url_followed, LOWER(h.text COLLATE {$collation}) ORDER BY frequency DESC";
//print $sql." - <br>";
$sqlresults = mysql_unbuffered_query($sql);
$csv->writeheader(array("frequency", "hashtag", "url", "domain", "status_code"));
while ($res = mysql_fetch_assoc($sqlresults)) {
    $csv->newrow();
    $csv->addfield($res['frequency']);
    $csv->addfield($res['hashtag']);
    $csv->addfield($res['url']);
    $csv->addfield($res['domain']);
    $csv->addfield($res['status_code']);
    $csv->writerow();
    $urlHashtags[$res['url']][$res['hashtag']] = $res['frequency'];
    $urlDomain[$res['url']] = $res['domain'];
    $urlStatusCode[$res['url']] = $res['status_code'];
}
mysql_free_result($sqlresults);
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your spreadsheet (CSV) file</legend>';
echo '<p><a href="' . str_replace("#", urlencode("#"), str_replace("\"", "%22", $filename)) . '">' . $filename . '</a></p>';
echo '</fieldset>';
Example #20
0
        <h1>TCAT :: Export URLs</h1>

        <?php 
validate_all_variables();
$filename = get_filename_for_export('urlsExport');
$csv = new CSV($filename, $outputformat);
$csv->writeheader(array('tweet_id', 'url', 'url_expanded', 'url_followed'));
$sql = "SELECT t.id as id, u.url as url, u.url_expanded as url_expanded, u.url_followed as url_followed FROM " . $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_urls u ";
$sql .= sqlSubset();
$sql .= " AND u.tweet_id = t.id ORDER BY id";
$sqlresults = mysql_unbuffered_query($sql);
$out = "";
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        $csv->newrow();
        $csv->addfield($data['id'], 'integer');
        $csv->addfield($data['url'], 'string');
        if (isset($data['url_followed']) && strlen($data['url_followed']) > 1) {
            $csv->addfield($data['url'], 'string');
        } else {
            $csv->addfield('', 'string');
        }
        if (isset($data['url_expanded']) && strlen($data['url_expanded']) > 1) {
            $csv->addfield($data['url_expanded'], 'string');
        } else {
            $csv->addfield('', 'string');
        }
        $csv->writerow();
    }
    mysql_free_result($sqlresults);
}
Example #21
0
        <h1>TCAT :: Export hashtags</h1>

        <?php 
validate_all_variables();
$filename = get_filename_for_export('hashtagExport');
$csv = new CSV($filename, $outputformat);
$csv->writeheader(array('tweet_id', 'hashtag'));
$sql = "SELECT t.id as id, h.text as hashtag FROM " . $esc['mysql']['dataset'] . "_tweets t, " . $esc['mysql']['dataset'] . "_hashtags h ";
$sql .= sqlSubset();
$sql .= " AND h.tweet_id = t.id ORDER BY id";
$sqlresults = mysql_unbuffered_query($sql);
$out = "";
if ($sqlresults) {
    while ($data = mysql_fetch_assoc($sqlresults)) {
        $csv->newrow();
        $csv->addfield($data['id'], 'integer');
        $csv->addfield($data['hashtag'], 'string');
        $csv->writerow();
    }
    mysql_free_result($sqlresults);
}
$csv->close();
echo '<fieldset class="if_parameters">';
echo '<legend>Your File</legend>';
echo '<p><a href="' . filename_to_url($filename) . '">' . $filename . '</a></p>';
echo '</fieldset>';
?>

    </body>
</html>