Concatenating columns to DataFrame using Python Pandas

Question

Dataframe contains

TRANSACTION FILE
 AS REPORT 2018-12-02
 Jeff Thomoson   000 11-28-2018 Payments  2,400    Wired transfer
 Jeff Thomoson   000 11-29-2018 Interest    100    account
 Paul Simson     000 11-12-2018 Payments  1,000  Wired transfer
 Paul Simson     000 11-18-2018 Payments    100    net banking
 John Sans       000 11-28-2018 Payments    300    cheque
                                Total   3,900

Code:

import pandas as pd
bad_words = ['TRANSACTION','REPORT','Total']
with open('e:\\data\\test.txt') as oldfile, open('e:\\data\\processed.txt', 'w') as newfile:
for line in oldfile:
    if not any(bad_word in line for bad_word in bad_words):
        newfile.write(line)

df_a = pd.read_csv('e:\\data\\processed.txt',header=None)
names = ['USER','NAME', 'TR Mode', 'Date', 'Narration', 'Amt', 'Mode']
df=pd.read_fwf('e:\\data\\processed.txt', header=None, names=names,dtype=str)

I was trying to concatenate the values from the variable (LedgerCode). but its adding all the columns as '02'

LedgerCode='02'

tried f-string expression, but no luck.

f"{LedgerCode}"+ df[['USER','NAME', 'TR Mode', 'Date', 'Narration', 'Amt', 'Mode']]

Expected results would like:

02 Jeff Thomoson   000 11-28-2018 Payments  2,400    Wired transfer
02 Jeff Thomoson   000 11-29-2018 Interest    100    account
02 Paul Simson     000 11-12-2018 Payments  1,000    Wired transfer
02 Paul Simson     000 11-18-2018 Payments    100    net banking
02 John Sans       000 11-28-2018 Payments    300    cheque

score 1 · Answer 1 · answered Dec 20 '18 at 08:41

1

Just insert a new ledger column in the dataframe (inplace):

df.insert(loc=0, column='ledger_code', value='02')

answered Dec 20 '18 at 08:41

Alexander

105,104
32
201
196

score 0 · Accepted Answer · answered Dec 20 '18 at 08:49

0

I am not sure why you stick to f-string expression.

df['LedgerCode'] = LedgerCode

Hopefully, the above code could solve your problem.

answered Dec 20 '18 at 08:49

two four

26
5

That was what I first tried, but you then need to rearrange the columns to match the desired output. Hence inserting it ends up being simpler. – Alexander Dec 20 '18 at 17:40
@Alexander Yeah, using _insert_ does make it simpler, but just for exploring, the following code also works for this case. `cols = df.columns df['LedgerCode'] = LedgerCode df = df[['LedgerCode'] + list(cols)] ` – two four Dec 21 '18 at 01:33

Concatenating columns to DataFrame using Python Pandas

2 Answers2