Python Forum
Inserting data from python list into a pandas dataframe
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Inserting data from python list into a pandas dataframe
#1
I have the following files in AAMC_K.txt, AAU.txt, ACU.txt, ACY.txt, and AE.txt in a folder called AMEX. I am trying to merge these text files into one dataframe. I have tried to do so with pd.merge() but I get an error that the merge function needs a right and left parameter and my data is in a python list. How can I merge the data in the data_list into one pandas dataframe.

import pandas as pd
import os

textfile_names = os.listdir("AMEX")
textfile_names.sort()
data_list = []

for i in range(len(textfile_names)):
   data = pd.read_csv("AMEX/"+textfile_names[i], index_col=None, header=0)
   data_list.append(data)

frame = pd.merge(data_list, on='<DTYYYYMMDD>', how='outer')
Data:
Output:
"AE.txt" <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> AE,D,19970102,000000,12.6250,12.6250,11.7500,11.7500,144,0 AE,D,19970103,000000,11.8750,12.1250,11.8750,12.1250,25,0 AAU.txt <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> AAU,D,20020513,000000,0.4220,0.4220,0.4220,0.4220,0,0 AAU,D,20020514,000000,0.4177,0.4177,0.4177,0.4177,0,0 ACU.txt <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> ACU,D,19970102,000000,5.2500,5.3750,5.1250,5.1250,52,0 ACU,D,19970103,000000,5.1250,5.2500,5.0625,5.2500,12,0 ACY.txt <TICKER>,<PER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> ACY,D,19980116,000000,9.7500,9.7500,8.8125,8.8125,289,0 ACY,D,19980120,000000,8.7500,8.7500,8.1250,8.1250,151,0
I want the output to be filtered with the DTYYYYMMDD and put into one dataframe frame.
Output:
<TICKER>,<PER>,<DTYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT>,<TICKER>,<PER>,<DTYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>,<OPENINT> ACU,D,19970102,000000,5.2500,5.3750,5.1250,5.1250,52,0,AE,D,19970102,000000,12.6250,12.6250,11.7500,11.7500,144,0 ACU,D,19970103,000000,5.1250,5.2500,5.0625,5.2500,12,0,AE,D,19970103,000000,11.8750,12.1250,11.8750,12.1250,25,0
** Moderator Note ** Use python, not icode for more than 1 line of code
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  How to search for specific string in Pandas dataframe Coding_Jam 1 193 Nov-02-2020, 09:35 AM
Last Post: PsyPy
  Inserting a variable as column name in sqlite3 python JellyCreeper6 3 258 Nov-01-2020, 01:44 PM
Last Post: ibreeden
  PANDAS: DataFrame | White Spaces & Special Character Removal traibr 1 490 Sep-10-2020, 07:02 PM
Last Post: eddywinch82
  No Output In Pandas DataFrame Query eddywinch82 1 335 Aug-17-2020, 09:25 PM
Last Post: eddywinch82
  strange error from pandas dataframe djf123 1 777 Jul-27-2020, 05:25 AM
Last Post: scidam
  [pandas] How to reshape the list Mekala 6 555 Jul-26-2020, 12:49 AM
Last Post: Mekala
  Filter data based on a value from another dataframe column and create a file using lo pawanmtm 1 444 Jul-15-2020, 06:20 PM
Last Post: pawanmtm
  Pandas DataFrame not updating HelpMePlease 3 527 Jul-11-2020, 07:19 PM
Last Post: jefsummers
  Pandas DataFrame visual Truman 8 703 Jul-10-2020, 06:11 AM
Last Post: hussainmujtaba
  pandas read_csv can't handle missing data mrdominikku 0 415 Jul-09-2020, 12:26 PM
Last Post: mrdominikku

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020