topic Re: Average of posts per day in Discussions

Average of posts per day

BorislavP00 — Sat, 10 Jun 2023 23:23:40 GMT

Hello,

I am working on a paper for my university and I want to find on average how often does Trump post every day. However, I do not need the mean of all the data I have, but the mean of each day since 2016 (data is from 2016-2020). In other words, I want to make a new column in which each row shows the average posts on that day. Example below the image.

Example:

2016.01.04 - 5 posts

2016.01.05 - 9 posts

2016.01.06 - 4 posts
Is this doable within JMP?

Another problem that I am facing is that I am not able to copy a column from a different data file and place it in another.

It just shows points instead of numbers.

I know that I am asking about a lot, but I will be thankful if someone is able to help me out.

Re: Average of posts per day

Georg — Sat, 12 Dec 2020 20:09:11 GMT

For the mean by day you could use the "col mean" function with by variable.

First create a new column with day from your date (context menu, new Formula column --> date --> day).

And then create the Formula column with "col mean(:date, :day)".

To join another column from a different table you Need to define a unique key, so that there is a clear rule. This can also be the rownumber.

Re: Average of posts per day

txnelson — Sat, 12 Dec 2020 20:36:04 GMT

It appears that your Date column has been input as a character string, instead of a JMP Date/Time value. The preferred thing to do, would be to convert the column into a Numeric, Date/Time value. To change to a Numeric, Data/Time do the following:
1. Double Click on the column header to bring up the Column Info window.
2. Change the Data Type from Character to Numeric
3. Change the Modeling Type from Nominal to Continuous
4. Click on the down arrow for the Format, and select Time=>yyyy-mm-ddThh:mm:ss
5. Click Apply. The column should now will be converted to the number of seconds since midnight, Jan, 1, 1904, and will be displayed using the yyyy-mm-ddThh:mm:ss
Now to get the count of the number of tweets per day, first a new column needs to be created which is just the date part of the Date column, which contains both the Date and Time of the day. To create that column do the following:
1. Click on the Cols pull down menu and select New Columns which will bring up the Column Info window.
2. Type in the Column Name you want to give it
3. Change the format to mm/dd/yyyy
4. Click on Column Properties and select Formula
5. Type in the following formula Informat( Short Date( :Column 1 ), "mm/dd/yyyy" )
6. Click on OK
7. Click on OK again
You now have a column that just contains dates
To Calculate the counts perday, go to the Tables pull down menu and select Summary. In the Summary dialog box do the following:
1. Select the column that was just created, and then Click on the Group button to move the column into the Group selection area.
2. With the new column still selected, click on Statistics button and select N
3. Click on OK
A new table will be created that has the Total counts for each day

Concerning your second issue, all you need to do copy a column from one table to another, is to

Click on the column header,
Go to the Edit pull down menu and select Copy With Column Names.
Go to the data table you want to copy the column to and double click on the header area after the last column in the data table
Go to the Edit pull down menu for the data table, and select Paste With Column Names

Re: Average of posts per day

txnelson — Sat, 12 Dec 2020 20:50:50 GMT

@Georg, I don't believe your suggestion will work. The Day function returns the Day of the Month (1-31), not the date part of the date/time.

Re: Average of posts per day

Georg — Sat, 12 Dec 2020 21:46:43 GMT

Thanks @txnelson, you're Right, "day" won't work,

but there is also a shortcut "date" on Right mouseclick on that numeric date/time column as you pointed out, that would bring up a new column serving as by column for col stats.

Re: Average of posts per day

BorislavP00 — Mon, 14 Dec 2020 13:01:09 GMT

One small problem arose. The dates between the 2 tables do not correspond. Meaning that when I copy-paste the column into the new table it does not match the data that it should be for. I tried looking for how to add 2 rows after every 5 rows and I found this from the Help menu:

Names Default To Here( 1 );
dt = Open( "$Desktop/2016-2020.jmp" );
dt << Add Rows( 2, after( 5 ) );

However, the formula only adds 2 rows after the 5th row, and I need it to add it after each 5th row.
So it would add 2 rows after the 5th, 10th, then 15th, and so on.

Re: Average of posts per day

txnelson — Sat, 12 Dec 2020 23:45:44 GMT

Here is a simple modification to the code you found, that starts at the last 5th row in the data table, and works backwards, adding 2 rows at each 5th row

Names Default To Here( 1 );
dt = Current Data Table();
whereToAdd = Floor( N Rows( dt ) / 5 ) * 5;

For( i = whereToAdd, i >= 1, i = i - 5,
	dt << Add Rows( 2, after( i ) )
);

Re: Average of posts per day

Georg — Sun, 13 Dec 2020 09:44:27 GMT

Instead of caring yourself About Right order of the rows and corresponding Dates,

I would strongly recommend to let JMP care About: I would define proper ID's like date in both tables,

and then join the data (join tables, or using symbolic link etc.).