1

I am writing to a txt the results of a group by in pandas. I would like to make a sentence that would refer to the range the info is about. Example:

data for date 12/09/2018 to 16/09/2018

        dates   user  quantity
0        Sep  user_05    23
1        Sep  user_06    22
2        Sep  user_06    23     
3        Sep  user_07    22    
4        Sep  user_11    22
5        Sep  user_12    20
6        Sep  user_20    34
7        Sep  user_20    34

If I do this:

x['dates'].max()

gives:

  Timestamp('2018-09-16 00:00:00')

and

 x['dates'].min()

gives:

 Timestamp('2018-09-12 00:00:00')

But how can I make it appear in a sentence before the results?

user10375183
  • 51
  • 4

1 Answers1

2

Use:

#sample data
rng = pd.date_range('2017-04-03', periods=10)
x = pd.DataFrame({'dates': rng, 'a': range(10)})  
print (x)
       dates  a
0 2017-04-03  0
1 2017-04-04  1
2 2017-04-05  2
3 2017-04-06  3
4 2017-04-07  4
5 2017-04-08  5
6 2017-04-09  6
7 2017-04-10  7
8 2017-04-11  8
9 2017-04-12  9

#convert timestamps to strings
maxval = x['dates'].max().strftime('%d/%m/%Y')
minval = x['dates'].min().strftime('%d/%m/%Y')

#create sentence, 3.6+ solution
a = f'data for date {minval} to {maxval}'
#solution bellow 3.6
a = 'data for date {} to {}'.format(minval, maxval)
print (a)
data for date 03/04/2017 to 12/04/2017

#write sentence to file
df1 = pd.Series(a)
df1.to_csv('output.csv', index=False, header=None)
#append DataFrame to file
x.to_csv('output.csv', mode='a', index=False)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Great! Can you tell me why you turned it to series?I get it that you did it in order to be appended but is there a rule?Also instead of csv doing it to txt as an extension of your answer? – user10375183 Sep 17 '18 at 13:28
  • @user10375183 - I want use function [`Series.to_csv`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.to_csv.html), so converted to Series. And for `txt` only change `output.csv` to `output.txt` – jezrael Sep 17 '18 at 13:30
  • I have only observed writing to txt files using `with open`. Your way seems more efficient? Whats up with those two methods? – user10375183 Sep 17 '18 at 13:34
  • @user10375183 - Question is need write to file sentence `data for date 12/09/2018 to 16/09/2018` and then append `DataFrame` ? Or write only sentence? – jezrael Sep 17 '18 at 13:35
  • i want to have a txt that will have the table results and above, the sentence that would explain the range of dates. Note: on txt it appears as intended but it looks unformatted. – user10375183 Sep 17 '18 at 13:42
  • So if need write only one line, then better is use `with open`, buut if want write line + DataFrame then use double `to_csv` like in my answer. – jezrael Sep 17 '18 at 13:43
  • I understand. Do you know why in the txt the last column goes in another line?all the columns are aligned but the last is like it doesn't fit so it places it below.Even though there is space. Maybe a method that says print everything in the same line? – user10375183 Sep 17 '18 at 13:49
  • @user10375183 - It is only display problem, check [this](https://stackoverflow.com/q/11707586) – jezrael Sep 17 '18 at 13:50
  • OK.About before i decided to do the csv option but some rows that belong to one column fall under the beside column and its a mess.Do you know why? – user10375183 Sep 17 '18 at 14:14
  • @user10375183 - I have no idea. – jezrael Sep 17 '18 at 14:15
  • It happens only in rows that contain long text. Some of its content gets seperated to two different columns.Some to one some to another.Can you suggest anything? – user10375183 Sep 17 '18 at 14:20
  • 1
    I fixed it. When open it in calc I unchecked the 'tab' separator and it worked. – user10375183 Sep 17 '18 at 14:28