3

I am very new to Python/Pandas and am using the Spyder IDF, via the Anaconda distribution with Python 3.6 (maybe 3.7?).I am importing an Excel file via the code below and would like to know how to get an output that looks as follows:

VOLTS/PHASE

Currently, the output in Excel is formatted as follows:

VOLTS.PHASE.0

(Also, the second column I am calling - PHASE, is a single digit. But, it is exporting it as two digits. A common example is that it calls a 1 but returns 01. Does this have something to do with Python assuming floats? How can I control for that?)

I built my code using the following resources:

https://pythonspot.com/en/write-excel-with-pandas/

Combine two columns of text in dataframe in pandas/python

BELOW IS THE CODE I AM USING.

import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile


df = pd.read_excel('C:VAV_Schedule1.xlsx', sheetname = 'VAV 1-2')

df["VOLTS/PHASE"] = df["VOLTS"].map(str) + df["PHASE"].map(str)


writer = ExcelWriter('Test2.xlsx')
df.to_excel(writer, 'VAV 1-2', index=True)
writer.save()
Community
  • 1
  • 1
Mathew Coalson
  • 41
  • 5

1 Answers1

1

All you need to do is add + '/' + between the two strings that you are joining.

df["VOLTS/PHASE"] = df["VOLTS"].map(str) + '/' + df["PHASE"].map(str)

For your second question, could you run df["PHASE"].dtype and share what datatype that series is? If it is a string, then whatever is in excel will be copied exactly. One possible solution would be to explicitly convert it to an integer first:

df["VOLTS/PHASE"] = df["VOLTS"].map(str) + '/' + df["PHASE"].astype(int).map(str)

Hope that helps.

datanerdjake
  • 43
  • 1
  • 9
  • Thanks for the answer @datanerdjake, in answer to your question, when I ran df["PHASE"] it returns as a float (float64, to be specific). I also tried to run that last line of code to 'explicitly convert it to an integer first'. Unfortunately, that returned: ValueError: Cannot convert NA to integer. Using the slash works perfectly, I am now returning the following (shown numerically to better illustrate the float) 277.0/1.0. I'd really like to make it simple integers. – Mathew Coalson Oct 04 '17 at 17:49
  • 1
    You can use fillna (http://pandas.pydata.org/pandas-docs/version/0.17.0/generated/pandas.DataFrame.fillna.html) to replace the NA values first and then you can explicitly set the values as an int – datanerdjake Oct 04 '17 at 22:05