CODEWITHSUNDEEP
pythonpandas
John Aiton
John Aiton

Reputation: 85

How to convert text and numbers to dataframe in Python?

I have a text document with spending information. I want to use pandas and Python 3 to convert the text to a dataframe with two columns, without repeating row names by combining same names into one row with the respective amounts added to produce a single total.

Original "spending.txt:"

shaving 150  
shaving 200  
coffee 100  
food 350  
transport 60  
transport 40

desired output dataframe: 

CATEGORY       TOTAL

shaving        350  
coffee         100  
food           350  
transport      100

Upvotes: 0

Views: 127

Answers (3)

tawab_shakeel
tawab_shakeel

Reputation: 3739

  1. Read file using read_csv
  2. The apply group by
df = pd.read_csv('test.txt', sep=" ", header=None)
df.rename(columns={0:'category',1:'Total'},inplace=True)

final_df = df.groupby(['category'],as_index=False)['Total'].sum()
print(final_df)

     category  Total
0     coffee    100
1       food    350
2    shaving    350
3  transport    100

Upvotes: 0

iamklaus
iamklaus

Reputation: 3770

Reading in data

temp = StringIO("""  
shaving 150
shaving 200
coffee 100
food 350
transport 60
transport 40
""")

df = pd.read_csv(temp, sep='\s+', engine='python', header=None)

df.groupby(0).sum().reset_index().rename({0:'category',1:'total'}, axis=1)

Output

   category  total
0     coffee    100
1       food    350
2    shaving    350
3  transport    100

Upvotes: 0

zipa
zipa

Reputation: 27869

This should do it:

df = pd.read_csv('spending.txt', header=None, sep='\s+')
df.columns = ['category', 'total']

df.groupby('category', as_index=False).sum()

    category  total
0     coffee    100
1       food    350
2    shaving    350
3  transport    100

Upvotes: 1

Related Questions