1

I have been trying to output a file from a pandas dataframe that looks like this and I have been trying to write it to a file using a different format

     codi      Acumulado Ahorro    Prestamo Fondo 1  Prestamo Empresa 1
0    0151           1425.76             28320                0.00
1    2361           1158.49              4100             1200.00
2    2663            737.10              2903              429.00
3    2792            897.75              1950              627.00
4    0737           1266.54              7859                0.00
5    5073            779.00               557              754.00
6    2819           1274.70               958                0.00
7    1558            738.51             10242              676.00
8    4105            695.49              2382                0.00
9    4146           1170.08              8623                0.00
10   0528           1337.17              1042                0.00

I tried something like: df.to_csv(output_file, sep = '|') but the output its just like a csv but instead of "," I get "|"

I'm trying to achieve this output:

0E0151
A1425.76|2019|Acumulado Ahorro
A0.00|2019|Prestamo Empresa 1
A28320.00|2019|Prestamo Fondo 1

Where 0E has to be concatenated to the number found in column codi, then concatenate the letter "A" to the number found in the column Acumulado ahorro then the char "|" and the name of the column and so on

How can I create this format so it can be written to an actual file?

Improve this question
3
  • 1
    Why don't you just iterate over the dataframe and save the data in the format you need? Commented Nov 1, 2019 at 21:11
  • Yeah the best way to go is to get your dataframe in the format you need, then to output it Commented Nov 1, 2019 at 22:07
  • because i worked the pandas dataframe to make it look like that it comes from 3 different places and took me my entire life to format it like this as you see im learning. Commented Nov 1, 2019 at 22:33

2 Answers 2

Reset to default
1

For such an unusual file format you need a custom writing routine, eg.:

with open("output.txt", "w") as file:
    for i in range(len(df)):
        file.write("0E" + str(df["codi"][i]) + "\n")
        for c in list(df.columns.values[1:]):
            file.write("A" + str(df[c][i]) + "|2019|" + c + "\n")
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

cant implement like this because it doesnt come from a single file i actually built this dataframe from different sources inside the code, i think i had to clarify that for starters im sorry :(
0

I am very certain that you have to create your own custom formatter. Hopefully this answer provides some guidance.

def format_row(row):
    first_line = f"0E{int(row['codi'])}" + '\n'
    second_line = [f"A{row[el]}|2019|{el}" for el in ['Acumulado Ahorro', 'Prestamo Fondo 1', 'Prestamo Empresa 1']]
    second_line = '\n'.join(second_line)
    return f"{first_line}{second_line}"

format_text = df.apply(format_row, axis=1)

for text in format_text:
    print(text)

Output:

0E151
A1425.76|2019|Acumulado Ahorro
A28320.0|2019|Prestamo Fondo 1
A0.0|2019|Prestamo Empresa 1
0E2361
A1158.49|2019|Acumulado Ahorro
A4100.0|2019|Prestamo Fondo 1
A1200.0|2019|Prestamo Empresa 1
...

You can easily save format_text to any text file you want

Improve this answer

1 Comment

This was awesome to implement and easy to understand, i didnt realize i needed further work on the dataframe aswell, like always having 2 decimals and let the codi column be a string because i wanted to preserve the "0"'s at the left, but your answer was super easy to follow and i was able to do those simple fixes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.